Sum select multiple column in related child by specific row

[JhunePol]

Hello guys,

I need a help in my expression.

How do i achieve my goal in 1 expression only.

I have an related child with a column named [Jo#] , [Good] , [Reworks] , [Spoilage] , [MR Spoilage]

and I create 4 virtual column in my parent then i enter this expression

for Virtual Column 1

SUM(
SELECT(
Offset Process Slip Sheet[Good],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 2

SUM(
SELECT(
Offset Process Slip Sheet[Reworks],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 3

SUM(
SELECT(
Offset Process Slip Sheet[Spoilage],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 4

SUM(
SELECT(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])))

I put this to get the sum value of each column by specific [Jo#]

Instead of doing 4 Virtual Column then add the 4 virtual column to get the total.

How do I sum the 4 column in 1 expression only?

I try the 

SUM(
SELECT(
Offset Process Slip Sheet[Good],
AND(
Offset Process Slip Sheet[Reworks],
AND(
Offset Process Slip Sheet[Spoilage],
AND(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])
)
)
)
)
)

and

SUM(
SELECT(
Offset Process Slip Sheet[Good],
AND(
Offset Process Slip Sheet[Reworks],
AND(
Offset Process Slip Sheet[Spoilage],
AND(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])
)
)
)
)
)

Thank you in advance!

[Arni_Kli]

Why don't you use Related REF_ROW records for sum?

Total = sum1+sum2+…

Sum([Related][Jo])

* Good instead Jo

[JhunePol]

I need by specific [Jo#]

I want to sum the values of [Good], [Reworks], [Spoilage], and [MR Spoilage]

What i did is I create 4 virtual column in my parent.

for Virtual Column 1

SUM(
SELECT(
Offset Process Slip Sheet[Good],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 2

SUM(
SELECT(
Offset Process Slip Sheet[Reworks],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 3

SUM(
SELECT(
Offset Process Slip Sheet[Spoilage],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 4

SUM(
SELECT(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])))

Then I create virtual column named [TOTAL]

I enter the [Virtual Column 1]+[Virtual Column 2]+[Virtual Column 3]+[Virtual Column 4]

Then it works! I got the Total of 4 column.

But i want to do this in 1 virtual column only.

[Arni_Kli]

@JhunePol ,

1) make Subtotal column in child table

[Good] + [Reworks] + [Spoilage] + [MR Spoilage]

 2) make Total column in parent table

Sum(Related[Subtotal])

[JhunePol]

@Arni_Kli ,

But i need it by specific [Jo#]

My expression with 4 virtual column is working. I already get the total of 4 column by specific Jo.

Im asking a help for 1 expression only 😔

Btw Thank you @Arni_Kli 😉

[Suvrutt_Gurjar]

Please try in the parent table in one virtual column called [Total]

SUM([Related Offset Process Slip Sheets][Good]) +

SUM([Related Offset Process Slip Sheets][Reworks]) +

SUM([Related Offset Process Slip Sheets][Spoilage]) +

SUM([Related Offset Process Slip Sheets][MR Spoilage])

[JhunePol]

Hello @Suvrutt_Gurjar 

Thank you for your expression, really appreciated, but i need to sum the values by specific Jo#.

My related child was already Ref to my parent, so meaning my Jo# in my parent was same in my child.

This expression is working 

SUM(
SELECT(
Offset Process Slip Sheet[Good],
([Jo#] = [_THISROW].[Jo#])))

but i need to add the [Reworks], [Spoilage], and [MR Spoilage]. How can i add these 3 column using 1 expression only?

I tried some expression like this one

SUM(
SELECT(
Offset Process Slip Sheet[Good],
AND(
Offset Process Slip Sheet[Reworks],
AND(
Offset Process Slip Sheet[Spoilage],
AND(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])
)
)
)
)
)

But it's not working. 

This is the example of what i want. Sum values of [Good] + [Reworks] + [Spoilage] + [MR Spoilage] by specific Jo#

[Suvrutt_Gurjar]

Is your parent table with each row having unique [Jo#] ?

[JhunePol]

Hi @Suvrutt_Gurjar 

The Item ID Only. Please see my data.

This one is the Parent

Then, this one is the child

Then my solution to get the Total is i create 4 virtual column for the sum values of each column. Please see my expression.

for Virtual Column 1

SUM(
SELECT(
Offset Process Slip Sheet[Good],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 2

SUM(
SELECT(
Offset Process Slip Sheet[Reworks],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 3

SUM(
SELECT(
Offset Process Slip Sheet[Spoilage],
([Jo#] = [_THISROW].[Jo#])))

for Virtual Column 4

SUM(
SELECT(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])))

Then I create virtual column named [TOTAL]

I enter the expression in the total is [Virtual Column 1]+[Virtual Column 2]+[Virtual Column 3]+[Virtual Column 4]

So i got the total, but i want that 4 virtual column is to re-express in 1 expression only. Hope you understand

[Suvrutt_Gurjar · Accepted solution]

Okay, thank you for the details.

Technically, you can simply add those expressions in one VC something like 

SUM(
SELECT(
Offset Process Slip Sheet[Good],
([Jo#] = [_THISROW].[Jo#])))

+

SUM(
SELECT(
Offset Process Slip Sheet[Reworks],
([Jo#] = [_THISROW].[Jo#])))

+

SUM(
SELECT(
Offset Process Slip Sheet[Spoilage],
([Jo#] = [_THISROW].[Jo#])))

+

SUM(
SELECT(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])))

But using 4 SELECT() expressions in a VC can be very much sync expensive. 

So as an alternative, please try the following

Please create a VC called say [Total_Quantities] in your child table with an expression something like 

[Good] + [Reworks] + [Spoilage]+[MR Spoilage]

Then in the parent table, the expression can be something like

SELECT( [Related Offset Process Slip Sheets][Total_Quantities], [Jo#]=[_THISROW].[Jo#])

[JhunePol]

Hi @Suvrutt_Gurjar 

I don't think that is possible to add (+) between 2 expression. I never try this 😅 but when i try now, it was working! Thank you so much! Really appreciated!

I'm okay with this expression, 

SUM(
SELECT(
Offset Process Slip Sheet[Good],
([Jo#] = [_THISROW].[Jo#])))

+

SUM(
SELECT(
Offset Process Slip Sheet[Reworks],
([Jo#] = [_THISROW].[Jo#])))

+

SUM(
SELECT(
Offset Process Slip Sheet[Spoilage],
([Jo#] = [_THISROW].[Jo#])))

+

SUM(
SELECT(
Offset Process Slip Sheet[MR Spoilage],
([Jo#] = [_THISROW].[Jo#])))

But one question, what do you mean for this? But using 4 SELECT() expressions in a VC can be very much sync expensive. 

You mean its take too long to sync the data?

[Suvrutt_Gurjar]

---

@JhunePol wrote:

You mean its take too long to sync the data?

---

Yes, especially when the number of records in your child table grow.

Please take a look at the following help article, especially at the section "Reduce the amount of computation "

Improve the speed of Sync - AppSheet Help (google.com)

Incidentally, the example given for an expensive VC in that section of the article is very much similar to your case. 🙂

Note: As your data grows in the app, these virtual columns with multi row functions like ( SELECT(),  MINROW(), LOOKUP(), MAXROW() ) can be very sync expensive. So , this is a very important topic in AppSheet app creation. Please take a look at the multiple community posts and help articles to know the best practices.

[Arni_Kli]

from this point of view I suggested doing calculations in child records:

[Suvrutt_Gurjar]

Yes, I know @Arni_Kli 

Your guidance was in the correct direction.

As we start responding to posts, we realize that we sometimes need a lengthier explanation depending on our understanding of the question poster's awareness of the platform. 

When I realized that @JhunePol needs a little more clarification, I pitched in with the following post.

Please try in the parent table in one virtual column called [Total]

SUM([Related Offset Process Slip Sheets][Good]) +

SUM([Related Offset Process Slip Sheets][Reworks]) +

SUM([Related Offset Process Slip Sheets][Spoilage]) +

SUM([Related Offset Process Slip Sheets][MR Spoilage])

Thereafter the flow continued with more queries from @JhunePol  and realization that the [Jo#] is not unique in the parent table. The entire intention was to make @JhunePol  aware of using related system generated columns and also that instead of 4 VCs, he/she can simply sum up in one column that she/he accepted as a solution, even though I emphasized that it is not ideal one 🙂

[JhunePol]

@Arni_Kli Sorry, I am a beginner only. But before i ask a question here, I do research first and I do trial and error in my app even i know i can't do it but i tried. Chances of doing my trial and error is 20% for the complicated expression. For @Suvrutt_Gurjar, he/she explain very well like he reply the whole Expressions then the requestor will copy/paste it in the app. and/or he/she ask follow up question in my app. And example, me, all of expression from @Suvrutt_Gurjar, he wrote the expression whole completely. But both of you, @Arni_Kli  & @Suvrutt_Gurjar  are appreciated! Thank you so much!! GodBless! 😇 Im on 90% on my app! 

[JhunePol]

@Suvrutt_Gurjar Thank you for the information! 😇

[Arni_Kli]

---

@Arni_Kli wrote:

Total = sum1+sum2+…

---

😁😁😁

[JhunePol]

Hi @Arni_Kli  Sorry, I didn't notice that expression. 

This expression caught my attention.

My bad! But thank you! 😉