Purpose
Utilizing the Advanced Filter can give you access to filtered data sets which users can use to review open items, or generate useful reports.
Using Advanced Filter
- Search: Perform a new search based on entered criteria.
- Extend: Add new records to the existing list based on entered criteria.
- Exclude: Remove records from the existing list based on entered criteria.
- Narrow: Perform a new search within the existing list based on entered criteria.
- Clear: Empty all filter fields of any criteria.
-
Favorite: Save and load commonly used Advanced Filter searches.
- Load Favorite: Load existing previously saved Advanced Filter searches.
- Save Favorite: Save the fields of the current Advanced Filter search.
Finding Empty or Non-Empty Fields
To Find |
Type in Search Field |
Example |
|---|---|---|
Not empty (fields that have data entered) |
* |
Entering "*" into the search field finds all records with data filled in for this field |
Empty (fields that do not have data entered) |
= |
Entering "=" into the search field finds all records with no data in this field |
Finding Duplicate Values
To Find |
Type in Search Field |
Example |
|---|---|---|
All duplicate values |
! |
Entering "!" finds all records with the same information in a specified field. Entering "!" in the Client Name field will find all records that contact the same Client Name if duplicates exist. |
Duplicate values of a specific type |
!characters |
"!ABC" in the Client Name field finds all records containing ABC when duplicate values exist for this client in multiple records. |
Finding Text & Characters
To Find |
Type in Search Field |
Example |
|---|---|---|
Literal Text |
Include the literal text, including spaces and punctuation between double quotation marks “ “ |
Use “TRUE Ltd.” to find all instances of TRUE Ltd. “@” finds an @ for an email address |
Wildcard Characters |
Use the @ for each unknown character |
"Gr@y" will find Gray & Grey |
|
Words with unknown or variable text
|
Use * for all unknown characters
|
"Jo*n" finds Jon and John
"J*r" finds Jr. & Junior
"*phan*" finds Phan and Stephanie
"S*" finds Sophie, Steve, and Sven |
Exact matches of the desired text that is specified |
== (two equal signs) |
"==John" finds John but not John Smith
"== John Smith" finds John Smith but not Smith, John, or John Smithers |
Finding Numbers, Dates, Times & Timestamps
To Find |
Type in Search Field |
Example |
|---|---|---|
Digits in a text field |
Include the literal text, including spaces and punctuation between double quotation marks “ “ |
"#" finds 3 but not 30
"##" finds 30 but not 3 or 300
"#3" finds 53 and 43, but not 3 |
A date |
Enter date as digits, separated by a valid date separator character such as a - or / |
"3/3/2019" finds 3/3/2019, March 3, 2019, and 3-3-2019 |
Today’s date |
// (double forward slash) |
"//" finds the current date (05/21/2018 when the current date is May 21, 2018) |
A time |
Enter the time as digits separated by colons |
"12:53:09" finds 12:53:09 |
A timestamp |
Enter the date as digits separated by a valid date separator character, then the time as digits separated by colons |
"3/3/2019" 12:59:09 PM finds 3/3/2019 12:59:09 PM |
Invalid dates, times, or timestamps |
? |
"?" finds Next Tuesday or 2/33/2019 in a date field, or midnight in a time field |
Dates on a day of the week |
Use the day of the week or enter full or = short day names (ex: Fri for Friday) |
"Tuesday" finds all dates that occur on a Tuesday
"=Thu" finds all dates that occur on a Thursday |
Any valid entry for a date or time component in a date, time, or timestamp |
Use * for information not specified, while specifying the other information to find |
"5/21/*" finds the 21st day of May in any year
"5/21" finds the 21st day of May in the current year
"*:15" finds times 15 minutes after any hour
"1/1/*" 7 PM finds timestamps in the 7 o’clock PM hour on January 1st in any year |
Date range, time range, specified period, or other specified range |
... |
"12:30...17:30" finds times between 12:30 and 17:30
"1/1/2019...6/6/2020" finds dates between January 1, 2019 and June 6, 2020
"Mon...Fri" finds days between Monday and Friday |
Date range, time range, specified period, or other specified range within the sub-range specified |
{...} |
"7/{1...15}/2019" finds dates between July 1st and 15th
"{1...3}/{10...16}/2020" finds dates within January to March of the 10th to 16th days of the month
"12:{30...45}" finds times for 12:30 to 12:45 |
Finding Ranges of Information
To Find |
Type in Search Field |
Example |
|---|---|---|
Less than a specified value |
< |
"<40" finds numbers less than 40
"<9/7/2019" finds dates earlier than September 7th, 2019 |
Less than or equal to a specified value |
<= |
"<=95129" finds values less than or equal to 95129
"<=05:00:00" finds times less than or equal to 5AM |
Greater than a specified value |
> |
">40" finds numbers greater than 40
">9/7/2019" finds dates later than September 7th, 2019 |
Greater than or equal to a specified value |
>= |
">=95129" finds values greater than or equal to 95129
">=05:00:00" finds times greater than or equal to 5AM |
Date range, time range, specified period, or other specified range |
... |
"12:30...17:30" finds times between 12:30 and 17:30
"1/1/2019...6/6/2020" finds dates between January 1, 2019 and June 6, 2020
"Mon...Fri" finds days between Monday and Friday |
Date range, time range, specified period, or other specified range within the sub-range specified |
{...} |
"7/{1...15}/2019" finds dates between July 1st and 15th
"{1...3}/{10...16}/2020" finds dates within January to March of the 10th to 16th days of the month
"12:{30...45}" finds times for 12:30 to 12:45 |
Based only on a certain date or time component, such as a month, year, or minutes |
Enter * or type nothing for each component that is not specified |
"3/*/2019" finds all dates entered for March 2019
"2/*" finds all dates in February
"2019 *:30 PM" finds all times for 2019 at half past the hour |
What are some common Advanced Filter searches?
Jobs
| Filtering Jobs by a specific Client | How can I view all active Jobs for “ABC Company?” |
|
| Filtering |
|
|
| Filtering by Date | Can I view Jobs that were sold within a certain time period? |
|
| Can I view Jobs that were created before a certain date? |
|
|
| Filtering for reporting | How can I filter by Stage? |
|
| How can I review all of my archived projects for a certain client? |
|
|
| Filtering by Item/Product | How can I view all Jobs that contain a specific product? |
|
| Filtering Jobs by Client/POC information | What Client or POC related fields can I perform a search for? |
Client: Displays Job records with an assigned Client that matches the name entered. Contact: Displays the Job records with an assigned Point of Contact that matches the name entered. Client (Phone): Displays the Jobs with an assigned Client record that matches the phone number entered. Contact (Phone): Displays any Jobs that have a primary POC with a matching phone number. |