How do you get a sum of all the related value...

GreenFlux · 08-05-2018 05:00 PM

How do you get a sum of all the related values for a column in a related table?

I have a [SqYd_Calc] table with columns for Length & Width. Then there are

virtual columns for SqFt & SqYd that auto-enter the converted values. These are working fine, and showing these related records In-Line with the Group Aggregate of SUM::SqYd is the exact value I need (77 in the image below).

However, I want to access this value for further calculations ([Price] = rate * SqYd). And although the Group Aggregate feature makes displaying this value very easy, I can’t figure out how to get the same value in the equation editor. I can only get the product of ALL SqYd rows. What formula is the Group Aggregate for SUM::SqYd using?

GreenFlux

I’ve tried several approaches in the equation editor and I either get a sum of ALL rows, or an error that the input is not the expected format. I just can’t get the syntax right to SUM only the related rows.

Stephen_Mattiso

@Aleksi_Alkio TY Sir! Translate

Stephen_Mattiso

@GreenFlux I think what you need is another Virtual Column {OrderTotal] with an App Formula something like this, which Totals up my OrderDetails LineTotals for my Order.

=SUM ( SELECT (OrderDetails[LineTotal], [OrderID] = [_THISROW].[OrderID]))

GreenFlux

Thanks @Stephen_Mattison ! Should this column be created in the Line Item table, or the Order table? I still can’t get a valid equation with this syntax.

Stephen_Mattiso

@GreenFlux Order Table, Virtual column, App Formula.

GreenFlux

Got it! Thanks @Stephen_Mattison!

=SUM ( SELECT (Line Item[SqYd], [Order] = [_THISROW].[Key]))

Stephen_Mattiso

@GreenFlux Sweet!

Good work!

Aleksi_Alkio

Just for the information… more effective way to calculate this is a format… SUM(SELECT([SqYd_Calc][SqYd],TRUE)).

With this format you don’t need to search the whole child table again because you have that list already in your hand (in the virtual list column).

OptimiX_XcrY

SUM(SELECT([SqYd_Calc][SqYd],TRUE))

Can’t this be just SUM([SqYd_Calc][SqYd]) ?

Marc_Dillon

Yes.
Also, things have changed a lot in 2 years, best to take advice in 2 year old threads with a grain of salt.

GreenFlux

@Aleksi_Alkio Thanks! This looks much closer to what I attempted yesterday, but I kept getting a syntax error.

I was missing the TRUE at the end. I guess the WHERE parameter is required in the AppSheet implementation of SELECT.

Aleksi_Alkio

Well… actually it’s more like a condition check.