Salesforce: File and Content report type

Run File and Content Report report type to analyze how users are engaging with files and Salesforce CRM Content, however, this report type will not show custom fields created in the Content. You can use Content Report report type to report all content, including the Library Name and Content Custom fields, however, Content Report report type will not tell you the content and file download history and versioning.

Let me take a screenshot of fields available from File and Content Report

If your report contains fields only in the red box, the report result will not tell you the download history and version for each file. So, if a file has multiple version, it will show only the latest version as # Versions and # Size (MB).

Now, let us add "File Version" from green box.

See that the 1st file have 4 version and show as different lines, but note that Size (MB) for each line is the file size of the latest version, not file size of each file.


Next, let us remove "File Version" and add "Download Date" from the blue box.

Each download will appear as a line with the download date time, downloaded by, and user type.


Last, let us add back "File Version" to the last report.

The primary lines here is still "download", file version added will tell you the version of the file downloaded by someone and when. This report will not show all file version.


Reference: File and Content Reports

Einstein Analytics: Add and Append in Recipe

When you create a new recipe in Einstein Analytics, there is 2 main data manipulation: Add Data and Append Data, some of us may confuse and mix up between them, including me.

Recipe in general
- Produce a new dataset (re-run the recipe with overwrite dataset created)
- Cannot overwrite source dataset

Add Data
Imagine that Add data is similar to VLOOKUP() in Microsoft Excel:
- Additional columns
- No change in the number of rows

you need matching key in each dataset, and fields to add

this recipe will produce a new dataset with additional columns from lookup result 

Append Data
This will add more rows in the new dataset, example: dataset A has 10 rows and dataset B has 8 rows, the result would be 18 rows in total.
If dataset A and B have different fields, the missing fields will be null in result dataset.

mapping fields between source dataset

the recipe will say how many rows to be added

the result after recipe run, appended dataset do not have "Kota"

Noted:

- Green icon means the recipe has been Pushed

- Yellow icon means the recipe is Saved, but has not been pushed

 Reference: Einstein Analytics: Add Data Recipe

Einstein Analytics: Using Append Transformation

Use Case: Every quarter, order data produce by an external system and need to be imported into Einstein Analytics with CSV file.

Until Winter 19, we cannot add data to the existing dataset, so the option:

1. Manually merge in Excel file
Download existing Einstein Analytics Order dataset into CSV file, manually combine with the latest quarter CSV file and existing Einstein Analytics. Then edit the dataset to Replace the data using combined CSV.


2. Using Append Transformation
Load the latest quarter CSV as a new dataset.
Create a dataflow to read existing dataset and append with the latest quarter CSV uploaded.
Register the combined data to the existing dataset.

notes:
append_Master node: "Source" is both dataset.
reg_Master node: set "Alias" with dataset API name for the master data, and "Name" with Name for master dataset.

You can check the dataset API name by edit the dataset.

yellow highlight is the dataset Name, which can be different with API name

green highlight is API name.

Make sure you just run the dataflow once, otherwise you will create duplication from Quarter data.

Einstein Analytics: Filter Dataset across Object

Use Case:
To produce a clean dataset in dataflow based on the following scenario:
There are 2 objects in SFDC which is not directly related, but both have Opportunity Id.
- Result OS = digest object OS with some filters, contain Opportunity Id.
- Result A = digest object A with some filters, also contain Opportunity Id.
- Result OS should only contain records where Opportunity Id available in Result A.

Solution:

• Start from Result OS
• Do augment with the Result A
• Create compute expression node -- case when ('OppId' is not null && 'OppId' == 'resultA.OppId' then "Yes" else "No" end 
• Finally, create filter node to filter "Yes"

Dataflow:

JSON (simplified)

 {  
  "getOS": {  
   "action": "edgemart",  
   "parameters": {  
    "alias": "dataOS"  
   }  
  },  
  "getA": {  
   "action": "edgemart",  
   "parameters": {  
    "alias": "dataA"  
   }  
  },  
  "augOS_A": {  
   "action": "augment",  
   "parameters": {  
    "operation": "LookupSingleValue",  
    "relationship": "A",  
    "left": "getOS",  
    "left_key": [  
     "OptyId"  
    ],  
    "right": "getA",  
    "right_key": [  
     "OptyId"  
    ],  
    "right_select": [  
     "AccId",  
     "Name"  
    ]  
   }  
  },  
  "Check_in_A": {  
   "action": "computeExpression",  
   "parameters": {  
    "mergeWithSource": true,  
    "source": "augOS_A",  
    "computedFields": [  
     {  
      "type": "Text",  
      "name": "Found",  
      "label": "Found",  
      "saqlExpression": "case when 'A.Name' is not null then \"Yes\" else \"No\" end"  
     }  
    ]  
   }  
  },  
  "regFilter": {  
   "action": "sfdcRegister",  
   "parameters": {  
    "alias": "Filter1",  
    "source": "Filter_Only_Yes",  
    "name": "Filter1"  
   }  
  },  
  "Filter_Only_Yes": {  
   "action": "filter",  
   "parameters": {  
    "filter": "Found:EQ:Yes",  
    "source": "Check_in_A"  
   }  
  }  
 }  

Salesforce: Opportunity Trends Report Type

We discussed a few objects related to Opportunity in detail in the previous blog, from:
- OpportunityFeed
- OpportunityFieldHistory
- OpportunityHistory
- Opportunity__hd

Also, a few report types related to Opportunity:
- Opportunity Field History
- Opportunity History
- Opportunities with Historical Trending

There is one more report type we have not discussed so far Opportunity Trends report type. The good thing about this report type is, it is enabled by default, however, you can't really customize a lot.

Opportunity Trends report takes a Snapshot "As of" the 1st of the month each passing month. These snapshots cannot look forward and the snapshot date and frequency can't be changed. If an opportunity created on 20 Nov 2018, the first snapshot for would be on 1 Dec 2018.

This report type will have information on:
- Historical Amount
- Historical Stage
- Historical Close Date
- Historical Probability
- As of Date

sample report

I do not see any object that stored this data, but let me know if you found it.


Reference:

• Opportunity Trend report differs from the output of Opportunity History report
• Opportunity Trends Report
• As of date on Opportunity Trend Report

Salesforce: Opportunity Tracking, Historical and Trending

Over the last few years, I shared in this blog about multiple objects and method to track and trend Opportunity data in Salesforce:

Trending with Reporting Snapshot
This is a method to capture the trend of Opportunity data by creating a snapshot report, and daily push the result into a custom object. After a while, we will have data to analyze the Opportunity trend based on data stored in the custom object.

Enable Feed Tracking versus Set History Tracking
The idea in this blog is to introduce OpportunityFeed (prefix 0D5 - FeedItem) and OpportunityFieldHistory (prefix 017 - Entity History) object.
Once Opportunity is enabled for Feed Tracking, when users make changes on a few fields in one transaction, it will always create only "one" record in OpportunityFeed. For a standard object, Salesforce will create a new object end with "Feed", and for a custom object, the object API name will end with "__Feed".
While using Set History Tracking, Saleforce will create a number of records in the OpportunityFieldHistory object, based on the number of fields changed, even it happened in one transaction.

Salesforce Opportunity History (Stage History)
This blog is explaining the difference between OpportunityFieldHistory (prefix 017 - Entity History) and OpportunityHistory (prefix 008 - OpportunityHistory) object.
Normally, when Set History Tracking is enabled to an object, Salesforce will create a new object with the suffix "History" for a standard object or "__History" for a custom object.
However, for Opportunity, instead of called as "OpportunityHistory" object, field tracking for Opportunity is called as OpportunityFieldHistory. The reason, simply because the API name of the OpportunityHistory object has been used for Stage History.

Opportunity Stage Aging
This blog is sharing on using "Opportunity History" report type to get the detail of the changes:
- Stage Name
- Amount
- Probability
- Close Date.

Using "Opportunity History" report type, we can get information:
- From Stage
- To Stage
- Stage Change (Y/N)
- Stage Duration
- Last Stage Change Date

Stage Duration in Opportunity History report type will count the number of days an opportunity is in a particular sales stage. While in Opportunity report type, Stage Duration is the number of days from the date when opportunity change to current Stage until today and the number of days keep counting even for the closed opportunity.


Summary:
So far we have discussed a few objects related to Opportunity tracking:

OpportunityHistory
Prefix: 008
Label: Opportunity History
Description: this object store Opportunity Stage detail and used by Opportunity History report type

OpportunityFieldHistory 
Prefix: 017
Label: Opportunity Field History
Description: this object store field value changes when History Tracking is enabled for Opportunity

OpportunityFeed
Prefix: 0D5
Label: Opportunity Feed
Description: similar to OpportunityFieldHistory, this object store feed tracking for Opportunity when Feed Tracking is enabled for Opportunity


Next, we are going to introduce another feature called Historical Trending
API Name: Opportunity__hd
Prefix: hxx
Label: Opportunities (Historical)
Description: this object store value of Opportunity fields enabled for historical trending, including the before and after values.

Once Historical Trending is enabled, following actions will be triggered:

Email
You will get an email from noreply@salesforce.com, this email will tell you that your historical trending data is ready.

New Object
Salesforce will create a new object, for a standard object, it will end with "__hd" e.g. Opportunity__hd, and for a custom object, it will end with "_hd" e.g. Object1__c_hd

Since this is considered custom objects, there will be no default prefix, but it will start with hxx, example h00 for the 1st object enabled for Historical Trending, the 2nd object would be h01, and etc.

Report Type

Salesforce will create a custom report type end with "with Historical Trending", e.g.:

- for Opportunity, it will be called "Opportunities with Historical Trending"

- for other objects, it will be called e.g. "Object1 with Historical Trending"

With this report type, you can compare data for up to five points in time (snapshots) within the previous three full months and all days so far in the current month.


Let us compare between the report and object query. I'll use Opportunity for Historical Trending. Special case for Opportunity: the Amount, Close Date, Forecast Category, Probability and Stage fields are always trended, but you can add more fields as required for trending.

The following report is created with Opportunities with Historical Trending report type and filter with Close Date within 2018.

* today date = 3 Dec 2018

For this sample, I will query with filter on 2 opportunities shown as above report:
SELECT ParentId, CreatedDate, ValidFromDate, ValidToDate, Amount__hpr, Amount__hst, CloseDate__hpr, CloseDate__hst, StageName__hpr, StageName__hst FROM Opportunity__hd WHERE ParentId IN ('0065000000Tmjwh','0065000000PWibH') ORDER BY ParentId, CreatedDate DESC

Note:

• For each field enable for trending, you will found _hpr and _hst fields
• _hpr = value at the start of the Date time range
• _hst = value at the end of the Date time range

There are a few things we can learn by query Opportunity__hd and the relation with the above report:

• ValidFromDate will determine the values in the trending slot
• If the value changed more than once in a day, only the last value of the day will be shown in the report, in this example: line 3 - $15,000.00 is never shown in the report because it is overwritten to $22,000.00 within the same day.
• Report filter is based on the current data
• The date return in the query is GMT, while the report will be based on the user time zone

Reference:

• Trending with Reporting Snapshot
• Enable Feed Tracking versus Set History Tracking
• Salesforce Opportunity History (Stage History)
• Opportunity Stage Aging
• Set Up Historical Trend Reporting
• Limitations on Historical Trend Reporting

Einstein Analytics: One-to-Many Relationship with Power of 1

A few months back, we write a blog on handling multi-select picklist value from Salesforce in Einstein Analytics, multi-select picklist value will flow to Einstein Analytics, but you cannot see the value in Table, but you still can filter it normally.

First use case:
Count the number of children record from a parent, example: number of won opportunities of an Account. I know we can easily use the roll-up summary field in Salesforce, but in case you do not have the power to add a new field, or it is a lookup relationship, where you cannot create a roll-up summary field.

For this use case, I am using Power of 1 in Einstein Analytics as the solution.

1. computeExpression
In the child object before augment with the parent object, add a computeExpression node and a compute field called "PowerOf1"

This will simply return 1 for each child record.


2. augment node
The left source would be the parent object, and the right source is the child object. This is similar normal many-to-one augment, but for Operation, select Look Up Multiple Values.

3. The Moment of Truth

Using Lookup Multiple Value operation, PowerOf1 field (numeric) will be added, in this case, it would be the numbers of opportunities record (as each record is 1). While Opportunity Name will show only 1 name selected randomly, this is similar to how multi-select picklist values works.  Let us see how it is actually stored at the backend:

Second use case
Show the number of children from the same parent in the child object.

This is the same as the first use case, but you need to add another augment, the left source would be the child object, while the right source would be the augment of parent-child contain the power of 1 value.

And this is the result:

Reference: Special Cases for Matching Records with the augment Transformation

Salesforce: Enhanced Email & Email Tracking

With Enhanced Email, emails sent from Salesforce are logged as Email Messages and Tasks, read this as: Salesforce create 2 records: 1 record in EmailMessage (02s) and 1 record in Task (00T). There is a field in EmailMessage point to Task called ActivityId. EmailMessage is a standard Salesforce object, and that’s how you get all of these great new email features.

Check this SOQL for the object structure:
SELECT Id, ActivityId, CreatedDate, FromName, Subject FROM EmailMessage ORDER BY CreatedDate DESC

To enable Enhanced Email, go to setup menu, search for Enhanced Email, and click Enable button. All existing email sent prior enable enhanced email will NOT change.

Let's see the different of email sent before and after enable Enhanced Email in the Past Activities panel.

yellow = before; green = after

Now let's click the email.
Before:
URL: https://ap1.lightning.force.com/lightning/r/Task/00T9000004pFPcXEAW/view

After: 

URL: https://ap1.lightning.force.com/lightning/r/EmailMessage/02s90000019V78ZAAS/view

Unlike the Task object which is editable, Email Messages cannot be edited through the User Interface or the API.


One more thing to notice, if you have enabled Enable Email Tracking, after enabling Enhanced Email, you can track if the recipient has open the email.

 

You also can check when the email first and last opened.

Reference:

• Enhanced email and the Email Message object
• Salesforce HTML email tracking capabilities
• Considerations for Using Enhanced Email

Salesforce: Case and Lead Team Report

We discussed about Account Team Report and Opportunity Team Report more than two years ago. In this blog, we would share on Case and Lead team report.

Let us start with Case, when you check options under "Show Me" filter, here are the options:

In the samples below, we'll login as Maria Ann:

My cases: ONLY cases that you OWNED.

User owned cases: all cases owned by a user (not a queue)

Queue owned cases: all cases owned by a queue (not a user)

My case team's cases: ONLY cases where you are on the Case Team, this NOT includes where users under your role hierarchy added as Case team.

My role-based team's cases: Cases you OWNED & cases owned by all of your subordinates in the role hierarchy.

** this is all cases owned by Maria Ann and her subordinates.


Now, let us look at "Show Me" filter available in Lead, here are the options:

We'll not discuss again for: My leads, User owned leads, Queue own leads, and All leads, but let us see what is the result for My team's lead.

** this is all leads owned by Maria Ann and her subordinates.

Salesforce: easier way to see what is inside a Permission Set

When you enable a permission using permission set or profile, Salesforce will automatically enable other permissions needed to enable that permission. Salesforce will tell you what permissions will be enabled when you enable a permission, however, it will not tell you other permissions will be enabled by enabling the required permissions. Imagine this, to enable permission A, the system will tell you that it will enable permission B as well, however, to enable permission B, it needs to enable permission C and D, so you are not aware that C and D actually enabled by a Permission Set.

For this example, I create a permission set to enable a permission called "Activate Contracts".

From the above screenshot, you aware that, by enabling Activate Contracts, it will enable Edit Order and Edit Contract, and not more than that. But, let's check with Setup Audit Trail.

Let us see each line from the above audit trail screenshot:
5th - create the permission set
4th - enable read and edit for Contract object, this makes sense that to edit, the user needs to read
3rd - enable read for Account object, this is because Contract record accessibility depends on Account.
2nd - enable read and edit for Order object, this makes sense that to edit, the user needs to read
1st - enable the permission set

Because the permission set is just created, we can monitor it from the audit trail, but for the existing permission sets, you can't find the history after 6 months. So, you need to open each tab in the permission set - that's not ideal :(

Also for the scenario where you have many admins and the documentation practice is not strong, some admin adding permissions without other admin awareness, so you have no visibility of what is inside the permission set.

Is there an easy way to tell what is "inside" a permission set?
Yes, as Salesforce awesome admin, you are supposed to fill in the "Description", this will help yourself in the future, or your successor. However, Description only can have maximum 255 characters.


Check with Metadata
Because of all extra permissions in the permission set are written as metadata, you can download the permission set metadata and "see" all permissions enabled for the permission set with a simple text editor.

Salesforce inspector comes to help, it is pretty handy and nothing needs to be installed to your Salesforce org., just follow the step to download permission set metadata into a zip file.

Salesforce inspector panel

select permissionsets and click Download metadata

wait for a few seconds to download the metadata

Extract the zip file, each permission set will be stored as a file with the extension ".permissionset". Open the file with a text editor, such as Notepad++, now you have full visibility the content of that permission set, it will tell field permission, object permission, custom permission, apex class, tab setting, apex page, user permission. etc.

<?xml version="1.0" encoding="UTF-8"?>
<PermissionSet xmlns="http://soap.sforce.com/2006/04/metadata">
    <fieldPermissions>
        <editable>true</editable>
        <field>Account.Acc_Percent__c</field>
        <readable>true</readable>
    </fieldPermissions>
    <fieldPermissions>
        <editable>false</editable>
        <field>Account.Active__c</field>
        <readable>true</readable>
    </fieldPermissions>
    <hasActivationRequired>false</hasActivationRequired>
    <label>Activate Contract 2</label>
    <objectPermissions>
        <allowCreate>false</allowCreate>
        <allowDelete>false</allowDelete>
        <allowEdit>false</allowEdit>
        <allowRead>true</allowRead>
        <modifyAllRecords>false</modifyAllRecords>
        <object>Account</object>
        <viewAllRecords>false</viewAllRecords>
    </objectPermissions>
    <objectPermissions>
        <allowCreate>false</allowCreate>
        <allowDelete>false</allowDelete>
        <allowEdit>true</allowEdit>
        <allowRead>true</allowRead>
        <modifyAllRecords>false</modifyAllRecords>
        <object>Contract</object>
        <viewAllRecords>false</viewAllRecords>
    </objectPermissions>
    <objectPermissions>
        <allowCreate>false</allowCreate>
        <allowDelete>false</allowDelete>
        <allowEdit>true</allowEdit>
        <allowRead>true</allowRead>
        <modifyAllRecords>false</modifyAllRecords>
        <object>Order</object>
        <viewAllRecords>false</viewAllRecords>
    </objectPermissions>
    <userPermissions>
        <enabled>true</enabled>
        <name>ActivateContract</name>
    </userPermissions>
</PermissionSet>


PermComparator
Another option is to use PermComparator, however you need to switch between User Permissions, Object Permissions, and Setup Entity Permissions to see all extra permissions added in the permission set.

However, from my experience, PermComparator does not list all permissions, probably it has not been updated for a while, example: Allow View Knowledge, Subscribe to Reports.

Salesforce Inbox License

We did write how a blog on how to check Einstein Analytics License Assignment, this blog will share about checking Inbox license in Salesforce Classic and Lightning.

Inbox License Assignment is pretty similar to Einstein Analytics License Assignment, you can see how many Inbox total licenses you have, how many licenses used and the remaining licenses in Company Information under Permission Sets Licenses in the setup menu.

Next question, I would like to see who has this license assigned? You can run SOQL to get the list of users assigned with Inbox license, here you go

SELECT Name, Id FROM User WHERE Id IN
(SELECT AssigneeId FROM PermissionSetLicenseAssign WHERE PermissionSetLicense.MasterLabel ='Inbox') AND IsActive=true
ORDER BY Name


Next, if I manually open the user detail, how I can see if the license has been assigned? Is it under Permission Set Assignments or Permission Set License Assignments related list? This is exactly the same with the Einstein Analytics licenses assignment, once you add the Permission Set, the Permission Set License will be auto-added, but not the other way round.

From the above screenshot, once you add the "Inbox With Einstein Activity Capture" permission set, the Inbox license will be auto-granted.


Reference:

• How to view Users assigned Salesforce Inbox licenses

Einstein Activity Capture

Einstein Activity Capture is introduced on Summer '18 release, it is free up to 100 licenses for org with Sales Cloud license. When sales rep connects their Google™ or Microsoft® account to Salesforce, activities from those accounts show up on business and person accounts, contacts, leads, opportunities, quotes, and contracts in Salesforce. Plus, reps get read-only access to the Activities dashboard, which lets them view a summary of sales activities.

Step to enable Einstein Activity Capture:

1. Activate Einstein Activity Capture
Reach out to Salesforce Support to enable Einstein Activity Capture. Once enabled, you should see Einstein Activity Capture under the setup menu.

Then, enable Einstein Activity Capture from Setting menu.

2. Assign Permission Set to user
You need one of the following Permission Set depends on your license:
License = Sales Cloud Einstein --> Permission Set = Sales Cloud Einstein  
License = Inbox --> Permission Set = Inbox with Einstein Activity Capture
License = Sales Cloud --> Permission Set = Standard Einstein Activity Capture

3. User Setup
Once the permission set given to the user, the user needs to configure it from their setup menu. Click username from the top right corner, click Settings,  looks for Einstein Activity Capture, then click Settings. 

It will popup Meet Inbox window, then click Next and follow the wizard.

Then, select a Google or Microsoft account to connect.

Once account added, it will be shown in Connected Account under user Einstein Activity Capture setup menu.

Here you have the option if you want to share the emails and events to Everyone, My Groups, and Only Me.


Activity Sharing

Only Me: 
Events - Others see only the event's date, time, and attendees.
Emails - Others see only an email's sender, recipients, and date.

Everyone:
Events - Others see all details for the event.
Emails - Others see the full contents of the email.

My Groups:
Events - Group members see all details for the event. Others see only the event's date, time, and attendees.
Emails - Group members see the full contents of the email. Others see only the email's sender, recipients, and date.
The group here refers to Private or Unlisted Chatter Group, if you are a member of the group, but it shows You have no groups to share with. To share with a group, join or create a private or unlisted group that allows sharing, this means the group has not been enabled for sharing.

add Enable Email and Event Sharing checkbox

Follow this step to enable Chatter Group sharing:

• Go to the Chatter Group 
• Click on Edit Object from the upper right corner;
• Select Page Layout;
• Add the Enable Email and Event Sharing field checkbox;
• Click Save.
• Then Go back to the Group
• Edit the Group and check the field Enable Email and Event Sharing field checkbox;
• Click Save.

4. The Moment of Truth

Sending an email out using your email client to Contact or Lead
Based on the email sent out, Salesforce will automatically tag the email to the Contact or Lead as Past Activities. You need to make sure the email address in Salesforce is correct.

Contact reply your email
When your contact email you and his/her email address is in Salesforce, the email will auto added as Past Activities too.

What happened if more than one Lead or Contact with the same email address? 
The email correspondence will be linked to all Contact and Lead.


Reference:

• How Admins Set Up Einstein Activity Capture
• How Users Set Up Einstein Activity Capture