sum products in parent table

[bolognesiedalla]

Hello everybody.
I would like to show in my FERTILIZATION PLANS parents table the list of products inserted in the grandchild INTERVENTION ROWS table, with the sum of the relative quantities / product price / total price per product (products can repeat in the grandchild table)
Something like:

PRODUCT 1          10 kg     3.00 €     total 30.00 €
PRODUCT 2           5 kg     2.00 €     total 10.00 €
PRODUCT 3            1 kg     7.00 €     total 7.00 €
.......

What is the best approach to achieve this in the parent table?

[Sakhy]

been having the same problem. Even checked the vehicle inspection video i didnt not win. i hope we get a solution. 

[Suvrutt_Gurjar]

Please raise such questions in the "Q & A" section.

If understanding of your requirement is correct, please create a VC called say [Related Grandchildren] in your grand parent table with an expression something like

SPLIT([Related Children][Related Grandchildren],",")

Where [Related Children] is the reverse reference column in the grand parent table and 

[Related Grandchildren] is the reverse reference column in parent table ( child of grand parent and parent of grand child)

Please make this column as type list  with element type as reference and referenced table name as that of the grand child table.

[bolognesiedalla]

thanks for this explanation !!
Instead of wanting to get the list of related records, I would like to get a view with the sum of total quantities and prices of each product used as Grandchild (can repeat the same product multiple times).

exactly how @Hussein_Osseily  explained below

I want it from this: 

Product A     10KG     3.00EUR     30.00EUR

Product A     5KG      3.00EUR     15.00EUR

Product B     10KG     2.00EUR     20.00EUR

To this: 

Product A     15KG     3.00EUR     45.00EUR

Product B     10KG     2.00EUR     20.00EUR

I wanted to understand if in this case it is necessary to create a special table or is it just a matter of virtual columns and formulas

[Suvrutt_Gurjar]

Well , you could use grouping in that related inline view. But you can group by any one entity such as cost or quantity. In the example below, the grandchild inline view is grouped by Total price. As you can see quantity being integer is generally easy to add.

If you want to do it exactly the way you want, I believe it will need to be done at the source (that is grand child table) level and row wise grouping and addition can be sync intensive in AppSheet. Instead it is better to use the power of AppSheet's grouping option in inline view , even though grouping will show at apes level by one item.

In the view , grouping is as follows

[Hussein_Osseily]

Dunno if I got your question correctly

you want it from this: 

Product A     10KG     3.00EUR     30.00EUR

Product A     5KG      3.00EUR     15.00EUR

Product B     10KG     2.00EUR     20.00EUR

To this: 

Product A     15KG     3.00EUR     45.00EUR

Product B     10KG     2.00EUR     20.00EUR

[bolognesiedalla]

yes you're right. exactly

[Hussein_Osseily]

I want to do the same exact thing in the past via Appsheet, but couldn't get the answer.

My alternative solution for you which I have used to solve it is to Use =QUERY() on a separate sheet in your google sheet with this sequence:

=QUERY( 'the range of the table , "SELECT Product Column, product price, SUM(Qty Column), SUM(Total Column) WHERE Product Column IS NOT NULL GROUP BY Product Column ORDER BY Product Column ASC")

Example:

=QUERY( 'FERTILIZATION PLANS'!A:P, "SELECT B, C , SUM(K), SUM(L) WHERE B IS NOT NULL GROUP BY B ORDER BY B ASC")

I hope that someone would suggest an Appsheet expression to achieve this. but in both cases this is working just well for me

[Sakhy]

Greetings please try this one out got assistance from @MultiTech 

 https://www.appsheet.com/templates/Shopping-Cart-Totals-Redo?appGuidString=382ac17a-b5bf-4ea1-9589-6...

IT Helped