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:
Typing a search, or picking values
Most columns have a search box at the top. Type a search term and press Enter, and the list shows the entries that match. Everything on this page works in that box.
As you type, the values in that column that match what you’ve typed appear in a list under the box. To see the entries with particular values, tick them and click Apply, or press Enter. You can tick as many as you like, and the list shows the entries that have any of them.
- Once you’ve ticked a value, the ticks are your search. Typing after that only narrows the list of values to tick.
- The values you’ve ticked show under the box. Click the × beside them to clear them.
- Tick Show Blanks to include the entries that have nothing in that column.
- If two values in the list look the same, each has a number in brackets after it, so you can tell them apart. They’re different entries that happen to have the same name.
- If a column has a lot of values, only the first 50 that match are listed. Type more to narrow them down.
- Click Cancel or press Esc to close the list and put your search back the way it was when you opened it.
- Click somewhere else on the page to close the list and keep what you’ve typed or ticked. It’s used the next time the list updates, such as when you press Enter or sort a column.
Some columns have a different search instead, such as a dropdown list to choose from, a pair of dates or times, or a plain box to type in.
The basics
| This search term: | Means this: | It will find: |
|---|---|---|
| orange | find “orange” by itself or inside something else | orange orange peels apples and oranges |
| =orange | find the exact text “orange”, no more, no less | orange (it won’t find “oranges”) |
| !orange | find entries that do not have orange in them | red purple banana peels |
| !=orange | find entries that do not match the exact text “orange” | red apples and oranges orange peels |
| 15 | find the text “15” by itself or inside something else | 15 baseballs 12715 15 |
| =15 | find the exact number 15 | 15 |
| !15 | find text that does not contain “15” | 1848 3 strikes etc |
| !=15 | find anything except the exact number 15 | 14 16 15 baseballs |
Numbers
| This search term: | Means this: | It will find: |
|---|---|---|
| >10 | find numbers greater than 10 | 11, 12, 1485 |
| >=1000 | find numbers greater than or equal to 1000 | 1000, 1001, etc |
| <65 | find numbers less than 65 | 64, 10, -401 |
| <=65 | find numbers less than or equal to 65 | 65, 64, etc |
Dates
| This search term: | Means this: | It will find: |
|---|---|---|
| 2009-01-01 | find January 1, 2009 | 2009-01-01 |
| >=2009-01-01 | find dates on or after January 1, 2009 | 2009-01-01 2009-05-23 2010-02-20 etc |
| <2009-01//>2007-12 | find dates in the year 2008 (see below for more info on using two terms at once) | Any date starting with 2008 |
| >=2008-10-01//<=2008-10-31 | find dates in October 2008 (see below for more info on using two terms at once) | Any date starting with 2008-10 |
| 2008-10 | find dates in October 2008 | Any date starting with 2008-10 (same as the previous, more complex search |
Times
Search for a time the way the list shows it. If the list shows 1:30pm, you can type 1:30pm, and you can also type 13:30.
| This search term: | Means this: | It will find: |
|---|---|---|
| 5 | find times with a 5 anywhere in them, as the list shows them | 5:30pm 1:05am 12:50pm |
| 5: | find times in the 5 o’clock hour | 5:00am 5:45pm (it won’t find 15:30 or 11:55) |
| pm | find times in the afternoon and evening, if the list shows am and pm | 12:15pm 7:00pm |
| 1:30pm 13:30 |
find exactly 1:30 in the afternoon, typed either way | 1:30pm |
| >=9:00 | find times at 9:00 in the morning or later | 9:00am 11:20am 4:00pm |
| >=9:00//<=12:00 | find times from 9:00 in the morning until noon | 9:00am 10:45am 12:00pm |
| >=22:00//<=2:00 | find times from 10:00 at night until 2:00 in the morning. When the first time is later than the second, the search goes past midnight. | 10:30pm 1:15am |
Whether a column shows 12-hour times, like 1:30pm, or 24-hour times, like 13:30, is set for each time element, and for the whole site under Date & time formats in the settings.
The Time range search
A column can have a Time range search instead of a box. Click it to choose a From time and a To time, then click Apply.
- Morning, Afternoon and Evening choose both times for you: midnight to noon, noon to 5pm, and 5pm to midnight.
- In the last hour and In the next hour choose the hour before or after now.
- Under each time, Now sets that time to now, and the box with a number sets it that many minutes before or after now. Clear leaves that side open, so the search has no start or no end.
- Choices made from now keep their meaning. If you save the list, In the next hour is the hour after whenever the list is opened, not the hour after you saved it.
- A From time later than the To time goes past midnight. From 10:00pm to 2:00am finds the times overnight.
Duration
| This search term: | Means this: | It will find: |
|---|---|---|
| 1d 1h 10m 10m 1d 1h |
Find a duration set to exactly 1510 minutes | A duration set to exactly 1510 minutes |
| <=1h 10m | Find a duration less than or equal to 70 minutes | Any duration less than or equal to 70 minutes |
| <5h AND >10m | Find a duration greater than 10 minutes and less than 300 minutes | Any duration greater than 10 minutes and less than 300 minutes |
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.
| This search term: | Means this: | It will find: |
|---|---|---|
| >10 AND <100 | find numbers between 11 and 99 | 11, 12, etc |
| >10 AND <10 | find entries that are both greater than 10 and less than 10 | it will find nothing…the search condition is logically impossible |
| santa AND !monica | find entries that contain “santa” and not “monica” | Santa Claus Santa Cruz |
| >=2008-10-01 AND <=2008-10-31 | find dates within October 2008 | 2008-10-01 2008-10-15 |
| red AND orange AND blue | find those three values. You can have as many terms as you want in a single column. | Searches like this will usually only find matches if the field you’re searching accepts multiple selections (ie: checkboxes), and red, orange and blue were three of the options. |
| red OR blue | find entries that match either one. Terms joined by OR only need one of them to match. | red blue |
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.
| This search term: | Means this: | It will find: |
|---|---|---|
| santa AND OR:cruz AND OR:claus | santa has to match, and then either cruz or claus | Santa Cruz Santa Claus (it won’t find Santa Fe) |
| ORSET1:red AND ORSET1:blue AND ORSET2:small AND ORSET2:large | two separate groups, each needing only one match. Use this when a plain OR: group isn’t enough, the same way it works across columns. | entries that are (red or blue), and that are also (small or large) |
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}
| This search term: | Means this: | It will find: |
|---|---|---|
| {TODAY} | find today’s date | the text of today’s date, in YYYY-mm-dd format |
| >={NOW-60}//<={NOW} | on a time, find the times in the last hour. {NOW+15} is 15 minutes from now. | the times between an hour ago and now |
| >={TODAY-30} | find dates anytime from the past 30 days into the future | you get the idea |
| >={TODAY-30} AND <={TODAY} | find dates between 30 days ago and today | |
| >{TODAY+14} | Find dates more than 14 days in the future | |
| {USER} | Find entries that match the current user’s full name, or if no full name is in their profile, then match on their username | |
| {BLANK} | Find entries that are blank or empty | |
| >{attendance_buyers} | Compare against the value in another column, instead of against a value you type. What goes in the braces is an element handle. Typed into the attendance_sellers column, this finds the entries where the value in attendance_sellers is greater than the value in attendance_buyers. | An entry with attendance_buyers 12 and attendance_sellers 15 |
| ={attendance_buyers} | Find entries where the two columns hold exactly the same value. Every operator works this way, so <, >=, != and the rest all compare the two values. | An entry with attendance_buyers 12 and attendance_sellers 12 |
| {attendance_buyers} | With no operator in front, it means “contains”, the same as any other search term. So this finds entries where the value in attendance_sellers contains the value in attendance_buyers. | An entry with attendance_buyers 12 and attendance_sellers 120 |
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.
| This search term: | Means this: | It will find: |
|---|---|---|
| on the fruit column: OR:apples and at the same time on the vegetable column: OR:carrots |
Find entries that match either apples in the fruit column, or carrots in the vegetable column (normally, search terms on multiple columns must all be matched) | An entry with Apples in Fruits, or an entry with Carrots in Vegetables |
| on the fruit column: ORSET1:apples on the vegetable column: ORSET1:carrots on the colour column: ORSET2:red on the size column: ORSET2:large |
Make more than one separate “OR” group. All the plain OR terms join together into a single group, but sometimes you need to ask two independent questions. Terms with the same number are OR’d together, and then every group has to be matched. | entries that have (apples or carrots), and that also have (red or large) |
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.
| This search term: | Means this: | It will find: |
|---|---|---|
| on the product column: EMPTYSET:shoes |
Find the customers who have never ordered shoes | Customers with no shoe order at all, including customers who have never ordered anything |
| on the product column: EMPTYSET:shoes and at the same time on the status column: EMPTYSET:cancelled |
Find the customers who have no cancelled orders for shoes. When you put EMPTYSET: on more than one column, each record is matched against all of those conditions together. | Customers with no cancelled shoe order. A customer who cancelled a sock order is found. A customer who ordered shoes and kept them, is found. |
| on the product column: EMPTYSET1:shoes and at the same time on the status column: EMPTYSET2:cancelled |
Ask two separate questions instead of one. Adding a number makes a separate group, the same way ORSET does above, so these become two independent conditions rather than a description of one order. | customers who have never ordered shoes, and who have also never cancelled anything |
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”.
Choosing the search for each column
This section is for people who set up forms and lists.
Each column in a list can have one of these searches at the top:
| Search | What people can do with it |
|---|---|
| Search box - type or pick values | Type a search term, or tick any of the values that match what they type. This is the default. |
| Search box - type only | Type a search term. |
| Dropdown list - select value | Choose one of the values in the entries. |
| Dropdown list - exclude value | Choose a value to leave out. |
| Date range | Choose a from date and a to date. Only for elements that hold dates. |
| Time range | Choose a from time and a to time, which can go past midnight. Only for elements that hold times. |
| Month selection - current year | Choose a month of this year. Only for elements that hold dates. |
| Month selection - last 12 months | Choose one of the last twelve months. Only for elements that hold dates. |
For an element, set How people search this element in lists on the element’s Settings tab. That search is used wherever the element appears in a list, unless the list chooses a different one. If you leave it, elements that hold dates get Date range, elements that hold times get Time range, numbers that people type in, like a number box or a slider, get Search box - type only, and everything else gets Search box - type or pick values.
For a list screen, the Display only these columns section of the screen’s Entries tab has a Search Interface for each column. Hover over the help icon beside that heading to see what each one is. When you add a column there, it starts with the element’s own search, and you can change it. Columns you haven’t listed there, including ones people add with Change columns, use the element’s own search. Map screens choose the search for each filter the same way, in the Filter Type of each filter on their Map Options tab.
Advanced developer-focused search terms
| This search term: | Means this: |
|---|---|
| {order} | Find entries that match whatever is in $_POST[‘order’] or if that’s empty, $_GET[‘order’]. This is meant for use in a complex application where a certain screen might need to show different things at different times. You can save one view with this kind of search term and then make some kind of architecture (with javascript maybe?) to populate $_POST or $_GET with the values you need at the right time. If there is no value to find, the search term is ignored. A name that is both a request value and an element handle is taken as the request value, so a request value always wins over the column comparison described above. |
| !{order} | Find entries that do not match whatever is in $_POST[‘order’] or $_GET[‘order’] |
| !orange! | Persist this search term even if this column is not included in the view. Certain columns are visible only to certain groups of users. You might want to use a search term on a certain column that only webmasters have access to, in order to limit the list of entries. Then you could publish that view to other users who do not have that column available, but the list of entries would still be limited by this search term. |
| !!monica! AND !santa! | When persisting a search that includes multiple terms, put the ! ! around each term separately, not around the whole thing. Don’t get confused by “not” operators (!) that may be part of the terms (as in “not monica” at the beginning of this set of terms). |