Comparison Operators
When working with lists, date fields, and number fields you have a variety of comparison operators from which to choose. This guide will help you determine the best comparison operator to use for filters and criteria, especially when working with dates.
Legend for Comparison Operators
Basic Comparison Operators
Equals and Not Equals work on all data types except for documents. Is Empty and Is Not Empty work on all data types.
Equals
Equals is best when looking for an exact value and can be used with any filter or criteria with any data type.
Not Equals
Not Equals is best when looking for anything except for a certain value. This comparison operator is best used with any filter or criteria on any data type.
Is Empty
The Is Empty comparison operator is used to look for missing data or incomplete processes. This can work with any data type on any filter or criteria. For example, on a Background Checks report, you might set an Is Empty comparison operator for the field All Background Checks Complete to see which background checks still need to be finished.
Is Not Empty
The Is Not Empty comparison operator is used to make sure there is a value. These are generally used on Update trigger Auto Actions. For example, on an Auto Action inserting Provider Training Year for an Adoptive Family triggered on the Update of a Home Study Record on the field Home Study Packet Sent Date, you would set a Criteria on that field to Is Not Empty that way the Auto Action can insert a Provider Training year starting on Home Study Packet Sent Date.
Text Only Comparison Operators
Text Only comparison operators work on fields types that are text based. This includes Text (Regular, Small, and Large Multi-Line), WYSIWYG, Hyperlink, Email, Phone, and Region.
Contains
Contains comparison operators check to make sure the specified value is contained within a word or text body. This is a good operator when searching Communications for a specific word or phrase.
Not Contains
Not Contains comparison operators check the searched field to ensure the specified value is not in a word or the text body. Not Contains works well when excluding records with a specific value in the memo field.
Starts With
Starts With comparison operators check the field for a specified value at the beginning of a field. For example, you could search for people with Anna as their first name to find people with the name Anna Marie or searching for a phone number starting with a specific area code.
Ends With
Ends With comparison operators check the field to make sure the specified value is at the end of a field. For example, you could search for families that have "berg" in their last name such as Jesse Eisenberg or Mark Zuckerberg.
Numeric Comparison Operators
Numeric comparison operators work with both number type fields like currency or decimal and date fields. The Timeline field type is considered a numeric field.
Greater Than
Greater Than checks to see if the field has a larger value than specified. For example, filter an Auto Action to trigger when a Household Member's age is greater than 14, meaning 15+, to add a background check.
Another example is when there's a new requirement to a process, you could make a field only required when the Family's inquiry date is greater than the start of that requirement.
Less Than
Less Than checks the field to search for a smaller value than specified. For example you can use this filter to search for minor household members who are less than than 18 years old.
Another example would be to use this filter on a placements for the number of children who weren't in your care a whole month by searching total number of days in care is less than the number of days in the month.
Date Comparison Operators
Date Comparison Operators work with all date type fields except where noted.
In Current (Week, Month, Quarter, Year)
In Current (Week, Month, Quarter, Year) searches for a date within a specified time period. An example searching for license or a background checks expiring within a time period.
*Note: In Current Year does not work with Annual Date (Month and Day Only) fields.
In Specific (Month, Quarter, Year)
In Specific (Month, Quarter, Year) searches the date in a specific period of time. An example of using this comparison operator is to see all the people who have a birthday in a specific month.
*Note: In Specific Year does not work with Annual Date (Month and Day Only) fields.
In Specific Month
In Specific Quarter
In Specific Year
Within (≤) # (Days Weeks, Months, Years) in Past (including/excluding Future)
This comparison operator checks to see if a date is within the past set amount of time. If you include the future, it includes that date span as well as the future. These comparison operators are good to use on reports to check and see expired items. For example, you could have a report of donors that have made donations in the past and any future pending donations.
*Note: Within (≤) # Years in Past (including/excluding Future) does not work with Annual Date (Month and Day Only) fields.
Within (≤) # (Days Weeks, Months, Years) in Past including Future
Within (≤) # (Days Weeks, Months, Years) in Past excluding Future
Within (≤) # (Days Weeks, Months, Years) in Future (including/excluding Past)
This comparison operator checks to see if a date is within a future set amount of time. If you include the past, it includes all that date span as well as the past. These comparison operators are good to use on reports to check and see future expiring items. For example, you could have a report of home studies that are about to expire within the next 3 months and all of those that have expired in the past year. This way you know who needs a new home study.
*Note: Within (≤) # Years in Future (including/excluding Past) does not work with Annual Date (Month and Day Only) fields.
Within (≤) # (Days Weeks, Months, Years) in Future including Past
Within (≤) # (Days Weeks, Months, Years) in Future excluding Past
Over > # (Days, Weeks, Months, Years) in (Past, Future)
This comparison operator checks to see if a date happen greater than the time period set in the (Past, Future). This comparison operator works well Auto Actions and checking ages. For example, say there's a requirement that for every Household Member over the age of 15 needs a background check. When a new Household Member is added, the Auto Action can check to see if the Date of Birth happened over 15 years in the past. When that condition is met a new Background Check record is added for them.
*Note Over > # (Days, Weeks, Months, Years) in Past does not work with Annual Date (Month and Day Only) fields.
Over > # (Days, Weeks, Months, Years) in Past
Over > # (Days, Weeks, Months, Years) in Future
On (=) (Day, Week, Month, Year) in (Past/Future)
This comparison operator check to see if a date is happened/happening on the exact time in the past/future. This works well with scheduled Auto Actions. For example, when an international adoption family is going to travel to the country they are adopting from, a week before they travel an Auto Action can send a checkup email making sure they have everything they need before they leave.
*Note On (=) (Day, Week, Month, Year) in (Past/Future) does not work with Annual Date (Month and Day Only) fields.
On (=) (Day, Week, Month, Year) in Past
On (=) (Day, Week, Month, Year) in Future
Month & Day are Same as Today
This comparison operator checks to see if the date has the same month and day as today. For example, say today's date is 8/30/2022, then this comparison operator would trigger on 8/30/2021, 8/30/2020, 8/30/2023, 8/30/2024, and so on. This comparison operator works well on anniversary dates where the year doesn't matter. Daily scheduled Auto Actions work well with this comparison operator. A scheduled Auto Action can check to see if a client's birthday is today and send them an email wishing them a happy birthday.
Annual Date Within # Days in (Past/Future)
This comparison operator checks to see if an annual date, the month & day are the same, happened/is happening with the amount of days set. This comparison operator works well with weekly scheduled Auto Actions. Like Month & Day are Same as Today, you can use it to check for anniversary dates.
Yesterday
This comparison operator checks to see if a date happened the previous day. This works well with either daily scheduled Auto Actions or reports that are scheduled daily. In a report that's checked daily for incident reports, the report can be filtered to view those that were submitted the previous day.
Today
This comparison operator checks to see if a date is the same as today. This works well with daily scheduled Auto Actions. With an Auto Action that's scheduled daily, it can notify case managers that one of their client's car insurance has expired.
Tomorrow
This comparison operator checks to see if a date is happening on the next day. This works well with daily scheduled Auto Actions. For example, an Auto Action can notify case workers that they have a case note due the next day.
Last (Week, Month, Quarter, Year)
This comparison operator checks to see if a date happened in the last time period selected as viewed on a calendar. These operators are good for gathering data when using reports. For example, on a report of all placements, the report can be filtered on case end date in the last quarter to view all the placements that have recently left.
*Note Last Year does not work with Annual Date (Month and Day Only) fields.
Next (Week, Month, Quarter, Year)
This comparison operator checks to see if a date will happen in the next time period selected as viewed on a calendar. These operators are good for gathering data when using reports. For example, on a report of all adoptions, the report can be filtered on case end date in the next month to view all the adoptions that are planned to take place that have recently left.
*Note Next (Week, Month, Quarter, Year) does not work with Annual Date (Month and Day Only) fields.
On/Not On Weekend
This comparison operator checks to see if a date falls (not) on a weekend. We haven't seen this used in practice so much. One example of using this comparison operator is on reports regarding child incidents. The report can be filtered on the incident occurred date and check to see if there's a pattern on the child's behavior during the week/weekend.
*Note On/Not On Weekend does not work with Annual Date (Month and Day Only) fields.
On Weekend
Not On Weekend
Comments
0 comments
Article is closed for comments.