Database Design Documentation
Database Design Documentation
Linking two fields in different sheets consists of creating a Linked Field and the corresponding Loaded Field(s).
For example, when you enter information in a "Sales Order", data from another "Customer" sheet can automatically pop up. In this case, the "Customer ID" in the "Sales Order" is the Linked Field, which is configured to select values from the "Customer ID" field in the "Customer" sheet.
After selecting the "Customer ID", other corresponding information such as the "phone number", "address", and "email" will automatically populate. These fields are called Loaded Fields.
Using Linked and Loaded fields can significantly reduce data entry time and prevent manual input errors.
The Linked Field and Loaded Fields can both be configured in the Link Manager. In the Design Mode of the Form page, click on Form Tools and choose Link & Load under the Sheet Linking options.
In the bottom right corner of the Link Manager page, you can choose the tab and sheet you want to link or select different fields within the same sheet to link.
First, select a Field Header on the right side of the page. It will become the Linked Field, displayed in "blue". The Linked Field must have the Unique Value so that the system can correctly identify each record.
After configuring the Linked Fields, link the corresponding Loaded Fields on both the left and right pages. Loaded Fields will be displayed with gray borders and there can be multiple instances. You can drag a Loaded Field onto an existing field or simply select an empty space on the left page, and the corresponding field will be automatically added.
If there are Linked and Loaded Fields already set up in the sheet, you can directly select the field and click "Go to Link Manager" from the left sidebar.
There will be a blue L icon in the Linked Field and a grey L icon in the Loaded Field.
Clicking on the icon will automatically highlight all the fields in the same set of Link & Load.
Note: If a light gray "L" icon is displayed at the top right of the loaded field, it indicates that the field is Linked From Parent Sheets. For more details, please refer to this chapter.
You can link and load data from other sheets into a Subtable. For instance, when entering data into a Subtable, you may want to automatically load related information into other fields after selecting a specific field, such as a "Product No.", which could then fill in the corresponding name, price, and so on.
For additional linking options related to Subtables, please refer to instructions on Generating a New Sheet From a Subtable and Adding a Reference Subtable.
When there is a Linked Field in the Subtable, you can enable the Multiple subtable rows at once option. However, you can only enable either Multiple subtable rows at once or Multiple select for a Linked Field.
You can select multiple entries at once and populate them into the Subtable.
Note: You will need to close the window manually after entering the data. (If the Multiple subtable rows at once configuration is not checked, the window will close after selecting one entry.)
If you need to link and load different data from the same sheet multiple times, such as when the "Orderer" and "Recipient" in a "Sales Order" sheet are different but both come from the "Customer" sheet, this situation is not suitable for using link and load for a Subtable
For example:
Open Link Manager, and you will see the first set of link and load from "Customer" on the "Sales Order" sheet. In the menu at the top right, you can select to create a new set of links.
Then, you can create a second set of link and load from the same sheet.
It is recommended to edit the field names to ensure that the information is clearly defined. For example, on the "Sales Order" sheet, the two sets of Links and Loads from the same source can be labeled as "Ordering Customer" and "Recipient Customer".
After saving, you can link and load the customer data twice.
To remove an existing link, go to the Link Manager, and click the red cancel icon next to the link you want to delete. If you remove the link for the Linked Field with the blue outline, all corresponding Loaded Fields will also be removed.
If you need to change the Linked Field, you can simply drag it to the desired field without unlinking all fields and resetting the Link and Load.
Example:
On the sheet "Sales Order," the Customer Name is linked from the Customer sheet. However, since there may be different customers who share the same name, it's better to change the Linked Field to "Customer ID" instead, which can identify customers more effectively.
To change the Linked Field, simply click and hold the left mouse button on the "Customer Name" field, drag it to the "Customer ID" field, and release the left mouse button to complete the change.
Then you can adjust the field names, and users will now select values from the "Customer" sheet based on the "Customer ID". Previously saved records will also have the "Customer ID" as the link.
When setting up Link and Load, fields in the source sheet that cannot be linked will appear gray and cannot be selected to prevent incorrect links.
Below are some common linking mistakes:
The Linked Field is one type of Selection field (select from another sheet ).
To ensure each selection is uniquely identifiable, the Linked Field must come from a Unique Value field on the source sheet (e.g., ID number, serial number). If some fields on the right-hand side appear grayed out during setup, it's usually because they are Selection fields, Linked fields, or Numeric fields, which typically do not qualify as Unique Value fields and cannot be used as the Linked Field.
(1) Selecting Subtables in the Linked Sheet
Since a Subtable can contain multiple values in an entry, whereas an independent field can hold only a single value, the system prevents you from storing multiple values in a single field. Consequently, the Subtable fields will appear grayed out and unavailable for selection from the source sheet in the Link Manager.
(2) Linking Independent Fields and Subtable Fields Simultaneously
A single set of Link and Load can only be configured for fields of the same type. For example, if the Linked Field is an independent field, the Loaded Fields must also be independent fields. The system will prevent you from linking Subtable fields after an independent field has already been linked.
Conversely, if the Linked Field is a Subtable field, then the Loaded Fields must also be Subtable fields.
(3) Linking Different Subtables Simultaneously
You cannot link different Subtables simultaneously because each Subtable stores data independently. This prevents the transfer of data selected in Subtable A to Subtable B. Once linked to one Subtable, the others will appear grayed out and cannot be selected.
There won't be any technical issues, but having different Subtables on the same row could lead to confusion.
It's recommended to link to another row of Subtables instead.
You cannot load a field again if it has already been loaded in another Multiple Versions sheet.
For instance, consider a "Delivery Note (For Sales)" and a Multiple Versions sheet named "Delivery Note (For Logistics)". In "Delivery Note (For Sales)", you have already loaded data from the "Customer Name" field.
In the Multiple Versions sheet "Delivery Note (For Logistics)", even if you delete the "Customer Name" field, you cannot select it again for loading (it appears greyed out). If you wish to add the field back, please refer to this article.
If a field on sheet A has its value loaded from sheet B, changes made to the value on sheet B will not be reflected in the previous entries on sheet A.
Assuming there is a sheet A called "Sales Orders" and sheet B called "Customer", when an order for Martha Stewart is finalized, her address information is populated using Link and Load from the "Customer" sheet. Suppose Martha changes her address in the "Customer" sheet. However, the previous orders should still display her original address at the time of the order, not the new address after moving.
In some cases, after modifying values on sheet B, you may want the Loaded Fields on sheet A to update synchronously. In such situations, you can go to Design Mode under the Form page and select Form Tools to see a list of sheets that are linked with Link & Load. Then, click on the gear icon next to the appropriate sheet.
In the pop-up window, you can choose:
1. To sync this set of linked fields or ALL linked fields with updated source values for all records at once.
2. To keep the loaded values always updated when the source sheet is updated, check the option Keep loaded value sync with source. This will trigger a sync whenever a record in the source sheet changes.
Note: The maximum limit for Keep loaded value sync with source in a single set of linked fields is 500 records. However, manually executing the link and load sync has no record limit.
The option to keep loaded values synced with the source data is turned off for imports. Users will need to perform a manual link and load sync after importing.
You can link and load field values from the system's User Sheet into your sheet using the Select User field. Please refer to this article for the instructions.
Since the Select User field is linked to the system's User Sheet, you can also click the gear icon in the "Field Settings" to open the "Dynamic Filter Setting".
For more detailed information about Link & Load, you can refer to the following tutorial video.
