How to retrieve specific value from LIST using SELECT

Question

I keep retrieving the entire LIST of Customer's Total Amount instead of the Specific Customer. I have tried using SELECT according to their Order ID to filter out other Customer. However, the result is still the whole list.

My code here

I have tried to FILTER using Customer Name and Order ID. However, the outcome is still the same, returning the entire LIST of Total Amount.

Answer 1

If you reference connect your line items to your order, app sheet will natively create a reverse reference on the order table that contains a list of all of the corresponding line items for that order.

The list that you're looking for lives in the data inside that column. To get the data out you need to do a list the reference, which will extract the value of whatever column you specify out of that reverse reference, creating a list of the values you want from those child records.

Then if you wanted to create a total for all of those line items, you could wrap that list dereference in sum().

References are the key to making just about everything advanced work inside app sheet, definitely worth wrapping your mind around.