Can I retrieve another column from a INDEX + ORDERBY?

AppSheet AppSheet Q&A
1

Next question:

I have this table with products and a child table with components. The components are - basic color paints, 15 in total, but normally a given mixed color will have 3-5 components. I want to have a log for each unit of mixed paint with all components and quantities, with a bot to print out a sticker with the amounts of each basic color. Therefore I need a system of computing each of the components. Right now I have a long series of 15 columns in the parent table. They look something like this:

Black _____ SUM (SELECT (child table [QUANTITY], and ( ProductId is = this row, Color = “Black”). Show if >0

I think there must be an easier way to do the job by getting that data from the “related items” list. With Index + Orderby i can sort the components, and it will show me the IDs of the related child records, but I need the Name and the Amount.

Is there any Lookup or something to retrieve the data from other columns than the ID? If I am not wrong, that way the app might run smoother.

Or maybe not…

2

Not sure what you mean by this as far as resultant values using expression. BUT…

You can create shorthand queries against only the child rows by doing something like these:

SUM(SELECT([Related Children][Quantity],  AND([Product ID] = [_THISROW].[Product ID], [Color] = "Black")))  <- A single SUM value

SELECT([Related Children][Name], IN([Product ID], [_THISROW].[Completed Products]))  <- A list of names for Completed Products

I hope this helps!

1 Like