MEQL Advanced Filtering and Functions
MEQL provides comprehensive filtering capabilities beyond basic comparison operators through specialized functions for date manipulation, text processing, and multi-select field evaluation. These advanced functions enable complex filtering logic, date arithmetic, timezone handling, and multi-value field operations that support sophisticated data retrieval requirements across diverse use cases and business logic implementations.
Understanding advanced function syntax, appropriate usage patterns, and field type-specific operators enables developers to construct precise queries that filter data based on temporal calculations, dynamic date ranges, text pattern matching, and multi-select field combinations.
Date and DateTime Functions
DateTime Constants
MEQL provides built-in DateTime constants that reference current date and time values dynamically at query execution, enabling queries that filter based on current temporal context without hardcoded date literals.
DateTime.Today: Represents the current date with time set to 00:00:00 (midnight). DateTime.Today excludes time components and time zone information, providing date-only comparisons.
DateTime.UtcNow: Represents the current date and time in UTC timezone including both date and time components. DateTime.UtcNow reflects the precise moment of query execution in Coordinated Universal Time.
Date Storage: All date and datetime values in Magentrix are stored in UTC format regardless of user timezone settings. DateTime.UtcNow comparisons match stored values directly without timezone conversion.
Current Date Comparisons
DateTime constants enable filtering based on current date relationships without requiring manual date literal construction or query modification for different execution times.
Records Created Today:
SELECT Id, Name FROM Account WHERE CreatedOn >= DateTime.Today
Retrieves Accounts created since midnight today
Future Date Comparison:
SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate > DateTime.Today
Finds Opportunities with close dates in the future
Recent Activity:
SELECT Id, Name FROM Contact WHERE LastModifiedOn >= DateTime.UtcNow.AddHours(-24)
Retrieves Contacts modified within the last 24 hours
Date Arithmetic Methods
DateTime constants support date arithmetic methods that add or subtract time intervals, enabling dynamic date range calculations based on current date and time.
AddYears(<years>): Adds or subtracts the specified number of years from the date. Negative values subtract years, enabling past date calculations.
SELECT Id, Name FROM Account WHERE CreatedOn > DateTime.Today.AddYears(-1)
Retrieves Accounts created within the last year
AddMonths(<months>): Adds or subtracts the specified number of months from the date.
SELECT Id, Name FROM Contact WHERE LastActivityDate >= DateTime.Today.AddMonths(-3)
Finds Contacts with activity in the last 3 months
AddDays(<days>): Adds or subtracts the specified number of days from the date.
SELECT Id, Name FROM Case WHERE CreatedOn = DateTime.Today.AddDays(-10)
Retrieves Cases created exactly 10 days ago
AddHours(<hours>): Adds or subtracts the specified number of hours from the datetime.
SELECT Id, Name FROM Lead WHERE CreatedOn >= DateTime.UtcNow.AddHours(-2)
Finds Leads created within the last 2 hours
AddMinutes(<minutes>): Adds or subtracts the specified number of minutes from the datetime.
SELECT Id, Name FROM Task WHERE CompletedDate >= DateTime.UtcNow.AddMinutes(-30)
Retrieves Tasks completed in the last 30 minutes
AddSeconds(<seconds>): Adds or subtracts the specified number of seconds from the datetime.
SELECT Id, Name FROM Event WHERE StartDateTime <= DateTime.UtcNow.AddSeconds(300)
Finds Events starting within the next 5 minutes
Field Date Arithmetic Functions
Fn.Add functions perform date arithmetic on field values rather than constants, enabling comparison of calculated field dates against literal dates or other calculated values.
Fn.AddYears(<field_name>, <years>): Adds or subtracts years from a date field value.
SELECT Id, Name, ContractEndDate FROM Account
WHERE Fn.AddYears(ContractEndDate, -1) > DateTime.Today
Finds Accounts whose contracts ended less than a year ago
Fn.AddMonths(<field_name>, <months>): Adds or subtracts months from a date field value.
SELECT Id, Name, CreatedOn FROM Contact
WHERE Fn.AddMonths(CreatedOn, 6) < DateTime.Today
Retrieves Contacts created more than 6 months ago
Fn.AddDays(<field_name>, <days>): Adds or subtracts days from a date field value.
SELECT Id, Name, DueDate FROM Task WHERE Fn.AddDays(DueDate, 7) < DateTime.Today
Finds Tasks whose due dates passed more than 7 days ago
Fn.AddHours(<field_name>, <hours>): Adds or subtracts hours from a datetime field value.
SELECT Id, Name, StartDateTime FROM Event
WHERE Fn.AddHours(StartDateTime, 2) > DateTime.UtcNow
Retrieves Events that started less than 2 hours ago
Fn.AddMinutes(<field_name>, <minutes>): Adds or subtracts minutes from a datetime field value.
SELECT Id, Name, CreatedOn FROM Case
WHERE Fn.AddMinutes(CreatedOn, 15) < DateTime.UtcNow
Finds Cases created more than 15 minutes ago
Fn.AddSeconds(<field_name>, <seconds>): Adds or subtracts seconds from a datetime field value.
SELECT Id, Name, ResponseDateTime FROM Request
WHERE Fn.AddSeconds(ResponseDateTime, 30) <= DateTime.UtcNow
Retrieves Requests with responses at least 30 seconds old
Date Part Extraction
Fn.DatePart extracts specific components from date or datetime fields, enabling filtering based on year, month, day, or time components without requiring full date comparisons.
Fn.DatePart Syntax:
Fn.DatePart(<date_part>, <field_name>) <operator> <value>
Supported Date Parts:
"year" - Four-digit year (e.g., 2024)"quarter" - Quarter number (1-4)"month" - Month number (1-12)"day" - Day of month (1-31)"hour" - Hour (0-23)"minute" - Minute (0-59)"second" - Second (0-59)
Date Part Examples:
Filter by Year:
SELECT Id, Name, CreatedOn FROM Account
WHERE Fn.DatePart("year", CreatedOn) = 2024
Retrieves Accounts created in 2024
Filter by Quarter:
SELECT Id, Name, CloseDate FROM Opportunity
WHERE Fn.DatePart("quarter", CloseDate) = 1
Finds Opportunities closing in Q1 (any year)
Filter by Month:
SELECT Id, Name, Birthdate FROM Contact
WHERE Fn.DatePart("month", Birthdate) = 12
Retrieves Contacts with December birthdays
Filter by Day:
SELECT Id, Name, CreatedOn FROM Case WHERE Fn.DatePart("day", CreatedOn) = 1
Finds Cases created on the 1st day of any month
Filter by Hour:
SELECT Id, Name, CreatedOn FROM Lead
WHERE Fn.DatePart("hour", CreatedOn) >= 9 AND Fn.DatePart("hour", CreatedOn) <= 17
Retrieves Leads created during business hours (9 AM - 5 PM)
Year Range Filter:
SELECT Id, Name, CreatedOn FROM Account
WHERE Fn.DatePart("year", CreatedOn) > 2020
Finds Accounts created after 2020
Timezone Conversion
Fn.ToLocalTime converts UTC-stored datetime values to the executing user's local timezone, enabling timezone-aware filtering based on local time rather than UTC time.
Fn.ToLocalTime Syntax:
Fn.ToLocalTime(<field_name>) <operator> <date_value>
Local Time Filtering:
SELECT Id, Name, CreatedOn FROM Contact
WHERE Fn.ToLocalTime(CreatedOn) >= DateTime.Today
Retrieves Contacts created today in the user's local timezone
Local Time Range:
SELECT Id, Name, StartDateTime FROM Event
WHERE Fn.ToLocalTime(StartDateTime) >= "2024-01-01"
AND Fn.ToLocalTime(StartDateTime) <= "2024-12-31"
Finds Events occurring in 2024 based on user's local timezone
Business Hours in Local Time:
SELECT Id, Name, CreatedOn FROM Case
WHERE Fn.DatePart("hour", Fn.ToLocalTime(CreatedOn)) >= 9
AND Fn.DatePart("hour", Fn.ToLocalTime(CreatedOn)) <= 17
Retrieves Cases created during business hours in user's timezone
Timezone Considerations:
- DateTime field values are always stored in UTC
- Fn.ToLocalTime converts to the user executing the query
- Different users may see different results for the same query if in different timezones
- Use Fn.ToLocalTime when filtering needs to respect user's local business hours or calendar dates
Date Function Best Practices
Dynamic vs. Static Dates: Use DateTime constants and arithmetic methods for queries requiring current date context. Use static date literals in ISO 8601 format for fixed date comparisons.
UTC Awareness: Remember that stored dates are in UTC. Use DateTime.UtcNow for precise timestamp comparisons and Fn.ToLocalTime when local timezone context is required.
Date Part Efficiency: Date part extraction enables filtering by calendar components without complex date range logic. Use date parts for recurring temporal patterns like monthly reports or quarterly analysis.
Arithmetic Method Chaining: DateTime arithmetic methods can be chained for complex date calculations:
DateTime.Today.AddYears(-1).AddMonths(3).AddDays(-15)
Field Arithmetic Comparisons: Use Fn.Add functions when filtering requires calculations on field values rather than current date. Field arithmetic supports comparisons like "contracts expiring within 30 days" or "events older than 2 hours."
Text Field Functions
String Manipulation Functions
Text field functions enable pattern matching, case-insensitive comparisons, and null/empty validation beyond simple equality comparisons. These functions support string fields including Text, Long Text, Phone, Email, Picklist, and Global Picklist field types.
String.IsNullOrEmpty(<field_name>): Tests whether a string field is null or contains an empty string, providing consolidated null and empty checking.
SELECT Id, Name FROM Contact WHERE String.IsNullOrEmpty(Email)
Retrieves Contacts without email addresses
<field_name>.ToLower(): Converts field value to lowercase, enabling case-insensitive string comparisons.
SELECT Id, Name FROM Account WHERE Name.ToLower() = "magentrix"
Finds Accounts named "magentrix" regardless of capitalization
<field_name>.ToUpper(): Converts field value to uppercase, enabling case-insensitive string comparisons.
SELECT Id, Name, Email FROM User
WHERE Email.ToUpper().Contains("ADMIN")
Retrieves Users with "admin" in email regardless of case
<field_name>.Contains(<string_value>): Tests whether field value contains the specified substring anywhere within the string.
SELECT Id, Name FROM Contact WHERE Name.Contains("David")
Finds Contacts with "David" anywhere in their name
<field_name>.StartsWith(<string_value>): Tests whether field value begins with the specified string.
SELECT Id, Name FROM Account WHERE Name.StartsWith("Mag")
Retrieves Accounts with names starting with "Mag"
<field_name>.EndsWith(<string_value>): Tests whether field value ends with the specified string.
SELECT Id, Email FROM Contact WHERE Email.EndsWith("@magentrix.com")
Finds Contacts with Magentrix email addresses
Text Function Examples
Combined Text Functions:
SELECT Id, Name, Email FROM Contact
WHERE (Name.Contains("David") OR Email.EndsWith("@example.com"))
AND NOT(String.IsNullOrEmpty(Phone))
Retrieves Contacts named David or with example.com emails who have phone numbers
Case-Insensitive Pattern Matching:
SELECT Id, Name FROM Account WHERE Name.ToLower().Contains("international")
Finds Accounts with "international" in name regardless of capitalization
Multiple Pattern Matching:
SELECT Id, Name, Email FROM User
WHERE Email.Contains("admin") OR Email.Contains("support") OR Email.Contains("help")
Retrieves Users with administrative or support-related email addresses
Supported Text Field Types
Text manipulation functions work on the following field types:
Text: Standard short text fields with character limits Long Text: Extended text fields for longer content Phone: Phone number fields formatted as text Email: Email address fields validated as email format Picklist: Single-select picklist fields storing selected value as text Global Picklist: Organization-wide picklist fields storing selected value as text
Text Field Function Limitations: Text functions operate only on string-based field types. Using text functions on numeric, date, or boolean fields produces query errors.
Text Function Best Practices
Case-Insensitive Searches: Use ToLower() or ToUpper() for case-insensitive pattern matching when exact case is unknown or variable.
Pattern Location: Choose appropriate functions based on pattern location: Contains for anywhere in string, StartsWith for prefixes, EndsWith for suffixes.
Null Handling: Use String.IsNullOrEmpty for comprehensive null and empty checking rather than separate null and empty string conditions.
Double Quote Requirement: Remember that string literal values in MEQL must use double quotes. Single quotes are not supported and produce syntax errors.
Multi-Select Field Functions
Multi-Select Field Filtering
Multi-select fields store multiple values in a single field using semicolon separators. Fn.Includes and Fn.Excludes functions enable filtering based on multi-select field content with support for complex inclusion and exclusion patterns.
Multi-Select Structure: Multi-select field values are stored as semicolon-delimited strings (e.g., "US;CA;MX" for United States, Canada, and Mexico). Functions evaluate these delimited values to determine matches.
Fn.Includes Function
Fn.Includes tests whether a multi-select field includes specified values, supporting multiple value sets where any matching set satisfies the condition.
Fn.Includes Syntax:
Fn.Includes(<field_name>, "<value_set_1>", "<value_set_2>", ...)
Single Value Set: Match records where field includes all values in the set (AND logic within set)
Fn.Includes(TargetedCountry__c, "US;CA")
Matches records where field contains both US AND CA
Multiple Value Sets: Match records where field includes any complete value set (OR logic between sets)
Fn.Includes(TargetedCountry__c, "US;CA", "UK")
Matches records where field contains (US AND CA) OR UK
Fn.Includes Examples
Single Country Required:
SELECT Id, Name FROM Contact WHERE Fn.Includes(TargetedCountry__c, "US")
Retrieves Contacts targeting United StatesMatches: "US", "US;CA", "US;CA;MX"Does not match: "CA;MX", "UK;FR"
Multiple Countries Required (AND):
SELECT Id, Name FROM Contact WHERE Fn.Includes(TargetedCountry__c, "US;CA")
Retrieves Contacts targeting both United States and CanadaMatches: "US;CA", "US;CA;MX", "CA;US;UK"Does not match: "US", "CA", "US;MX"
Alternative Country Sets (OR):
SELECT Id, Name FROM Contact WHERE Fn.Includes(TargetedCountry__c, "US;CA", "UK")
Retrieves Contacts targeting (US and CA) or UKMatches: "US;CA", "US;CA;MX", "UK", "UK;FR"Does not match: "US", "CA", "FR;DE"
Complex Multi-Set Logic:
SELECT Id, Name FROM Account
WHERE Fn.Includes(Regions__c, "EMEA;APAC", "AMER;LATAM", "GLOBAL")
Matches accounts targeting (EMEA and APAC) or (AMER and LATAM) or GLOBAL
Fn.Excludes Function
Fn.Excludes tests whether a multi-select field does not include specified values, enabling exclusion-based filtering.
Fn.Excludes Syntax:
Fn.Excludes(<field_name>, "<value>")
Single Value Exclusion:
Fn.Excludes(TargetedCountry__c, "UK")
Matches records where field does not contain UK
Fn.Excludes Examples
Exclude Single Country:
SELECT Id, Name FROM Contact WHERE Fn.Excludes(TargetedCountry__c, "UK")
Retrieves Contacts not targeting United KingdomMatches: "US;CA;MX", "FR;DE", "JP"Does not match: "UK", "US;UK;CA", "UK;FR"
Exclude Multiple Countries:
SELECT Id, Name FROM Contact
WHERE Fn.Excludes(TargetedCountry__c, "UK") AND Fn.Excludes(TargetedCountry__c, "FR")
Retrieves Contacts targeting neither UK nor FranceMatches: "US;CA", "DE;IT", "JP;CN"Does not match: "UK;US", "FR;DE", "UK;FR"
Combined Include and Exclude:
SELECT Id, Name FROM Account
WHERE Fn.Includes(Regions__c, "AMER") AND Fn.Excludes(Regions__c, "LATAM")
Retrieves Accounts targeting Americas but not Latin AmericaMatches: "AMER", "AMER;EMEA"Does not match: "LATAM", "AMER;LATAM", "EMEA"
Multi-Select Best Practices
Value Ordering Independence: Multi-select functions match values regardless of order in stored field. "US;CA" and "CA;US" are functionally equivalent.
Case Sensitivity: Multi-select value matching is case-sensitive. "US" and "us" are different values. Ensure consistent value capitalization in multi-select field definitions and queries.
Semicolon Delimiter: Values within Fn.Includes value sets must be separated by semicolons without spaces. "US;CA" is correct; "US; CA" or "US, CA" are incorrect.
Set Logic Understanding: Within each value set in Fn.Includes, all values must be present (AND logic). Between value sets, any set matching satisfies the condition (OR logic).
Exclusion Limitations: Fn.Excludes tests for absence of specific values. To test for complete field emptiness, use String.IsNullOrEmpty instead of Fn.Excludes.
Boolean Field Filtering
Boolean Comparison Syntax
Boolean (checkbox) fields require lowercase true and false keywords for value comparisons. Boolean filtering enables identification of enabled, disabled, or toggled states in entity records.
True Value Comparison:
SELECT Id, Name FROM Contact WHERE IsActive = true
Retrieves active Contacts
False Value Comparison:
SELECT Id, Name, IsOptOut FROM Contact WHERE IsOptOut = false
Finds Contacts who have not opted out
Boolean Field Comparisons:
SELECT Id, Name FROM User WHERE IsActive = true AND IsLocked = false
Retrieves active, unlocked Users
Boolean Comparison Rules
Lowercase Requirement: Boolean values must be lowercase true and false. Uppercase TRUE and FALSE, numeric 1 and 0, or string "true" and "false" are not supported and produce query errors.
Direct Comparison Only: Boolean fields support direct equality comparison with true and false keywords. Inequality operators (!=) work with boolean fields but other comparison operators (<, >, <=, >=) are not applicable to boolean types.
Field-to-Field Comparison: Boolean fields can be compared to other boolean fields:
SELECT Id, Name FROM Account WHERE IsActive = IsVisible
Retrieves Accounts where active status matches visibility status
Boolean Filtering Examples
Single Boolean Condition:
SELECT Id, Name FROM User WHERE IsActive = true
Finds active Users
Multiple Boolean Conditions:
SELECT Id, Name FROM Contact
WHERE IsActive = true AND IsOptOut = false AND IsDeleted = false
Retrieves active Contacts who haven't opted out and aren't deleted
Boolean with Other Conditions:
SELECT Id, Name, Email FROM Contact
WHERE IsActive = true AND NOT(String.IsNullOrEmpty(Email))
Finds active Contacts with email addresses
Inverted Boolean Logic:
SELECT Id, Name FROM Account WHERE NOT(IsActive = true)
Retrieves inactive Accounts (alternative to IsActive = false)
Boolean Best Practices
Explicit Comparisons: Always use explicit = true or = false comparisons rather than relying on implicit boolean evaluation. Explicit comparisons improve query readability and prevent syntax errors.
Lowercase Keywords: Remember that boolean keywords are case-sensitive lowercase. Using uppercase or mixed case produces syntax errors.
NOT Operator Alternative: Both IsActive = false and NOT(IsActive = true) produce equivalent results. Choose the pattern that makes query intent clearest.
Function Combination Patterns
Complex Multi-Function Queries
Advanced filtering often requires combining multiple function types to implement sophisticated business logic. Understanding function combination patterns enables construction of powerful queries that filter across text, date, boolean, and multi-select criteria simultaneously.
Date and Text Combination:
SELECT Id, Name, Email, CreatedOn FROM Contact
WHERE CreatedOn >= DateTime.Today.AddMonths(-6)
AND Email.EndsWith("@magentrix.com")
AND NOT(String.IsNullOrEmpty(Name))
Retrieves recent Contacts from Magentrix domain with names
Date and Boolean Combination:
SELECT Id, Name, LastActivityDate FROM Account
WHERE IsActive = true
AND LastActivityDate >= DateTime.Today.AddDays(-30)
Finds active Accounts with recent activity
Multi-Select and Date Combination:
SELECT Id, Name, TargetedCountry__c, CreatedOn FROM Contact
WHERE Fn.Includes(TargetedCountry__c, "US;CA")
AND CreatedOn >= DateTime.Today.AddYears(-1)
Retrieves North America-targeted Contacts created in last year
Text, Date, and Boolean Combination:
SELECT Id, Name, Email, CreatedOn FROM User
WHERE IsActive = true
AND Email.Contains("admin")
AND Fn.DatePart("year", CreatedOn) >= 2023
Finds active Users with admin emails created since 2023
Parentheses for Logic Grouping
Complex queries require parentheses to control logical operator precedence and ensure correct filter evaluation order.
AND/OR Precedence Control:
SELECT Id, Name, Email FROM Contact
WHERE (IsActive = true OR LastLoginDate >= DateTime.Today.AddDays(-7))
AND Email.EndsWith("@magentrix.com")
Retrieves Magentrix Contacts who are either active or logged in recently
Nested Logic Groups:
SELECT Id, Name FROM Account
WHERE (Type = "Partner" AND IsActive = true)
OR (Type = "Customer" AND AnnualRevenue > 1000000)
Finds active Partners or high-revenue Customers
Multi-Level Grouping:
SELECT Id, Name, Email FROM Contact
WHERE ((Name.Contains("Manager") OR Name.Contains("Director")) AND IsActive = true)
OR (Email.Contains("executive")
AND LastActivityDate >= DateTime.Today.AddMonths(-3))
Retrieves active managers/directors or executives with recent activity
Function Combination Best Practices
Test Incrementally: Build complex queries incrementally, adding one function or condition at a time and testing after each addition. Incremental development isolates issues and simplifies debugging.
Explicit Parentheses: Use parentheses liberally to make logic explicit even when operator precedence would produce the same result. Explicit grouping prevents logic errors and improves query readability.
Function Order Optimization: Place more selective conditions earlier in WHERE clauses when possible. Database engines may optimize queries more effectively when highly selective conditions appear first.
Readability Formatting: Format complex queries with line breaks and indentation for readability:
SELECT Id, Name, Email
FROM Contact
WHERE (IsActive = true AND Email.EndsWith("@magentrix.com"))
OR (CreatedOn >= DateTime.Today.AddMonths(-1) AND NOT(String.IsNullOrEmpty(Phone)))
The comprehensive understanding of advanced filtering functions including date manipulation, text processing, multi-select evaluation, and boolean comparisons enables construction of sophisticated MEQL queries that implement complex business logic, temporal filtering, pattern matching, and multi-dimensional data retrieval across organizational portal implementations.
<< MEQL Reference