Saved Search Formula That Compares Value Between a Custom Field and a System Note Field

A user creates a CRM date field to capture the date of the first email sent by a Support Rep. The field is namedFirst Message Date (custom) in this example. A case search should return all the cases where the Date Assigned To (Date when the case was assigned to Support Rep) in the system note is equal to the custom date field.

1. Navigate to Lists > Search > Saved Searches > New
2. Click Case
3. Search Title: Enter Title
4. Filter:
   • Select Formula (Text)
     1. Formula: Enter CASE WHEN decode({systemnotes},'Assigned',trunc({systemnotes})) = trunc({custevent3}) THEN 'YES' ELSE 'NO' END
        Note: custevent3 is the ID of the custom date field. Change it to the corresponding custom date field ID.
     2. Click Set
5. Click Results
6. Field:
   Note: Always click Add once Field is selected and adjusted as needed.
   • Select Number
   • Select Date Created
   • Select Assigned To
   • Select Last Message Date
   • Select First Message Date (custom)
     Note: First Message Date (custom) is a custom field and may not be available for selection.
   • Select Formula (Text)
     • Formula: Enter CASE WHEN decode({systemnotes},'Assigned',trunc({systemnotes})) = trunc({custevent3}) THEN 'YES' ELSE 'NO' END
7. Click Save & Run