Queries, matching, and ordering

A Search starts with a profile and may add a table, free text, column conditions, ordering, a limit, and an offset. The useful combinations are:

Goal	Table	Free text	Column conditions
Open a table picker	Required	Empty	None
Search within one table	Required	Optional	Optional
Search across a profile	Omitted	Optional	Optional
Sort matching rows by a column	Required	Optional	Optional

A request must contain free text or at least one column condition unless it is listing a selected table.

Free text

Free text searches all searchable values in a row. Every word in the query must be matched somewhere in that row, but the words do not have to come from the same column.

For example, the query:

Acme Bratislava

can match a row whose customer value contains “Acme” and whose city value contains “Bratislava”. Rows where the words occur together and in a similar order receive a stronger relevance score.

Matching is case-insensitive and folds accents to their basic ASCII form. A query for cafe can therefore match Café.

Punctuation separates ordinary search words. Individual word tokens longer than 80 characters are ignored, which prevents unusually long unbroken values from dominating the index.

Search favors, in order:

1. complete word matches;
2. prefixes of words;
3. nearby words and phrases;
4. tolerated spelling differences and three-character fragments.

Short terms of up to three characters do not receive typo tolerance. Terms of four to six characters allow one edit, and longer terms allow up to two. These rules reduce noisy matches for short codes while still supporting ordinary misspellings in names and descriptions.

Column conditions

A column condition means “this public column must match this value.” All supplied conditions are required, and free text is also required when it is present.

Suppose an invoice table contains customer, status, and number. A request can express:

free text: annual
customer: Acme        fuzzy
status: issued        exact

A result must contain the free-text word annual, must fuzzily match Acme in customer, and must have the complete normalized value issued in status.

If the selected public column does not exist, Search rejects the request instead of silently returning no rows. Without a table selection, Search resolves that public name across all tables in the profile and matches only a table-and-column pair that actually owns it.

Fuzzy mode

Fuzzy mode behaves like free-text matching but stays inside the selected column. Every word in the condition must match that cell. It supports complete words, prefixes, limited misspellings, nearby multi-word phrases, and substring-like matching through three-character fragments.

Unspecified matching mode is treated as fuzzy.

Exact mode

Exact mode compares the complete cell value after trimming surrounding whitespace, lowercasing it, and folding accents. It is not a substring search.

For a stored value of Café Central :

Exact query	Matches?
cafe central	Yes
CAFÉ CENTRAL	Yes
cafe	No
central	No

Use exact mode for statuses, codes, categories, and other fields where a partial or misspelled value would be misleading. Use fuzzy mode for names, descriptions, addresses, and user-entered text.

Relevance and result cut-off

Without explicit ordering, matches are returned from highest to lowest relevance. Search also removes candidates scoring below one quarter of the best result’s score. This keeps a strong match from being followed by much weaker incidental matches.

The default limit for a populated search is 60 results. The default limit for an empty-query table listing is 5. A caller can request another limit, but the server caps it at 200. The offset defaults to zero.

Apply the same query and ordering when requesting later pages. Because the text index updates asynchronously, inserts or edits between page requests can change ranking and shift page boundaries.

Ordering

Ordering is available only when one table is selected, because a public column can have different types and meanings in different tables. The direction must be ascending or descending.

An order can name:

• a public column from the selected table;
• a public system column such as created_at;
• row_display_columns, meaning the table’s first configured row display column, or id when none is configured;
• position, meaning the row’s position in ascending ID order.

Null values sort last when ordering by a column. Equal values use ascending row ID as a stable tie-breaker.

Ordering a populated search does not discard relevance. The server first places results into broad relevance bands, keeps stronger bands first, and applies the requested column order inside each band. This prevents a weak textual match from outranking a strong one merely because its date or name sorts earlier.

An empty-query listing has no relevance bands. Without an explicit order, it sorts by descending position, which means higher row IDs first.

Position is not a stored sequence

Position is recalculated over non-deleted rows in ascending ID order. Deleting a row can therefore change the displayed positions of later rows.

Choosing a search shape

• Use an empty query with a table for the initial contents of a picker.
• Use table-scoped free text for a general picker search.
• Add fuzzy column conditions when the user fills separate search fields.
• Use exact conditions for codes and controlled values.
• Search the whole profile for a global search screen, and expect permission filtering to make some pages shorter.
• Add ordering only when the UI needs predictable business ordering inside relevance groups.

For the indexed data lifecycle and the shape of returned rows, return to How Search works.