Filtering(Querying) Table Records
There is only one field that you can use to decide how you want records to be filtered. Filter : Text type field. This field is a required field. Table records will be filtered(query) by this. If you…
There is only one field that you can use to decide how you want records to be filtered.
- Filter: Text type field. This field is a required field. Table records will be filtered(query) by this. If you need to use master table fields to compare with subtable’s field, use that master table field id as “~Field ID~”.
Examples: {6.EX.~3~}
{6.EX.~3~}AND{15.GT.0}{6.EX.~3~}AND({7.EX. 'Group A'}OR{7.EX. 'Group B'})
You can see some of syntax and list of component examples below from Quickbase help site.
Comparison Operator | Description | Applicable field types |
CT | Contains either a specific value or the value in another field of the same type. | Do not use this operator with list-user fields; instead, use HAS. |
XCT | Does not contain either a specific value, or the value in another field of the same type. | Do not use this operator with list-user fields; instead, use XHAS. |
HAS | Contains a specific set of users. For each user you are trying to find, you must enter the user's ID, user name, or email address. You can also enter placeholder names. Be sure to surround placeholder names with double quotes. | Used with list-user fields and multi-select text fields only. |
XHAS | Does not contain a specific set of users. The query parameter must contain the user's ID, email address, or user name. You can also specify a placeholder name. Placeholder names must be enclosed in double quotes. The entire query parameter must be surrounded by single quotes. Separate users in the list using semi-colons. | Used with list-user fields and multi-select text fields only. |
EX | Is When specifying values to query from List - User and Multi-select Text fields, enclose the entire query parameter in single quotes. Separate the values you're looking for using semi-colons. When you use a query like 10.ex. where 10 is a user field, ex is going to compare to the textual representation that the builder has configured on field props which is either full name, last name first, or username (which is email unless configured otherwise). | Is equal to either a specific value, or the value in another field of the same type. |
TV | True Value (compares against the underlying foreign key or record ID stored in relationship fields). When you use tv, you are comparing against the unique value of the user which is either the hashed UID like 123456.abcd OR the email address OR the screen name. | Also used for queries on User fields. |
XTV | Not True Value (compares against the underlying foreign key or record ID stored in relationship fields). | Also used for queries on User fields. |
XEX | Is not Is not equal to either a specific value, or the value in another field of the same field type. | When specifying values to query from List - User and Multi-select Text fields, enclose the entire query parameter in single quotes. Separate the values you're looking for using semi-colons. |
SW | Starts with | Starts with either a specific value or the value in another field of the same type. |
XSW | Does not start with | Does not start with either a specific value or the value in another field of the same type. |
BF | Is before | Is before either a specific value or the value in another field of the same type. |
OBF | Is on or before a specific date | Is on or before either a specific date or the value in another date field |
AF | Is after a specific date | Is after either a specific date or the value in another date field |
OAF | Is on or after a specific date | Is on or after either a specific date or the value in another date field |
IR | Is in range. | Use this operator with date fields, to determine whether a particular date falls within particular date range relative to the current date. |
XIR | Is not in range. | Use this operator with date fields, to determine whether a particular date falls within a particular date range relative to the current date. |
LT | Is less than | Is less than either a specific value or the value in another field of the same type. |
LTE | Is less than or equal to | Is less than or equal to either a specific value or the value in another field of the same type. |
GT | Is greater than | Is greater than either a specific value or the value in another field of the same type. |
GTE | Is greater than or equal to | Is greater than or equal to either a specific value or the value in another field of the same type. |
Sample queries
To query a particular field and exclude a string:
{'7'.XCT.'John'}
To query any field for a date:
{'6'.OAF.'4/31/01'}
To combine multiple query strings:
{'9'.SW.'this'}OR{'10'.XSW.'that'}
How did we do?
Table Main Fields
Sorting Table Records
