Sunday, May 26, 2019

Customer Saved Search > Add a Column in the Results Page to Display the Latest Three User Notes

Show the latest three User Notes of Customer Records using a Saved Search:

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 > Customer> 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. Save & Run.

This Saved Search still shows up records but the column for the Formula shows blank if the User Notes is not in the latest three. The Formula shows the latest three User Notes in the Customer record since the Rank is based on User Notes Date.

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

No comments:

Post a Comment