Appsheet Database as a Data Source - How to auto update the Data

AppSheet AppSheet Q&A
, , , ,
1

I have an Appsheet Database which supports my App.
I have numerous columns which are calculated and totaled in another column.
This works fine when the data is initially entered via a form.
I setup a Virtual Column to auto-update the “Total” column if any corresponding columns in the equation are changed, this also works fine in the “App”.
The data in the “App” is up to date, when looking at the Database after information gets updated in the “App” the columns which were updated are correct, the “Total” column in the Database remains totaled with the initial data.
I have added a script initiated by a bot to update the database (as I had done in Tables.120 and it worked fine) but it does not update.

Is there any solution to automatically updating a Database “Total” column after relevant columns have been updated and show that they are updated.

Thank you.

1 Like
2

Depends on how the relationship is set up. Actions can work to do the data change when a new record is added that is needed to be added to your totals and adjust that row. or on the row itselt it can be a reset on change and the initial value will be your formula of the total needed.

1 Like
3

I use this in the ‘Formula’ for the field initially. It doesn’t update anything (that is why I have a Virtual Column - it updates the App when something is changed in the Total)
SUM(
LIST(
IF([M_IETotal] <> “”, [M_IETotal], 0),
IF([PlannedLodgingCosts] <> “”, [PlannedLodgingCosts], 0),
IF([PlannedLodgingTax] <> “”, [PlannedLodgingTax], 0),
IF([AirFareCost] <> “”, [AirFareCost], 0),
IF([BaggageFees] <> “”, [BaggageFees], 0),
IF([AirportParking] <> “”, [AirportParking], 0),
IF([HometoAirportRoundtripPOVCost70] <> “”, [HometoAirportRoundtripPOVCost70], 0),
IF([DrivingtoDestinationCost70] <> “”, [DrivingtoDestinationCost70], 0),
IF([RentalCar] <> “”, [RentalCar], 0),
IF([FuelRentalCarOnly] <> “”, [FuelRentalCarOnly], 0),
IF([Conf_RegFees] <> “”, [Conf_RegFees], 0),
IF([OtherCostsTotalOnly] <> “”, [OtherCostsTotalOnly], 0)
)
)
I have a Bot setup with a script to update the Totals Column when any update it made to a column affecting the Total.

function UpdateAppTripCost(TravelRequests) {
// Get the table
var table = appsheet.tables[TravelRequests];

// Get all records from the specified table
var records = table.newQuery().run();

records.forEach(function(record) {
// Calculate the Trip Cost Estimate
var tripCostEstimate = SUM(
record.M_IETotal,
record.PlannedLodgingCosts,
record.PlannedLodgingTax,
record.AirFareCost,
record.BaggageFees,
record.AirportParking,
record.HometoAirportRoundtripPOVCost70,
record.DrivingtoDestinationCost70,
record.RentalCar,
record.FuelRentalCarOnly,
record.Conf_RegFees,
record.OtherCostsTotalOnly
);

// Update the Trip Cost Estimate column
record.TripCostEstimate = tripCostEstimate;

// Save the changes to the record
table.update(record);
});
}

I’ve tried Return Values as well.. That didn’t work with the Bot either.

4

If you are using this in appsheets in the initial value then yes that is wrong function layout. You can just get the SUM of all of your columns as below

SUM(LIST([PlannedLodgingCosts], [PlannedLodgingTax], [AirFareCost], [BaggageFees], [AirportParking], [AirportParking], [HometoAirportRoundtripPOVCost70], [DrivingtoDestinationCost70], [RentalCar], [FuelRentalCarOnly], [Conf_RegFees], [OtherCostsTotalOnly]))

Set the inital value of all of these columns to 0. Skips your need for the IF() statements

That will get your total value column. make the column a reset on edit. that way any time that the columns listed above change in value they the total will always update from the change.

1 Like