Salesforce: Query on Group and GroupMember

Group object is quite unique as it stores multiple types of data, from Public Group, Queue, Role, etc., you can see all the Type values in this article Group.

Note: The Type for Public Group is Regular

Sample query: SELECT Type, COUNT(Id) FROM Group GROUP BY Type

For user members in Group and Queue can be a query to the GroupMember object. Sample query:

All queue with queue members
SELECT Id, GroupId, Group.Name, UserOrGroupId FROM GroupMember WHERE Group.Type = 'Queue'

All public group with public group members
SELECT Id, GroupId, Group.Name, UserOrGroupId FROM GroupMember WHERE Group.Type = 'Regular'

All inactive users in a queue or public group
SELECT Id, GroupId, Group.Name, Group.Type, UserOrGroupId FROM GroupMember WHERE UserOrGroupId IN (SELECT Id FROM User WHERE IsActive = false) ORDER BY Group.Name

Reference:

• GroupMember
• Group

Salesforce: Query Org ID and Sandbox

You can get your salesforce Org ID and other info from Company Information in the Setup menu. But, if you need to get the data from SOQL, here is the sample query

SELECT Id, Name, InstanceName, DefaultLocaleSidKey, TimeZoneSidKey FROM Organization

While for Sandbox info, you can go to the Sandboxes in the setup menu in Prod. The same, you can use SOQL to get the information, but you need to use Tooling API. 

SELECT Id, SandboxName, Description, LicenseType, HistoryDays FROM SandboxInfo

The Id returned here is SandboxInfo ID, not the Org ID and unfortunately, we can't get the Org ID.

You will get the same result if perform query with Rest Explorer from Workbench

GET /services/data/v53.0/tooling/query/?q=+Select+Id,SandboxName,LicenseType,Description+from+SandboxInfo

Reference:

• SandboxInfo

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: 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.

https://johantest.my.salesforce.com/p/share/OppSharingDetail?parentId=0062H0000123456QAN

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.

Summary:

• 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.

Reference:

• OpportunityShare

Salesforce: Query User Assigned with Package License

Scenario: your company purchased X licenses for a package software installed in Salesforce, what is the easy way to extract users has been granted with that package software?

Solution: open the Package Details then click the "Manage Licenses" button. However, this is not easy when you have thousands of users.

Alternative solution: use SOQL, so we can extract the data easily.

1. Query the Package Id

You need to know the Namespace Prefix of the package to get the Package Id. You can get the Namespace Prefix from Package Details, including allowed licenses and used licenses.

SELECT Id, NamespacePrefix, AllowedLicenses, UsedLicenses, CreatedDate, ExpirationDate, Status FROM PackageLicense ORDER BY NamespacePrefix

2. Query User Package License object

SELECT Id, PackageLicenseId, CreatedById, CreatedDate, UserId FROM UserPackageLicense

By query UserPackageLicense object and filter with PackageLicenseId, you can get :

- UserId: user assigned with the license

- CreatedById: who grants the license

- CreatedByDate: when is the license granted

But unfortunately, you can't use relationship query for UserId to get the name and profile and so on, so you need to query to User object again, subscribe for this KI.

Salesforce: Export Query Result from Developer Console

In case you have no access to the workbench, can we download the query result from the Developer Console?

Build a Query in Developer Console

Open Developer Console, File > Open > Objects > select an object > click Open (or double click the object name). 

Select the fields by hold the Ctrl key (and Shift key) for Windows users, then hit the Query button, Id field will be auto-added.

Click the Execute button, developer console will open a tab with the query result.

Export query result to Excel file

This is tested in Chrome and chromium-based web engines (such as Edge and Opera), so not sure for Firefox and Safari.

Right-click on the query result and select Inspect.

Look for the table tag, then do "Copy element".

Open Excel and Paste to get the result.

 

Salesforce: SOQL Picklist Values & API Name

As you are aware that we can have different names between Picklist Values & API Name in Salesforce, see this screenshot:

When users enter the data or run a report, they will only see Values and not API Name.

When admin or developer do a query with SOQL, the result is API Name
SELECT Id, Name, AccountSource FROM Account WHERE AccountSource <> ''

If you need to get the values from SOQL, use tolabel() function. Here is the updated query
SELECT Id, Name, toLabel(AccountSource) FROM Account WHERE AccountSource <> ''

Reference: Translating Results

Salesforce: ForecastingQuota and ForecastingItem object

ForecastingQuota, this object stores an individual user’s or territory’s quota for a specified time period. The “Manage Quotas” user permission is required for creating, updating, or deleting quotas.  The “View All Forecasts” permission is required to view any user's forecast, regardless of the forecast hierarchy. Available in API versions 25 and greater. Object Id prefix is 0J9.

Once the quota added from the setup menu or Data Loader, it will be stored in this object and you can query it.

Query ForecastingQuota
SELECT Id, ForecastingTypeId, QuotaAmount, QuotaOwnerId, QuotaOwner.Name, StartDate FROM ForecastingQuota ORDER BY StartDate DESC

ForecastingItem, this is a read-only object used for individual forecast amounts. Users see amounts based on their perspectives and forecast roles. Available in API versions 26 and greater. Object Id prefix is 0G3.

The amounts users see include one of the following when forecasting in revenue: AmountWithoutAdjustments, AmountWithoutManagerAdjustment, ForecastAmount, OwnerOnlyAmount.

The amounts users see include one of the following when forecasting in quantity: QuantityWithoutAdjustments, QuantityWithoutManagerAdjustment, ForecastQuantity, OwnerOnlyQuantity.

Additionally, note that users:
- with the “View All Forecasts” permission have access to all ForecastingItem fields.
- without the “View All Forecasts” permission have access to all fields for their own subordinates and child territories.
Other users can see the ForecastingItem object, but not its records.

Let us see a sample of how the data is stored
SELECT Id, OwnerId, Owner.Name, ForecastAmount, ForecastCategoryName, ForecastingTypeId, PeriodId FROM ForecastingItem WHERE Period.StartDate = 2019-07-01 ORDER BY ForecastingTypeId, Owner.Name, ForecastCategoryName

** unfortunately we cannot do relationship query for ForecastingType from this object

figure 1

Notes from the above query and the result:
- ForecastCategoryName is Forecast Category which is mapped to Stage
- There is no forecast period in this object, but PeriodId, so we can use it to determine Forecast period
- ForecastingTypeId is referred to Forecast Type configured

Query ForecastingType
SELECT Id, DateType, DeveloperName, MasterLabel, RoleType FROM ForecastingType WHERE IsActive = true

soql result from ForecastingType

Now let us see the data by running an Opportunity report:

figure 2

Then let us also see the Forecast result:

figure 3

Now, let us compare how is the data stored in ForecastingItem object, remember this object is read-only, so we can't update it manually.

• Figure 1; row 1; Charlie User C in Jul 2019; for Forecast Category = Pipeline with Forecast Amount = $55,000 -- if you see figure 3, it is $0 for Charlie User C, but since Dave User D is reported to Charlie in the role hierarchy, so amount from Dave roll-up to Charlie. 
• Figure 1; row 2; Charlie User C in Jul 2019; for Forecast Category = Closed with Forecast Amount = $295,000 -- look at figure 3, this amount is sum of $120,000 (Dave's amount) + $139,500 (Charlie's amount). If we further drill Charlie's amount of $139,500 -- look at figure 2, it is the sum of row 1 ($50,000) and row 2 ($89,500)
• Figure 1; row 3; Dave User D in Jul 2019; for Forecast Category = Pipeline with Forecast Amount =$55,000 -- as no one report to Dave in the role hierarchy, this number is only his opportunity, drill down to figure 2, it is row 3 ($15,000) + row 4 ($40,000) 
• Figure 1; row 3; this is simply Dave User D amount for Forecast Category = Closed
• Figure 2; row 6; this is not calculated into ForecastingItem, because forecasting setting in this example in monthly, while row 6 closed date is Aug 2019.

Make sure Charlie User C is enabled as Forecast Manager in Forecast Hierarchy, otherwise Dave number (and all other users under Charlie) will not roll-up to Charlie.


In this sample, we are using monthly forecast setting, Salesforce also supports option to use Quarter forecast period too.



Reference:

• ForecastingQuota
• ForecastingItem

Einstein Analytics: Using SOQL

So far, we all know, in building dashboards in Einstein Analytics, we need to bring the data into Einstein Analytics and stored it as Dataset. In this blog, I will share how to directly get data from Salesforce using SOQL, this means we can create a chart wizard or table in  Einstein Analytics with Salesforce live data.

You need to know the basic JSON dashboard in Einstein Analytics. After you create the dashboard, create a step with the type = soql, e.g.

 "soql_step_name": {  
  "type": "soql",  
  "query": "SELECT Name from ACCOUNT",  
  "strings": ["Name"],  
  "numbers": [],  
  "groups": [],  
  "selectMode": "single"  
 }  

Once the step added, you can use it in any wizard. The isFacet and useGlobal properties don't apply to this step type. You can use a binding to filter other steps based on a selection in a soql step.

Let's see more samples:

 "soql1": {  
         "type": "soql",  
         "query": "SELECT Id,Name,NumberOfEmployees,Type from ACCOUNT",  
         "strings": [  
           "Type",  
           "Id",  
           "Name"  
         ],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "groups": [],  
         "selectMode": "single"  
       }  

 "soql2": {  
         "groups": [],  
         "numbers": [  
           "foo"  
         ],  
         "query": "SELECT count(id) foo from ACCOUNT",  
         "selectMode": "single",  
         "strings": [],  
         "type": "soql"  
       }  

 "soql3": {  
         "groups": [],  
         "numbers": [],  
         "query": "SELECT Id,Name from USER where Id = '{!User.Id}'" ,  
         "selectMode": "single",  
         "strings": ["Id","Name"],  
         "type": "soql"  
       }  

  "soql4": {  
         "type": "soql",  
         "query": "SELECT NumberOfEmployees,Name,Type from ACCOUNT",  
         "strings": [  
           "Type",  
           "Name"  
         ],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "groups": [  
           "Type"  
         ],  
         "selectMode": "single"  
       }  

Notes:
- same as the normal step in JSON, the order of parameters will be ignored
- type parameter is "soql"
- query parameter must be valid soql and contain all fields needed
- fields from query result should be put under strings or numbers parameter
- groups parameter is optional, but needed when you have grouping in the wizard


Here is the wizard result from each step above:

step soql1

step soql2

step soql4

Use SOQL result for binding

 "soql1": {  
         "groups": [],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "query": "SELECT Id,Name,NumberOfEmployees,Type from ACCOUNT order by NumberOfEmployees",  
         "selectMode": "single",  
         "strings": [  
           "Type",  
           "Id",  
           "Name"  
         ],  
         "type": "soql"  
       }  

 "all_1": {  
         "type": "saql",  
         "query": "q = load \"All_Accounts\";\nq = group q by all;\nq = foreach q generate {{cell(soql1.result,1,\"NumberOfEmployees\").asObject()}} as 'name1';\n",  
         "useGlobal": true,  
         "numbers": [],  
         "groups": [],  
         "strings": [],  
         "visualizationParameters": {...},  
         "label": "all_1",  
         "selectMode": "single",  
         "broadcastFacet": true,  
         "receiveFacet": true  
       },  

result:

* 18 is the result from the second row cell(soql1.result,1,\"NumberOfEmployees\").asObject()



Reference:

• soql Step Type Properties
• SOQL Step in Salesforce Analytics Cloud Wave

Salesforce: App Visibility and Query

This blog is written for Apps in Classic, not Lightning apps.

To check app visible and set to default for a user, go to the User Profile, and looks for Custom App Settings.

But, as you may know, in addition to Profile, admin also can assign the user with Permission Set, and additional apps can be added from Assigned Apps.

So, if you would like to trace down apps visible for a user, check user Profile + Permission Set assigned.


App Visibility 
To check an app is enabled for which profiles, open the app page, and click Edit button, scroll down to the bottom and check for Assign to Profiles section, users in the selected Profile will able to see the app.

SOQL
With SOQL, we can query for more apps and profile/permissions set.

1. AppMenuItem
This object store item in the app menu.
Sample query: SELECT Id, ApplicationId, Name, Label, NamespacePrefix, IsAccessible, IsVisible FROM AppMenuItem WHERE Type = 'TabSet' ORDER BY ApplicationId

Id - 0DS prefix
ApplicationId - this is the app Id (prefix 02u)
Label/Name - app label and API name
NamespacePrefix - usually part of a managed package
IsAccessible - if true, the current user is authorized to use the app
IsVisible - if true, the app is visible to users of the organization

2. SetupEntityAccess
When granting a user access to an entity, associate the appropriate SetupEntityAccess records added with PermissionSet that’s assigned to a user.
Sample query: SELECT Id, SetupEntityId, ParentId, Parent.Label, Parent.IsCustom, Parent.IsOwnedByProfile, Parent.ProfileId FROM SetupEntityAccess WHERE SetupEntityType = 'TabSet' ORDER BY SetupEntityId

Id - 0J0
SetupEntityId - this is the app Id (prefix 02u)
ParentId - this is Permission Set Id (prefix 0PS), it can be Profile or Permission Set, check field IsOwnedByProfile. You can use parent relationship queries with . (dot)


By combining the 1st query on AppMenuItem and 2nd query on SetupEntityAccess using Application Id (prefix 02u), you can relate app accessibility with profile and permission set.

Sample: you would like to check all profile and permission have access to Sales app.
1. Query AppMenuItem to get Application Id for Sales app
2. Query SetupEntityAccess to get the list of Profiles and Permission Set for that application id (looks for SetupEntityId)

To enhance this, you can query to object PermissionSetAssignment to get users able to access the app, check out this blog Using Permission Set to Query User Permission.


Reference:

• AppMenuItem
• SetupEntityAccess
• CustomPermission
• Using Permission Set to Query User Permission

Salesforce Files Type and Sharing

"Files" in Salesforce introduce after Attachment and Document. They are not related each other. However, Files is related to Library, they support content search, while Attachment and Document are not. When you upload a file to library, the same file will be available in "Files" tab as well.

Here are few type on how the Files loaded and the differences:

1. Upload file directly to Files tab

2. Contribute file to Private Library

3. Contribute file to Shared Library

4. Upload file from Chatter record feed, or user feed, or Chatter group

If you click "Go to Content Detail Page", here is the differences:

1. Upload file directly to Files tab

2. Contribute file to Private Library

3. Contribute file to Shared Library

4. Upload file from Chatter record feed, or user feed, or Chatter group

We discussed about Content Architecture sometimes back, Files API is called ContentDocument start with prefix 069, while Library API is called ContentWorkspace start with prefix 058.

Let's query and see what is the difference from the backend
SELECT Id,ContentSize,FileExtension,FileType,ParentId,PublishStatus,Title FROM ContentDocument WHERE Id IN ('0693B00000083pw','0693B00000083q1','0693B00000083q6','0693B00000083qG') ORDER BY Title

- ParentId is refer to Library only - private library, record feed or chatter group is Not count.
- PublishStatus: P = Public, R = Private


Reference:

• File Sharing Settings and Permissions
• Content Architecture
• ContentDocument

Salesforce: SystemModstamp

There is one standard field in Salesforce that many of us not aware of, and not really use it, it is available in standard and custom object which is called as SystemModstamp. So what is SystemModstamp field? It is a date time field. Is this similar with LastModifiedDate? Yes in common, but sometimes it may differ.

LastModifiedDate is the date and time when a record was last modified by a user.
SystemModstamp is the date and time when a record was last modified by a user or by an automated process (such as a trigger). In this context, "trigger" refers to Salesforce code that runs to implement standard functionality, rather than an "Apex trigger".

As a result, LastModifiedDate and SystemModstamp will differ when automated processes update the records, which will happen in the following scenarios (asynchronously in some cases):

a) The archive date is extended to greater than 365 days.
b) An existing picklist value is updated (not replaced with an existing picklist value).
c) A contact's e-mail address is flagged as per the Email Bounce Management configuration.
d) The LastActivityDate field is modified
e) Roll-up summary field is created, which will update all the parent records' SystemModstamp asynchronously. Recalculation will also take place if the Summary Type is updated.
f) Some Salesforce Internal backend processes also update SystemModstamp as SystemModstamp is used internally to signal that a record (or related data) may have changed and that internal processes may need to synchronize themselves to the new record data.

Sample: 

I have a custom picklist field in Account called Brand__c. I will change one of the picklist value from Toyota to Honda. This field is also set for tracking. 

Before I do that, let me query all the records where brand is Kia 

SELECT Id,Name,Brand__c,LastModifiedDate,SystemModstamp FROM Account WHERE Brand__c = 'Toyota' ORDER BY LastModifiedDate

As you see here, all records have LastModifiedDate = SystemModstamp

Now, let me update the picklist value from Toyota to Honda. Salesforce by default will auto update all records where Brand__c match Toyota to Honda. 

Then let's do another query.

SELECT Id,Name,Brand__c,LastModifiedDate,SystemModstamp FROM Account WHERE Brand__c = 'Honda'

Notice here that SystemModstamp is updated, but LastModifiedDate stay the same. As this field also enable for tracking, let's check to Account History.

The value change is not tracked and Last Modified By also stay the same.

Another sample here when user try to send email to invalid email address.

Email Bounce Management will mark the email address as invalid, this will update SystemModstamp, but the LastModifiedDate will stay the same.

Reference: When is SystemModStamp different from LastModifiedDate?

Salesforce: Multi-Person Calendar Event (User as invitee)

In previous blog Multi-Person Calendar Event (Contact / Lead as invitee), we discussed about adding contact or lead as invitee, we also discussed the structure at the backend and how we can use API to add contact or lead as invitee.

But, when the invitee is a user, the structure will be a little different, although the objects used is the same.

1. Event object
Instead of create only 1 event record as add contact/lead as invitee, invite user will create multiple event record based on number of invitee + sender. In this scenario, we are going to invite 2 users and 1 contact.

Sample query: Select Id, Subject, OwnerId, GroupEventType, IsChild, WhatId, WhoId, CreatedDate, ActivityDate, ActivityDateTime, StartDateTime, EndDateTime, EventSubtype, IsReminderSet, Type, WhatCount, WhoCount FROM Event WHERE Subject = 'Test with User'

This will create 3 events with same subject, one event for each User, but only 1 with IsChild = False, which is "parent" event. Also notice that each record have different Owner Id, and all events with GroupEventType = 1, remember that we cannot populate / update GroupEventType manually.


2. EventRelation object
Sample query: SELECT Id,EventId,RelationId,IsParent,IsWhat,IsInvitee,Status,CreatedDate FROM EventRelation WHERE EventId IN ('00U63000001T8Y9EAK','00U63000001T8YAEA0','00U63000001T8YBEA0')

Notice that records created in this object is relate to: 1 opportunity, 1 contact and 2 users, but all linked to the same event, which is parent event where IsChild = False.


3. EventWhoRelation object
Same with EventRelation object, only 1 record created as we only have 1 contact relate to the event, and it is linked to the parent Event (IsChild = False)

Sample query: SELECT Id, EventId, RelationId, Type, CreatedDate FROM EventWhoRelation WHERE EventId IN ('00U63000001T8Y9EAK','00U63000001T8YAEA0','00U63000001T8YBEA0')

How to auto add User as Invitee Event with API?

You just need to insert record(s) to EventRelation object, 1 record for 1 contact / lead. Here are fields you need to populate:
1. EventId = Event Id
2. RelationId = User Id
3. IsParent = False
4. IsWhat    = False
5. IsInvitee = True
6. Status   = New

This is similar with add Contact / Lead as invitee, the difference is: Salesforce will auto create event record for the user as child record, if the parent record initially is not for multi-person, GroupEventType will change from 0 to 1.