Conditions to filter records based on Creator fields | Zoho Creator Help

Conditions to filter records based on Creator fields

In a nutshell
Learn about the criteria that you can set to filter records and run workflows within the Creator application.

1. Overview

Conditions help in filtering records from a Zoho Creator report. Based on the criteria set in a condition, actions such as report filtering, data aggregation in page elements and workflow automations can be configured further.
You can add multiple criteria to a condition. These criteria can be related by two logical operators:
  1. AND - All mentioned criteria in the relation needs to be met
  2. OR - At least one criterion in the relation needs to be met
The final structure of a condition can look like this:
This example is made up of three outer criteria, with criteria 2 having two inner criteria.
  1. Choosing AND or OR operators at the end of a criteria will add inner criteria.
  2. Clicking + Add New lets you add outer criteria with AND or OR operators. By default, the AND operator is chosen.
  3. Clicking the bin icon next to a criteria will remove it from the list.

Now, let's look at the structure of a single criteria:
  1. Select Field - Choose the Creator field for which this criteria needs to be evaluated.
  2. Select Operator - Choose an operator that either defines the comparison between the field and the entered value (such as greater than, equals), or is a category for the value (such as days, yesterday)
  3. Enter Value - Input a value based on which the criteria will run.

1.1. Use cases

1. Multiple Criteria Configured in a Report using Custom Filters
A Project Management application tracks all projects and their timelines. To monitor the progress of different country-based teams, an All Projects report has been configured with three custom filters, USA, Mexico, and Canada, each using its own set of criteria. The filter criteria are as follows:
Custom Filter 1: USA
Team field equals California (OR) Team field equals Texas (OR) Team field equals Florida
Custom Filter 2: Mexico
Team field equals Jalisco (OR) Team field equals Mexico City
Custom Filter 3: Canada
Team field equals Ontario (OR) Team field equals British Columbia (OR) Team field equals Quebec
In the live mode, when a specific custom filter is selected, the projects that were undertaken in that state will be displayed. This ensures that all data is maintained in the same report while creating a categorization that can be made use of only when necessary.


2. Criterion Configured for a Workflow
In an Event Management Application, an Assign Vendors form lets the customer assign a vendor of the customer's choice for managing an event. While an external vendor can coordinate independently when assigned, if the internal team is designated as the vendor, a preset condition is satisfied. This initiates a blueprint to streamline and manage the full process of the internal team. Therefore, here, the blueprint initiation will be on the condition:
Assigned Vendor field is not equal to Internal team

3. Multiple Criteria Configured for a Page Element
In an Order Management application, a dashboard panel displays the number of big orders that have been placed. The count is based on the All Orders report. To filter only the big orders, a criteria to count only the records that have the following criteria is configured:
Overall Price field greater than or equal to $500,000 (OR)
Product Quantity greater than or equal to 5000 (AND)
Overall Price field greater than or equal to $300,000

1.2. Supported scenarios for criteria

You can set criteria in multiple scenarios within an application:
  1. Forms
    1. Lookup field filters - You can set a filter for the lookup field in a form. This configuration will display only the values from the looked-up form that meet the specified criteria in the live mode of the current form.

    2. Advanced field search for lookup fields - Enable this feature to allow users to search for records in the lookup field's live mode extensively using basic criteria Is, Is not, Is empty, Is not empty.
  2. Reports
    1. Filtered display of matching records - Set a filter to display only a set of records that match the criteria in a report. All users will be able to access only the matched records in the live mode.
    2. Dynamic live mode filters - Create multiple custom filters each with different criteria. These custom filters will be displayed as a dropdown in the live mode of the report. According to the selected criteria, the appropriate set of records will be displayed.
    3. Conditional record formatting for specific fields - Style a set of records from a report based on a criteria. For example, Completed Projects can be marked in green background, while New Projects are marked in blue.
    4. Action execution based on filters - Set a filter based on which an action item (report workflow) gets executed for records displayed on a report.
  1. Pages
    1. Filtered report - Embed a report that only displays records based on a specific criteria.
    2. Visual page elements - Configure a chart based on selective records that are filtered by a criteria. You can also build a gauge or panel with arithmetic operations such as calculating the median, finding the maximum value, and record count, based on a Creator field's input values. The records that will be taken into account for these operations can be based on a specific criteria.
    3. Criteria-based search element - Insert a search bar (page element) that will only filter records based on a criteria and display them in the search result.
  2. Workflows
    1. Criteria-based action-trigger for form workflows - Set a criteria for specific action blocks to get executed based on the condition.
    2. Criteria-based workflow initiation triggers - Criteria can be defined during workflow creation to control when they are to be executed:
      1. Schedules that are based on a Creator field
      2. Approval workflows
      3. Payment workflows
      4. Batch workflows
      5. Blueprints

    1. Criteria-based blueprint transition triggers - Configure blueprint transitions to be triggered based on a specific criteria.
    2. Criteria-based report workflows - While creating report workflows by configuring an action item from within a report, you can set a criteria based on which those actions need to be executed.
    3. Criteria-based workflow data access action - Configure to add, update, and delete records based on a criteria inside the Data access action of form workflows, schedules, and approvals workflows.

1.3. Supported Creator fields and operators

You can configure a criteria only based on certain Creator fields. Each field will support different operators based on their data types and purpose. This is discussed in detail in the below table.
Supported Fields
Supported Operators
  1. Name (Prefix, Suffix, First Name, Last Name)
  2. Email
  3. Address (Line 1, Line 2, Postal Code, State/Province, City/District, Country)
  4. Phone
  5. Single line
  6. Multiline
  7. Drop Down
  8. Radio
  9. Multi Select
  10. Checkbox
  11. Rich text
  12. Users
  13. AR
  14. Prediction
  15. Keyword extraction
  16. Sentiment Analysis
  17. OCR
  18. Object Detection
  19. Added User IP Address (system field)
  20. Modified User IP Address (system field)
  1. is null
  2. is not null
  3. equals
  4. not equal to
  5. starts with   
  6. ends with
  7. contains
  8. does not contain
  1. equals
  2. not equal to
  3. less than
  4. less than or equal to
  5. greater than
  6. greater than or equal to
  7. is null
  8. is not null
  1. equals
  2. not equal to
  3. before
  4. after
  5. is null
  6. is not null
  1. Date
  2. Date -Time
  3. Added Time (system field)
  4. Modified Time (system field)
  1. equals
  2. not equal to
  3. before
  4. after
  5. is null
  6. is not null
  7. Day (yesterday, today, tom, n days ago, last n days, next n days)
  8. Week (last week, this week, next week, n weeks ago, last n weeks, next n weeks)
  9. Month (last month, this month, next month, n months ago, last n months, next n months)
  10. Year (last year, this year, next year, n years ago, last n years, next n years)
  1. equals
  1. Added User (system field)
  2. Modified User (system field)
  1. is null
  2. is not null
  3. equals
  4. not equal to
  5. starts with   
  6. ends with
  7. contains
  8. does not contain
  9. is Adminuser
  10. is Loginuser
Notes
Note: The following fields are not supported for configuring a criteria: URL, Image, Signature, File upload, Audio, Video, QR/Barcode, Section, Add Notes, Integration.

1.4. Supported input values for criteria

The input value is the value based on which the operators work. The type of input value one can enter depends on the type of Creator field selected. This is discussed in detail in the table below.
Supported Fields
Supported Values
  1. Name (Prefix, Suffix, First Name, Last Name)
  2. Email
  3. Address (Line 1, Line 2, Postal Code, State/Province, City/District, Country)
  4. Phone
  5. Single line
  6. Multiline
  7. Number
  8. Rich text
  9. Percent
  10. Currency
  11. Decimal
  12. Autonumber
  13. Users
  14. Prediction
  15. Keyword extraction
  16. OCR
  17. Object Detection
  18. ID (system field)
  19. Added User (system field)
  20. Modified User (system field)
  21. Added User IP Address (system field)
  22. Modified User IP Address (system field)
  1. Any input value
  2. Global variables
  1. Any input value
  2. Deluge system variables (zoho.loginuser, zoho.adminuser, zoho.loginuserid, zoho.adminuserid, zoho.appname, zoho.ipaddress, zoho.appuri, zoho.currentdate, zoho.currenttime)
  3. Global variables
  1. Date
  2. Date -Time
  3. Added Time (system field)
  4. Modified Time (system field)
  1. Any input value
  2. Pick calendar date/time
  3. Global variables
  1. Decision Box
  2. Formula
  1. true
  2. false

2. Points to note

  1. While setting criteria for a lookup, subform, or a composite field (such as address or name field), you can choose and set criteria distinctively for all the fields within them.
  2. In the live mode of an application's report, the search filter feature () allows you to filter and list records based on their field values, using a query builder. The operators for this feature include Is, Is Not, Is Empty, Is Not Empty, Starts With, Ends With, Contains, Not Contains.