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.
Create lookup based on a specific value
Let us take the example of the Library Manager application, which consists of the following forms:
- Book form with the fields (Name, Author, Status). The Status field can have one of the following values 'Available' / 'Issued'
- IssueBook form with the fields (BookName, IssueDate and IssuedTo). Here, the field 'BookName' is a look up to the 'Name' field of 'Book' form.
By default, the field 'BookName' in 'IssueBook' form will display all the books present in the 'Book' form. But our requirement is to list the books with Status as 'Available' in the 'BookName' picklist field. We can easily achieve the above requirement by giving the following criteria for the look up field 'BookName'. In the screen-shot given below, the deluge criteria Status="Available", is added to the BookName lookup field.
Create lookup based on login user
Another widely used scenario is restricting the picklist entries based on the user who logs into the application. For example, assume that an organization has created an appraisal form to be used by managers to appraise his subordinates. When a manager logs in, only his subordinates must be available for choosing in the employee picklist. In this case, the lookup field will be the 'Employee_Name' from 'Employee' form and the 'Set Criteria' will be ManagerMailID == zoho.loginuserid, where ManagerMailID, is the deluge field name in the Employee form which holds the skydesk login emailid of the manager and zoho.loginuserid returns the email id of the user who has logged into the application.
Create Dynamic Lookup
You can dynamically add items to a picklist based on selection in a previous picklist. Let us take the sample of the Dynamic Picklist application, which comprises of the following forms:
- Form named "Countries" with the following fields:
- Name: Text field to enter the name of the country
- Form named "States" with the following fields:
- Country Name: Lookup field imported from the Countries form
- State Name: Text field to enter the name of the States belonging to a country.
- Form named "Registration Form" with the required fields like Name, Email etc. Add the Country, and States field from their respective forms as a lookup field to the Registration form,
- While creating the States lookup, enable the Set filter option in Field Properties and add the following criteria to display only the States belonging to the selected country.
- Click on Done to add the criteria in the Set Criteria text box. The criteria will be added in the Set Criteria text box as shown below:
Country_Name.ID == input.Country
In the above criteria,
- Country_Name.ID refers to the ID of the "Country_Name" lookup field in the "States" form
- input.Country refers to the "Country" lookup field in the current form, (i.e) Registration Form.