Sum of Ref data - using "Is a part of"

AppSheet AppSheet Q&A
,
1

Hi support,

I’m build a Production application.

The shop supervisor does a floor tour in the morning where he audits every department.
For every product that the department has done so far, he has to enter:
• Product #
• Quantity

Then, the apps calculates the Quantity * Product standard time (that we have in another table).

My issue is that sometimes the department has done several different part #. Therefore, I’ve built another table to enter the information I need for every part as:
• Product #
• Quantity
• Planned time (Quantity * Standard time of the product)

I’ve also set this reference as “Is a part of” so that for every floor tour, we could see as the image below the part production.

2X_4_49738eeb70ce9f3eed1aeb141c0805cd7f666160.jpeg
2X_4_49738eeb70ce9f3eed1aeb141c0805cd7f666160.jpeg580×787 31.2 KB

Now the data I’m trying to get is the sum of the “Planned time”. As the Floor tour form is not saved yet, I cannot search in the database for my data.

Can you please help me on how I could get this data?

2

I’m a little fuzzy on the exact table structure but you say you have used the “Is part of” feature. This creates a Parent/Child relationship between two tables. I’ll reply using assumptions and details to try to help best I can. So this is bit lengthy!

In your example I’ll call the understood Parent/Child tables - Table A and Table B. Table B is where you have these three columns you mentioned defined:

• Product #
• Quantity
• Planned time (Quantity * Standard time of the product)

I’ll then assume that you want the Sum of [Planned Time] to appear in Table A. I will also assume that you go THROUGH a Table A record Form view to be able to add the Table B record using its own Form view. (These assumptions are all important which I’ll explain below)

If all of these assumptions hold true and because of the “IsPartOf” switch, when you create the child records in Table B they are AUTOMATICALLY added to a Virtual Column list named by default as “Related Table B”.

Assuming the name of the summed column in Table A is [Total Planned Time], you can simply set the App Formula of this column to the expression `SUM([Related Table B][Planned Time])’ - note there is NO period in this expression.

Why All Those Assumptions Are Important

In order for the SUM to properly update (without any additional work), you MUST “touch” the Table A Form view AFTER adding/updating any of the Table B child records. This is so the SUM expression is activated to re-compute the value. In most cases this IS the normal flow so there are no issues. You would open the Table A record for edit, choose the Table B record to change, or add a new record. And when the Table B Form view is Saved, you are automatically sent back to the Table A Form view and all of its calculations are re-triggered, including the SUM.

So why do I bring it up?

Not all designs follow the intended flow. In those cases, custom work will be needed in order to get the SUM calculation to update when expected. That means we would need more details.

Please consider the above and if your structure seems to follow the default/intended flow, you are good to go. Let us know that this helped.

If not, post back and provide more insight to your implemented design and then we can help more from there.

2 Likes
3

Wow! Thank you very much!

I was trying the following formula but with a period!
“`SUM([Related Table B][Planned Time])’ - note there is NO period in this expression.”

Thanks again!

2 Likes