I have two tables: Customers and Orders. Customers has a virtual column that contains a list of Refs to their open (not completed) orders. So far, this virtual column seems only useful as a list of order keys.
Hereโs my question: Is there a way to use this list of Refs directly to extract specific values from the related rows โ for example, to get a list of item names from open orders?
Use Case:
When a user tries to create a new order, I want to prevent them from creating another order for the same item if one already exists and is still open.
Sure, I can use a SELECT() expression to check all open orders for that customer and see if the item already exists. But if I already have a virtual column like [Key Customer].[Open Orders], it feels redundant to repeat the same filtering logic with SELECT().
Wouldn't it be more efficient and readable if I could just do something like:
[Key Customer].[Open Orders].[Item]
to get a list like: {"Pen", "Pencil", "Tape"} instead of having to manually write:
SELECT(Orders[Item], AND([Key Customer] = [_THISROW].[Key Customer], [Status] <> "Completed", [_THISROW].[Key Order]<>[Key Order]))
This would be similar to how dereferencing works for a single Ref, but applied to a list of Refs.
Suggestion:
If this isnโt currently possible, Iโd like to propose adding a native function that allows transforming a list of keys (Refs) into a list of values from a specific column. That would open up a lot of clean and powerful expressions.
Is there a way to do this already? Or is anyone using a workaround for this?
LinkedIn
Twitter
