Unable to Fetch Values from Email Message ST & Email Activities ST in Saved Search

1. Communication Messages – Need to include all communication messages, excluding invoices and service order emails.
2. Communication Activities – Need to include all activity types (calls, events, etc.).
3. Sales Email Messages ST – Should fetch all email messages under Sales.
4. Sales Email Activities ST – Should fetch all email activities under Sales.

• Provide a solution to ensure data retrieval for all relevant customer records.
• Provide a resolution to fetch the Last Modified Date for specific records.
• Confirm that communication messages and activities are correctly included in the saved search.

Solution:

Last Modified Field Formula

CASE
WHEN {messages.messagedate} IS NOT NULL THEN {messages.messagedate}
WHEN {activity.date} IS NOT NULL THEN {activity.date}
WHEN {custrecord_jj_st_email_customer.custrecord_jj_st_email_date_time} IS NOT NULL THEN {custrecord_jj_st_email_customer.custrecord_jj_st_email_date_time}
WHEN {custevent_jj_st_activities_id.date} IS NOT NULL THEN {custevent_jj_st_activities_id.date}
END

Saved Search Code

var customerSearchObj = search.create({

   type: "customer",

   filters:

   [

      ["isinactive","is","F"], 

      "AND", 

      ["max(formuladate: CASE WHEN {messages.messagedate} IS NOT NULL THEN {messages.messagedate} WHEN {activity.date} IS NOT NULL THEN {activity.date} WHEN {custrecord_jj_st_email_customer.custrecord_jj_st_email_date_time} IS NOT NULL THEN {custrecord_jj_st_email_customer.custrecord_jj_st_email_date_time} WHEN {custevent_jj_st_activities_id.date} IS NOT NULL THEN {custevent_jj_st_activities_id.date}END)","isnotempty",""]

   ],

   columns:

   [

      search.createColumn({

         name: "entityid",

         summary: "GROUP",

         label: "ID"

      }),

      search.createColumn({

         name: "altname",

         summary: "GROUP",

         label: "Name"

      }),

      search.createColumn({

         name: "formuladate",

         summary: "MAX",

         formula: "CASE WHEN {messages.messagedate} IS NOT NULL THEN {messages.messagedate} WHEN {activity.date} IS NOT NULL THEN {activity.date} WHEN {custrecord_jj_st_email_customer.custrecord_jj_st_email_date_time} IS NOT NULL THEN {custrecord_jj_st_email_customer.custrecord_jj_st_email_date_time} WHEN {custevent_jj_st_activities_id.date} IS NOT NULL THEN {custevent_jj_st_activities_id.date}END",

         label: "Last Activity Date"

      })

   ]

});

var searchResultCount = customerSearchObj.runPaged().count;

log.debug("customerSearchObj result count",searchResultCount);

customerSearchObj.run().each(function(result){

   // .run().each has a limit of 4,000 results

   return true;

});



/*

customerSearchObj.id="customsearch1740131600881";

customerSearchObj.title="Last Activity of Customer - DAJ (copy)";

var newSearchId = customerSearchObj.save();

*/