MCAE: Open Prospect user interface with Prospect ID

If you have the Prospect ID, you can open the Prospect detail with the following URL with parameters:

With the Pardot user interface:
https://pi.pardot.com/prospect/read/id/179597888
https://pi.pardot.com/prospect/read?id=179597888

With the Account Engagement user interface:

https://mydomain.lightning.force.com/lightning/page/pardot/prospect?pardot__path=%2Fprospect%2Fread%2Fid%2F179597888

B2B Marketing Analytics app: sfdc_internal__B2BMA

B2B Marketing Analytics, a CRM Analytics app within Salesforce designed to analyze marketing and sales data. It leverages datasets from Account Engagement (formerly Pardot) and Salesforce to provide insights into campaign performance, prospect behavior, and overall marketing effectiveness. 

You can create the B2B Marketing Analytics app from Analytics Studio, select B2B Marketing Analytics, and follow the wizard.

You need to enter your Pardot Account ID, then select optional features, such as Account-Based Marketing, Multi-Touch Attribution, Prospect and Activity dataset, etc. If you stopped with the following error:

Unable to create app based on template: [sfdc_internal__B2BMA].

* Your org does not currently meet minimum data requirements to proceed. Please fix the following issues before creating a 'B2B Marketing Analytics' Application:

In the 'sfdcDigest_Contact_CWA' node, the 'pi__grade__c' field doesn't exist, is deprecated, or isn't accessible to the Integration User. In the 'sfdcDigest_Contact_CWA' node, the 'pi__score__c' field doesn't exist, is deprecated, or isn't accessible to the Integration User.

You need to adjust the fields in both Lead and Contact, the Account Engagement Score (pi__score__c) and the Account Engagement Grade (pi__grade__c) fields, to make them visible (as read-only is sufficient) to the Analytics Cloud Integration User profile. 

From the object manager, open each field and update the field-level security settings to grant visibility to the Analytics Cloud Integration User profile. 

Re-create the B2B Marketing Analytics app from Analytics Studio, and you should be able to pass the error.

Once the app is created, it can also be seen in the "Auto-Installed Apps" in the setup menu.

Salesforce Field history tracking on Record creation

In Salesforce, field history tracking can be setup for both standard and custom fields. 

On Record Creation
No, field history tracking does not log the initial values when a record is created.

On Record Update

Yes, it tracks changes to the specified fields when they are updated. It logs the old value, new value, user who made the change, and timestamp.

As noted in the last screenshot, the Mailing Country should ideally be auto-updated (from Flow, Trigger, etc.) after the record is created; however, the order can be misleading, as they have the same timestamp. If we use SOQL, will we see the difference? No, they may have the same exact milliseconds

Reference:

• Field History Tracking on Record Creation

Salesforce: Sales Territories (I)

Sales Territory

As per the Summer '24 release, Enterprise Territory Management is now called Sales Territories. 

Out-of-the-box sharing mechanism to share record access to users based on the territory setup:

• Account (View OR View and edit OR View, edit, transfer, and delete) 
• Contact (No access OR View all OR View and edit all contacts associated with accounts)
• Opportunity (No access OR View all OR View and edit all opportunities associated with accounts)
• Case (No access OR View all OR View and edit all cases associated with accounts)
• Leads (View, edit, and transfer OR View, edit, transfer, and delete)

* You can set access for the Contact and Lead if you use Private as the default internal access for contacts or cases.

Default access levels determine the default selection when creating new territory.

Territory Model

• Defines the structure and assignment of sales territories and their associated accounts and users.
• It's a hierarchical structure that organizes sales teams and their responsibilities.
• Allow for flexible assignment of accounts and users to territories based on various criteria.
• You can create and preview multiple models (2 for EE, 4 for UE/PE), but only one can be active at a time.
• Once a model has been created, you can open it to Clone, Activate/Archive, or view more info, such as the activation date, etc.

Territory Type

• It can help you group territories based on geography (e.g., North America, Europe), industry (e.g., Financial Services, Retail), or other factors.
• To categorize territories at a more abstract level, not within the hierarchy itself.
• Can be used in reports to analyze sales performance by type (e.g., compare sales across geographical regions). 
• Every territory you create must have a territory type.
• Territory types are used for organizing and creating territories only: they do not appear on territory model hierarchies.
• Each territory type has a priority (it must be a number). For example, "1" indicates the highest or lowest priority.

Create New Territory

Once the Territory Type and Territory Model are created (Territory Model does not need to be activated), you can add the Territory from the "View Hierarchy" in a Territory Model. When creating a new territory, you need to select:

• Territory Type 
• Parent Territory, this is populated by default
• Description (optional)
• Access level for: Account, Contact, Opportunity, and Case

After a territory is created, you can:

• Edit the Territory to set the Forecast Manager (for forecasting purposes)
• Assign users
• Manually add accounts
• Create assignment rules

- An assignment rule can be assigned to multiple territories
  - You need to run the rule when the account or assignment criteria are changed

• Create a child territory

- Inherited Assignment Rules from the parent territory

• Click the 'View Accounts' button to see all Accounts that fit into the rule. 

Account

Add the Assigned Territories related list to the Account page layout, and you will see all Territories related to the Account

Reference:

• Enterprise Territory Management Is Now Sales Territories
• Salesforce Sharing Using Enterprise Territory Management
• What's new in Enterprise Territory Management
• Managing Users and Roles Within Territories

Salesforce: Inactive User

When a Salesforce user has been deactivated, can we still assign the user as the record owner, or populate the user into a lookup field? 

1. Lookup Fields

From the Page Layout (UI): In the standard Salesforce user interface, you typically cannot select or populate a lookup field with an inactive user. The UI filters out inactive users from lookup fields to prevent confusion and ensure that only active users are selectable. Therefore, if you are trying to populate a lookup field from the page layout, you will not see inactive users listed.

From the Backend: You can populate lookup fields with inactive users through backend processes. This includes using data import tools, APIs, or automated processes (like Apex code, workflows, or triggers).

2. Record Ownership

From Setup > User Interface > User Interface, look for "Set Audit Fields upon Record Creation" and "Update Records with Inactive Owners" and enable it. Then, create a permission set with "Update Records with Inactive Owners" permission or enable the permission in the profile.

From the Page Layout (UI): In the standard Salesforce user interface, you typically cannot find or populate the record owner with an inactive user, even if you already have the above permission granted.

From the Backend: If you have "Update Records with Inactive Owners" permission enabled, you can populate record ownership with an inactive user through backend processes. This includes using data import tools, APIs, or automated processes (like Apex code, workflows, or triggers).

Without the permission enabled, you will receive the error message INACTIVE_OWNER_OR_USER:

CRM Analytics: Unsupported Salesforce Data

CRM Analytics (CRMA) is an analytics tool by Salesforce and is hosted in Salesforce's core platform; however, not all of Salesforce's objects and fields are supported by CRM Analytics data sync, including external objects, so you can't build a CRMA dashboard that uses those fields or objects. Check out the following article.

A supported object is one for which you can grant field-level access in Setup. If you're unable to assign field access to a Profile in Setup, that object is considered unsupported in CRMA, e.g. LoginHistory object.

The following statement from the above help article explains this: "To extract fields, the object must be visible to the Integration User. If you can’t manage a field’s visibility in Setup, you can’t grant the Integration User access to it."

Reference:

• Unsupported Salesforce Objects and Fields in CRM Analytics

Salesforce: Retrieve Report Metadata (2)

In a previous blog, we discussed how to use package.xml in Workbench to retrieve report metadata. However, if we simply need to retrieve the report metadata, e.g., to understand the object of a field added to the report, we can simply use REST Explorer in Workbench.

Use GET method /services/data/v62.0/analytics/reports/00OGB00000Dt9Qv2AJ

Change the highlighted ID above to the report ID you want to analyze.

A few items to note from the above data:

• factMap > T|T > aggregates - number of rows of the report
• factMap > T|T > rows - the cell value of each row
• reportMetadata > detailColumns - API field name for each column in the report, including the object name and whether it is a lookup field in the report type
• reportMetadata > reportType - the report type used for the report

The same result (in XML format) you can get using REST Explore from Salesforce Inspector Reloaded.

Salesforce: Custom Report Type Metadata

The Spring '25 release enhanced the Custom Report Types user experience on the setup page; "Enhanced Custom Report Type Setup Page" in the "Reports and Dashboards Settings" should be enabled in the setup menu, which should be turned on by default; otherwise, your Salesforce admin needs to enable it.

The Custom Report Type is stored as metadata, which we shared here. How can I retrieve the Custom Report Type metadata without an IDE? You need to use a tool that supports REST Explorer:

1. Workbench

• Go to Salesforce Workbench.
• Log in with your Salesforce credentials.
• Navigate to Utilities > REST Explorer.
• Use the REST API endpoint mentioned above to retrieve report metadata.

Use the GET method, for example: 

/services/data/v62.0/analytics/report-types/Account_custom__c

You can get the report type API Name from the Custom Report Type user interface in the setup menu, and add a suffix of __c.

2. Salesforce Inspector Reloaded

• Open the Salesforce Inspector Reloaded panel
• Select the REST Explore menu
• Use the GET request as in Workbench 

/services/data/vXX.0/analytics/report-types/Account_custom__c

Salesforce: Upload Files to a Record

As shared in the Files sharing Query blog, a file can be shared with users, groups, records, and the content library. 

Let's see how the system works when uploading a file from a record related list.

By default, when a file is loaded from a record, the user who uploaded the file becomes the File Owner, and the record owner has extra permissions compared to other users; this applies when default file access is set to Set by Record, check this article.

File owner:

Record owner:

Users with edit access to the record <-- similar to the record owner

Users without edit access to the record:

Users with permission "Modify ALL records" or "Modify ALL data" <-- similar to file owner

Additionally, users with "Create Public Links" permission

The user will be able to share the file externally with an expiration date and a password:

Who Can Access?

Click the Share dropdown from the Files related list, and you can see "who can access"? Remember the  "Set by Record" mentioned earlier, and the ShareType in Query is "I", not "V".

Let's see it in a query:

SELECT Id, ShareType, ContentDocumentId, ContentDocument.Title, LinkedEntity.Name, LinkedEntity.Type FROM ContentDocumentLink WHERE ContentDocumentId = '069VZ000006928nYAA'

Share the file with another user as a Collaborator manually: 

Share the file with a Library

What happened when we shared the file with a library? 

Notice that the share type for the record and the user who shared earlier has changed to Viewer. When a file is added to a library, it means that access to the file follows access to the library; this setting can’t be changed. You can't manually re-share the file to the user as a Collaborator. You also can't remove the file from the Library. Vote for this idea. But, if you have a folder in a library, you can move the file within the library and the subfolder.

Reference:

• Set File Sharing to Inherit Record Settings
• Change File Access in Lightning Experience

CRM Analytics: Why I don't see Action

CRM Analytics offers users immediate action on a particular data in the dashboard. The "action" here refers to Salesforce action, where you can create and edit data directly from a CRM Analytics dashboard. 

You can configure this from the dataset level by editing the dataset and then clicking the Configure Actions button or from the dashboard/lens, then click Configure Actions from the drop-down menu. 

However, you may experience the action is not visible to you or your user; here 2 things that you can check:

1. CRM Analytics displays only the actions that are added to both the Quick Actions in the Salesforce Classic Publisher and the Salesforce Mobile and Lightning Experience Actions sections of the layout. This applies to the page layout assignments as per the user profile, too.

2. The user doesn't have permission to perform the action on a specific object, such as not having Edit permission on the record or object to update the record action.  

 

Reference:

• Perform Actions on a Salesforce Record from CRM Analytics

CRM Analytics: Dynamic table with Input Widget

Use case: give users the ability to show top N open opportunities based on Amount in a dashboard.

Step:

1. Add a table widget with the columns, set a query limit (example 10), and add filters as needed; for my use case, the filter isClosed = false and sorted by Amount.
2. Add an input widget and set the parameters in Input Values and Input Style.
3. Add a text widget to show the top X rows for the column.

Here is the result:

What needs to be configured?

• There is no need to change the input widget except the parameters mentioned above. 
• For the table widget to show top X records, we need to change the limit from 10 (example) with binding [ "{{cell(input_1.selection, 0, \"input\").asObject()}}" ]; input_1 is the Query ID of the input widget.
• For the text widget, click the "Add Query Data" button and select the Dynamic Data Source, Dynamic Text Field, and Interaction Type as below; you can manually add "Top " text after configuring the query data.

Reference:

• Create an Input Widget

CRM Analytics: Duration between Date or Date/Time Fields

In the SAQL samples blog, we briefly share samples of formulas for calculating duration between 2 date or date/time fields using daysBetween(start date, end date) and date_diff(datepart, start date, end date).

Let's see more details in this blog here and use the sample data below with the user timezone as GMT.

SAQL to calculate days between date/time with date field:

• daysBetween(toDate(DateTime1__c_sec_epoch), toDate(Date1__c_sec_epoch))
• date_diff("day", toDate(DateTime1__c_sec_epoch), toDate(Date1__c_sec_epoch))

Notes: 

• If date1 is after date2, the number of days returned is a negative number.

Analysis:

• date_diff() will simply compare the date value only, ignoring the time value. If you see rows #9, 10, and 11, the time value is removed, so the duration would be 2 days. The same behavior applies to all other rows.
• daysbetween() is a bit complicated; the formula will consider the time value in the calculation, but the result is truncated without decimal points; for example, row #11 -- start date = 2025-02-27T11:00:00.000Z, end date = 2025-03-01 is treated as 2025-03-01T00:00:00.000Z; exact time difference = 1 day 13 hours, the result is truncated as 1. take another example from row #2, where start date = 2025-03-02T23:45:00.000Z, exact time difference =  -1 day, -23 hours, -45 minutes, the result is truncated as -1.

Now, let us change the user timezone to Singapore, which is GMT+8.

The result in CRM Analytics is the same as before changing the user timezone. The Date/Time field will be converted into GMT timezone (for Custom Time Zone Support is not enabled).

If you need to calculate the duration in hours or minutes, you can only use date_diff(). Let's see some samples below. This is similar to the "day" datepart, which ignores the whole time; if we use "hour," it ignores minutes onwards.

Analysis: Rows #10 and 11 have the same result because the minute in DateTime1 is ignored; the same is true for rows #1 and 2, even though they are just 15 minutes different in the exact time difference, but they will be shown as 1 hour.

Check out this blog to calculate case aging excluding weekends.

Reference:

• date_diff()
• daysBetween()

CRM Analytics: Long Text Area field only show 255 chars

The Long Text Area field or Rich Text Area field allows users to enter up to 131,072 characters on separate lines, which by default is 32,768 when creating the field. However, the table widget in CRM Analytics only shows 255 characters. What is the cause?

By default, Precision is blank in the sync setup; you can navigate from Data Manager >>  Connections >> SFDC_LOCAL, click the object name, click the pencil icon next to the field name, enter the length of the characters as you need in the Precision box.

Salesforce: Case fields that cannot be removed from page layout

Certain Salesforce Case standard fields cannot be removed from the case page layout, but you can set them as read-only and tell your users to ignore those fields, it would be a good idea to move them to the bottom of the page. 

These fields are marked with a blue dot when you open the case page layout:

 

• Contact Name
• Status
• Priority
• Case Origin
• Subject
• Description
• Web Email

dsds

If you upgrade the form to a dynamic layout, the restriction that the fields must always be shown is no longer applied.

CRM Analytics: Calculate aging excluding weekends in Dataflow

We shared the Salesforce formula field to calculate aging without the weekend here. But can we replicate it in CRM Analytics? 

Here is the formula using dataflow in compute expression:

Full_Weeks (in Numeric)
floor(date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))/7) * 5

Remaining_Days (in Numeric)

date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch)) % 7

Start_Day (in Numeric)

day_in_week(toDate(CreatedDate_sec_epoch))

Weekend_Adjustment (in Numeric)

case when (Start_Day + Remaining_Days) > 7 then 2 

     when (Start_Day + Remaining_Days) == 7 then 1 else 0 end

Business_Days_Aging

Full_Weeks + Remaining_Days - Weekend_Adjustment

Notes:

• The floor(numeric) function rounds a numeric value down to the nearest integer. For example, floor(125.625) will return 125.
• For more information and samples on date_diff() function, check out this blog Duration between Date or Date/Time Fields. 
• The day_in_week(date) function returns the day of the week as numeric, where Sunday = 1, Monday = 2, etc.

Sample:

Convert SOQL result Date/Time field to Excel

Sample: SELECT Id, CreatedDate FROM Account

Result of CreatedDate: 2024-08-14T14:48:05.000+0000

To Convert into Excel as Date/Time format:

1. Formula =VALUE(SUBSTITUTE(LEFT(A2, 19), "T", " "))
2. Format Cells... (or Ctrl+1) for the above formula cell, select Custom, and Type: yyyy-mm-dd hh:mm:ss
3. Done