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

(GreenFluxLLC) #1

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?

(GreenFluxLLC) #2

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 Mattison) #3

@Aleksi_Alkio TY Sir! Translate

(Stephen Mattison) #4

@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]))

(GreenFluxLLC) #5

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 Mattison) #6

@GreenFlux Order Table, Virtual column, App Formula.

(GreenFluxLLC) #7

Got it! Thanks @Stephen_Mattison!

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

(Stephen Mattison) #8

@GreenFlux Sweet!

Good work!

(Aleksi Alkio) #9

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).

(GreenFluxLLC) #10

@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) #11

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