Solved: how to update data on one table with related data ... - Page 2

cessnafrrg · 04-06-2022 11:02 AM

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

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.

View solution in original post

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