Salesforce: Case Age

In the Salesforce report, we can quickly add the 'Age' field to the report; it is even more flexible, where we can select the "unit" in Minutes, Hours, and Days. But before we discuss this further, let us agree on what is Case Age? 

• For open cases, the elapsed time from creation to the present
• For closed cases, the elapsed time from creation to the closing time of the case

Because 'Age' in the report is not an actual field, we cannot show it in the page layout or list view, to get this, we can create a custom Age field with the following formulas:

• Case Age in Days - IF(IsClosed, ClosedDate - CreatedDate, NOW() - CreatedDate)
• Case Age in Hours - IF(IsClosed, (ClosedDate - CreatedDate) * 24, (NOW() - CreatedDate) * 24)
• Case Age in Minutes - IF(IsClosed, (ClosedDate - CreatedDate) * 1440, (NOW() - CreatedDate) * 1440)

But what if we want to exclude the weekends? Here is the formula to calculate case age without weekends in Days:

IF (IsClosed,
IF ((CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Datevalue(ClosedDate) - Datevalue(CreatedDate))/7)*5)
- 1) < 0,
0,
CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Datevalue(ClosedDate) - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Datevalue(ClosedDate) - Datevalue(CreatedDate))/7)*5)
- 1),
IF ((CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Today() - Datevalue(CreatedDate))/7)*5)
- 1) < 0,
0,
CASE(MOD( Datevalue(CreatedDate) - DATE(1900,1,1),7),
0, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6, CASE( MOD( Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Today() - Datevalue(CreatedDate))/7)*5)
- 1)
)

Some example: 

Questions:

• Will the calculation include decimal points for time differences? No, unless someone has an updated formula to include the time
• Will the calculation honor the user's timezone? Yes, the date is based on your user timezone in Salesforce. For example, user A with Pacific time will see a date time field as "10/01/2021 18:27"; however, user B in Singapore time will see the same data as "1/11/2021 10:27 AM". The formula field calculation will only based on date; therefore, the result of aging between A and B will be one day different. For example, the below case is an open case, and today's date is 3rd Nov 2023.

User A

User B

Note: since this formula field is using the DATEVALUE() formula, if your users are also located in the daylight savings timezone, you need to enable "Improve DATEVALUE() accuracy for DST" under "Company Information" in the setup menu (check out this article); otherwise the calculation for users (with daylight saving timezone) related to processing times between 11:00 PM and 1:00 AM may wrong.

Another sample: 

The report was run on 1-Dec-2023 at 3:23 PM, SGT.

The same report is opened by different users with Pacific time with "Improve DATEVALUE() accuracy for DST" enabled. The current time for this user was 30-Nov-2023 at 11:23 PM PST.

Case 12311766, 12311380, and 12311379 were created on the same day as the current day, 30-Nov-2023, for users in the Pacific time, so those cases' ages (excluding weekends) are 0 days.

Time difference

The above case age calculation will ignore the time of case creation and time of case closed. The following formula will return the time in hours (while ignoring the date) -- result in the number field with 2 decimal points.

(TIMENOW() - TIMEVALUE(CreatedDate))/3600000

The above report is run on 30-Nov-2023 at 4:34 PM:

• the row 4th from bottom: this is counted between 4:46 PM with 4:34 PM = 23.81
• the row 3rd from bottom: this is counted between 3:56 PM with 4:34 PM = 0.64

So, this is clear that the dates are simply ignored.

Reference:

• Calculate Case Age in Reports with a Custom Formula Field
• Business Hours Age field values explained
• Calculate Case age based on working business hours
• SimplySfdc: Calculate the age for weekdays and weekends
• DATEVALUE

CRM Analytics: Salesforce Output Connection

For those who use CRM Analytics, we know that we can use dataflow/recipe to prepare datasets, which means transforming data for use in dashboards. Additionally, using a recipe, you can use the data prepared (with transformed data) to store in Salesforce or another Salesforce org. Imagine that CRM Analytic experts now become a specialist without the need to write scripts for Apex Scheduler.

Push your prepared dataset data into any Salesforce org from CRM Analytics Salesforce Output connector and Data Prep. With your prepared data back in Salesforce, you can integrate external data, apply the suite of Salesforce automation tools to act on the data, and allow non-CRM Analytics users access to the data through reports and dashboards.

Here are the steps:

1. Enable "Enable Salesforce output connection" in the Analytics Setting in the Salesforce setup menu.

2. From Analytics Studio >> Data Manager, create an Output Connection; remember to add the security token after the password and change the Service URL if you plan to test in a sandbox

3. In the recipe Output node, select "Write To" with "Output Connection"; the operation could be Insert, Update, or Upsert. For the update operation, select a field from the destination object for the External ID to act as the unique record identifier, such as an Id from the source object, but it can't be a look field. The system will match using this field to determine if the operation to insert to update.

Scenario: sync a custom object from Account with a lookup field from the custom object to Account. 

As mentioned above, you can't use a lookup field as an External Id, so we need to create a text field to store the Account Id in the target object for matching. 

All columns from the source must be mapped one-to-one to the output object, which also means we can't map the field from the source node twice, so we need to clone the source field in a transform node.

Here is the detail of the Output node in a simple recipe:

External Id is set to the "Account Id (copy)" field, which is a text field in the target object to hold the Id of the Account record.

These are the fields of the target object :

Result after recipe run:

Reference:

•  Salesforce Output Connection

Salesforce: Related components

There are many components available in Lightning Page Builder related to the child object, parent, or child of the parent. Let us discuss each component here:

Related List

This component will show all "related lists" (child objects) that are added to the Classic page layout assigned. There is nothing new here; all setup is done in the Classic page layout. It will show all data from the child objects, but we cannot filter the related list based on something, such as "Open Opportunities".

The benefit of adding the child objects to the related list is, if you have the Related List Quick Links component, the child records will be automatically shown there.

Related List - Single

The idea of using this component is to show records from a child object that links to the record, instead of all child objects that are added to the related list. 

Additionally, instead of showing details of the child record, we can configure the component to show the child of the parent record. 

Example: On the Contact page, we can configure the component to show Contact Roles (which is a child object of the Contact). Or, we can also configure the component to show Assets (which is a child object of the Account).

One thing to make sure here is, the object must be added to the related list of the "parent record"object (as in the above screenshot).

Here is the option of "Parent Record" for Contact, select "Use This xxxx object" to show the child the current object; if the current object has other parents, it will show the field name here.

For the "Related List Type", the list (Basic List and Enhanced List) will only work when the component is used in the broader panel; otherwise, it will shown as a Tile.

Dynamic Related List - Single

We discussed Dynamic Related List in the previous blog; in short, this component is enhanced from Related List - Single:

• It does not depend on the related list
• Ability to filter only specific data, such as Open Opportunities, Open Cases, etc.
• Show/hide action bar
• Configure the fields, sort field and order

But because this does not depend on page layout, the object added here will not shown in the Related List Quick Links component.

However, there is a limitation of Dynamic Related List - Single, it does not support all child objects as in the "Related List - Single" component, see the difference here:

Related List - Single

Dynamic Related List - Single

As you see from the above screenshots, for the Case object, Dynamic Related List - Single does not have Email, Files, etc.

Vote for this idea Dynamic Related List - Single to support all objects.

Related Record

This component allows the admin to show a set of fields from the object itself or from the parent record.

You need to create "action" for the object and set it as Update Action. When creating the action, you can select the fields to be added, set as mandatory or as read-only.