Criteria to fetch records

[ <condition_1><logical_operator_1><condition_2> <logical_operator_2> ..... <condition_n>]

<field_link_name><operator><expression>

<field_link_name>.<operator>(<expression>)

(input.email == "tony@zylker.com" &&  input.phone == "+91xxxxxxxxxx")

Example of using criteria in NUMBER type fields

• Example 1: Fetch records based on the currency field
  The following example demonstrates how to fetch all orders where the Order Amount is greater than 10,000 and the Order Status is Confirmed:

  High_Value_Orders = Orders[Order_Amount > 10000 && Order_Status == "Confirmed"];
  info High_Value_Orders;
  //Returns: Orders[ ID in (4364236xxxxx1352013,4364236xxxxx1352113)]

• Example 2: Fetch and update values in the number field
  This example shows how to fetch records from the Returned Items form where the status is set to Inspected, and then update the corresponding stock values in the Inventory form. The criteria matching is done using the Product_ID stored in both forms, and the product’s stock quantity in the Inventory form is increased based on the returned item quantity:

  //Fetch all inspected returned items
  returned_items = Returned_Items[Status == "Inspected"];
  
  for each item in returned_items
  {
      // Get product ID and quantity from returned item
      Return_product_ID = item.Product_ID;
      Return_product_Quantity = item.Quantity;
  
      // Update stock in Inventory by fetching the product based by matching the Product_ID of products in    Inventory _Form and the Return_product_ID
  Add_inventory = Inventory_form[Product_ID ==  Return_product_ID];
  
      if(Add_inventory.count() > 0)
      {
          Add_inventory.Stock = Add_inventory.Stock + Return_product_Quantity;
      }
      }

• Example 3: Criteria to fetch the most recently added record
  The following example demonstrates how to fetch the most recently added record from a form. To achieve this, all records are retrieved using the ID system field, then sorted in descending order of Added_Time, and finally restricting the range to the first record:

  Fetch_Records = Form_link_name[ID != 0] sort by Added_Time desc range from 0 to 0;

DATE-TIME fields

Example of using criteria in DATE-TIME type fields

• Example 1: Fetch and update records based on the date field
  Fetch delivery records from the Deliveries form that are scheduled on an emergency national holiday, then automatically reschedule them to the next day and update the delivery Status using the following script:

  //Specify the emergency holidays in a list
  holidays = ['08-Sep-2025', '15-Sep-2025'];
  
  //Fetch deliveries scheduled on a holiday
  holiday_deliveries = Deliveries[Delivery_Date in holidays];
  
  for each delivery in holiday_deliveries
  {
          old_date = delivery.Delivery_Date;
          
          // Reschedule delivery to next day using addDay() function.
          new_date = old_date.addDay(1);
          delivery.Delivery_Date = new_date;
          delivery.Status = "Rescheduled due to Holiday";
      }

• Example 2: Fetch records with date fields values for the next day
  The following example demonstrates how to fetch all tasks from the Tasks form that are due tomorrow, and send a notification email to the task owner:

  //Fetch tasks due tomorrow
  tasks_due_tomorrow = Tasks[Due_Date == tomorrow];
  
  //Loop through the fetch tasks and send email to the task owner
  for each task in tasks_due_tomorrow
  {    
         
     sendmail
      [
           from    : zoho.adminuserid
           to      : task.Owner_Email
           subject : "Task Due Tomorrow: " + task.Task_Name
           message : "<div>Dear " + task.Owner_Name + ",<br><br>Your task <b>" + task.Task_Name + "</b> is due tomorrow.<br>Please make sure to complete it on time.<br><br>Thank you,<br>Task Management Team</div>"
          ];
      }

• Example 3: Fetch records based on date field values between two dates
  The following example demonstrates how to fetch records from a form where the Date field values fall between two specified dates:

  //Fetch records where Delivered_Date is between 1st September 2025 and 15th September 2025.  
  Fetch_Records = Form_link_name[Delivered_Date >= '01-09-2025' && Delivered_Date <= "15-09-2025"];  
  info Fetch_Records;
  //Returns: Form_link_name[ID in (4364236xxxxx1352003, 4364236xxxxx1352004)]

BOOLEAN fields

<collection_variable> = <Form>[<decision_box>];
//implies the decision box value is true

<collection_variable> = <Form>[!<decision_box>];
//implies the decision box value is false

Examples of using criteria in BOOLEAN type fields

• Example 1: Fetch records with unchecked decision box fields
  Fetch all task records from the Tasks form where the Completed decision box is unchecked using the following script:

  pending_tasks = Tasks[!Completed];
  info pending_tasks;
  //Returns: Tasks[ ID in (4364236xxxxx1352003,364236xxxxx1347011,4364236xxxx1347)]

  (OR)

  pending_tasks = Tasks[Completed == false];

  (OR)

  pending_tasks = Tasks[Completed != true];

List fields

Examples of using criteria in LIST type fields

• Example 1: Fetch records using a lookup field with a related form
  The below example fetches the records from the Orders form where the Customer lookup field matches a specific customer based on a condition in the related Customer form.

  // Fetch the ID of the customer whose name is "Max"
  Customer_ID = Customer[Customer_Name == "Max"].ID;
  
  // Fetch all orders for that customer
  customer_orders = Orders[Customer == Customer_ID];

• Example 2: Fetch the records based on a multiselect field
  The following example demonstrates how to fetch records from a form where the values in a multiselect field match a specified list of items:

  // Create a list of items
  items_list = List();
  items_list.add("Item A");
  items_list.add("Item B");
  items_list.add("Item C");
  
  // Fetch records where the multiselect field matches the items in the list
  fetch_records = Form_Link_Name[Multiselect_Field == items_list];
  info fetch_records;
  
  //If any one of the items specified in the list is present in the multiselect field of a record, that record will be fetched and returned in the response.
  
  //Returns: Form_link_name[ ID in (4364236000001352xxxx,436xxxx000001352035) ]

• Example 3: Fetch the records in multiselect field with contain operator 
  This example shows how to fetch records from the Products form that store product details, and then retrieve only the products that are cloud-based. The filtering is done using the Product_Features multi-select field, where any product record with at least one value in the field containing the string 'Cloud' (such as Cloud Backup or Cloud Integration) will be matched and fetched:

  // Fetch products where the multiselect field contains the text "Cloud"
  cloud_products = Products[Product_Features.contains("Cloud")];
  
  //Since 'Cloud' is used in an expression with a contains operator here, it will be treated as string. All features containing the word Cloud (such as Cloud Backup or Cloud Integration) will be matched and the respective records will be returned. 
  
  // Loop through and print product names
  for each product in cloud_products
  {
      info product.Product_Name;
  }
  //Returns:
  Zoho Creator 
  Zoho CRM 
  Zoho Books

• Example 4: Fetch records records based on the multiselect lookup with the ID's fetched in another form
  The following example fetches the record IDs from the Products form where the stock is below the 10 units. These IDs are then used to fetch matching records from the Vendors form, where the Supplied_Products field is a multiselect lookup referring to the Products form. This helps identify which vendors supply the low-stock products.

  // Fetch the list of IDs of records where stock is low using fetch records and getall() function.
  low_stock_products = Products[Stock < 10].ID.getAll();
  
  // Fetch vendors who supply those products
  vendors_for_restock = Vendors[Supplied_Products == low_stock_products];
  
  // Loop through and print vendor details
  for each vendor in vendors_for_restock
  {
      info "Vendor: " + vendor.Vendor_Name;
      info "Contact: " + vendor.Contact_Email;
  }
  // Returns:
  Vendor: Zylker
  Contact: zylker@zylkermail.com

Use case scenarios

• Case 1: Filtering candidates by skills
  Consider a recruitment agency that uses a Zoho Creator application to manage candidate information in an Add_Candidates form, which includes a multiselect field named Skills to capture each candidate’s areas of expertise. To fetch candidate records of those proficient in either Python or Java, criteria can be applied to filter records where the Skills field contains at least one of the required skills, as shown in the script below:

  Fetch_skills = Add_Candidates[Skills.contains("java")||Skills.contains("python") ];
  //Returns: Add_Candidates[ ID in (4364236xxxxx1352003,364236xxxxx1347011,4364236xxxx1347)]

• Case 2: Identifying tasks assigned to specific employees
  Consider a project management company that uses a Zoho Creator application to track employee assignments across different projects. The application includes an Employees form that stores employee details, such as name, department, and designation. It also includes a Tasks form, where each record captures a task and has a single select lookup field named Assigned_To that links to the Employees form to identify the employee responsible for that task.
  To view only the tasks assigned to employees belonging to the IT department, fetch the record IDs of all employees in the IT department from the Employees form. These IDs are then supplied as criteria in the Tasks form to fetch the list of matching tasks, as shown in the script below:
   

  //Fetch employees from IT department and create a list with their record ID's using getall() function.
  IT_Employee_IDs = Employees[Department == "IT"].ID.getall();
  //Fetch tasks assigned to these employees and store the Task_Names from the fetched records as list using getall() function.
  IT_Tasks = Tasks[Assigned_To in IT_Employee_IDs].Task_Name .getall();
  //Returns: Task A,Task B,Task C

• Case 3: Identifying vendors serving specific regions with active contracts
  Consider a procurement company that uses a Zoho Creator application to manage vendor information and their supply capabilities. The application includes a Vendors form that captures vendor details such as name, contact information, and contract status. It also includes a lookup multiselect field named States_Served, which is linked to a States form to record the specific states where each vendor operates.
  To view only the vendors who serve either California or Texas and currently have an Active contract, fetch the record IDs of the relevant states from the States form. These IDs are then used as criteria in the Vendors form, along with the contract status condition, to fetch the list of matching vendors, as shown in the script below:

  //Define a list of state names.
  Region_list = list();
  Region_list.add("California");
  Region_list.add("Texas");
  
  // Fetch the IDs of records in the States form where the Location field matches the given state names in the Region_list.
  States = States[Location in Region_list ].ID.getall();
  
  // Fetch the active vendors who serve these states by matching the  States_Served multiselect field with the list of ID's in States variable. 
  Vendors_List = Vendors[States_Served == States && Contract_Status == "Active"];
  //Returns: Vendors[ ID in (4364236000001349003,4364236000001349007,4364236000001349007) ] 

• Case 4: Filtering high-value orders requiring priority shipping
  Consider an e-commerce company that uses a Zoho Creator application to manage customer orders. The application includes an Orders form with fields such as Order Amount, Order Status, and Shipping Type.
  To focus on processing and handling high-value orders promptly, identify all high-value orders where the shipping type is set to Priority and the status is Not Confirmed. The Deluge script to achieve this is shown below:

  HighValue_Orders = Orders[Order_Amount > 50000 && Shipping_Type == "Priority" && Order_Status == "Not Confirmed"];
  
  //Returns: Orders[ ID in (4364236xxxxx1349003,4364236xxxxx1349007,4364236xxxxx1349007) ] 

• Case 5: Filter upcoming portal subscription renewals
  Consider an IT service company that uses Zoho Creator provides a customer portal where users can manage their subscriptions. The application has a Subscriptions form with fields such as Customer Name, Subscription End_Date, Subscription Status, and User Email.
  To notify portal users in advance of subscription expiration, fetch all active subscriptions that are due for renewal within the next two weeks, and send mail to the corresponding users using the Deluge script below:

  // Fetch active subscriptions expiring within the next 2 weeks
  Renewals_Due = Subscriptions[Subscription_Status == "Active" && Subscription_End_Date in next 2 weeks];
  
  
  
  // Loop through the subscriptions and send notification to the users
  for each subscriber in Renewals_Due
  {
      sendmail
      [
          from    : "support@zylker.com"
          to      : subscriber.User_Email
          subject : "Your subscription is expiring soon"
          message : "Dear " + subscriber.Customer_Name + 
                    ", your subscription will expire soon. Please renew via your portal account to avoid service interruption." +      
      ];
  }

• Case 6: Retrieving recent records created by the logged-in user
  Consider a Zoho Creator application used by a vendor to manage orders. The application includes an Orders form that records details such as Order ID, Order Date, and Name. To allow users to view only their own recent orders, fetch all order records from the Orders form where the Added_User field matches the logged-in user and the record Added_Time falls within the last week using the Deluge script below:

  //Fetch orders created by the logged-in user in the last week
  recent_orders = Orders[Added_User == zoho.loginuser && Added_Time in last week].ID.getAll();
  
  info recent_orders; 
  //Returns: Orders[ID in (987654xxxxx2345, 98765xxxxx12346, 98765xxxxx12347)]

• Case 7: Fetching records based on nested lookup.
  Consider an organization that uses a Zoho Creator application to manage projects and employee assignments. The application has three forms: Departments, Employees, and Projects. The Employees form includes a lookup to the Departments form, and the Projects form includes a lookup to the Employees form. To identify all projects managed by employees belonging to the Finance department, fetch the department ID of the Finance department, and then use it in the Projects form criteria through the nested lookup. This ensures that only the projects associated with employees from the finance department are retrieved.

  //Fetch the department ID of the "Finance" department
  fetch_DepartmentID = Departments[Department_Name == "Finance"].ID;
  
  //Fetch all project records where the employee belongs to the "Finance" department
  Fetch_projects= Projects[Employee_Lookup.Department_Lookup == fetch_DepartmentID];
  
  //Returns: Projects[ID in (9876xxxxx012345, 987xxxxx1012346, 987654xxxxx2347)]

Limitations

• Fetch records criteria restriction

  • When applying criteria on Name or Address composite fields, or system fields such as Added_Location and Modified_Location, the same fields cannot be used as the expression of the criteria.

    //The following operation will throw an error.
    Fetch_Records = Form_Link_Name[Name == Name];
    Fetch_Records = Form_Link_Name[Address == Address];

  • Fields such as Lookup (Multi Select), Subform, Multi Select, and Check Box store values in a list format and cannot be compared with single select fields, such as Radio, Drop Down, Lookup (Drop Down), or Lookup (Radio), using the '==' operator.

    //The following operation will throw an error.
    Fetch_Records = Form_Link_Name[MultiSelect == Radio];
    Fetch_Records = Form_Link_Name[MultiSelect_Lookup == SingleSelect_Lookup];

  • When applying criteria with Multi Select field types, such as Lookup (Multi Select), Subform, or Check Box, the same type of fields cannot be used in the expression of the criteria.

    //The following operation will throw an error.
    Fetch_Records = Form_Link_Name[ MultiSelect
    _Lookup == MultiSelect ];
    Fetch_Records = Form_Link_Name[ MultiSelectField_A == MultiSelectField_B ];
    Fetch_Records = Form_Link_Name[ SubForm == SubForm ];

    • Multi Select
    • Check Box
    • Lookup (Multi Select)
    • Subform
    • Single_Select_Lookup.MultiSelectField - when a single select lookup field (dropdown or radio) references a multi select field in the parent form.

  //The following operation will throw an error.
  Fetch_Records = Form_Link_Name[MultiSelect.contains({"Choice 1", "Choice 2"})];

  //Instead, use the following method to fetch records.
  Fetch_Records = Form_Link_Name[MultiSelect.contains("Choice 1") && MultiSelect.contains("Choice 2")];

• When applying criteria for Multi Select, Lookup (Multi Select), Subform, or Check Box fields, the list expression cannot contain more than six elements.

  //The following operation will throw an error.
  Fetch_Records = Form_Link_Name[ MultiSelect == {"Choice 1", "Choice 2", "Choice 3", "Choice 4", "Choice 5", "Choice 6", "Choice 7"} ];
  Fetch_Records = Form_Link_Name[ MultiSelect IN {"Choice 1", "Choice 2", "Choice 3", "Choice 4", "Choice 5", "Choice 6", "Choice 7"} ];

• Fields such as Lookup (Multi Select) and Subform cannot be used with subfields of type Multi Select, Lookup (Multi Select), Subform, or Check Box in the format <field>.<subfield> in fetch records criteria.

  //For example, let's consider a Lookup_Form with a Multi Select field, which is linked to a Main_Form using a Lookup(multiselect) field.
  
  //If you fetch the records based on the Lookup(multiselect)field with the following method will throw an error.
  Fetch_Records = Form_Link_Name[Multi_Select_lookup_field.Multi_Select_field == {"Choice 1", "Choice 2"}] ;

  //Instead, use the following method to fetch records:
  //Fetch the IDs from the lookup form that match the required Multi Select values.
  fetch_ID = Lookup_Form[Multi_Select_field IN {"Choice 1", "Choice 2"}].ID.getAll();
  
  //Use the fetched IDs to fetch records in the main form.
  variable = Main_Form[Multi_Select_lookup_field in fetch_IDs];

Best practices

• Use equalsIgnoreCase instead of '== ' for text based comparisons to perform case-insensitive matching.
• Using the OR (||) operator in criteria is less efficient compared to the AND (&&) operator, as it requires evaluating multiple conditions separately. Use alternative conditions or logic wherever possible to optimize performance.
• Use mandatory or unique fields such as ID, Email, or User name for faster and more reliable record fetching.
• Use the contains operator only on short text fields to maintain performance.

Related links

• Fetch records
• Data types of Zoho Creator fields
• Logical operators
• Relational operators