Saturday, September 8, 2018

Create a saved search showing when a customer record was created and who created it

Problem/Business Requirement/Unique Question:

 

How to create a saved customer search that will show who and when a customer was created

 

Symptoms/Details of Business Requirements/Any Additional Information:

 

1.       On the System Notes, the following are the information that needs to be captured:

a.       Type = Create

b.       Field = Record

2.       If user have updated the address for this customer, the change or update is noted as Create as per system notes. Thus, the saved search results to multiple entries.

a.       Example of customer record system notes: Address was added at a different date

 

Date

Set by

Type

Field

Old Value

New Value

07/23/2008 11:25 am

E1 John Doe

Create

Record

CustJob

125

05/25/2009 7:15 am

E1 John Doe

Create

Address

 

OMG 10th Ave Concord NH 03301 United States

07/23/2008 11:25 am

E1 John Doe

Set

Status

 

Closed Won

07/23/2008 11:25 am

E1 John Doe

Set

Login Access

 

F

07/23/2008 11:25 am

E1 John Doe

Set

Customer ID

 

OMG

07/23/2008 11:25 am

E1 John Doe

Set

Global Subscription Status

 

Soft Opt-In

07/23/2008 11:25 am

E1 John Doe

Set

Inactive

 

F

b.       The field "Record" is not available on saved search. (System Notes Fields… = Field)  Using "Type=Create" as the filter will show multiple results.

c.       We cannot use "Type= Set" on filter because this will also show multiple results

 

 

Solution/Workaround/Gotcha/Recommendation Details:

 

1.       On the Criteria tab

a.       System Notes : Type = Create

b.       Formula (Numeric) = is 1 ; Formula : DECODE({systemnotes.field},'Record',1,0)

2.       On the Results tab

a.       Name

b.       Date Created

c.       System Notes : Type

d.       System Notes : Set by

 

The saved search result will now capture Type = Create and Field = Record.


No comments:

Post a Comment