How to recalculate value in the parent table when a value in a child table has changed

[Arni_Kli]

Hi,

Previously, the question of how to automatically recalculate the value in the parent table when the value in the child table changed was discussed here https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Updating-Records-in-Another-Table-Best-Practice...

This comparative example shows two options for implementing automated recalculation of a parent table when changes occur in child tables. In both cases the REF-link is established and the "Is Part of" parameter is on.
Case 1 - the calculation is performed in the parent table using a formula in column. This option does not automatically update the parent table (tables Parent and Child).
Case 2 - the calculation is performed by running a sequence of actions that react to saving the child table's form. This option works correctly (tables Parent2 and Child2).

DATA

Case 1

SUM([Related Childs][Qty])

Case 2

ACTIONS

Case 1

No actions.

Case 2

FILTER("Parent2", [Parent2_ID] = [_THISROW].[Parent2])

SUM([Related Child2s][Qty])

VIEWS

Case 2

Child2_Form (system generated)

-------------------------

RESULTS

Case 1

You cannot change a value in a parent table using a formula alone.

Case 2

 
Using actions and assigning them to the Save button allows you to successfully synchronize the value in the parent table automatically.

-------------------------

So you can use this complete instruction for automatic recalculation of the value in the parent table when the value in the child table changed.

[Arni_Kli · Accepted solution]

But...
Let's not completely discard the first option. We can use a formula, but not in the application, but in the data source. More precisely, we can create a new data source using the formula.

Let's create another empty table "Parent_Total" in Google Sheets (in the same book) and in the first cell we'll build a query to the child table "Child", specifying the necessary grouping and aggregation parameters in the query:

=QUERY(Child!A2:C;
"select
min(A), B, sum(C)
where
A is not null
group by
B
order by
B ASC
label
min(A) 'Unique_ID', B 'Parent', sum(C) 'Total_Qty'
format
sum(C) '0'
"; -1)

Let's add this table to the application and configure it as read-only and set the columns as follows:

After saving, a REF-link will be automatically created and a REF_ROWS backlink to our new table will be generated in the Parent table:

Next, you just need to include this column in the detailed view and you will receive an aggregated amount for the product, which is synchronized automatically with any changes in the child records, is always in the Google Sheet database and does not require any additional settings in the application.

[Arni_Kli]

Dear moderator,
I posted this topic in Tips&Tricks but the topic was moved to Q&A charter, although it does not contain questions, the two posts above contain only solutions of different alternatives. Please review the content and move this topic back to Q&A.