Salesforce: Files sharing Query

More than 3 years ago, I wrote a blog Content Architecture sharing how to query. This blog would be the continuation on how to query the File is sharing to who or what, and the level of visibility.

There is an object called ContentDocumentLink (with prefix 06A), this object is a child of ContentDocument (prefix 069). This object represents the link between a Salesforce CRM Content document or Salesforce file and where it's shared. A file can be shared with other users, groups, records, and Salesforce CRM Content libraries. This object is available in version 21.0 and later for Salesforce CRM Content documents and Salesforce Files.

To query this object, it must be filtered on one of Id or ContentDocumentId or LinkedEntityId. Let's see some samples:

SELECT Id, ContentDocumentId, LinkedEntityId FROM ContentDocumentLink 
This query will throw an error because of not filtering on anything.
MALFORMED_QUERY: Implementation restriction: ContentDocumentLink requires a filter by a single Id on ContentDocumentId or LinkedEntityId using the equals operator or multiple Id's using the IN operator.


To query for a file is sharing to
SELECT Id, ContentDocumentId, ContentDocument.Title, LinkedEntityId, LinkedEntity.Name, LinkedEntity.Type, ShareType, Visibility FROM ContentDocumentLink WHERE ContentDocumentId = '0690k000000TzTMAA0'

To query all files shared to a record
SELECT Id, ContentDocumentId, ContentDocument.Title, LinkedEntityId, LinkedEntity.Name, LinkedEntity.Type, ShareType, Visibility FROM ContentDocumentLink WHERE LinkedEntityId = '0010k00000IKS3YAAX'

To query all files shared to an object
SELECT Id, ContentDocumentId, ContentDocument.Title, LinkedEntityId FROM ContentDocumentLink WHERE LinkedEntityId IN (SELECT Id FROM Account)

To query all files shared

SELECT Id, ContentDocumentId, ContentDocument.Title, LinkedEntityId, LinkedEntity.Name, LinkedEntity.Type, ShareType, Visibility FROM ContentDocumentLink WHERE ContentDocumentId in (SELECT ID from ContentDocument)

Reference: 

• ContentDocumentLink
• File and Content Report Type / Query All Files

Einstein Analytics: Setting Initial Selection

Adding List Selector or Date Selector in Einstein Analytics is pretty simple, just a few clicks, and you will get the result. However, if you notice at the bottom of Step Properties, there is a textbox for Initial Selection, this is to set the initial value for the List when running the dashboard, but we cannot set it from the UI.

When you add a widget, by default, it will be "No Selections".

To set value for Initial Selections, edit JSON dashboard and looks for steps, add "start": with the value. Make sure the value is available in the list.

Now, back to the UI, and check the Initial Selections

Preview the dashboard and check the initial value will be the same as the value we put in JSON.

We can do the same for Date selector. Edit JSON and looks for the related step. Below sample, if you would like to set the date range with current quarter.

This is the result when you run the dashboard, as we set in JSON, initial selection will be from current quarter to current quarter, you can change it to year, month, week, or day, or also using the absolute date.

However, if you hate to edit JSON code for this you can set the initial value from UI too, click ... button next to save icon and select Pick Initial Selections, then select date selector or range selector or list selector to set the initial value, this activity will edit the same JSON at the back end.

Reference: 

• Work with Logged in User Detail

Einstein Analytics: The quest for Binding in Dashboard

This blog is written in Summer '18 release, so it may become invalid when Salesforce makes "binding" become more user-friendly in the future.

Until Summer '18 release, if you would like to implement binding in the dashboard, you need to manually edit the dashboard JSON.

Use case: you would like to give the user flexibility to change chart grouping, for example group by Region, or by Country, or by Status. Another use case, the user would like to have the flexibility to change chart type without the need to edit the dashboard or would like to implement both in the same dashboard, this makes sense when you change grouping, the chart type probably needs to change for better visualization.

This blog will not share on how to create binding or static with toggle, you can watch the awesome Peter Lyon's videos Binding Basic, and Rikke Hovgaard's blog the power of static steps.

A. Binding for Chart
In this sample, I have one chart and 3 toggles:
- Chart
   Step Id: step_1
- Field Names for grouping
   Step Id: static_1
- Chart Type to change the visualization
   Step Id: static_2
- Order by ascending or descending
   Step Id: sort_1

This is how the dashboard looks

To make the binding work, I am going to edit the JSON with Ctrl-E.

Binding to change Grouping

1. Change "groups" query under "step_1"

change from

"groups": ["field_name"]

change to

"groups": ["{{cell(static_1.selection, 0, \"value\").asString()}}"]

When previewing the dashboard, if the selected field in toggle different from the field use in the initial value for grouping, the chart will error:

2. Change "columnMap" in "widgets"

"columnMap": null OR delete the whole columnMap

Binding to change Chart type

1. Change "columnMap" in "widgets" (if you have not done it)

"columnMap": null OR delete the whole columnMap

2. Change "visualizationType" under "parameters" in "widgets" 

change from

"visualizationType": "hbar"

change to

"visualizationType": "{{cell(static_2.selection, 0, \"value\").asString()}}"

Binding for Date

In this scenario, we would like to give users the flexibility to view a timeline chart by week and by day. Sure we need binding for this.

Here is the static step:

                    {

                        "display": "Day",

                        "value": [

                            "TIMESTAMP_Year",

                            "TIMESTAMP_Month",

                            "TIMESTAMP_Day"

                        ]

                    },

                    {

                        "display": "Week",

                        "value": [

                            "TIMESTAMP_Year",

                            "TIMESTAMP_Week"

                        ]

                    }

and change step query for "groups" 

from: 

"groups": [

                        [

                            "TIMESTAMP_Year",

                            "TIMESTAMP_Month",

                            "TIMESTAMP_Day"

                        ]

                    ]

to:

"groups": [

   "{{cell(staticTime_1.selection, 0, \"value\").asObject()}}"

          ]

* staticTime_1 is the step name

Binding for Order

1ST OPTION  - put "ascending" in static

1. Edit the static step under "sort_1"

change from 

"values": [

           {"display": "Asc","value": "true"},

           {"display": "Desc","value": "false"}

          ]

change to 

"values": [
           {
             "display": "Asc",
             "value": 
               [
                 -1,
                 {"ascending": true}
               ]
           },
           {
             "display": "Desc",
             "value": 
               [
                 -1,
                 {"ascending": false}
               ]
           }
          ]

2. Add "order" query under "step_1"
"query": 
{ 
  "measures": [["count","*"]],
  "groups":[
             "{{cell(static_1.selection, 0, \"value\").asString()}}"
           ],
  "order": [
             "{{column(sort_1.selection, [\"value\"]).asObject()}}"
           ]
}

2ND OPTION - put "ascending" in query order

1. Edit the static step under "sort_1"

change from 

"values": [

           {"display": "Asc","value": "true"},

           {"display": "Desc","value": "false"}

          ]

change to 

"values": [

           {"display": "Asc","value": true},

           {"display": "Desc","value": false}

          ]

2. Add "order" query under "step_1"
"query": 
{ 
  "measures": [["count","*"]],
  "groups": [
             "{{cell(static_1.selection, 0, \"value\").asString()}}"
            ],
  "order": [
            [
              "count",
              {
                "ascending": "{{cell(sort_1.selection, 0, \"value\").asObject()}}"
              }
            ]
           ]
}



B. Binding for table
We also can use a toggle to sort a table widget.

Here is a sample with JSON snippet from steps:

  "query": {  
           "values": [  
             "Region__c",
             "Skill__c",
             "Name",
             "Id",
             "LastModifiedDate"  
           ],  
           "order": [  
             [  
               "{{ cell(static_2.selection, 0, \"value\").asString() }}"               
               {  
                  "ascending": "{{ cell(sort_2.selection, 0, \"value\").asObject }}"
               }  
             ]  
           ]  
         }  

This is the JSON step for the fields selection:

      "static_2": {  
         "broadcastFacet": true,  
         "label": "static 2",  
         "selectMode": "singlerequired",  
         "type": "staticflex",  
         "values": [             {  
             "display": "Region",  
             "value": "Region__c"  
           },  
           {  
             "display": "Status",  
             "value": "Status__c"  
           }  
         ]  
       }  

This is the JSON step for the order of the field:

     "sort_2": {  
         "type": "staticflex",  
         "broadcastFacet": true,  
         "selectMode": "singlerequired",  
         "label": "sort 2",  
         "values": [  
           {  
             "display": "Ascending",  
             "value": true  
           },  
           {  
             "display": "Descending",  
             "value": false  
           }  
         ]  
       }  

notice that true and false are not in the double quote ""

Table result:

Here is the full JSON for the dashboard.

Side note: old-style binding
"{{ value(selection(static_2)) }}" 
this is equal to
"{{ cell(static_2.selection, 0, \"value\").asObject() }}"



Reference:

• Bind Parts of a Step Query
• Selection Binding in a Static Step
• Binding Functions
• Bindings in Analytics Dashboards

Einstein Analytics: License Assignment

So your company purchase X licenses of Einstein Analytics, perhaps with Event Monitoring too. To check the licenses you have acquired, go to Company Information in setup menu.

In Company Information page, scroll down to Permission Set Licenses section.

From above screenshot:

• Analytics Platform (yellow highlight): this is the license for Einstein Analytic, I have a total of 2 licenses, but 1 used, so remain 1 license.
• Event Monitoring Analytics Apps (green highlight): this is the license for Event Monitoring, I have a total of 2 licenses, but 1 used, so remain 1 license.

To assign licenses to the user, go to the user detail, you will notice 

1. Permission Set Assignments (PS)
2. Permission Set Assignments: Activation Required
3. Permission Set License Assignments (PSL)

When assigning licenses, you just need to pay attention to PS (1) and PSL (3) only. I borrow a good sample from Trailhead Assign Permissions:

•  A PSL is like a passport. It grants you the right to travel, but you can’t visit the great land of Analytics without the right visa. 
• A PS is like a visa. You can get a 3-day tourist visa, a work visa, or a student visa. Each visa type lets you do certain things.
• Just like a traveler needs both a passport and a visa, your Analytics users need at least one PSL and a PS.

Back to license count, once you assign a user with a PSL, it counts as a license is used. Imagine that your country has a right to issue 100 passport, once a user Mr. X get a passport, as a country, you only can issue another 99 passports for your citizen, no matter if Mr. X apply any visa to USA, UK, or etc. But, for Mr. X to travel to the USA, he needs to obtain a visa, which is Permission Set.

In the real world, user ideally needs to get a passport first (PSL), before applying for a visa (PS). But, Salesforce makes our life as an admin easier, we can grant PS (visa) Einstein Analytics Platform User or Einstein Analytics Platform Admin directly to the user, at the same time, a passport will be issued too (PSL) Analytics Platform.

Now if you check back your license usage, it will mention 2 Analytics Platform has been used. 

Because PSL is required for PS Einstein Analytics Platform User or Einstein Analytics Platform Admin, you cannot delete the license PSL Analytics Platform (passport) before deleting the PS Einstein Analytics Platform User or Einstein Analytics Platform Admin (visa). 

But, when you delete PS from the user detail, the PSL will stay, and will still count to your license usage.

Query License Usage

To understand who is assigned with the PSL, you can do a simple query:

SELECT Id, PermissionSetLicense.MasterLabel, PermissionSetLicense.TotalLicenses, PermissionSetLicense.UsedLicenses, Assignee.Name FROM PermissionSetLicenseAssign

PS: Einstein Analytics Platform User or Einstein Analytics Platform Admin
Einstein Analytics Platform User is designed to be assigned to users need to explore dataset with lenses and build dashboards.
Einstein Analytics Platform Admin is designed to be assigned to admin, they will be able to create and customize Apps, Dashboards, Datasets, Dataflows, and Recipes, including Monitor from Data Manager. These users will be able to view all apps in Einstein Analytics, except items stored in My Private App.

	Einstein Analytics Platform User	Einstein Analytics Platform Admin
Access EA	Analytics tab Analytics Studio app	Analytics tab Analytics Studio app
Allow creating	Dashboard	App Dashboard Dataset
View all Apps/data	No	Yes
Explore Lens	Yes	Yes
Access Data Manager	No	Yes (incl. Dataflow & Recipe)

Reference:

• Assign Permissions
• Assign Permission Set Licenses for Einstein Analytics

Salesforce: Picklist Default Value

Since Summer '17 release, Salesforce supports Default Value at the field level, this means we can define different default value based on the user, example: when front-end support creates a new case, Priority default value "high", while all other users will have the Priority default value to "low", although they can change it manually.

But, the field with picklist type will have its own default value, how this works with the default value introduced in Summer '17 release? In summary, it can be up to 3 places for a picklist field to have a default value defined.

Below is a simple logic on how this works, I would agree it would be easier to read this in a flowchart.

If Default Value in General Options (field level) is defined
    if Record Type* for the object is defined
        if Formula General Option resolve** to an active item & available in Record Type
            --> use resolve Value from General Options
        else if there is Default Value selected in Record Type
            --> use selected Default Value in Record Type
        else
            --> no default value
        endif
    else
        if Formula General Option resolve** to an active item
            --> use resolve Value from General Options
        else if there is Default Value selected in Field Picklist
            --> use selected Default Value in Field Picklist
        else
            --> no default value
        endif
    endif
else
    if Record Type for the object is defined
        if there is Default Value selected in Record Type
            --> use selected Default Value in Record Type
        else if there is Default Value selected in Field Picklist
            --> use selected Default Value in Field Picklist
        else
            --> no default value
        endif
    else
        if there is Default Value selected in Field Picklist
            --> use selected Default Value in Field Picklist
        else
            --> no default value
        endif
endif

* even there is only 1 record type defined and active
** the formula resolved is case-sensitive with the item API name

Note: from the above logic, the default value from the field level is processed first, if no or fail fit to API name, check if any record type and the default value, and the last would be the default value in the picklist. 

The default value in record type is treated at the same level as the default value from the picklist, therefore, for an object with record type, if there is no default value defined, the system will not check further if any default value selected in the picklist field.


Here a few samples:

there is no default value defined at the field level, all user will get the default value from the selected item

The default value in the field resolved as High1, while the picklist API name is High, the default value from the field level will not apply - it is case-sensitive too.

Reference:

• Default Picklist Value
• Define Default Values at the Field Level
• Define Default Field Values

Einstein Analytics: Convert DateTime field to Date field or Text field

This would be a simple tip for you that start using Einstein Analytics.

Use case: to convert DateTime field (copy from Salesforce) to Date field, or Text field in Einstein Analytics.

In this blog, I'll add new fields in Dataflow using computeExpression. We will use a DateTime field with API name: Submitted_Date_Time__c

Text
formula: substr(Submitted_Date_Time__c,1,10)
This formula will take the first 10 characters.
Original: 2017-05-03T09:43:28.000Z --> 2017-05-03

Date
formula: toDate(Submitted_Date_Time__c_sec_epoch)
Remember to enter "Date Format" (you need to scroll down to find it), otherwise, you can't upload the Dataflow.

Side note: toDate() is case-sensitive.

Reference:

• Date Functions
• String Functions