Sunday, October 17, 2021

Tableau CRM: Getting Started with Recipe a.k.a Data Prep 3.0

Recipe has existed in Tableau CRM for a long time, but not really used a lot because Dataflow offers more powerful data transformation, although there are functions which only available in Recipe, such as Join. However, in the last few releases, Salesforce introduced a new term called "Data Prep 3.0", which actually is Recipe, and stop to enhance everyone's darling "dataflow".

When Data Prep 3.0 was introduced (I have no idea if there was Data Prep 2.0 and 1.0), it was not so popular because of the lack of functionalities, such as support to upload/download JSON, use SAQL, etc. Since then, in every release, more and more features are added to the Data Prep/Recipe, so it may be worth starting to look at it.

As per the Winter '22 release, at a glance, Recipe now looks quite promising, it supports many things: 

  • Download/upload recipe in JSON -- same with Dataflow
  • Formula with query -- comparable with computeExpression in Dataflow
  • Multiple row formula -- comparable with computeRelative in Dataflow

Here are things that are better in Recipe compare to Dataflow

  • Join (Left, Right, Inner, Outer) -- this is not available in Dataflow
  • Preview -- we cannot preview data in Dataflow
  • Layout stay when save -- you need EADashboardHelper in Dataflow
  • Multiple actions in a Transform node -- less node compare to Dataflow
  • Aggregate
  • Sentiments, Predict Missing Values, Cluster
  • Trim, Substring, Split, Uppercase, Lowercase, Replace -- some of them can be achieved with computeExpression in Dataflow
  • Format Dates, Bucket, Edit Attributes

Here are things that can be improved in Recipe
  • Ability to re-order items in Transform node 
  • Ability to re-order column in Preview data
  • Ability to sort and filter Preview data
  • Running a recipe does not show details in Monitor

A sample of Recipe

Formula in Transform node is based on TCRM SQL, so this is not SAQL, or SOQL.
for example: SQL
when LeadSource = 'Partner' OR  LeadSource = 'Web'
then 'Partner Web'
else LeadSource

for example: SAQL
when LeadSource == "Partner" OR  LeadSource == "Web"
then "Partner Web"
else LeadSource

Direct Data in Recipe

Winter '22 release also make Direct Data in Recipe become General Available. With Data Direct, you can access Salesforce live data in the recipe, not accessing data stored in Tableau CRM via sync. However, but sync needs to be enabled in the Tableau CRM to use this.

Another catch is, you cannot use direct data when the object has been sync to Tableau CRM, example: you have dataflow or recipe that sync Opportunities, then Opportunities will no longer be available for Data Direct, which is quite sad, sometimes we need to access the real-time data directly, not "obsolete" stored data in Tableau CRM.

When I search "Opportunity". because the opportunity object already syncs in Tableau CRM from other dataflow/recipe, it will only be available via Connected (in yellow), not Direct (in green)

If you have more things that not cover here or the information shared here is wrong, feel free to comment on this blog or reach me.

Wednesday, October 13, 2021

Salesforce: Email Template with Hyperlink

We shared about the Lightning Email Template seven months ago, as you read, it is easy to create an email template with merged fields, such as Account Name, Opportunity Name, etc.

However, those merge fields are not clickable (not hyperlink), so if the email sent is to your internal Salesforce users, it will be ideal if recipients are able to open the link directly from the email received.

Let's do a quick hands-on, in this example, I would like to build an email template for Case, contain 2 hyperlinks:

  • Case Number, to open the case
  • Account Name, to open the Account in the case

Here is the step:

1. Create the email template 

Create the email template as per normal, add all merged fields needed

2. Case Number hyperlink

Open a case from your browser and grab the URL and copy to a text editor, for example,

Change the Case ID (as in red) and replace with {{{Case.Id}}}, now we have{{{Case.Id}}}/view

Copy the above URL, select {{{Case.CaseNumber}}} from the email template, then click the Link icon (2nd from right) and paste the URL above (in the yellow background) here, then click the Save button.

3. Account Name hyperlink

Repeat the same for Account Name, get the URL and change the Account Id, so it will look like{{{Case.AccountId}}}/view 

Copy the above URL, select {{{Case.Account__c}}} from the email template, then click the Link icon (2nd from right) and paste the URL above, then click the Save button.

If you notice I use Case.Account__c, this is simply telling that I have a formula field in Case to get Account Name from AccountId.

4. Sending Email

Open a case and click the Email tab, select the email template created, and notice the email body is auto-populated with hyperlinks.


Friday, October 1, 2021

Salesforce: Clone & Fields in Page Layout

  • When you clone a record, by default, fields that are not in the page layout will not copy to the new record.
  • If the record you’re cloning or a related record contains a field you don’t have access to, the field in the new record is blank.
  • If the record you’re cloning contains a field you have read-only access to, the field in the new record is blank.
  • If a related record contains a field you have read-only access to, the field value is copied into the new record.

Related List
When you create a child record from the parent Related List, even the parent field is not in the child page layout, the parent value will be populated.


Sunday, September 19, 2021

Salesforce: Sharing Detail

More than 5 years ago, we wrote about Sharing Button URL, where you can check Sharing Detail and Access, which is useful to troubleshoot why a user is able to view or edit a record.

On the Sharing Detail page, it will show you all User, Public Group, Role, Role and Internal Subordinates and the access level. In this blog I am using Opportunity as a sample, Opportunity visibility in my org is Private.

We can get the same result from SOQL: SELECT Id, OpportunityAccessLevel, RowCause, UserOrGroupId FROM OpportunityShare WHERE OpportunityId = '0062H0000123456QAN' ORDER BY UserOrGroupId 

Let us compare from Sharing Detail page:
  1. Sharing Detail #1 = SOQL row #7, reason = Account Sharing which = Implicit Child [in Account Sharing Rules, to Role]
  2. Sharing Detail #2 = SOQL row #6, reason = Opportunity Sharing Rule which = Rule [in Opportunity Sharing Rules, to Role and Internal Subordinates]
  3. Sharing Detail #3 = SOQL row #5, reason = Account Sharing which = Implicit Child [in Account Sharing Rules, to Role and Internal Subordinates]
  4. Sharing Detail #4 = SOQL row #4, reason = Opportunity Sharing Rule which = Rule [in Opportunity Sharing Rules, to Public Group] 
  5. Sharing Detail #5 = SOQL row #1, reason = Account Sharing which = Implicit Child [User is Account Owner] 
  6. Sharing Detail #6 = SOQL row #2, reason = Owner which = Owner [User is Opportunity Owner]
  7. Sharing Detail #7 = SOQL row #3, reason = Opportunity Team with Split which = Team [User in Opportunity Split]
  8. Sharing Detail #8 = SOQL row #8, reason = Account Sharing which = Implicit Child [in Account Sharing Rules, to Public Group]

Access level comparison:
- In Sharing Detail, Full Access = All in SOQL
In Sharing Detail, Read/Write = Edit in SOQL
In Sharing Detail, Read Only = Read in SOQL

Copy from Sharing Detail

  • Full Access - User can view, edit, delete, and transfer the record. User can also extend sharing access to other users.
  • Read/Write - User can view and edit the record, and add associated records, notes, and attachments to it.
  • Read Only - User can view the record, and add associated records to it. They cannot edit the record or add notes or attachments.
  • Private - User cannot access the record in any way.

  • Sharing Detail page will only show User, Public Group, Role, Role and Internal Subordinates, but does not show: users in the higher role hierarchy of another user, users in the higher role hierarchy of a role hierarchy, users in the higher role hierarchy of user in Public Group. Click the Expand List button to see each user, access level, and the why.
  • Query to OpportunityShare will show the same thing as on Sharing Detail page, so you can't just depend on OpportunityShare to determine all users that have access to a record.


Thursday, September 16, 2021

Tableau CRM : using SourceField in computeRelative node

The computeRelative node is useful to traverse across rows to identify something or calculate something.

Three important items in a computeRelative node:
  1. Partition By
  2. Order By
  3. Sort Direction (for 2 - Order By)

From the above screenshot:
- It is Opportunity 
- Partition By Account Id
- Order by CreatedDate and sort by Descending

The result in the calculated fields: 
- the first record in the partition would be the Last Opportunity created for an Account
- the last record in the partition would be the First Opportunity created for an Account
This is because we order by Descending.

There is two Expression Type in the calculated field for computeRelative: 
2. SourceField

Use Case: to identify the First or Last or Previous record

using SAQL
SAQL Expression = case when previous(Id) is null then "Yes" else "No" end
* Id in above SAQL can be any fields as it just use to check if any previous record exists.

using SourceField
In this method, the new calculated field will contain "Yes" if that's the first row, the rest will be the Id from the previous record, which is not important.

Based on my testing, using SourceField is faster than SAQL in the dataflow. In the below example, the dataflow processing close to 1.6 million rows

Monday, September 13, 2021

Happy Sweet 17teen

Oh, this is not a blog about teen turn adult, but a celebration of the 17th anniversary of my 1st Salesforce org., which is still active till now.

If this org is a human, today it is officially an adult (in many parts of countries). While the org has been evolved a lot, at least from the shape which is the user interface. I did not screenshot every year, but here are a few milestones:

super old user interface  image credit to

Theme 2 — The Salesforce Classic 2005 user interface — Its Summer 21

Theme 3 — The current Salesforce Classic user interface (a.k.a. Aloha)

The current Salesforce Lightning user interface 


Friday, September 10, 2021

Tableau CRM: Nodes with no data in Dataflow


First, it sounds weird when you said that your dataflow processed no data, but that could happen when you put a filter that clears off all data. What happened with the further nodes that create fields in the dataflow:

Augment with Right node has no data
  • Warning: The dataflow was completed, but the NodeName node didn't augment any columns. Either the node didn't find any matches, or a join key contains only null values.
  • Augment fields are created

Augment with Left node has no data
  • Warning: The dataflow was completed, but the NodeName node didn't augment any columns. Either the node didn't find any matches, or a join key contains only null values.
  • Augment fields are created

  • No error and no warning
  • ComputeExpression field is created

ComputeRelative with Expression Type = Source Field
  • No error and no warning
  • ComputeRelative field is created

ComputeRelative with Expression Type = SAQL
  • No error and no warning
  • ComputeRelative field is NOT created, because the field is not created, if you have any further transformation node that includes the new field, such as in slice, augment, etc., the dataflow will error. As per Salesforce, this is a defect, but no KI is created yet, the KI has been created, so feel free to "subscribe".

Monday, September 6, 2021

Salesforce Dashboard: Dynamic Gauge Charts

In Winter 22 release, Salesforce releases Dynamic Gauge Charts in Dashboard, even still beta. Before this, all segments (low, medium, high) values must be entered manually by editing the component. With Dynamic Gauge Charts, you just need to update a field and the segment target changed.

To use Dynamic Gauge Chart, you need to enable the feature from Reports and Dashboards Settings in the setup menu. 

The concept

You need to have a target value, this is the max value of the gauge chart. The value should be defined in an object, within a record, and in a field. Let say you use the Opportunity report for the component, it does not make sense to put the target in the Opportunity object. 

So, here is my idea:
- Create a custom object
- Create a number field: this to store the value or target
- Create a text field: this field use to store description

The component

Once "Enable Dashboard Dynamic Gauge Charts (Lightning Experience only)" is enabled, you will see Mode: Standard and Dynamic when editing a Gauge component. Standard = existing mode where you need to enter values for each segment, while Dynamic is the new one, where the segment is divided by % of target.

Object = which object to refer to chart target
Record = which record that holds the value for this component
Field = which field from the above record that hold the value

Instead of value as per normal, we need to enter % for each segment to divide segments of the low, medium, high.

Footer: once you select "dynamic" mode, you cannot make use of the footer by entering any text, it will be locked and will show the description, for example, Sum of Amount (USD) compared to Target (Dynamic: Rec-1)

  • Sum of Amount (USD) = the needle of gauge chart, which is the current value in the database
  • Target = field name
  • Dynamic = object name
  • Rec-1 = record name


Sunday, August 29, 2021

Salesforce Joined Report: Add Cross-Block Summary Formula

We shared how to add the same report type in Joined Report (in Classic) back in 2013. Despite the hate and love of joined reports, there is a feature that is not utilized by many users in the joined report, called Cross-Block Summary Formula.

Cross-Block Summary Formula is pretty similar to Summary Formula, with only different fields to be calculated from across blacks.

Not going to talk much about what is a summary formula is, but here is the sample on using Cross-Block Summary Formula. In this blog, I am going to calculate the percentage of Closed opportunities compare to All opportunities by the Opportunity Owner.

Add from Cross-Block Summary Formula, the field will be added to the selected block

Percentage number of closed opportunities compare to all.
formula B0#RowCount/B1#RowCount

My first block is filtered by closed opportunity, and the second block is all opportunity.

Percentage Amount of closed opportunities compare to all.


To move the fields to a new block on their own, add a new block and add a name field. To avoid duplication, you can remove the fields from created earlier.

Here is the complete report

Here is the result

Here are a few notes related to cross-block custom summary formulas:
  • You can add a cross-block formula to any block in the report.
  • In Lightning Experience, cross-block formulas are automatically added to the report when you create them. In Salesforce Classic, cross-block formulas aren't automatically added to the report when you create them. To add a cross-block formula in Salesforce Classic, drag it to a block.
  • When you add cross-block formulas to a block, they appear to the right of standard ones in the order in which you add them to the block.
  • The results of cross-block formulas are affected by the filter options applied to the blocks in the report. As a result, a cross-block formula can yield different results when you change filter options.
  • Each joined report can have up to 10 cross-block custom summary formulas.
  • Deleting a block that’s used in a cross-block formula also deletes the formula from both the Fields pane and any remaining blocks containing it.
  • Each cross block formula must have a unique name. However, standard and cross-block custom summary formulas can have the same name.
  • Cross-block formula names can't include brackets (“[“ or “]”).


Thursday, August 19, 2021

Salesforce: Personalize Order of items in Related List

Salesforce admin can set the order of the related list in page layout. The screenshot below is an example from the Account page.

How can I change Opportunities as the 1st tab instead of Contacts? You can personalize this for yourself.

1. Click your avatar, select Settings

2. Click Display & Layout, then Customize My Pages

3. Select Accounts from dropdown, then click Customize Page button

4. Move Opportunities above Contacts

5. Click the Save button

Now you have personalized your Opportunities in the related list above Contacts, and this does not impact anyone else.

Friday, August 13, 2021

Salesforce: Extract Fields using Report

As a Salesforce admin, sometimes we need to extract the list of all fields from an or multiple objects, for audit, clean-up, documentation, checking, and so on. 

In Classic, we can simply copy and paste the fields list to an Excel file, but in Lightning, it is not so easy when you have lots of fields in an object. What is the alternative?

This blog will discuss how to create Salesforce reports to get the list of objects and fields.

1. Create a Report type
Select the Primary Object = Entity Definitions, then relate to Field Definitions. Deploy it if you want to share the report with your users.

2. Create a Report
Create a new report as normal, use the report type created in (1), and of course as per normal Salesforce report, you can export the report to an Excel file.

In the screenshot below, I want to get all fields from the Opportunity object, add a filter to the report Master Label = Opportunity. make sure the Master Label in the filter is from Entity Definitions, not from Field Definitions.

Few interesting information of field can be gathered here:
- Relationship Name: what object does the field refer to
- Calculated Formula Field References: list of all fields used in the formula 

You also can add Description, Last Modified Date, if the field is sortable, filterable, calculated,  indexed, etc.

One of the drawbacks of using a report to get fields in the object, if your Profile does not have access to the field (Field-Level Security), you will not see those fields in the report.

Wednesday, August 11, 2021

Tableau CRM: Adding Row Number

Adding a row number would be something simple in Tableau CRM, but sometimes we just forget how to add it.

In a Compare Table

Here is the step:

1. Open the dataset in a lens or dashboard widget

2. Add "Group By" a field

3. Select the value for "Column", by default is "Count of Rows", you can change to Sum or Average or others

4. Sort the "Count of Rows" descending or ascending

5. Click the "+" button under "Count of Rows" and click Add Formula link at the top right corner of the window

6. In the new window, enter "Row Number" at the Column Header, you need to click Apply button to see the changes

7. Under "Calculation", select f(x) then "Rank Within Group"

8. Under "Function", select "Row Number"

9. Now we have Row Number in the compare table 😊

In a Values Table

This is much simpler, we just need to enable the Show Row Index Column under the "Other" parameter.

Saturday, July 17, 2021

Tableau CRM: Using Multi Value (2)

In the previous blog, the solution using dataflow (by adding isMultiValue and multiValueSeparator) only works if the multivalue field is originated from Salesforce as picklist (multi-select), but not for a field created by augment from parent to child object.

If you need to do the same in the scenario of augmenting from parent to child object, you need to use the mv_to_string() function, and please note this feature is still in beta (as of Summer 21 release), and there is a statement "Indexing multivalue fields can slow down dataflows", make sure you test your dataflow properly in a sandbox before turning on this feature.

ComputeExpression node does not support multi-value field, so this also means that we cannot use this feature in dataflow, but only in the dashboard.

Opportunity Text is the result of the mv_to_string('Opportunity.Name') added in the Table widget, while we maintain to use 'Opportunity.Name' field in the list or filter widget.

"query": "q = load \"Acct_Opty\";\nq = foreach q generate
mv_to_string('Opportunity.Name') as 'Opportunity Text',
'Opportunity.Name' as 'Opportunity.Name','Branch_Multi' as 'Branch_Multi',
'Branch__c' as 'Branch__c';\n\nq = order q by 'Name' asc;\nq = limit q 1000;",


Friday, July 16, 2021

Tableau CRM: Using Multi Value (1)

We discussed Multi-Select Value to Text two years ago. To get a field with multi-value:

  • source field in SFDC is picklist (multi select)
  • use augment node in dataflow, where child object as the right source

In this blog, we have another use scenario of using multi-select values related to the Filter/List and Table widget. This blog is only applicable for multivalue fields originated from Salesforce as picklist (multi-select).

Using multi-value field in a Filter or List widget
It is great to use a multi-value field as a filter or list widget, it will show you unique values of each, instead of combined the values with a delimiter.

multivalue in Filter

multivalue in List

Using multi-value field in Table widget
However, it does not looks good when you add that field in a table, it will only show you the 1st values, instead of all values for that field.

select Singapore, but show Indonesia, because Indonesia is the 1st value

The need, to show all values in the table, however, the filter only shows one unique value. 

Multi-value field limitation
We cannot use the multivalue field in the computeExpression node, so we cannot "clone" the field.

Solution use Dataflow
1. In sfdcDigest node, convert the field not as multivalue by adding "isMultiValue": false

2. In the computeExpression node, clone the text field (as per 1), so now we have a cloned multivalue field in the non-multivalue format.

3. Convert the clone field back as a multivalue field. Download the JSON file, edit the file and add with below parameters in the computeExpression node
"isMultiValue": true,
"multiValueSeparator": ";"

4. Upload back the JSON file updated

  • Branch_Multi is the new field created from computeExpression which now is a multivalue field.
  • Branch__c is the original field from sfdcDigest, where we "force" this as a non-multivalue field in sfdcDigest node.

Here is the result

select Singapore from List/Filter, but the table shows all values


Thursday, July 15, 2021

Tableau CRM: Broadcast and Facet across dataset

Tableau CRM offers dashboard creators to use more than 1 dataset in a dashboard, such as the Parent and Child dataset. Then we can link the dataset with a key using Connect Data Source. Selecting a chart or list view value will filter the other dataset too.

Let's see how this works. For this blog, I will just simply use the Account and Opportunity objects.

Account dataset
- Id
- Name

Opportunity dataset
- Id
- Account Id
- Name

Then, we connect Account.Id with Opportunity.AccountId in the dashboard using Connected Data Source.

1. Simply add Account Name and Opportunity Name only in the chart and list view

Clicking Account value does not filter Opportunity - FAIL

Clicking Opportunity value does not filter Account  - FAIL

2. Add (Account Name) and (Opportunity Name, Account ID) in the chart

Clicking Account value does not filter Opportunity - FAIL

Clicking Opportunity value filter Account - SUCCESS

3. Add (Account Name, Id) and (Opportunity Name, Account ID) in the chart

Clicking Account value filter Opportunity - SUCCESS

Clicking Opportunity value filter Account - SUCCESS

Lesson learned
We need the key use to link added to the chart of broadcast, otherwise, it will not facet the selected value as a filter to the other connected dataset.

Using the setup from scenario (3), let us test further by selecting Account Name and Opportunity Name from the List widget.

Selecting Account list does not filter Opportunity - FAIL

Selecting Opportunity list does not filter Account - FAIL

4. Keep all set up as (3), then add Account ID to List widget for Opportunity Name

Selecting Account list does not filter Opportunity - FAIL

Selecting Opportunity list does not filter Account - SUCCESS

Lesson learned
Same as the previous lesson learned, we need to have the key in the widget that broadcast value for filtering. 

5. Adding Id in Account list view 

This will succeed as we have the key in the broadcaster widget.

For aesthetic reasons, we do not want to show ID in the list widget, we can remove it from the display, but not remove it from the widget, with this way, selecting a Name will still broadcast the key (in this case ID), select the widget, select "Customize display and format" then remove the[ ID].

Now, we don't have the "ugly" ID anymore appear as the list of values 😊

Page-level ad