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.
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).
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.
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.
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.
In Ragic, fields should be linked to only the fields in same set of table (linking to independent fields only; or linking to fields in one subtable only).
Below is an example of incorrect linking (linking to both independent and subtable fields).
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.
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 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 "Car Model" field on top of or on the left side of "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 “Car Model” (child field).
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 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.
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.
Here are video tutorials showing how the link & load type of linking can be used with your other sheets.