Salesforce: Retrieve Custom Field Created Date

If you notice in Salesforce: Retrieve all fields from an Object, all options to retrieve all fields will not include Created Date, but only Last Modified Date, so this blog will share how to get the custom field creation date.

You need to query the CustomField object and select Use Tooling API.

Sample query: SELECT DeveloperName, CreatedDate FROM CustomField WHERE TableEnumOrId = 'Account'

use Salesforce Inspector

use Developer Console

Description:

• DeveloperName field in the Tooling API does not include the suffix __c, so you need to manually add __c to get the API Name, e.g. DeveloperName = Region --> API Name = Region__c
• TableEnumOrId field, for the standard object, it would be the object name e.g. Account, for custom objects, it would be the object Id, starting with 01Ixxxxxx, you can easily get the ID from the URL of the object from Object Manager.

Reference:

• CustomField
• Retrieve all fields from an Object
• Easily Find Newly Created Fields

Salesforce: Retrieve List View columns and filters

Five years ago, I blog retrieving List View Sharing to get the list of users, groups, and roles that can access list views. This blog is to continue to get the columns and filters.

Open Workbench and from Rest Explorer, example: /services/data/v53.0/sobjects/Case/listviews/00B0o00000Fq3xQEAR/describe

Make sure to update the object name in red and List View id in orange.

To get the list of columns, click the "Show Raw Response" link. 

Copy the result of the column and use JSON to CSV tool to convert it into CSV or Excel file. Some of the columns here are added by the system and do not appear in the list view, such as Id, Record Type Id, etc.

To get the filters; looks for the query underneath, this will also show the fields, filter, and order in SOQL format:

SELECT CaseNumber, Contact.Name, Subject, toLabel(Status), toLabel(Sub_Status__c), toLabel(Priority), CreatedDate, Owner.NameOrAlias, RecordType.Name, Id, RecordTypeId, CurrencyIsoCode, LastModifiedDate, SystemModstamp, Contact.Id, ContactId, Owner.Id, OwnerId FROM Case WHERE IsClosed = false AND (Contact.Name != null OR Status = 'Escalated') ORDER BY CaseNumber DESC NULLS LAST, Id DESC NULLS LAST

** Use toLabel(fields) to translate SOQL query results into the user’s language.

Here is the original list view:

Salesforce: Case Team Report

We shared Account Team Report and Opportunity Team Report a few years back, in this blog, we will look into the Case Team Report.

Similar to the Account and Opportunity report, you can filter Case based on the owner too, here are the options:

Let us go through each of them:

My cases: ONLY cases that are assigned to you.

All cases: ALL cases that you have access to, no matter who is the case owner.

User owned cases: ALL cases that you have access to, and are owned by a user, not a queue.

Queue owned cases: ALL cases that you have access to, and are owned by a queue, not a user.

My case team's cases: These are cases where you are added as a Case Team member.

My role-based team's cases: These are cases owned by you and your subordinates in the role hierarchy, but not owned by a queue, although you or your subordinate is a member of the queue.

Reference:

• Account Team Report 
• Opportunity Team Report

Salesforce: File and Content Report Type / Query All Files

File and Content report type produce a report of Salesforce CRM Content and Files in Salesforce. When you create a new report using the File and Content report type, a lot of fields will be added by default.

When you run the report with standard fields, you may see no result, this is because the data, instead of showing as blank, this report type will just remove the rows altogether, such as Downloaded By, if the files have never been downloaded, the files will not appear as a row in the report. The same behavior for fields such as Link Name, if the link for the file has not been created.

On the other hand, if the file has been uploaded in multiple versions, the same file name will appear in a few rows based on the number of file versions uploaded, if you add the File Version field to the report.

This report will show files that you owned and shared with you, even if you are a system admin or user with View All Data permission, and selects Show Me All Files in the filter.

Query All Files

There is permission called Query All Files, this permission is not enabled by default for the System admin profile, it allows users with View All Data permission can query ContentDocument and ContentVersion and retrieve all files in the org, including files in non-member libraries and files in the unlisted groups, and not just files that are owned or shared to the user. 

However, this permission does not impact to result of the report using the File and Content report type, even if the user has Query All Files permission.

Reference:

• Query All Files
• Files sharing Query

Salesforce: Send List Email (2)

This is to continue Salesforce: Send List Email blog written 19 months ago.

Task

As per the EmailMessage blog, every email sent out will also create Task, the same for emails send from Send List Email in list view, it will create a Task too, but a bit different. Let us query the Tasks created.

From the above screenshot:

1st row is manual sending email from the Contact record

2nd row is using send list email from the Contact list view

Subject

1st row - Subject start with Email:

2nd row - Subject start with List Email:

Related To (or WhatId in API)

1st row - WhatId is blank

2nd row - WhatId starts with 0XB

Comments (or Description in API)

1st row - contain: To, CC, BCC, Subject, and Body of email

2nd row - blank

Bonus:

If you do not see the Send List Email button in the Lead or Contact list view, check this:

1. Make sure Send List Email is selected in the List View Button Layout in Contact or Lead object setup, it used to be called Search layouts for Salesforce Classic prior Summer '22 release. 

2. Make sure the users have Allow sending of List Emails permission. However, if you remove Mass Email permission, this permission will be auto removed too, and re-enable back the Mass Email permission will not auto-enable Allow sending of List Emails permission.

Salesforce: Case Lifecycle report type

Case Lifecycle report is a standard Salesforce report type provided, but probably the one that is not so famous, not many people use, a bit confusing and very less official article about this.

What is the Case Lifecycle report?

Run case lifecycle reports to view the results of the Range field, which indicates the length of time since the case last changed status or owner. Each time the status or owner changes, the counter begins again at zero --- copy from Using Custom Report Types to Report on Support Activity.

Here are a few in the trailblazer community throughout the years:

• 2011 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7ThYSAV
• 2013 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7Mt7SAF
• 2013 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7Z2gSAF
• 2013 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7iXtSAJ
• 2014 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7syiSAB
• 2014 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A8JsWSAV
• 2015 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7dltSAB
• 2016 - https://trailhead.salesforce.com/trailblazer-community/feed/0D53A00002j2vkQSAQ
• 2017 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A80zOSAR
• 2018 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A7UcBSAV
• 2018 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A8FNMSA3
• 2020 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A8ifqSAB
• 2020 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A91WPSAZ
• 2021 - https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A96K8SAJ

Ideas related to this in IdeaExchange:

• 2013 - Need to add "Action" filter Case Lifecycle Report
• 2015 - Case Lifecyce report Business Duration Hours
• 2015 - Bucket Business Hours Duration in Case LifeCycle Report 

List of Salesforce articles:

• 2021 - Case Lifecycle report with duration filter doesn't filter as expected
• 2022 - Enable Case duration or age in business hours to enable the 'Business Hours Age' field

From all the above documents and conversations that can be found using Google search, nothing really explained how Case Lifecycle report works?

What is the Case Lifecycle report?

It is a standard report type that tracks the duration of a case when the case owner or status is changed.

There are 5 fields available in the Case Lifecycle report:

• Case History Owner
• Case History Status
• Case History Last Modified By
• Case History Last Modified Date
• Duration

Let us walk through this report type with samples:

1. New Case creation

Remember, every time the case owner or status is changed, a new entry will be created for the case lifecycle report, so creating a new case will generate a new entry for the lifecycle report.

The below report is using minutes unit.

After 2 minutes, let us refresh the report, both the duration and age increase by 2 minutes

2. Change the Case owner

As mentioned earlier, changing the case owner will create a new entry to the lifecycle report.

- 1st row: duration at 7.9 minutes, the case owned by 'Johan Dev501' for 7.9 minutes, before hand over to the new owner.

- 2nd row: duration at 1.083 minutes, the latest duration since it was assigned to a new owner and it has been held for 1.083 minutes  

Let us refresh the report without changing anything on the case

- 1st row: no change, this is completed, the duration will no longer be changed

- 2nd row: now the duration change to 5.1 seconds

 

3. Change the Case owner

Changing the case owner will create a new entry to the lifecycle report.

- 2nd row: this is completed, the duration will no longer be changed

- 3rd row: the duration start for the new owner

4. Change Status

Changing the case status will create a new entry to the lifecycle report.

- Case History Last Modified By, show who change the status or owner

- Case History Last Modified Date, show when does the change happen 

- Case History Owner, show the owner when the entry is created 

- Case History Status, show the status when the entry is created

- Notice that Age keeps counting from the beginning, this is because the case is still open

5. Closing the case

Changing the case status will create a new entry to the lifecycle report.

Let us refresh the report without changing anything on the case

- Notice that the age stops at 26 minutes because the case has been closed

- But, the duration on the last entry keep counting, no matter the case status  

6. Re-open the case

Changing the case status will create a new entry to the lifecycle report.

- Age recalculate again from the beginning

- Duration of the initial Closed entry stopped at 4.033 minutes

7. Re-closing the case

After a few minutes or hours or days, refreshing the report will not change anything, except the duration for the last items, which will keep counting.

Can this report type be customized? 

Such as using a custom field instead of standard status and owner? unfortunately not, see the above list of idea exchanges. Can we only track the owner only or status only? unfortunately also not.

Can we get the data with SOQL? 

The closest we can get is by query to the CaseHistory object, but without duration and age. 

SELECT Id,CaseId,CreatedBy.Name,CreatedDate,Field,OldValue,NewValue FROM CaseHistory WHERE CaseId = '5000I00002FBKqF' AND Field IN ('Owner','Status') ORDER BY CreatedDate

Note: owner will show double, as Id and Name, so you just need to get one as needed.

If you have anything to add for the Case Lifecycle report type, please comment on this blog.