Skip to main content

Field Types & Their Comparators

The comparators valid inside a where predicate depend on the field's data type. The table below summarizes which operators each type supports.

Field typeSupported comparators
Text / String=, !=, like, not like, in, not in, is null, is not null
Number / Decimal / Currency=, !=, >, >=, <, <=, between, not between, in, not in, is null, is not null
Boolean=
Date / DateTime=, !=, >, >=, <, <=, between, not between, in, not in, is null, is not null
Enum / Picklist=, !=, in, not in, is null, is not null
Lookup=, !=, in, not in, is null, is not null (compares on related record id)
Multi-Lookupin, not in, is null, is not null

Text#

Use like with the % wildcard for partial matches:

  • like 'AC%' — starts with AC
  • like '%pump' — ends with pump
  • like '%filter%' — contains filter
  • not like '%draft%' — does not contain draft
select id, subject from workorder where subject like '%inspection%' limit 50

Number / Decimal / Currency#

select id, totalCost from workorder where totalCost between 1000 and 5000

Boolean#

select id, subject from workorder where isCompleted = true limit 50

Date / DateTime#

Date and DateTime field values can be supplied either as epoch time in milliseconds (a number) or as a quoted UTC string in the format 'YYYY-MM-DDTHH:mm:ssZ'. For example, 1735669800000 represents 2025-01-01T00:00:00+05:30. Range filtering uses between:

select id, createdTime from workorderwhere createdTime between 1735669800000 and 1743445799000

The same range can be expressed with UTC strings:

select id, createdTime from workorderwhere createdTime between '2024-12-31T18:30:00Z' and '2025-03-31T17:29:59Z'

Enum / Picklist (single & multi)#

Single-enum fields compare against their value. Multi-enum fields (such as multi_enum_field_workorder) hold a list; querying returns the selected labels.

select id, multi_enum_field_workorder from workorderwhere multi_enum_field_workorder in ('Electrical', 'Plumbing')

Lookup#

A lookup field stores a reference to a record in another module. Selecting the lookup field by itself (vendor, priority) returns the related record id as a flat scalar value — e.g. "vendor": 88 — not a nested object. To read fields from the related module, traverse with a dot (vendor.name, priority.displayName). See Lookup Traversal.

Filtering on a lookup uses its id:

select id, subject, vendor from workorder where vendor = 12345 limit 25

A record's status is also a lookup field (it references a state record), so it behaves exactly like any other lookup — selecting it returns the flat state id, and you traverse it to read a label:

select id, status, status.displayName from workorder where status = 'Open' limit 50

Multi-Lookup#

A multi-lookup field (e.g. multi_lookup_field_workorder) references several records. It returns a list of ids and supports membership filtering:

select id, multi_lookup_field_workorder from workorderwhere multi_lookup_field_workorder in (101, 102, 103)
On This Topic