Josh, without fully submerging myself into your data to recommend a restructure, the main problem is, and no feature request will change it, you can’t dereference a dereference. Thus, AppSheet won’t bring grandparent information down to the grandchildren. That won’t change. But there is always different routes to the same destination.
Double Dereference Expressions
You cannot dereference a dereference.
For example, you might have a three level hierarchy consisting of a Customer record, a child Order record, and a grandchild Order Detail record. The Order Detail record contains a Reference to the Order record. The Order Detail record contains a Reference to the Order record.
From the Order Detail record, you can access the value of a column in the Order record using a Dereference expression as described above.
From the Order Detail record, you cannot directly access the value of a column in the Customer record by chaining dereference expressions. That is, you can’t dereference from the Order Detail record to the Order record, and then dereference from the Order record to the Customer record in a single expression.
However, from the Order Detail record you can indirectly access the value of a column in the Customer record. Do this as follows:
- Create a new virtual column in the Order Detail record that will serve as a Reference to the Customer record. For example, you might call the virtual column CustomerRef .
- In the AppFormula of the new virtual column enter an expression that copies the value from the Reference column in the Order record. Consider the Order Capture sample app. In it, the AppFormula in the new Order Detail virtual column would be [Order Id].[Customer Name] , where [Order Id] is the name of the Reference column in the Order Detail record that refers to the Order record,and [Customer Name] is the name of the Ref column in the Order record that refers to the Customer record. This expression copies the value of Customer Name Reference column to the new virtual column in the Order Detail record.
- Ensure that the type of the new virtual column is Ref and the Referenced Table is the Customer table.
In the Order Detail workflow template, you can now refer to column names in the Customer table be dereferencing the new virtual column. For example, if the new virtual column is called CustomerRef , you can retrieve the customer’s email address using this expression. [CustomerRef].[Email]
This 3 tier hierarchy you want to do is totally feasible, but you might have to think a little outside the box. Reanalyze your table structure and column layout. See if you can use this example to help. If you want any suggestions, I’d be happy to look at your overall table structure if you send screenshots (both in the editor and the spreadsheet) with some sample data filled in.