Dereference a dereference

[AlfredoChavez]

Hi!

I have 3 tables. Customers, orders, and order details which contain all products from an order. I want to show a list of all the products bought by the customer on the customer table. I tried the [Related Orders][Related Details] in a VC of type list with the base ref but I get the error.

Column Name 'Products' in Schema 'Customers_Schema' of Column Type 'List' has an invalid app formula '=[Related Orders][Related Details]'. The type of the app formula 'List of List of Ref to table 'Details' of Text' does not match the column type 'List of Ref to table 'Details' of Text'.

It works as expected as long as I don't save my progress. 

[TeeSee1 · Accepted solution]

What the error says is the column definition you have (assuming you have it like below) does not match the type of [Related Orders][Related Details] which is a list of list (all the element in Details are not expanded to a flat list but kept in the original lists).

What you could do instead is to change the expression of the VC to something similar to 

SELECT(
 Detail[detail id],
 IN(
  [detail id],
  [_THISROW].[Related Orders]
 )
)

and set other attributes as below (I believe you already have it set)