Advanced searching allows you to search for records by entering criteria directly into form fields. When you enter search mode, the input fields turn yellow to indicate that you can type search criteria.
You can use Advanced searching to:
Find records that match specific field values.
Search using wildcards and operators for flexible matching.
Search within sublists (related records) to find records based on their relationships.
Combine multiple search criteria to narrow down results.
Enter search mode
To enter Advanced search mode:
Open the form you want to search (for example, the Contact form).
Click the Search button in the toolbar.
The form fields will turn yellow, indicating you are now in search mode.
Enter your search criteria into the relevant fields.
Press Enter or click the Search button again to execute the search.
Basic search operators
When entering search criteria in Advanced fields, you can use various operators to control how the search matches records. Click the filter button next to any field to open the filter window, where you can select from available operators.
Common operators for text fields include:
Equals: Matches the exact value you enter.
Contains: Finds records where the field contains the text anywhere within it.
Begins with: Finds records where the field starts with the specified text.
Ends with: Finds records where the field ends with the specified text.
Is blank / Is not blank: Finds records where the field is empty or has a value.
For date fields, additional operators are available:
Between: Finds records within a date range.
This year / Last year / Next year: Finds records matching relative time periods.
This month / Last month / Next month: Finds records matching relative months.
Today / Yesterday / Tomorrow: Finds records matching specific days.
Last X Days / Next X Days: Finds records within a specified number of days.
For numeric fields:
Greater than / Less than: Finds records above or below a value.
Between: Finds records within a numeric range.
🤓 Tip: You can also type search expressions directly into Advanced fields using wildcards. Use an asterisk (*) as a wildcard. For example, typing "Smith*" will find all records beginning with "Smith", while "*son" will find all records ending with "son".
Sublist searching
Sublists display related records within a form. For example, on a Contact form, you might see sublists for Donations, Memberships, or Activities. Advanced searching allows you to search within these sublists to find records based on their related data.
To search within a sublist:
Enter search mode on the main form.
Expand the sublist you want to search by clicking its header.
A search area will appear with a dropdown to select the field you want to search.
Select a field from the dropdown (for example, "Amount" or "Date").
Enter your search criteria in the text box next to the dropdown.
You can add additional search criteria by selecting more fields.
When you execute the search, the system will find all main records (for example, Contacts) that have related sublist records (for example, Income) matching your criteria.
Search groups
When searching within sublists, you can organise your criteria into groups. Each group represents a set of conditions that must all be true together (AND logic). You can add multiple groups to create more complex searches.
To add a new search group, click the Add Group button below the current search criteria. To remove a group, click the X button next to the group.
Special search functions
In addition to searching by field values, sublist searching provides special search functions that allow you to search based on saved searches and record counts. These options appear at the bottom of the field dropdown when searching within a sublist.
In Saved Search / Not In Saved Search
These options allow you to search for records that are included in (or excluded from) a previously saved search.
Select Saved Search or Not In Saved Search from the field dropdown.
Click the filter button to open the saved search picker.
Select the saved search you want to use.
The search will find records that are in (or not in) the selected saved search.
For example, if you have a saved search called "Major Donors" on the Donations sublist, you can use "Saved Search" to find all Contacts who have donations that appear in that saved search.
At least X records / Without at least X records
These options allow you to search for records based on how many related sublist records they have.
Select At least X records or Without at least X records from the field dropdown.
Enter a number in the text box (for example, "3").
Execute the search.
"At least X records" finds main records that have at least the specified number of related sublist records. For example, selecting "At least X records" with a value of 3 on the Donations sublist will find all Contacts who have made 3 or more donations.
"Without at least X records" finds main records that do NOT have the specified number of related records. For example, using this with a value of 1 would find Contacts with no donations at all.
At least X records in group / Without at least X records in group
New feature: These options extend the record count functionality by allowing you to count only the sublist records that match other criteria in the same search group. This is particularly useful when you want to find records based on a count of related records that meet specific conditions.
The key difference between "At least X records" and "At least X records in group" is:
"At least X records" counts ALL related sublist records, regardless of any other search criteria.
"At least X records in group" counts only the related sublist records that match the other field criteria you have specified in the same search group.
How to use "At least X records in group"
Enter search mode and expand the sublist you want to search.
Add your field criteria first. For example, select "Amount" and enter "> 100" to search for donations greater than 100.
Add another criterion by selecting "At least X records in group" from the field dropdown.
Enter the minimum count you want (for example, "3").
Execute the search.
The search will find all main records that have at least the specified number of sublist records matching ALL the other criteria in the group.
Example:
Suppose you want to find all Contacts who have made at least 3 donations of more than 100 pounds each. Using the Donations sublist:
Enter search mode on the Contact form.
Expand the Donations sublist.
Select Amount from the dropdown and enter "> 100" (greater than 100).
Select At least X records in group from the dropdown and enter "3".
Execute the search.
This will return all Contacts who have 3 or more donations where each donation amount is greater than 100. Without the "in group" option, using "At least X records" with a value of 3 would find Contacts with 3 or more donations of any amount, which is a different result.
Another example:
To find Contacts who have attended at least 2 events in the current year:
Enter search mode on the Contact form.
Expand the Event Bookings sublist.
Select Event Date and choose This year as the operator.
Select At least X records in group and enter "2".
Execute the search.
This finds Contacts with 2 or more event bookings where the event date is in the current year.
Using "Without at least X records in group"
The "Without at least X records in group" option works similarly but finds records that do NOT have the specified count of matching sublist records. This is useful for finding records that are missing certain related data.
For example, to find Contacts who have NOT made at least 2 donations this year:
Enter search mode on the Contact form.
Expand the Donations sublist.
Select Date and choose This year.
Select Without at least X records in group and enter "2".
Execute the search.
This will find Contacts who have made fewer than 2 donations this year (including those who have made no donations this year).
Keyboard shortcuts
There are a few keyboard shortcuts available to efficiently navigate Advanced search:
Alt + S – Enter Search if in view mode, apply the search if in search mode
Alt + A – Add new Include search section
Alt + E – Add new Exclude search section
Tips for effective searching
When using "At least X records in group", add your field criteria before adding the count criterion. This makes it easier to see which conditions will be included in the count.
You can combine multiple field criteria with the "in group" count option. All field criteria in the same group will be used to filter which records are counted.
If you want to count all sublist records regardless of other criteria, use "At least X records" instead of "At least X records in group".
Save frequently used searches as saved searches so you can quickly run them again later.