A reference can be added to a table in two ways.
- AppSheet automatically adds references when you generate or regenerate a table.
- You can manually add references.
System Added References
When you initially create your app or when you regenerate a table's column structure, AppSheet will try to automatically infer references between tables. For example, if you have a Customers table with a Name column as its key, and if the Orders table has a column called "Customer Name", the "Customer Name" field is assumed to be a Ref column.
User Added References
You can add a reference as follows:
- Ensure your worksheet has a column to contain the reference.
- If you needed to add a new worksheet column to contain the reference, go to the Data > Column Structure pane and click the Regenerate button for the table. This will include the newly added worksheet column in the table.
- Go to the Data > Column Structure pane, show the table schema, and select the column you wish to make a reference.
- In the "Column Type" drop down select "Ref".'
- In the "ReferencedTableName" drop down select the referenced table.
- Click "OK"
- Save the changes.
System Added Reverse Reference
Whenever a Ref column is created, AppSheet automatically creates a "reverse reference" virtual column in the referenced table. This is true whether you add the reference or the system does.
The "reverse reference" virtual column is given a default name which you can change.
The "reverse reference" virtual column is assigned an app formula which uses the special function REF_ROWS(). REF_ROWS() is the name of a complex SELECT() function.