Skip to main content

Data Filtering

This article introduces the custom filter in the worksheet, data filtering in the view and filters required in triggering and branch nodes in the workflow.

Type of Fields​

Different types of fields require different filtering methods.

  • Text type: Text, Telephone, Email, ID Number, Concat, Autonumber

  • Number type: Number, Amount, Formula, Rollup

  • Single-select type: Single-select, Region/City, Level, Members (single-select), Department (single-select), O-role (single-select), Relationship (single row)

  • Single-select type (two options): File (yes/no), Check items (checked/unchecked, on/off, yes/no), Positioning (empty/not empty), Signature (yes/no), Subform (empty/not empty)

  • Multi-select type: Multi-select, Members (multi-select), Department (multi-select), O-role (multi-select), Relationship (multiple rows)

  • Date: Time, Date, Date+Time

  • Relationship: Associated worksheet

Filtering Rules​

1. Support for multiple conditions or condition groups​

  • Multi-field Condition

    As shown below, the condition contains 2 or more fields.

    Condition 1 is that [Department is equal to Sales Department] and Condition 2 is that [Amount is greater than 5000].

    The relationship between the two conditions can be chosen as "and / or".

  • Condition Group

    You can set condition groups. Click [+ Condition Group] to add a condition group.

    As shown below, there are two condition groups, just match any one of them.

2. Empty/Not empty​

The filter of Empty/Not-empty is available for all types of fields. If there is a field value, it is not empty, and if there is no field value, it is empty.

"None" in the file field and "Unchecked" in the check items filed means empty.

If the purchase quantity (number field) is 0, it is not empty.

3. Text Field​

  • Equal/Not equal:

    Precise query. For example, if the condition is [Equal to "zero code"], it will only query the records where the text is completely "zero code", and if the text is "zero code platform", it does not meet the condition.

  • Contain/Not contain:

    Fuzzy query. For example, if the condition is [Contain "zero code"], as long as there is "zero code" in the text, like "zero code", and "zero code platform ", the record will be queried. If you select "Not contain", as long as the text has "zero code", the record will be excluded.

  • Start with/Not start with:

    After setting this condition, you can filter the records according to the beginning of the selected text field. For example, if the beginning of the text is "HAP", the records started with "HAP" will be found.

  • End with/Not end with:

    Query for records where the end of the text matches the condition value.

4. Date Field​

  • Equal/Not equal:

    It means the date is equal to or not equal to the specified date. For example, if the condition is [Date is equal to Today], the records of today are displayed.

  • If the date is accurate to minutes and seconds, and filtering date is today, only the date part is filtered. For example, if today is 2022-10-24, the date field in a field is 2022-10-24 10:00, and you set [Equal to Today] when filtering, so this record is matched.

  • When filtering records in the workflow, for the date fields 2022-9-1 and 2022-9-1 10:00 , the two fields are the same because they can only be compared to the date part and not precisely to the time.

  • Before/After:

    Condition values are not included. For example, if you want to get records created before today, records created today zre not included.

  • On or Before/On or After:

    Condition values are included. For example, to get records created before or today, records created today and before today are all included.

    If it is in date/time format, for example, the current time is 2022-9-23 10:00, if you set [On or before today], the record is included; if if you set [On or after today], the record is also included.

  • Within the range of:

    Condition values are included. For example, in the range from 4.22 to 4.25, the queried dates are {4.22, 4.23, 4.24, 4.25}.

  • Out of the range of:

    The condition value is not included. For example, if you set [out of the range from 4.22 to 4.25], the queried dates are dates before 4.21, and dates after 4.26.

Filtering of date and date/time fields​

1. When filtering, it supports matching only to the high level​

For date fields, there are several formats you can select from, such as Year-Month-Day, Year-Month, Year-Month-Day:Hour-Minute-Second, and so on.

For example, the date/time field of a record is 2022-09-23 10:00:00, and today is 2022-09-23.

  • This record is queried when the filtering condition is [Date is equal to Today].

  • This record is queried when the filtering condition is [Date within the range of 2022-09-01 to 2022-09-30].

2. For date fields, it can only be precise to the minimum level in custom filter​

For example, if the date field is formatted as Year-Month-Day, you can only filter to the day. If the format of the date field is Year-Month-Day Hour:Minute:Second, you can filter to the second.

5. Single-select Field​

The [is one of, not any of, equal to, not equal to] are available for fields like single-select, members (single-select), department (single-select), and o-role (single-select).

Example:

  • Equal/Not equal

    Precise query. For example, for task status (single select), if you set [Status equals Completed], only records with status "Completed" will be matched. If you set [Status not equal to Completed], records with status Not Started, In Progress, or Overdue will all be matched.

  • Is one of:

    You can set multiple condition values and the record matches any one of them. For example, to get tasks that are not completed, you can set the task status is any of {Not Started, In Progress}.

    The configuration is the same as the following two.

  • Not any of:

    You can set more than one condition value, which means "not any of the condition values". If you set [Status is not one of Not Started or In Progress], the task will not be matched, regardless of whether the status is "Not Started" or "In Progress".

Is one of/Not any of: you can set more than one fixed parameter; Equal/Not equal: the parameter can only be set one, while the dynamic parameters are all can be set only one.

6. Multi-select Field​

[Equal/Not equal, Is one of/Is none of/Contain all of] is available for fields such as multi-select, members (multi-select), department (multi-select), o-role (multi-select), etc.

  • Equal/Not equal:

    For example, if [Interest Equals Horse Riding, Singing] is set, only records that select both Horse Riding and Singing and no more other hobbies are queried. Records with [Horse Riding, Dancing], [Singing, Dancing], or [Horse Riding,Singing,Dancing] selected will not be queried.

  • Is any of:

    For example, if you set [Interest is one of Riding,Singing], records will be queried as long as either Singing or Horse Riding is selected, and both are selected as well. Records with [Horse Riding,Dancing], [Singing, Dancing], [Horse Riding, Singing, Dancing] selected will be queried.

  • Is none of:

    A record where none of the option values match the condition value is queried. For example, if you set [Interest is none of Singing, Dancing], the record will not be queried as long as Singing or Dancing is selected.

  • Contain all of:

    For example, if you set [Interests contains all of Singing, Dancing], only records with [Singing, Dancing] selected at the same time will be queried, and those with [Singing, Dancing, Basketball] selected will be queried as well.

7. Region/Department Field​

Take the following organization structure as an example:

  • Belong to/Not belong to:

    Example 1: Condition: [Department belongs to Sales Department]

    Records with the Sales Department or any of its sub-departments selected can be queried.

    If the department field is multi-select, the record can be queried as long as one of the selected departments meets the condition.

    Example 2: Condition: [Department belongs to Sales Department, Product Department]

    It means that if the selected department is a sub-department of Sales Department or a sub-department of Product Department, the records can also be queried.

  • 's subordinate contain/'s subordinate not contain:

    Example 1: Condition: [Department's subordinate contains North China]

    North China's superiors are the Sales Department and the North China. Records with these 2 departments selected will be queried.

    Example 2: Condition: [Department's subordinate contains "North China, Product Department"]

    The superiors for North China are Sales Department and North China, and the superiors for Product Department are only the Product Department. The records with these 3 departments selected will be queried.

    If the department field is multi-select, the record can be queried as long as one of the selected departments meets the condition.

Note: The structure of the department is set by the organization administrator in the [Members and Departments] section of the organization management page.

8. Relationship Field​

  • Equal Record ID

    For example, if you want to find out which contacts are associated with the current customer, the query condition can be set to [Associated customer record is one of the record IDs], where the record ID represents the customer record in the previous node.

9. Number/Amount/Formula/Rollup Field​

  • Within the range of: For example, the queried number is {1, 2, 3}, if the condition is set to [Within the range of 1 to 3], including the condition value.

  • Out of the range of: For example, if the condition is set to [Out of the range of 1 to 3], the queried number can be {... -1,0,4,5...} , not including the condition value.

Have questions about this article？ Send us feedback