Salesforce-related

  1. For Excel
  2. SOQL
  3. REST / Tooling API
    1. …but, but why?
    2. Page Layout Assignments
    3. Validation Rule Errors
    4. Email Alerts
    5. Where Is This Used?

For Excel

  • Custom Column Format For Insert, Upload, Upserts (Date, Date/Time)
    • yyyy-mm-ddThh:mm:ssZ
  • Convert ID 15 to ID 18
    • =CONCATENATE(A2,
      MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ012345”,
      IFERROR(IF(FIND(MID(A2,1,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,1,0),0)
      +IFERROR(IF(FIND(MID(A2,2,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,2,0),0)
      +IFERROR(IF(FIND(MID(A2,3,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,4,0),0)
      +IFERROR(IF(FIND(MID(A2,4,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,8,0),0)
      +IFERROR(IF(FIND(MID(A2,5,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,16,0),0)
      +1),1),
      MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ012345″,(IFERROR(IF(FIND(MID(A2,6,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,1,0),0)
      +IFERROR(IF(FIND(MID(A2,7,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,2,0),0)
      +IFERROR(IF(FIND(MID(A2,8,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,4,0),0)
      +IFERROR(IF(FIND(MID(A2,9,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,8,0),0)
      +IFERROR(IF(FIND(MID(A2,10,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,16,0),0)
      +1),1),
      MID(“ABCDEFGHIJKLMNOPQRSTUVWXYZ012345″,(IFERROR(IF(FIND(MID(A2,11,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,1,0),0)
      +IFERROR(IF(FIND(MID(A2,12,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,2,0),0)
      +IFERROR(IF(FIND(MID(A2,13,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,4,0),0)
      +IFERROR(IF(FIND(MID(A2,14,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,8,0),0)
      +IFERROR(IF(FIND(MID(A2,15,1),”ABCDEFGHIJKLMNOPQRSTUVWXYZ”)>0,16,0),0)
      +1),1))

SOQL

  • Salesforce Licenses (Count)
    • SELECT Id, Name, Status, TotalLicenses, UsedLicenses 
      FROM UserLicense 
      WHERE Name = ‘Salesforce’
  • PermSet Licenses (Count)
    • SELECT Id, MasterLabel, Status, ExpirationDate, TotalLicenses, UsedLicenses 
      FROM PermissionSetLicense 
      WHERE Status = ‘Active’ 
      ORDER BY MasterLabel
  • CPQ Assigned (Count)
    • SELECT Id, Name, Status, TotalLicenses, UsedLicenses 
      WHERE Name LIKE ‘Salesforce CPQ%’
      FROM UserLicense 
  • Find Users by…
    • Username
      • SELECT Id, Name, Username, Email, UserRole.Name, Profile.Name
        FROM User
        WHERE username = ‘name@domain.tld’
        ORDER BY Name
    • Email Address
      • SELECT Id, Name, Username, Email, UserRole.Name, Profile.Name
        FROM User
        WHERE email = ‘name@domain.tld’
        ORDER BY Name
    • Federation ID
      • SELECT Id, Name, Username, Email, FederationIdentifier, UserRole.Name, Profile.Name
        FROM User
        WHERE FederationIdentifier = ‘could be email address or ask IT’
        ORDER BY Name
  • User counts of Types & Profiles
    • SELECT count(id) Users, UserType, ProfileId, Profile.Name
      FROM User
      WHERE IsActive = TRUE
      GROUP BY UserType, ProfileId, Profile.Name
      ORDER BY Profile.Name
  • Users with…
    • Roles & Profiles
      • SELECT Id,Name, UserRole.Name, Profile.Name
        FROM User
        WHERE IsActive = TRUE
        ORDER BY Name
    • Permission Sets
      • SELECT Id,PermissionSetID, PermissionSet.Label, Assignee.Id, Assignee.Name
        FROM PermissionSetAssignment
        WHERE Assignee.UserType = ‘Standard’
        AND Assignee.IsActive = TRUE
        AND (NOT PermissionSet.Name LIKE ‘X00%’)
        ORDER BY PermissionSet.Label, Assignee.Name
    • Permission Set Licenses
      • SELECT Id,PermissionSetLicenseID, PermissionSetLicense.MasterLabel, Assignee.Id, Assignee.Name
        FROM PermissionSetLicenseAssign
        WHERE Assignee.UserType = ‘Standard’
        AND Assignee.IsActive = TRUE
        ORDER BY PermissionSetLicense.MasterLabel, Assignee.Name
    • Specific System Permissions
      • SELECT Id, PermissionSetID, PermissionSet.Label, PermissionSet.Profile. Name, Assignee.Id, Assignee.Name
        FROM PermissionSetAssignment
        WHERE Assignee.UserType = ‘Standard’
        AND Assignee.IsActive = TRUE
        AND PermissionSet.PermissionsAuthorApex = TRUE
        ORDER BY PermissionSet.Profile.Name, Assignee.Name
  • Specific Record Access for Specific User
    • SELECT RecordId, MaxAccessLevel, HasAllAccess, HasDeleteAccess, HasEditAccess, HasReadAccess, HasTransferAccess
      FROM UserRecordAccess
      WHERE UserId = ‘005…’
      AND RecordId = ‘001…’
  • Object Permissions based on Profile
    • SELECT Id, SobjectType, PermissionsCreate, PermissionsRead, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords, ParentId, Parent.Profile.Name
      FROM ObjectPermissions
      WHERE (NOT Parent.Profile.Name = ”)
      ORDER BY SobjectType
  • Object Permissions based on Permission Set
    • SELECT Id, SobjectType, PermissionsCreate, PermissionsRead, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords, Parent.Name
      FROM ObjectPermissions
      WHERE (NOT Parent.Name LIKE ‘X00%’)
      ORDER BY SobjectType
  • Field Permissions based on Profile
    • SELECT Id, Field, PermissionsEdit, PermissionsRead, ParentId, Parent.Profile.Name
      FROM FieldPermissions
      WHERE (NOT Parent.Profile.Name = ”)
      ORDER BY Field
  • Field Permissions based on Permission Set
    • SELECT Id, Field, PermissionsEdit, PermissionsRead, ParentId, Parent.Name
      FROM FieldPermissions
      WHERE (NOT Parent.Name LIKE ‘X00%’)
      ORDER BY Field
  • Field Permissions by Specific Object
    • SELECT Id, Field, PermissionsEdit, PermissionsRead, ParentId, Parent.Profile.Name
      FROM FieldPermissions
      WHERE SobjectType = ‘Opportunity’
      ORDER BY Field
  • All Record Types
    • NOTE: This will list Custom Objects first – not sure why
    • SELECT Id, IsActive, SobjectType, Name, DeveloperName, Description
      FROM RecordType
      ORDER BY SobjectType, Name
  • Field Information by Specific Object
    • SELECT PublisherId, EntityDefinition.QualifiedApiName, MasterLabel, QualifiedApiName, DataType, Length, Description
      FROM FieldDefinition
      WHERE EntityDefinition.QualifiedApiName = ‘Account’
      ORDER BY MasterLabel
  • Text-Related Field Lengths
    • SELECT PublisherId, MasterLabel, QualifiedApiName, DataType, Length, Description
      FROM FieldDefinition
      WHERE EntityDefinition.QualifiedApiName = ‘Task’
      AND DataType LIKE ‘%TEXT%’
      ORDER BY MasterLabel
  • Fields with History Tracking by Specific Object
    • SELECT QualifiedApiName, LastModifiedDate
      FROM FieldDefinition
      WHERE IsFieldHistoryTracked = true AND EntityDefinition.QualifiedApiName = ‘Opportunity’
  • Field History Tracking by Specific Object
    • NOTE: Examples :: Account = AccountHistory, Contact = ContactHistory, Opportunity = OpportunityHistory
    • SELECT AccountId, Field, OldValue, NewValue, CreatedBy.Name
      FROM AccountHistory
      WHERE Field <> ‘created’
      ORDER BY AccountId, Field
  • Field History by Specific Custom Object
    • SELECT Name, CreatedBy.Name, (SELECT OldValue, NewValue FROM Histories)
      FROM Project__c
  • Dynamic Dashboards
    • SELECT Id,FolderName,Title,Type
      FROM Dashboard
      WHERE Type = ‘LoggedInUser’
      OR Type = ‘MyTeamUser’
      ORDER BY FolderName,Title
  • Scheduled Jobs – Reports/Apex
    • SELECT CreatedDate, TimesTriggered, CronExpression, CreatedBy.Name, CronJobDetail.Name, CronJobDetail.JobType
      FROM CronTrigger
      ORDER BY CronJobDetail.Name
    • NOTE: How to interpret in Excel (possibly outdated)
      • =SWITCH(CELL,1,”Data Export”,3,”Dashboard Refresh”,4,”Reporting Snapshot”,6,”Scheduled Flow”,7,”Scheduled Apex”,8,”Report Run”,9,”Batch Job”,”A”,”Reporting Notification”,”E”,”Search Engine Incremental Optimization”,”G”,”Search Engine Optimization Generation”)
  • Financial Services Cloud
    • Account-Contact Relations
      • SELECT AccountId,Account.Name,ContactId,Contact.Name,Id,Roles,IsActive,IsDirect,FinServ__Primary__c,FinServ__PrimaryGroup__c,FinServ__IncludeInGroup__c,FinServ__Rollups__c
        FROM AccountContactRelation
        WHERE accountid = ‘<ID>’
    • Roles & Reciprocal Roles
      • SELECT FinServ__CreateInverseRole__c,Name,FinServ__InverseRole__c
        FROM FinServ__ReciprocalRole__c

REST / Tooling API

…but, but why?

Sometimes I like to make things more difficult 😁

I find it easier to look things up this way than page by page…

Page Layout Assignments

  • …via Developer Console using Tooling API
    • SELECT RefMetadataComponentName, MetadataComponentId, MetadataComponentType, MetadataComponentName
      FROM MetadataComponentDependency
      WHERE RefMetadataComponentType = ‘CustomField’ AND RefMetadataComponentId = ”
      ORDER By RefMetadataComponentName
  • …via REST Explorer Utility in Workbench
    • <I don’t want to type this up yet>

Validation Rule Errors

  • …via Developer Console using Tooling API
    • SELECT Id, Active, Description, EntityDefinition.DeveloperName, ErrorDisplayField, ErrorMessage
      FROM ValidationRule
  • …via REST Explorer Utility in Workbench
    • /services/data/v50.0/tooling/query?q=Select+Id,Active,Description,EntityDefinition.DeveloperName,ErrorDisplayField,+ErrorMessage+From+ValidationRule

Email Alerts

  • …via Developer Console using Tooling API
    • SELECT Id, DeveloperName, SenderType 
      FROM WorkflowAlert
  • …via REST Explorer Utility in Workbench
    • /services/data/v50.0/tooling/query?q=Select+Id,DeveloperName,SenderType+From+WorkflowAlert 

Where Is This Used?

NOT overly useful but there are suggestions cooler functionality may be coming

  • …via Developer Console using Tooling API
    • SELECT RefMetadataComponentName, MetadataComponentId, MetadataComponentType, MetadataComponentName
      FROM MetadataComponentDependency
      WHERE RefMetadataComponentType = ‘CustomField’ AND RefMetadataComponentId = ‘<FIELDID>’
      ORDER By RefMetadataComponentName
  • …via REST Explorer Utility in Workbench
    • <I don’t want to type this up yet>