Creating Relationship

Overview

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.

Creating a Relationship Using Single-select Lookup Field

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

• The Create Lookup Relation dialog will open. By default you will be on the New Relation screen, where you have to create a new relationship between forms. Click on Existing Relation to view the relations that were already created.
• Import data by selecting Employee Manager - Department - Department Name where, Employee Manager is the application name, Department is the form name, Department Name is the field whose data is imported.
• Select the Display Type as Dropdown/Radio-button (Single select).
• Click Done to import the data.
• Now in the Field Properties, specify a field name, for example, Department

2. In the Employee form, the Department lookup field will display the name of all the departments from the Department Form.

Creating Relationship with Multi-Select Lookup Field

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.

• The Create Lookup Relation dialog will open. By default you will be on the New Relation screen, where you have to create a new relationship between forms. Click on Existing Relation to view the relations that were already created.
• Import data by selecting Employee Manager - Roles - Role where, Employee Manager is the application name, Add Role is the form name, Role is the field whose data is imported.
• Select the Display Type as a multi-select lookup.
• Click Done to import the data.
• Now in the Field Properties, specify a field name, for example, Roles.

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.

Creating relationship from existing relations

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,

1. Add a lookup to the Department Form. In the Select Form drop-down, the existing relationships will be listed under the head Existing Relations, as shown in the screen-shot below. For example, select Form as Employee - Department, select the required lookup field and the display type and click on Done to add the lookup field.
   
2. In the Department view, the Employees belonging to a department will be listed, as shown below. You can now manage the Employees belonging to a department from both the Employee form and Department form.

Creating Combined Reports from Related Forms

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.

Customizing the Display of a Lookup

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.

Adding new entries to the Parent Form

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.

Creating a Link to Parent Records

You can create a link to the lookup field to view the parent record details.

To do this,

1. Select the report of the form to which the lookup is added.
2. Select Report Settings -> Column Properties and click on the Link icon displayed beside the name of the lookup field, as shown in the screen-shot below.

   

3. Selecting the report from Select the view to be linked dropdown list will create a link to the parent form.
4. Click on Related Forms on the right bottom, and drag and drop the columns to be shown.
5. Now, When you access your application and view the data, the lookup field values will be linked to the parent form, as shown in the screen-shot below.

   

Creating a Bi-directional relationship

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

1. Check the option Bidirectional Relation under the head Bidirectional Relation in Field Properties, as shown in the screen-shot given below.
2. Select the field to be related in the current form from the drop-down list.
3. Select the display type of the related field as a single-select or multi-select list.
   
4. A bi-directional relationship is created between the Employee and Department Forms. The Bi-directional field is the Email-id field in the Employee form.
5. In the Employee Form, the field Department is a single-select lookup to the Department Form, as shown in the screen-shot below.
   
6. The Email Id field in the Employee Form which is configured as the bi directional field of type multi-select, is added to the Department form, as shown in the screen-shot below.
   

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.

Fetching Data from related forms

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.

Setting Lookup Criteria

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.

Configure Lookup field across applications - Example

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:

1. Student Name (Single Line)
2. Student Grade (Dropdown with choices A, B, C and D)

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:

1. Name (Lookup to Student Name field in Teachers Report)
2. Grade (Single Line)

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: