A relationship is a link between two forms in Zoho Creator, within the same application or different applications. When you create a relationship, you tell Zoho Creator to link a record in one form to one or more records in another form. Assume that you are creating an Employee Manager application, to manage the employee details in your organization. You create a form called Employee to enter employee details like Name, DOB, Email, Qualification, Department etc. When you enter the details, you find that one or more employees belong to the same department and you have to enter the department name, again and again. This leads to wastage of time and duplication of values. Moreover, you also need to maintain other information related to each department, like department head, contact ID etc. To manage data efficiently and avoid duplication of values, we will create another table called Department to hold information of all the departments in the organization and create a relationship between the Department and Employee forms. To create the relationship, you just add a lookup field in the Employee form to import data from the field Department name in the Department form.
The lookup field will lookup the ID of the record in the source form. It displays a dropdown menu containing items from the source form. You can fetch and update records from the related form using the ID field. Refer the topic Fetch records from a form and use it in another form, for the sample script.
Let us take the example of the Employee Manager application described above, with the Department and Employee forms. Typically an employee will be assigned to only one department and hence a relationship between the Employee and Department form is created using single-select lookup field. To do this,
1. Add a Lookup field in the Employee form
2. In the Employee form, the Department lookup field will display the name of all the departments from the Department Form.
Continuing with the Employee Management application, assume you have a form called Roles that stores the different type of employee roles. Typically an employee can be assigned to one or more roles and hence a relationship between the Employee form and Role form is created using multi-select lookup field. To do this,
1. Add a Lookup field in the Employee form.
2. In the Employee form, the Role lookup field will display the name of all the Roles from the Role Form. You can relate one or more roles to each employee by selecting the required options.
You can create a relationship from existing relations. For example, in the section Creating a relationship using single-select lookup, we added Department as a lookup to the Employee Form and created a Employee -> Department relationship. Now, you can add the same relationship to the Department form and make it a bi-directional relationship. This will enable you to track the related items from both the Employee and Department form. To create a bi-directional relationship from existing relations,
You can create combined reports that display the required fields from one or more related forms. For example, we created three forms - Employee form, Department form and Roles form and created relationship across these forms using Lookup fields. Refer Combined Reports, for more information.
You can customize the display of the lookup field by selecting the Display Fields options as shown in the screen-shot given below. You will have an option to construct the display value using any separator between the fields that you choose.
For example, if Form A stores the name of an employee in two different fields - "First Name" and "Last Name", you can create a lookup to Form A to display the name field in the format First Name - Last Name. In the screenshot shown below, the Lookup field Department displays the values in the specified format (Department Name - Department Head) in the Add Employee Form, as shown in the screen-shot given below.
Selecting the option "Allow new entries in <FormName>" will add a link with the specified text to the right or the bottom of the lookup field. Users will be able to add new entries directly to the parent form by selecting this link.
In Add Employee form shown below, the lookup field "Department" displays the link to add new entries directly to the Add Department (Parent Form).
The Parent form (Add Department) is displayed from Add Employee Form, as shown in the screen-shot below.
The new entry is also updated in the lookup field in Add Employee form.
You can create a link to the lookup field to view the parent record details.
To do this,
You can create a Bi-directional lookup to track the related items from both the Parent and Child form.
Steps to create a bi-directional lookup
In the above example, the lookup field (Department) is of type single-select and the bi-directional related field (Email Id) is of type multi-select. A one-to-many relationship is established between the Department and the Employee Forms. (i.e) one department can have many employees. The employee details can be managed from both the Employee and Department forms.
If the lookup is of type single-select and the bi-directional related field is also of type "single-select", a one-to-one relationship is established. For example, in the Employee form, if Department is added as a single-select lookup with Employee email-id as a single-select related field, then a department can be assigned to only one employee. Assigning the same department to another employee, will remove the existing relationship.
If the lookup is of type multi-select and the bi-directional related field is of type multi-select, a many-to-many relationship is established. For example, in the Employee form, if Department is a multi-select lookup with Employee email-id also as a multi select related field, one department can have many employees and each employee can belong to more than one department.
The lookup field will lookup the ID of the record in the main form. You can fetch and update records from the related form by using the ID field in Deluge script. Refer the topic Fetch records from a form and use it in another form, for the sample script.
You can use the Set Criteria option in lookup fields to restrict the entries that will be displayed in the lookup field, based on a given criteria. Please refer this help topic for more information.
A lookup field can be configured to fetch data across applications. Lets say we have an Application named Teachers with a form named Teachers Report which contains the following fields:
Teachers use this form to allocate grades to their students. We have another application named Students with a form named Students Report which contains the following fields:
Students use this form to enter their name and check their grades allocated by teachers. The aim is to use a lookup field in Students Report to automatically fetch data from Teachers Report. For example, if a teacher gives Grade A to John in Teachers Report form, John must be able to view his grade by just selecting his name in the Students Report form. To achieve this, in Teachers Report, go to Workflow > Functions > New Function > select Write script / Copy sample function, add the following code and click on Create the function:
In the Student Report form, go to Workflow > Name field> On User Input, add the following code and click on Save Script: