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.
The Linked Field and Loaded Fields are easy to set up in the Link Manager. On the Form page, navigate to the Design Mode by clicking the "Change Design" button on the upper right side. On the design mode, click on Form Tools and choose Link & Load under the Sheet Linking Tools options.
In the Link Manager, you will see two sheets. The one on the left is the sheet you're currently in, and the other one on the right is the sheet from which you're going to load data. To create links, you are required to set up the Linked Field first by simply clicking on the key fields of both sheets (e. g. Customer ID, Product ID, or other fields with unique field value). This allows the system to identify and map each record correctly in the source sheet and the target sheet, respectively.
Note that this field will be automatically linked to the Selection Title Field of the form that is being linked, as described above. The Linked Fields are displayed in 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. The Loaded Fields are displayed in a greyish-black outline.
In the Link Manager, you don't have to always create the corresponding fields for the form that will include the linked fields beforehand in both steps. Clicking a key field from the right sheet and dragging it to an empty cell on the left sheet will also automatically create linked fields for you.
If the sheet has existing link and load fields, you can focus on the field and Go to link manager in left side panel as well.
There is a blue L icon in linked field; a grey L icon in loaded field.
Clicking on the icon, system will automatically highlight all of the fields in the same set of Link & Load.
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.
If there is a Select from other sheet field (linked field) in the subtable, you can apply the Multiple subtable rows at once configuration to select entries and populate multiple subtable rows at once.
Note: You can only check either Multiple subtable rows at once or Multiple select configuration in a linked field.
You will need to close the window manually after the data entry.
(If the Multiple subtable rows at once configuration is not checked, the window will close after you select one entry).
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.
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 independent fields and subtable fields or fields from different subtables at the same time
In Ragic, you cannot link and load independent fields and subtable fields in the same set, nor can you apply it to different subtables.
Below are examples of incorrect linking:
You cannot link to a subtable field and load to an independent field.
Also, you can't link to an independent field to load other subtable fields.
Or link and load a set of data to different subtables.
3. Linking a different subtable on the same row.
This would not have any technical issues on Ragic, but if you have other users using the same account, having different subtables on the same row could lead to confusion.
4. Linking from a non unique field, especially a linked field or numeric field as the linked field.
The linked field is one kind of selection field (select from another 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 a 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.
5. When loaded fields include cascaded fields in which “parent field” is placed below or on the right side of the same row as the “child field” in the target sheet.
Ragic system loads data in an up-to-down, left-to-right sequence in the target sheet with link and load relationships. That is to say, the system will load the field values up-to-down wise when the loaded fields are placed in different rows, and left-to-right wise if the loaded fields are in the same row.
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” in the target sheet, the “child field” will not be able to load the corresponding value.
Here's an example:
You have loaded fields which include cascaded fields where “Car Brand” is the “parent field” and “Car Model” is the “child field”. If you put the "Car Model" field on the top of or on the left side of the "Car Brand" field, the "Car Model" field will not be able to load its values correctly due to improper placement of parent and child fields. In this case, as a parent field, “Car Brand” should be placed on top of or on the left side of the “Car Model” (child field).
You cannot load a field again if it has already been loaded in another multiple versions sheet.
For instance, your database has a "Delivery Note (For Sales)" and a multiple versions sheet called "Delivery Note (For Logistics)". In "Delivery Note (For Sales)", you have already loaded from the "Customer Name" field.
In the multiple versions sheet "Delivery Note (For Logistics)", even if you deleted the "Customer Name" field, you cannot select to load it again (greyed out). If you would like to add the field back, please use this method.
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 synchronize 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 synchronizing.
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.
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.
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.