The vast majority of online databases deal with related tables, but only the best ones provide an easy and intuitive way to establish tables’ relationship. The easiest way of creating relations between tables is adding a single-reference column. With this approach, each record stored in the first (‘Master’) table can relate to a record stored in the second (‘Details’) table. Adding a single-reference column creates a lot of stuff behind the screen and we would like to disclose its little secrets 🙂
1. What kind of tables can be related?
Usually you would need to relate data kept in two separate tables, however it could also be a case when a table refers to its own data, but it is rather an exception.
Let’s assume that in your online database you would need to relate Clients table with Invoices table.
2. Name Column as a vital element of relation.
The issues would occur with relations if the Name Column is not chosen correctly in the master table. The Name Column is a column which values are going to be used to represent table records as a short text, since the Name Column works as the Default Record Picker option. More details about the record picker are described below.
Please note that any column created in the table can be set as this table’s Name Column. Moreover, the Name Column is a required option in the table properties, therefore this field can’t be blank.
In our example we selected the Company column keeping client company names as the Name Column.
As a result, a Company column value is displayed as a header of the data entry client view/edit form. It was made possible with a help of Default Record Picker settings, where the Name Column is selected by default.
3. Creation of a Single-reference column.
The next step is the single-reference column creation to relate Clients table with Invoices table. We know that many Invoices can be related to one Client but every invoice can be related to one client only. That’s said, when you add a single-reference column to the Invoices table, you select Many-to-One relationship and set ‘Clients’ table as a ‘related’ table.
4. New relation is built automatically.
When a single-reference column is added, the tables become related and, as a result, a new relation appears on the database. In our example, this is One-to-Many relation: One Client – Many Invoices. In online database terminology, ‘Clients’ table is a Master table while ‘Invoices’ table is ‘Details’ table.
5. Details view.
Details View option appears in the relation settings by default. The system specifies ‘Default View’ as the ‘Details View’.
This layout makes it easier for you to see related details under the master record form, for example, invoices that are related to a Client record.
Any view built in the details table (Invoices in our case) can be set as Details View, except for a cross-tab view. In cases when the Details view is not needed, the “None” option should be selected in the relation settings.
6. Proxy Column and Proxy Lookup.
The other part of the puzzle is a Proxy column. When Client reference column is created in the Invoices table, the system sets above-mentioned Name Column (for example, Company) as the Proxy column by default.
Lookup Column referring to this Proxy Column is created automatically. Let’s call this lookup ‘Proxy Lookup’.
As a result, you get two new columns: one ‘reference’ and one ‘lookup’ (proxy lookup).
The Proxy column values (Company values) replace the Reference column values (in our example Client column keeps Client Id values) on the record’s View form.
The lookup column (Client Company) is displayed by default in the prebuilt views/reports, such as ‘List Changed’ or ‘List All’. Should you display a reference column (Client) in any view, you’ll see ‘Client Id’ key column value.
A Proxy column can be changed, but, because of this change, the Proxy Lookup would also change simultaneously. When a Proxy column value is set to ‘None’, the lookup is deleted automatically.
More details about the Proxy column are listed here >>
7. Record Picker.
While editing a form, if you need to select a related Client in the ‘Client’ single-reference column of any Invoice record, Company values will be listed in the dropdown instead of Client Id. This replacement depends on the Record Picker settings and helps to simplify related records selection. By default, the Name Column is used in the Record Picker (in our case, the Company column from the Clients table is set as the ‘Name’ Column). If you need other values to represent the list of related Clients on the edit form, you can change the default record picker of the master table (Clients)
or adjust records picker settings directly in the Client reference column of the details table (‘Invoices’ in our case).
8. Inline Edit feature.
The last part of the puzzle is Inline Edit feature. This option allows you to edit a record directly in the table view list. But what happens with the single reference column in this case?
As we already know, a proxy lookup column is shown in the table view by default (for example, in ‘List Changed’ or ‘List All’ views). It was mentioned above that this lookup refers to the Proxy Column. If the Inline Edit option is activated in the View, the lookup is replaced with a record picker when a user clicks on the Edit button in such a view. In case when the Client Company lookup is a Proxy lookup and the record picker settings include both the Company column and the Country column, the Company – Country record picker dropdown will be displayed in the Edit mode.
Conclusion: ‘reference column’ is a handy tool allowing to create relations between tables. By adding a reference column, you create or change many online database objects. These changes may not be obvious for some users, that’s why a good understanding of ‘Name Column’, ‘Proxy Column’, ‘Record Picker’ and other terminology would help to make your database setup more intuitive and productive.
