Linked Field & Loaded Fields

Linking two fields in different forms together consists of creating a Linked Field and the corresponding Loaded Fields.

Let's say that we want to create a form for entering sales orders, in which we would like to include the related customer information, linked from another form that keeps all our customer data.

The Linked Field would be the field in which the user could select a Customer ID for this sales order.

A linked value is configured as a field linked to the Selection Title Field of another form. The selection title field is basically the name to identify an entry in a selection box. The "Customer ID" field in the sales order in this example connects to the "customer ID" field in the customer form. Therefore, it's a good practice to choose a field that can clearly and uniquely identify an entry as the Selection Title Field.

When the user chooses a Customer ID by either clicking on the field, or typing in the sales order form, Ragic can automatically load the customer's information, such as their phone number and address into the sales order. This can greatly reduce data entry work and will also give users a more responsive feeling to your application. We call these type of fields Loaded Fields.

Link Manager

The Linked Field and Loaded Fields are really easy to set up using our Link Manager. On the Form page, just go to the Design Mode from the "Change Design" button on the right side, and click on the Link & Load option in the Form Tools menu, listed under Sheet Linking Tools.

The Link Manager will ask you to set up the Linked Field first. You can create the Linked Field by clicking on the field you would like to load on the right side of the page (which shows the form you will be loading data from) and click on the corresponding field on the form on the left side (the form that you are editing the design of). Note that this field will automatically be linked to the Selection Title Field of the form that is being linked, as we described above. The Link Manager will display the Linked Field with a blue outline.

After creating the Linked Field, you can link the Loaded Fields to the corresponding cells. These fields will load the corresponding values when the user selects a value in the linked field. Creating Loaded Fields is done the same way as the Linked Field. Note that the Link Manager displays the Loaded Fields with a gray outline.

You don't have to create the corresponding fields for the form that will include the linked fields beforehand in both steps, as the Link Manager will automatically create the field for you.

Using Link & Load for a Subtable

You may link & load data from other forms as a subtable. This works best if you would like to manually select (or type to see options) in one field of the data, and load the corresponding information in other fields in a row of the subtable, such as having multiple line items for products in a sales order.

Linking Multiple Sets of Link & Load to the Same Sheet

When using link & load for a subtable isn't appropriate, you may link & load data from a form in multiple sets to your sheet. This works best in cases where you'd like to load multiple instances of data that have similar information, such as having multiple legal guardians assigned to one student in the example below.

First we link the first legal guardian from our "Guardians" sheet as usual. On the top right, you'll see a dropdown menu that allows you to create a new data link from the sheet you're linking data from.

Creating a new set of link will allow you to use link & load from the same data source again.

It is recommended to edit your field names on the sheet you have linked to, to make sure that the information can be clearly defined when a user is browsing your sheet.

Once you're done and have saved your design, you will be able to link & load from the same source sheet.

This feature is also useful when you'd like to link data from the sheet you're linking to.

Unlinking Fields

If you would like to remove an existing link, you can do so by going to the link manager, and clicking on the link you want to remove. This will display a red icon on the sheet you're linking from. Click on this icon to remove the link for these two fields on Loaded Fields.

If you remove the link for the Linked Field with the blue outline, links you created for all Loaded Fields will also be removed.

Common Mistakes when Creating Links with Link & Load

1. Linking data from subtables to your form.

This type of link will not be permitted in the link manager, as you cannot link from a subtable and load to a regular field (from a subtable on the right side of the link manager to the left side), since a subtable could have many different values entered in an entry, but a regular field would only have one value.

2. Linking a different subtable on the same row.

This would not have any technical issues on Ragic, but bear in mind that if you have other users that use the same account, having different subtables on the same row could lead to confusion.

3. Linking from a linked field or numeric field as the linked field

The linked field is one kind of selection field (select from other sheet), in order to identify each selection unique, the linked field should be linked from a key value that can identify each entry on the source sheet as an unique entry, which is usually the id number or serial number field. Thus, you cannot link from a linked field or a numeric field as the linked field.

Repopulating Loaded Fields from their Source Sheet for Link & Load

For fields that load data on a sheet A, that links data from another sheet B with the link & load method, changes that are made on the data in sheet B will not be reflected on sheet A on previous entries.

The reason for this we can explain with an example. Let's say sheet A is sales orders, and sheet B is customers. When a sales order is entered by user Martha Stewart, her address information is populated with link & load on your sales order. Let's say Martha changed her address. Her previous sales order would still be under her previous address for the records, but new sales orders will be saved with her new address.

We do understand this might not be how you use link & load, and that you will need to repopulate the loaded fields in all your previous records in sheet A.

To do so, navigate to the Form page Design Mode, and click on Form Tools to see a list of sheets that are linked with Link & Load under the category. Click on the gear icon next to the appropriate sheet.

A prompt will show you the option for dynamic filtering above, and the option for repopulating below, that gives you the option to syncronize a single set of linked fields with the updated source value for all records, or all of your linked fields on this form.

After choosing your option, a prompt will state that loading has started. This means that your loaded fields are reloading in the background, so you may now close this window and continue using your database while your data is syncronizing.

To keep the loaded values updated, you can check the box next to the option "Keep Loaded Value Sync With Source". Every time the source data changes, Ragic will check your entries for records that are loading values from this source, and will execute a Link & Load sync for all the records that load values from the specific record that has been edited.

Please note that the option to keep loaded values synced with the source data is turned off for imports. Users will need to do a manual link & load sync after doing an import.

As an alternative method for using the gear icon to repopulate loaded fields, the following URL (modified according to your own account) will run a script that will force the loaded fields to reload according to the current data in sheet B, provided you still have the same info on the linked field, like an ID number.

If the URL link of the sheet you want to run this script is

https://www.ragic.com/accountname/tabname/3

Use this URL to reload all loaded fields according to the current data from where it is linked from:

Changing the linked field

A good rule of thumb is to use the linked field to be a link from a key field, which can identify each record as a unique entry on the source sheet (on the right side of the link manager). If you'd like to change a linked field that is already set, you can simply drag the linked field indicated in blue without unlinking all of your fields and re-setting the link & load.

Example:

On the sheet "Sales Order", there's a set of link & load from the sheet "Customer", which uses the field "Customer Name" as the linked field. This is not the most ideal field to be set as the linked field, since there may be different customers who share the same name. In data management practices, it's best to use a unique identifier to eliminate user error. We will change the linked field to "Customer ID" instead, so that we can identify customers in a better way.

To change the linked field to "Customer ID" (right side), you can first unlink this field to remove the gray line that indicates this field is a loaded field.

We change the linked field from "Customer Name" to "Customer ID" by dragging the blue linked field on the right side within the link manager.

The linked field is now linked from the field "Customer ID" on the right side.

You may want to adjust the field names on the sheet you are designing. The field names do not affect links. Now the users would select the value from the sheet "Customer" according to "Customer ID". The previously saved records will also have the Customer ID as the linked field value.

Video Tutorials

Here are video tutorials showing how the link & load type of linking can be used with your other sheets.