Hi there. I am trying to get a List of all related rows from an Inspection table stored into a virtual column for the Facility table. However, the current structure of the Inspections uses multiple tables, as a different Facility Type will require different entries needing to be filled, hence the separate Inspection tables for each type. A one-to-many relationship is set up with the multiple Inspection tables using a Facility ID column as a Ref to the Facility table’s ID.
I have tried the following expressions for the virtual column in Facility:
- REF_ROWS(IFS([Facility Type] = “type-1”, “inspection table 1-name”, …, …), “Facility ID”)
- IFS([Facility Type] = “type-1”, REF_ROWS(“inspection table 1-name”, “Facility ID”), …, …)
- IFS([Facility Type] = “type-1”, [automatically generated ref list for inspection table 1 to facility]), …, …)
The error with the first method was that the text from the IFS expression would not be identified as a table name. The error with the other two methods was that the return values from the IF pairs in IFS were not the same type.
Is there any way I can use the current table structure to get the virtual column, or would it be better if I restructure the tables so all of the Inspections are in one table and make certain entries hidden based off Facility Type?
