I’m looking for a best practice on updating a table from another.
Scenario: user enters multiple rows in table A for different products. So maybe there are three rows where the product is Pizza and the quantities are 1, 5 and 10. Table B is a roll up of Table A by product and sums total quantities. So table B would reflect Pizza with a total of 16.
Table A with 3 columns: ID, Product and Quantity
Table B with 3 columns: ID, Product and Total Quantity
What is the best approach to store the value in a sheet when a quantity in Table A is added or edited?
Would it be to do a computed value?
Would it be to create an action where if the table is updated or a new record is added to update to edit Table B?
Im looking forward to the replies!
You would want to create a Parent/Child relationship where Table B is the parent and Table A is the child. The Product column in Table A would be a Ref column back to Table B. In the Product column of Table A you would also turn on the the “Is Part of” property.
With this setup, you would not need to do ANYTHING to ensure the Total Quantity in Table B is updated. If you enter the Table B row in EDIT mode, there would automatically be an Inline table of the Related Table A entries. If you then use that Inline table to edit existing rows or add new Table A rows, the act of Saving those entries will automatically trigger the Table B expression to recalc the Total Quantity.
Refer to this article and scroll to “Expression ownership between tables” for a few additional details
References between tables
I hope this helps!
2 Likes
This is definitely the most expedited and accurate way to calculate the totals. I’ve tried different ways with Selects and Filters but there was always a disconnected. I read on IsPartOf in the ref settings, but couldn’t quite grasp the setting’s concept, but you’ve clearly explained it!
1 Like
@WillowMobileSys , you are right in first part but in second the “Is Part of” property will not automatically trigger the parent table expression to recalc the Total Quantity. Admin will need to use a separate action bound to the save button in the child table form. “Is Part of” property control the keys operations (deletion e.g.) but not the non-keys columns changing.
@Arni_Kli
this was the second part of the scenario that you’ve hit it spot on!
Through my experience, I’ve seen that auto compute is only applicable during an on screen event (physically looking at the data in add/edit). Displaying the detail doesn’t automatically auto computer. Hence the reason an action is needed to update the db column field.
Based on your experience, do you feel that this is a correct statement?
What I said was…"the act of Saving those entries will automatically trigger the Table B expression to recalc the Total Quantity. "
Not that I also said… “If you enter the Table B row in EDIT mode” - meaning you acces the Parent row through the Form View.
“Saving” being the key word here. The use case I outlined was entering the Parent table record FIRST and then selecting the Child record to edit OR the Add/New button to add a new Child record. Tap Save on the Child AND THEN you MUST tap Save on the Parent for all Child updates to be saved. This action WILL trigger any App Formulas on the Parent row. If the Total Quantity is implemented with an App Formula it will get updated.
There are scenarios where you can enter the Child record WITHOUT going through the Parent Form row first. Such as from the Detail View of the Parent row IF the child rows are being displayed. These use cases WILL NOT update the Parent row and some action will be needed to force the updates. I didn’t mention these because the question was about “best practice”. What I outlined is the best practice for that feature.
3 Likes
@WillowMobileSys , I misunderstood what was said, I apologize 
@DayToDataInc , please look for link below
No need to apologize! Things are not always clear through text. I only responded to make sure it could be clarified to others reading in the future!!
So with this being said “Tap Save on the Child AND THEN you MUST tap Save on the Parent for all Child updates to be saved. This action WILL trigger any App Formulas on the Parent row. If the Total Quantity is implemented with an App Formula it will get updated.” The scenario would be: edit parent > add/edit children > save child > save parent.
If I went in through detail view on the parent and added/edited children records then the separate action of updating the field for that parent row would be required?