Saturday, May 25, 2019

Lead Saved Search > Add a column in the Results page to display the three most recent User Notes

1. Navigate to Reports > Saved Searches > All Saved Searches > New.

2. Search Type: Customer.

3. In the Criteria tab, add the following filters:

  • Stage > any of > Lead > Set
  • User Notes Fields… > Memo > is not empty > Set

4. In the Results tab, add the following:

  • Field: Name
    Summary Type: Group
  • Field: Formula (Text)
    Formula: case when ((dense_rank () over (order by {usernotes.notedate} DESC)) < = 3) then {usernotes.note} else NULL end
    Custom Label: Latest Three Memo

5. Rename the Saved Search.

6. Click Save & Run.

*This Saved Search will still show up records but the column for the Formula will show blank if the User Notes is not in the latest three. The Formula will show the latest three User Notes in the Lead Record since the Rank is based on User Notes Date.

Note:  If the User Notes are created within the same time, the formula will not be able to identify which was created first.

No comments:

Post a Comment