Database Design Documentation
How can we help?
Full Site Search

Link and Load

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.

Link Manager

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.

Using Link and Load for a Subtable

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.

Selecting Multiple Subtable Rows at Once

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.)

Linking Multiple Sets of Link and Load to the Same Sheet

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.

Unlinking Fields

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.

Changing the Linked Field

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.

Common Mistakes

1. Linking to Non-Unique Value Fields (such as Selection Fields, Linked Fields, or Numeric Fields)

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.

2. Illogical Link and Load Setting Between Independent and Subtable Fields

(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.

3. Field Arrangement Issues With Link and Load

(1) Linking a Different Subtable on the Same Row

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.

(2) When Loading Fields That Include Cascaded Fields, Place the Parent Field Below or to the Right of the Child Field in the Same Row on the Target Sheet

The Ragic system loads data in an up-to-down, left-to-right sequence in the target sheet with Link and Load relationships. This means that when the Loaded Fields are placed in "different rows", the system will load the field values from top to bottom. If the Loaded Fields are in the "same row", it will load them from left to right.

When the Loaded Fields include Cascaded fields, the "Child field" will not load its value until the "Parent field" is loaded. Therefore, if you place the "Parent field" below or on the right side of the same row as the "Child field" on the target sheet, the "Child field" will not be able to load the corresponding value.

For instance, you have Loaded Fields that include Cascaded fields where "Car Brand" is the Parent field, and "Car Model" is the Child field. Placing the "Car Model" field above or to the left of the "Car Brand" field will prevent the "Car Model" field from loading its values correctly due to the improper placement of the Parent and Child fields. In this case, the Parent field "Car Brand" should be placed above or to the left of the Child field "Car Model".

4. Loading From a Field Which Has Already Been Loaded in Multiple Versions Sheets

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.

Syncing Linked and Loaded Fields

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 to Sync this set of linked fields with updated source values for all records or Sync ALL linked fields on this form with updated source values for all records.

To keep the loaded values always updated when the source sheet updates, you can check the option Keep loaded value sync with source.

Please note that the maximum limit for synchronizing data in a single set of Linked Fields is 500 records.

Note: 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.

Link & Load Sync for Select User Fields

Link & Load syncing can be applied to Select User Field which is linked to the system's users sheet. You can click the gear icon next to the field's settings to open the interface.

Video Tutorial

For more detailed information about Link & Load, you can refer to the following tutorial video.

Top of Page Table of Contents

See also

Start Ragic for Free

Sign up with Google