Creating Relationship

Overview

A Relationship in Zoho Creator is a link between two Forms. These Forms can be in the same Application or across different Applications in the same account. 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 Application named Employee Manager to manage the details of the employees in your organization. In it, you create a Form called Employees to enter employee details like Name, DOB, Email, Date of Joining, Department etc. While entering these details, you may find that one or more employees belong to the same department. This means that you will have to enter the department name again and again (for each employee). This leads to wastage of time and gives room for duplication of values (eg: "Engineering", "Enginering"). Moreover, when it comes to the departments in your organization, apart from the Department Name, you may also need to maintain other information like Department Head, Contact ID, etc. To manage data efficiently and avoid duplication of values, you may -

  1. Create another Form called Department to store information related to all the departments in the organization, and,
  2. Create a Relationship between the Department and Employees Forms.

To create this Relationship, you just need to add a Lookup Field in the Employees Form such that it displays data from the Department Name Field in the Department Form.

The Lookup Field will lookup the ID of the record in the source Form (Department, in the above example) and accordingly display data from it. You can also fetch and update records from the related Form using the ID Field. Refer the topic Fetch data from a Form and use it in another Form to see examples.

Create a Relationship using single-select Lookup Field

Consider the example of the Employee Manager Application described above, which contains the Department and Employees Forms. Typically, an employee will be assigned to only one department (one-to-one or 1:1) and hence a Relationship between the Employees and Department Form is created using single-select Lookup Field. To do this,

  1. Add a Lookup Field in the Employees Form
    1. The Create Lookup Relation dialog box will open. By default, you will be on the New Relation screen, where you can create a new Relationship between Forms. You may view any existing relation by clicking on Existing Relation.
    2. Select the Application, Form and Field from which the data is to be displayed. In the screenshot given below, it is, Employee Manager - Department - Department Name.
    3. Select the Display Type as Dropdown or Radio-button. If you chose Dropdown, the data will be displayed as a dropdown menu. If you chose Radio-button, values stored in the looked-up Field (Department Name, in this case) will be displayed with a radio-button beside them.
    4. Click Done to include the Lookup Field in your Form.
    5. Now in the Field Properties, specify the Lookup Field's name, for example, Department​.
  2. In the Employees Form, the Department Lookup Field will display all the departments from the Department Form.
    ​​

Create a Relationship using multi-select Lookup Field

Continuing with the example of the Employee Manager Application (mentioned above), assume you have a Form called Roles that stores the different type of employee-roles in your organization . Typically, an employee can be assigned with one or more roles (one-to-many or 1:M) and hence a Relationship between the Employees and Roles Form is created using multi-select Lookup Field. To do this,

  1. Add a Lookup Field in the Employees Form.
    1. The Create Lookup Relation dialog box will open. By default, you will be on the New Relation screen, where you can create a new Relationship between Forms. You may view any existing relation by clicking on Existing Relation.
    2. Select the Application, Form and Field from which the data is to be displayed. In the screenshot given below, it is, Employee Manager - Roles - Role.
    3. Select the Display Type as Multi Select.
    4. Click Done to include the Lookup Field in your Form.
    5. Now in the Field Properties, specify the Lookup Field's name, for example, Roles.
  2. In the Employees Form, the Roles Lookup Field will display all the roles from the Roles Form. You can assign one or more roles to an employee by selecting the desired options.

Create a Bidirectional Lookup

A Lookup being Bidirectional means that the data stored in the two related Forms are associated or linked bi-directionally. In the section Creating a Relationship using single-select Lookup Field, we added a Lookup Field Department in the Employees Form. This created an Employees -> Department Relationship, which defined how each employee was associated or linked with a department. Now if this Lookup were to be Bidirectional, then the below two should happen -

  1. Each employee is associated with a department (already achieved through the single-select Lookup Field Department in the Employees Form).
  2. Each department is associated with an employee data, i.e., Departments Form should display the information of employees working in each department.

There are two ways to create a Bidrectional Lookup Field,

  1. By using an existing relation
    1. A Relationship between Forms can be created using existing relations as well. On adding the Department Lookup Field in Employees Form, the Employee-Department relation got created.
    2. Now go to the Department Form and add a Lookup Field in it. In the Create Lookup Relation dialog box, go to the Existing Relation screen and select the Employee-Department relation. (This relation appears to be available as the two Forms are already related)
    3. Select the required Field (the data you want to display in the Department Form) and the Display Type, and click Done.
    4. Now in the Field Properties, specify the Lookup Field's name, for example, Employees.
    5. Now, as shown below, the Department Report will list the employee working in each department. This enables you to view and manage the employees belonging to a department from both the Employees and Department Form.
    6. Now if you go to the Employees Form. Check the Field Properties of the Department Lookup Field. In the Bidrectional Relation section, the Bidrectional Relation checkbox will appear checked. You may also note that the Related Field and Display Type parameters would hold values that you set while adding the Employees Lookup Field in the Department Form, as described above.
       
  2. Checking the Bidirectional Relation checkbox
    1. Add a Lookup Field in any one of the Forms that you are looking to relate.
    2. After this, check the Bidirectional Relation checkbox in the Field Properties of your Lookup Field.
    3. A dialog box Choose bidirectional lookup field will open. Select the Fields to be Related (Fields of the current Form) and the Display Type. Click Done.​
    4. Now go to the other Form. You would see a Lookup Field added there automatically. (The Email Field was shown to be selected in the above given screeshot. On checking the Birectional Relation checkbox, Zoho Creator will automatically provision a Lookup Field in the other Form (in the Relationship). You may note that the Lookup Field's name will be in the Form_Name - Field_Name format. In the below screenshot, the Employees - Email Lookup Field is the was created by Zoho Creator.)
Note:
  • In the above screenshot, the relation between departments and employees is one-to-one (1:1), i.e., each department has one employee associated with it. This was achieved by configuring the two Lookup Fields Department (in Employees Form) and Employees (in Department Form) as single-select Lookup Fields.
  • In general, departments to employees relation would be one-to-many (1:M) as each department would have many employees in it. To achieve this, while adding the Employees Lookup Field in Department Form, set the Display Type as Multi Select (in the Create Lookup Relation dialog box).
  • If a many-to-many (M:M) relation is to be established, the Display Type of the Lookup Fields in the related Forms should be Multi Select.

Create Combined Reports from related Forms

Combined Reports are Reports which can display data from one or more related Forms. Consider the Employee Manager Application (discussed above) which had three Forms - Employees, Department and Roles. Relationships between these Forms were created using Lookup Fields. A Combined Report based on these would provision a single-point to view the data stored in all three of them. Refer Combined Reports, for more information.

Customizing the Display of a Lookup

You can customize the data that the Lookup Field is to display using the Display Fields section in Field Properties. As shown in the below given screenshot, you can select the Fields and mention if any separator is to be used between the Field values, and Preview how the display will be.

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 based on Form_A to display the employee names in the format First Name - Last Name. In the below given screenshot, the Department Lookup Field in the Employees Form displays the values in the format: Department Name - Department Head.

Adding new entries to the Source Form

In the Field Properties of the Lookup Field, there is a checkbox Allow new entries. To provision the adding of new entries to the source Form (from the Lookup Field present in the destination Form), this should be checked. On checking this, two additional parameters appear - Link Name and Align.

As shown below, the Add Departments option appears in the Lookup Field. This is the link to add new entries to the Department (source) Form from the Department Lookup Field of the Employees (destination) Form.

On clicking Add Departments, the Department Form is displayed, as shown below. The new entry is also updated in the Department Lookup Field of the Employees Form.

Create a link to the records of the source Form

Data displayed in the Lookup Field can be hyperlinked such that on clicking it, the source Form's records can be viewed. To do this,

  1. Select the Report of the Form to which the Lookup Field is added.
  2. In the Report Settings -> Column Properties, click on the link icon displayed beside the Lookup Field as shown below.
  3. Select the Report from the Select the view to be linked dropdown.
  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 hyperlinked as shown in the screenshot below.

Fetching Data from related Forms

The Lookup Field will lookup the ID of the record in the source Form. By using the ID field, you can fetch and update records from related Forms. Refer the topic Fetch data from a Form and use it in another Form, for a sample Deluge script.

Set Lookup Filter

You can use the Set Filter option in Field Properties of the Lookup Field to restrict the data that will be displayed by it. In other words, a criteria can be employed to filter or selectively display data from the source Form. Refer this help topic for more information.

Configure Lookup Field across Applications - Example

A Lookup Field can be configured to fetch data from other Zoho Creator Applications as well. Consider this sample Application Teachers. It has a Form named Teachers Report which contains the following Fields:

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

This Form is meant for teachers to allocate grades to students. Now consider another sample Application Students. It has a Form named Students Report which contains the following Fields:

  1. Name (Lookup Field displaying data from Student Name Field of the Teachers Report Form)
  2. Grade (Single Line Field)

This Form is meant for students to enter their name and check the Grade allocated to them. For example, if a teacher gives the Grade "A" to the student "John" in the Teachers Report Form, then on entering "John" as the Name in the Students Report Form, the allocated Grade should be automatically fetched. To achieve this,

  1. Name Field in Students Report Form should be a Lookup Field that displays data fetched from the Student Name Field of the Teachers Report Form.
  2. In the Teachers Report Form, go to Workflow > Functions > New Function and click Create Function.
     
  3. A dialog box will open. Select Write script / Copy sample function, add the following Deluge script and click Create Function.
string externalapps.GetType(int name)      //GetType is the name of the Function
{
     if (Teachers_Report[ID == input.name].count() > 0)      //"Teachers_Report" is the Form Link Name
     {
          return Teachers_Report[ID == input.name].Student_Grade;      //"Student_Grade" is the Field Deluge Name
     }
     return "";
}

In the Students Report Form, go to Workflow > Fields section > Name Field > On User Input, add the following Deluge script in it and click Save Script.

input.Grade = teachers.externalapps.GetType(input.Name);     //"teachers" is the Application Link Name