Update Accounts Table based on Sales and Payments

Hello Everyone!!!
I’m new to AppSheet. I built an App where I have an Accounts table that I want to update every time a sale is made to an existing customer or a customer makes a payments (total or part). Accounts have cumulative rows for Total (sales to a certain customer within a certain month/year), Paid, and Pending (= Total - Payments).

Let’s say I have the following row in Accounts table (Initial State):
Customer = A
Month = 9
Year = 2021
Total = 1000
Paid = 200
Pending = 800

Sales Use Case (Based on Initial State)
If I make a sale to Customer A worth 100, I add a new record in Sales table, with the following:
Customer = A
Month = 9
Year = 2021
Amount = 100
After I tap on “Save”, I want Accounts table to be updated, so the corresponding row for Customer A would be updated to:
Customer = A
Month = 9
Year = 2021
Total = 1100
Paid = 200
Pending = 900

Payment Use Case (Based on Initial State)
If Customer A makes a Payment, let’s say worth 400, on table Payments the App adds a row with the following:
Customer = A
Month = 9
Year = 2021
Payment = 400

After I tap on “Save”, I want Accounts table to be updated, so the corresponding row for Customer A would be updated to:
Customer = A
Month = 9
Year = 2021
Total = 1000
Paid = 600
Pending = 400

How can I automate these Use Cases?
All answers will be greatly appreciated. Thanks.

1 Like

Welcome to the AppSheet community.

Please take a look at the sample app that demonstrates reference actions that update values in the other table from other table.

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-3d26-475c-a8f8-9911f5015920

Please take a look at the action description " Execute an action on a set of other rows (Reference Action)"

In general, you will find many useful AppSheet references in the post below.

1 Like