Cheat Sheet for searching through your entries

There are a lot of different search terms that you can type in the “quicksearch” boxes at the top of each column in the list of entries pages. Here is a brief summary:

The basics

Numbers

Dates

Duration

More than one search on the same column

You can put several search terms in one box, joined by AND or by OR. Type them in capitals, with a space on either side.

If you use OR in more than one column at a time, all of those terms end up in a single group, so an entry only has to match one of them. See “OR searches on multiple columns” below for how that works, and how to make more than one separate group.

Using AND and OR in the same column

Writing AND and OR between terms in the same box does not work — there is no way to tell which part of “red OR blue AND green” you meant to group together, so the whole thing is searched for as plain text and finds nothing.

To do it, join every term with AND, and put OR: on the front of the ones that should only need one match. The OR: terms become a group of their own, and that group is then one of the things that has to be matched.

Remember that plain OR: terms all join one group no matter which column they are in, so if you are already using OR: on another column, use the numbered ORSET form here to keep the two sets of terms apart.

Wildcard terms {TODAY}, {USER}, {BLANK}, and {element_handle}

The name inside the braces is an element handle — the handle of the element whose column you want to compare against, exactly as it appears in the form editor. The search does not look for that text. It reads the value held in that column and compares this column against it, and it does that for each entry using that entry’s own two values, so every entry is measured against itself rather than against any other entry.

A few other things to know about comparing two columns:

• The element you name has to be on the same form as the column you are searching, or on the main form of the list. A reference to an element on any other form is ignored.
• The comparison follows the kind of column it is. Two number columns compare as numbers, and two date columns compare as dates, but two ordinary text columns compare as text, where “9” is bigger than “10” because it starts with a bigger character.
• If the name in the braces is not an element handle, and is not being filled in from the page address, the search term is ignored.

“OR” searches on multiple columns

The OR you type between two terms only joins terms inside that one box. To make an OR search that spans two different columns, put OR on the front of the term in each of them instead.

Finding entries that have nothing matching in a connected form

Some lists show information from more than one form at once. A list of customers might also show each customer’s orders, for example. In a list like that, a single customer might have multiple orders showing.

Searching for what is missing in a list like that needs a special search term. If you search the product column for “!shoes” (not shoes), you will find customers who have at least one order that isn’t shoes. That includes customers who have an order for shoes, and an order for socks (because “socks” matches “not shoes”). Usually that isn’t the question you meant to ask.

EMPTYSET: asks it a different way. Instead of looking at each order (row) on its own, it looks at all of one customer’s orders together, and finds the customers where none of them match.

A few things to know about EMPTYSET:

• It only works on columns that come from a connected form. In the example above, EMPTYSET: belongs on Order form columns, not on Customer Profile form columns.
• Entries with nothing connected to them at all are always found. A customer who has never placed an order would be included, regardless of the search term.
• You can search for anything you would normally type, not just plain words. EMPTYSET:>100 on an Amount column finds the entries with no connected record with an amount over 100.
• EMPTYSET terms are always combined with “and”.

Advanced developer-focused search terms