Skip to main content

Data Filtering


Filtering is used throughout HAP, including custom filters in worksheets, view filters, and workflow triggers, branches, and search nodes. This article explains the available filtering options and how they work across different field types.

Field Types​

HAP provides filtering options based on field type. Fields in the same category generally support similar filtering operators.

  • Text Fields: Text, Telephone, Email, ID number, Concat, Auto Number
  • Numeric Fields: Number, Amount, Formula, Rollup
  • Single-Select Fields: Single Select, Region/City, Level, Members (Single Select), Department (Single Select), Organization Roles (Single Select), Relationship (Single Record)
  • Two-State Fields: File (Has/Does Not Have), Check item (Checked/Unchecked, On/Off, Yes/No), Positioning (Empty/Not Empty), Signature (Has/Does Not Have), Subform (Empty/Not Empty)
  • Multi-Select Fields: Multi Select, Members (Multiple Select), Department (Multiple Select), Organization Roles (Multiple Select), Relationship (Multiple Records)
  • Date Fields: Time, Date, Date & Time
  • Relationship Fields: Related Worksheet

Filtering Rules​

1. Multiple Conditions and Condition Groups​

  • Multiple Field Conditions

    You can combine two or more field conditions in a filter.

    In the following example:

    • Condition 1: Department equals Sales.
    • Condition 2: Amount is greater than 5,000.

    Choose AND or OR to define how the conditions are combined.

  • Condition Groups

    Filters also support condition groups. Click + Condition Group to add a group.

    In the example below, there are two condition groups. A record matches the filter if it satisfies either group.

2. Is Empty / Is Not Empty​

All field types support Is Empty and Is Not Empty.

  • Is Empty: The field has no value.
  • Is Not Empty: The field contains a value.

For Attachment fields, Does Not Have is equivalent to Is Empty. For Checkbox fields, Unchecked is treated as empty.

Note: A numeric field containing 0 is not considered empty. For example, a Purchase Quantity field with a value of 0 does not match Is Empty.

3. Text Filtering​

  • Equals / Does Not Equal

    Performs an exact match against the entire text value.

    For example, if the condition is Equals "no code", only records whose text value is exactly "no code" are returned. Records containing "no code platform" do not match.

  • Include / Does Not Include

    Matches records based on whether the text includes the specified keyword.

    For example, Includes "no code" matches both "no code" and "no code platform". Does Not Include "no code" excludes any record containing that phrase.

  • Also Include

    Matches records only when the text contains all specified keywords.

    For example, consider two records with the following values:

    • Record 1: "no code"
    • Record 2: "Build apps with no code"

    If the condition is Also Include "no code" and "apps", only Record 2 matches.

  • Starts With / Does Not Start With

    Filters records based on whether the text begins with the specified value.

    For example, Starts With "HAP" returns records whose text begins with "HAP" and excludes records that start with other text.

  • Ends With / Does Not End With

    Filters records based on whether the text ends with the specified value.

When filtering text fields, all operators except Equals and Does Not Equal support case-sensitive matching. To enable case sensitivity, click the Aa button on the right side of the input field.

4. Date Filtering​

  • Equals / Does Not Equal

    When filtering Date or Date & Time fields, you can use Equals or Does Not Equal with a specific date or relative date range.

    • Equals: Matches dates that fall within the selected date or time range.
    • Does Not Equal: Matches dates that fall outside the selected date or time range.

    The following options are available:

    Specific Date

    Select a specific date to filter records.

    For example, Contract Expiration Date Equals 2025-10-01 returns records with a contract expiration date of October 1, 2025.

    For Date & Time fields, you can also specify hours or minutes, depending on the field's display precision.

    Preset Past / Future Ranges

    Select a commonly used relative date range, excluding today by default. Options include:

    • Past 7, 14, or 30 days
    • Next 7, 14, or 30 days

    For example, Expiration Date Equals Next 30 Days returns records with expiration dates within the next 30 days.

    Custom Past / Future Ranges

    Select Past... or Next..., enter a number, and choose a time unit: years, quarters, months, days, hours, or minutes.

    For example: Completion Date Equals Past 5 Days.

    You can optionally select Include Today. When the time unit is months, quarters, or years, the option changes to Include This Month, Include This Quarter, or Include This Year, respectively.

    Whether the current period is included depends on the selected option.

    1. Available filtering options depend on the date field's display precision. For example, a field configured to display only the year or year and month does not provide more granular options such as days or hours.

    2. In workflow queries, when a Date field containing 2022-09-01 is compared with a Date & Time field containing 2022-09-01 10:00, the values are considered equal because the comparison is performed at the date level rather than the time level.

  • Before / After

    Excludes the specified boundary value.

    For example, Before Today returns records dated before today, excluding today itself.

  • On or Before / On or After

    Includes the specified boundary value.

    For example, On or Before Today returns records dated today or earlier.

    For Date & Time fields, suppose a record contains 2022-09-23 10:00. Both On or Before Today and On or After Today match the record when today is September 23, 2022.

  • In Range

    Includes both boundary values.

    For example, Between April 22 and April 25 matches April 22, 23, 24, and 25.

  • Out of Range

    Excludes the specified range, including its boundaries.

    For example, Not Between April 22 and April 25 matches dates before April 22 or after April 25, such as April 21 and April 26.

Differences Between Date and Date & Time Filtering​

1. Match at a Higher Date Precision Level​

Date fields support different display formats, such as year-month-day, year-month, and year-month-day hour:minute:second.

For example, suppose a record contains the value 2022-09-23 10:00:00, and today is September 23, 2022.

  • The record matches Date Equals Today.
  • The record also matches Date Equals This Month.

This is because date filtering can match values at a broader date precision level without requiring an exact match down to the time.

2. Filtering Precision Depends on the Field Format​

When configuring a custom date filter, the smallest available time unit depends on the field's configured display precision.

For example:

  • If the field format is Year-Month-Day, filtering can be configured down to the day.
  • If the field format is Year-Month-Day Hour:Minute:Second, filtering can be configured down to the second.

5. Single-Select Field Filtering​

Single Select, Member (Single Select), Department (Single Select), and Organization Role (Single Select) fields support Is One Of, Is None Of, Equals, and Does Not Equal.

The differences are explained below.

Examples:

  • Equals / Does Not Equal

    Matches a single specified value.

    For example, if the Task Status (Single Select) condition is Equals "Completed", only tasks with a status of "Completed" match.

    If the condition is Does Not Equal "Completed", tasks with statuses such as "Not Started", "In Progress", or "Overdue" match.

  • Is Any Of

    Allows multiple condition values. A record matches if its field value equals any one of the selected values.

    For example, to find tasks that are not completed, you can select Is Any Of and specify "Not Started" and "In Progress" as the allowed statuses.

  • Is None Of

    Allows multiple condition values. A record matches only if its field value does not equal any of the selected values.

    For example, if Task Status (Single Select) is configured as Is None Of In Progress, records with either of these statuses are excluded.

Is Any Of / Is None Of: You can specify multiple static values. Equals / Does Not Equal supports only one static value. All four operators support only one dynamic value.

6. Multi-Select Field Filtering​

Multi Select, Members (Multiple Select), Department (Multiple Select), and Organization Roles (Multiple Select) fields support Equals / Does Not Equal, Is Any Of / Is None Of, and Contains All.

  • Equals / Does Not Equal

    Matches the exact set of selected values.

    For example, if Interests Equals Singing, only records with exactly these two interests selected match.

    Records with Dancing, Dancing, or Dancing do not match.

  • Is Any Of

    Matches records containing at least one of the specified values.

    For example, if Interests Is Any Of Singing, records containing either "Horse Riding" or "Singing" match, including records containing both.

    Matching combinations include:

    • Dancing
    • Dancing
    • Dancing
  • Is None Of

    Matches records that do not contain any of the specified values.

    For example, if Interests Contains None Of Dancing, any record containing "Singing" or "Dancing" is excluded.

  • Also Include

    Matches records containing every specified value, even if additional values are also selected.

    For example, if Interests Also Include Dancing, records with both "Singing" and "Dancing" match.

    Records with Basketball also match.

7. Department and Region/City-Specific Filtering​

The following examples use the organizational structure shown below:

  • Belongs To / Does Not Belong To

    Example 1: Department Belongs To Sales

    Records match if the selected department is Sales or any of its subdepartments at any level.

    For multi-select Department fields, a record matches if at least one selected department meets the condition.

    Example 2: Department Belongs To Product

    Records match if the selected department belongs to either the Sales or Product department hierarchy.

  • Includes Subordinate / Does Not Include Subordinate

    Example 1: Department Includes Subordinate "North China"

    The parent departments of North China are Sales and North China Headquarters.

    Records associated with either of these two departments match.

    Example 2: Department Includes Subordinate Product

    The parent departments of North China are Sales and North China Headquarters, while Product has only Product Headquarters as its parent department.

    Records associated with any of these three departments match.

Note: Organization administrators configure the department hierarchy under Or Admin > User & Department.

8. Relationship Field-Specific Filtering​

  • Equals Record ID

    For example, to find Contact records linked to the current Customer record, configure the Contact worksheet filter as below.

    In this case, the Record ID refers to the Customer record referenced by the preceding workflow node.

9. Filtering Number, Amount, Formula, and Rollup Fields​

  • Between: Includes both boundary values. For example, Between 1 and 3 matches 1, 2, and 3.

  • Not Between: Excludes the specified range, including its boundaries. For example, Not Between 1 and 3 matches values such as -1, 0, 4, and 5.

Was this document helpful?