how to update data on one table with related data coming from another table

[cessnafrrg]

Hi, 

I am an SQL developer but new to AppSheet. I am trying to update a table with data coming from another table but couldn't find a solution for it. Here is the scenario:

1. I have a table called Customers that has a column called [Current Balance]

2. I have a Transactions table that records all payments and purchases for customers.

Any time a new record is added into the Transactions table for purchases or payments I would like to add or subtract from the [Current Balance] column in the customer table. The following is a SQL equivalent of what I am trying to achieve:

Update Customer_Table

   SET   Current_Balance = Current_Balance + (Select TOP 1 Transaction_Amount From Transactions ORDER BY 

WHERE ID = Transactions.CustomerID

[WillowMobileSys]

There are two ways to handle this:

1)  Define [Current Balance] as a Virtual column and use a SUM() calculation to derive the value

2)  On Save of a Transaction, call an action to update the Balance

Because your app is likely Transaction heavy,  I would recommend 1).  It is the easiest to implement.  So you would switch your column to a Virtual Column and then insert into the "App Formula" property this expression:

SUM([Related Transactions][Amount])

This assumes that you have defined the [Customer ID] column on the Transactions table as a the type REF pointed to the Customers table.  This will automatically create a Virtual Column named [Related Transactions].  The syntax, [Related Transactions][Amount], will return a list of Amount (assuming that's the column name) values related this THIS customer row.  Then just SUM them up.

NOTE:  This approach works well as long as you are pulling in all of the data.  In many Transaction apps, the list of transactions can get large.  This could slow down the Virtual column calculation which happens on every row on every Sync.  Many developers will filter out the older transactions to keep the data lean.  If you decide to filter out data then you may need to modify this approach which would depend on your implementation OR replace it altogether with something like Option 2) above.

I hope this helps!

[cessnafrrg]

Excellent suggestions. I have implemented a solution along the lines of Option 1 above, but you are absolutely right. With large number of transactions, it will slow down the response quickly. I was trying a way to implement this through Actions. I am assuming the Action need to be placed on the Customer table while the Event trigger needs to be on the Transaction table (On add new record). But in that case how will the Action know which Customer to update and by what amount. Any pointers on how to implement Option 2? I am really tempted to put a script on the underlying google sheet and be done with it, but hate that idea as that will not play nice with the rest of the AppSheet ecosystem.

[WillowMobileSys]

---

@cessnafrrg wrote:

Any pointers on how to implement Option 2? 

---

Yes.  AppSheet provides the ability to execute an action on Save of a Form.  Each Form View has a Behavior section with a "Form Saved" property.  You can attach a custom action (or action Group) to this property.

You would want to attach such an action to the Transaction Form so that for any New or Edited Transaction you can adjust the associated Customer Balance. 

[cessnafrrg]

Thank you so much for your inputs.

I was able to implement the actions in the following way:

1. Create an even for Add New Records in the Transaction Table

2. The event was firing a Group Action in the Transaction Table calling Action 1, Action 2 and Action 3 (All in Transaction Table)

3. Action 1 in Transaction Table of type "add a new row to another table with values from this table" called by the group action was Adding a new record in a Temporary Table with TransactionID, CustomerID and Amount

4. Action 2 in Transaction Table  was "execute an action on a set of rows" in a Reference (Customer) Table where Reference Row was equal to [CustomerID] and calling Action 4

5. Action 3 in Transaction Table  was "execute an action on a set of rows" in a Reference (Temporary) Table where Reference Row was equal to [TransactionID] and calling Action 5

6. Action 4 in the Customer table of type "set the value of some columns in this row"  is Looking up the Amount from the Temporary Table and updating the Balance column

7. Action 5 in the Temporary Table was deleting the current row

8. Create a bot to Connect the Event to fire Action 1 and Action 2

As you can see this is a really messy solution for such a simple task. I think following your suggesting I can get rid of the bot and directly fire the Group Action from the Form close system event. But I am still worried if there will be race condition where Action 5 gets executed (deleting the row from the temporary table) before Action 4 gets a chance to query that data.

Let me know if you can think of a better solution than what is described above.

[WillowMobileSys · Accepted solution]

Passing a value from one table to update another has always been a pain point.  You used a Temporary table to solve it.  There are a couple of other approaches.

1)  AppSheet has introduced an INPUT() function,  Supposedly you can use it to pass a value between tables but I have not been able to get it to work.  Others seem to have.

2)  I typically just add a column on the table that needs to supply the update value.  In your case on your Transaction table, I would add something like [Updated?].  Also, I would include an [Update Amount] column to specify the difference to be updated to account for edits on the row.   These columns are set based on Form changes so no additional actions are needed.  When Form is saved and the process gets to the update of the Balance, I simply look for related rows where [Updated?] is NOT TRUE and apply a SUM of the Update Difference - a sum in case of multiple rows edited.  Lastly, set the Updated? column to TRUE.  This eliminate the need for a Temporary table and related Add/Delete actions.

[cessnafrrg]

I like your solution 2. I think I will use that approach to distinguish which Transaction rows are Invoiced. Thanks a lot for your help.