Salesforce Customizeable Forecast Report

In previous blog, we discussed about Forecast Category, and now we would like to continue on how to create forecast report in this blog.

You can create forecast vs quota report the same as you usually create Salesforce report, just select report type 'Customizable Forecasting: Forecast Summary' or 'Customizable Forecasting: Forecast History'.

You will find the same forecast category as in Forecast tab: Quota (Rev), Closed (Rev), Commit (Rev), Best Case (Rev), Pipeline (Rev). With additional info Closed % of Quota (Rev) and Closed + Pipeline (Rev) which is actually total Amount of all Opportunity.

Do you realize the amount is exactly the same with amount in Forecast tab?

Issue: a Sales Director come to me and asked, why I can only see forecast for managers in my direct report, not for users underneath those managers?

After do some finding, issue found in Forecast Hierarchy. Go to Setup - Customize - Forecasts (Customizable) - Forecasts Hierarchy, make sure each role in Forecast Hierarchy have user enable, so user in higher role hierarchy can see forecast of user below.

Salesforce Forecast Category

In Salesforce, each value of Opportunity Stage is mapped to Type, Probability and Forecast Category. Changing the Stage of in Opportunity page layout will automatically change the Probability value and Forecast Category, although users can manually overwrite the default value of Probability and Forecast Category, if the fields are made available in the Opportunity page layout.

We can customize Forecast Category value from Forecast Category field in Opportunity object, but not the Status Category.

From the above screenshot, Forecast Category value has been modified from Best Case to Best Case OK, but the Status Category will remain as Best Case. There are 5 Forecast Category and we cannot add/delete it, although you can skip to use in from Stage assignment:

 - Pipeline

 - Best Case
 - Commit
 - Closed
 - Omitted


Customizable Forecast
When you click the Forecasts tab, you will find the 5 Forecast Category:
 - Quota
 - Closed
 - Commit
 - Best Case
 - Pipeline

The Forecast Category on the Opportunity record does NOT map directly on a one-to-one basis to the one on the Forecast tab. The amount in Closed, Commit, Best Case, Pipeline is NOT total from the same Forecast Category in  Opportunity Forecast Category. Click the Forecasts tab and notice that your forecast amounts are listed in different categories.

Forecasting Category value calculation:
Closed includes amounts for closed/won opportunities.
  Status Category = Closed
Commit includes amounts you are confident about closing and closed/won opportunity amounts.
  Status Category = Closed + Commit
Best Case includes amounts you are likely to close, closed/won opportunity amounts, and amounts in the Commit category.
  Status Category = Closed + Commit + Best Case
Pipeline includes amounts from all open opportunities.
  Status Category = Commit + Best Case + Pipeline

Sample of source data from Opportunity and relation with Forecast Category (bottom image):

Collaborative Forecast
This is the newer version of Forecast offer by Salesforce out of the box, you can have either Customize-able forecast or Collaborative Forecast in an org, not both.

When you click the Forecasts tab, you will find the 5 Forecast Category:
 - Quota (only when "Show quotas" are enabled from Forecast setting)
 - Closed
 - Commit
 - Best Case
 - Pipeline

cumulative forecast rollups are not enabled for this screenshot.

If you see above screenshot, Best Case is shown as Upside, this is changed in Forecast Category value.

If we enable cumulative forecast rollups, we can't change the header:

cumulative forecast rollups are enabled for this screenshot.

Here is the description for each category - we can't change the rollup setup:

Last update: 10 February 2018


Reference: Working with Forecast Categories (Customize-able Forecast)

Salesforce: Workflow - Evaluation Criteria

Workflow is one of the feature used most in Salesforce, because of the simplicity and it is so powerful. With workflow, administrator can defined Immediate or Time-Dependent Workflow Actions to create new Task, send Email alert, field update and send Outbound Message without the need of a developer to write code.

You need to define Evaluation Criteria correctly to fit your business requirements:

created
This option is pretty clear, workflow will be evaluated only when user create a record.

created, and every time it’s edited
For this option, workflow will be evaluated when user create new record AND anytime user edit the record.

created, and any time it’s edited to subsequently meet criteria
For this option, workflow will be evaluated when user create new record AND anytime it hasn't trigger. Example:
- Workflow with rule criteria Execution Time equals null AND Request Time not equal to null
- User create new record with both Execution Time and Request Time is null, workflow will not be triggered as not meet rule criteria
- User update Request Time not to null --> workflow trigger
- User update other field, it still meet rule criteria, but workflow will be not evaluate again, because it has been evaluate when user update Request Time not to null.

So, how/when workflow will be evaluate again?
- User update back Request Time to null OR Execution Time not to null, here rule criteria broken.
- User update Request Time not to null OR Execution Time to null, workflow will be evaluate again.

Salesforce: Contact, Opportunity, Case access

We discussed Account sharing rule related to Contact, Opportunity, and Case in the previous blog.

Another area needs to take note related to this, is default Contact, Opportunity, Case Access setting in Role Hierarchy. Go to Setup - Manage Users - Roles and click on any role, you will see Contact, Opportunity, Case Access related to Account.

So, how this work and what is the difference with Account OWD sharing rule related to Contact, Opportunity, and Case?

In the Role Hierarchy access setup, we define user visibility and edit permission to all Contact, Opportunity, and Case access related to Account. If a user in the role owned an Account, can the user view or edit Contact, Opportunity, and Case tag to that account? So, this is not related to access permission to records under role-subordinates.

While in Account sharing rule, although it is almost similar, but sharing rule in Account is beyond default access, it is to share account from a role/group to other role/groups, including access (view or edit) Contact, Opportunity, Case related to Account.

Hope this explaining :)

Salesforce: Web-to-Lead validation

In previous blog, we discuss about adding record type as hidden field in Web-to-Lead, so when Lead created in Salesforce, it automatically assigned with a specific record type and Assignment Rules, it will be assigned to specific queue or user.

This blog will discuss about validation in Web-to-Lead. Can we have validation rule on data submit by user?
Out of the box, Salesforce do not provide any validation. For example: when user submit with invalid email address, it will be rejected by Salesforce by default. So, where the lead go? I'll be not created in Salesforce.

Salesforce will email to Default Lead Creator with subject Salesforce Could Not Create This Lead. And in the email body, it will capture all data enter by user with reason why it failed, for this case, it would be InvalidEmailValue

The same if you have validation rule in Lead, Default Lead Creator will get the same email with the reason as error message set in validation rule.

But, please note if you have required custom field, Salesforce will skip this one and create the Lead, so next time if you have Lead without required field, it may be created from Web-to-Lead.

So, back to the original question, can we add validation in Web-to-Lead form? Yes, you can, you need to learn a simple javascript. Sample:

<script type='text/javascript'>
    function ValidateForm() {
        var helperMsg = '';

        if (document.getElementById('first_name').value.length == 0) {
            helperMsg += "Please enter First Name \n\r";
        }
        if (document.getElementById('last_name').value.length == 0) {
            helperMsg += "Please enter Last Name \n\r";
        }
        if (document.getElementById('email').value.length == 0) {
            helperMsg += "Please enter valid Email Address \n\r";
        }     
        if (document.getElementById('phone').value.length == 0) {
            helperMsg += "Please enter Phone \n\r";
        }      
       
         if (helperMsg.length > 0) {
            alert(helperMsg);
            return false;
        }
        return true;
    }
</script>

Salesforce: Setting Record Type in Web-to-Lead

Web-to-Lead is a simple and good feature in Salesforce out-of-the box. It will create HTML code and you just need to put it into your website and lead will be create in your Salesforce. It is simple enough without need to hire a developer to build code, but if your business requirements are much more complex, such as: integration with other system, build a custom code using API web service is the option.

This blog is not telling about how to set up Web-to-Lead, you can find the information here.

OK, so now you have web-to-lead form and you want it be assigned to a specific lead record type based on criteria.

Use hidden fields
You can add following input into HTML script generated by Salesforce
<input type=hidden name="fieldname" id="fieldid" value="value">
example:
<input type=hidden name="lead_source" id="lead_source" value="Email">
<input type=hidden name="recordType" id="recordType" value="01250000000HkoV">

If you set record type in Lead, you need to set in record type setting to "Keep the existing record type".
In Setup - Customize - Leads - Settings change the record type settings to "Keep the Existing Record Type".  The other option is "Override the existing record type with the assignee's default record type" which as stated means it will constantly be overridden by the record type of whoever you assign the lead to.

You can get the RecordType Id by going to Setup - Customize - Lead - Record Types.

Make sure that the profile assigned to the owner of the leads has the particular record type you're trying to assign visible to that profile. You can check from: Setup - Manage Users - Profiles - Edit. Find record type settings click "Edit" next to leads and add / remove record types to that profile.

Use workflow with field update
You also can use workflow to update Field Update field based on some criteria you can set in workflow, this option will work when you want to set different record type based on some criteria.

Salesforce: Multiple ways to add People to Campaign

I think most of Salesforce admin know, Salesforce is huge, there are so many features that we are not aware and further, Salesforce add hundred of features in every release. So, it is good to keep our knowledge up to date.

In this blog, I would like to share on how to add people (Contact and Lead) to Campaign. Most of us only know to add Campaign members from Campaign page. But, there are few more ways:

1. Manually from Contact/Lead page Campaign History related list
From Contact or Lead page layout, scroll down to Campaign History related list, you can add Contact or Lead to a Campaign. This is manual effort to add 1-by-1 record as Campaign member. Make sure to have following permission:

• “Read” permission on Campaign object AND 
• “Edit” permission on Contact/Lead object

If your user do not see this button, make sure they have Edit permission for Contact or Lead object in the profile, even user not able to Edit the records, but user need Edit permission for the object.

2. Mass add with Contact/Lead Report
You can add Contacts or Leads from Contact or Lead report, for this option make sure that you or your users have following permissions:

• Marketing User is checked on the user detail AND
• “Edit” permission on Campaign AND 
• “Read” permission on Contact/Lead AND 
• Run Reports permission

Report should not return more than 50,000 records.

More information here.

3. Mass add using Contact/Lead List View

From Contact or Lead view, you can add multiple contacts or leads, from records return in View, select all or selected records to add to campaign. Make sure to have following permission:

• Marketing User is checked on the user detail AND 
• “Edit” permission on Campaign object AND 
• “Read” permission on Contact/Lead object

More information here.

4. Mass add from Manage Members in the Campaign detail
This is the most common way to add campaign members and mostly used. Make sure to have following permission:

• Marketing User is checked on the user detail AND 
• “Edit” permission on Campaign object AND 
• “Read” permission on Contact/Lead object

Note: to mass enable user with Marketing User in user detail, you can use Data Loader to update User object field UserPermissionsMarketingUser.


Reference:

• Add Contacts or Leads from a List View to a Campaign
• Add Campaign Members from Reports

Last update: 9 Aug 2016

Salesforce: Sharing of Contact, Opportunity, Case in Account

One of the powerful feature (but look simple) in Salesforce is Sharing. Yes, it just records sharing and many of us as Salesforce admin/consultant not really care about "Sharing Settings". But, you need to configure it correctly to make sure your sales reps are not fighting.

I will share a little on sharing for Contact, Opportunity, and Case, based on my experience.

If you go to Account Sharing Rules, you will find Contact, Opportunity, Case sharing.

Then if you go to Contact, you can find Contact Sharing Rules, the same applies for Opportunity and Case.

So why double? Is it the same thing? Yes and No.

For example Contact:
- Yes, when Contact owner = Account owner, so it will be same.
- No, when Contact owner <> Account owner, why?

In Account sharing rule, if the Account owned by Mike, and contact tagged to that Account is owned by Bruce, the sharing rule in Contact will not effect for Bruce.

But, if the sharing rule is in Contact, no matter who owned the Account, the sharing rule in Contact will ALWAYS effect.

So, which one is the best? No right answer, it will be based on your business requirements.

Hope this makes sense and help.

Salesforce: user able to access Private account

We set our Account and Contact OWD (Organization-Wide Defaults) sharing to Private. But somehow a user still can view Account not owned by him or sharing to him. Why?

Luckily Salesforce helps us as admin to be Sherlock Holmes to track this.
1. Go to the affected account page layout
2. Click Sharing button, make sure it is added to Account page layout and you login as a system admin
3. Click 'Expand List' button

4. Look for the user able to view the account and click Why? link in this last screen, look for 'Reason for Access'.

In my case, I found that user able to view that Account, because there is a Read/Write sharing rule in the Contact to allow the user to edit the contact. But, somehow Salesforce give Read Only permission for that user to View Account that tagged to Contact where user gets access from Contact sharing rule.

Interesting??? There is so many secrets within Salesforce.

Salesforce: How to undelete records

Salesforce keep record you delete to Recycle Bin for 15 days with maximum record of 25 times the Megabytes (MBs) in your storage. For example, if your organization has 1 GB of storage then your limit is 25 times 1000 MB or 25,000 records. If your organization reaches its Recycle Bin limit, Salesforce automatically removes the oldest records if they have been in the Recycle Bin for at least two hours.

You can manually undelete records by click Recycle Bin icon in bottom left menu. Undelete account will include contact, case, opportunity tagged to that Account. But, if you have many records or you have specific record Id or you want to undelete records with some conditions, manually delete is not an option, use Apex code for it.

You not really need to be good developer to utilize Apex code. Here we go:

1. Open Developer Console
From your name in top right, look for Developer Console

2.From Developer Console
Go to Debug menu and select Open Execute Anonymous Window

3. Enter SOQL 
Example:
Account[] a = [SELECT Id FROM Account WHERE name = 'singtel' ALL ROWS];
undelete a;
Important to add ALL ROWS, otherwise deleted records will be not return on query.

4. Monitor Apex Debug Log
Go to Monitor - Logs - Debug Logs (in Salesforce menu, not Developer console)
Add user to monitor, default it will capture 20 log request for the users

5. Execute 
Click Execute button in Enter Apex Code window

6. Back to Debug Log
Select latest debug log and click View to monitor

Salesforce field usage

This blog is not about how to identify where a field used in: reports, triggers, workflow, field update, etc. If you are reading this blog for that purpose, out-of-the-box that feature it has not available yet, so please vote this post in IdeaExchange. But, there is a workaround to find field usage using Force.com IDE, just retrieve items you want to find and find using field API name.

Ok, back to the main topic. Over time, as user request, admin create more and more fields in standard or custom objects, some of them is not so popular, may be not a mandatory in page layout. Now, we want to know, which fields are unpopular or even never used. So, it is worth to clean it. How to find it easily?

1. Export all data in CSV file
Once data exported into CSV file, use Microsoft Excel to open it, then use formula COUNTBLANK() to count how many cell with no data and COUNTA() to count how many cell with data, so the formula would be COUNTBLANK(A1:A20) / (COUNTBLANK(A1:A20)+COUNTBLANK(A1:A20)) x 100%. This will return percentage number of record with data. Then you need to copy it for all fields.

2. Field Trip 
This free application create by Qandor will help you analyze the data and you can produce nice report. But unfortunately, I found not all fields are analyze, although no filter add in the SOQL. From report below, you can easily understand field like: Deleted, NRIC never used and Brand is very seldom used.

3. Salesforce Enabler
This is free plug-in for Microsoft Excel 2007 and 2010 by Taralex. Although it is not working in my Office 2007 machine, but running well in Office 2010. If you already in Salesforce arena for quite sometimes, you may be familiar with Excel-Connector http://code.google.com/p/excel-connector/, by Ron Hess. But, Salesforce Enabler give us more functions, such as: analyze objects, unreportable objects, field utilization, diff, custom reports.

Salesforce: New Setup Menu

In Summer '13 release, Salesforce introduce new setup menu, see Salesforce.com Summer ’13 Release Notes page 187.

Setup link previously down beneath user name, in new user interface, it is appear next to Help & Training link at top right after username, My Settings will replace the existing place for Setup. See screenshots below, this is only when Accessibility Mode if off for your user detail.

Before

After

You can activate new setup menu from Setup | Customize | User Interface, look for Setup section and select Enable Improved Setup User Interface. Please note, this will affects all users in your organization, so you better communicate with your other admins and users before activate it. It is good practice to always try in the sandbox instance or developer instance if you do not have any sandbox instances, to get your users approval.

For experience admin, new menu will make us to re-understand where the menu move to, but after a while you will be familiar and this new menu is make sense. You will see it will be group into better order and less click to reach a menu.

Thanks for Mike Gerholdt for create and sharing the changes into a graphical presentation.

right click image above and click 'Open link in new tab' to see bigger image.

Salesforce Joined report: Using the same Report Type in multiple Block

If you are using Salesforce Enterprise or Unlimited edition, Joined Report has been introduced sometimes back, with some limitations, slow performance, and need some improvements. But, in some business cases, we can take advantage of using this joined report.

The joined report format lets you view different types of information in a single report. A joined report can contain data from multiple standards or custom report types.

To get started with joined reports, create a new or edit an existing report in report builder, click the Format drop-down, and choose Joined. Most of the things you can do with summary or matrix report you can also do with joined reports. For example, you can find, add, and remove fields; summarize fields, and run and save reports. You can show a joined report that includes a chart on a dashboard.

We can easily add a new block with a different type by clicking the "Add Report Type" button, and it will create a new block and users can group the "common fields".

But, how we can have multiple blocks with the same object? Easy.... just drop a field at the right of the last block. See this video on Youtube.

Salesforce: Private Contact & Person Account

Private Contact
By default, Salesforce allow Contact do NOT link to Account, unless you set Account as required in Contact Page Layout or Validation Rule.

Although Account sharing rule is Public (Read Only or Read/Write), for a Contact without linked to Account, regardless what is the Contact sharing rule, it is considered as Private contact, meaning: only contact owner, user with View All Data and View All [on Contact object] permission able to see the Contact, while all other users will not able to see the contact (even if the user copy and paste Contact URL, he will get error message: Insufficient Privileges), and the contact will also not shown in the search result and report.

Sharing rules and workflow rules do not apply to private contact, you also cannot manually share and enable self-service for those contacts, both buttons will not be visible.

So in summary, it is not a good idea to leave Contact without Account. You can request Salesforce Support to enable Person Account for such scenario. Using an account as place holder is not a good idea as well, as the number of contacts linked to that place holder account growth, it will cause data skew issue.


Person Account
If you enable Person Account, contact sharing in Organization-Wide Default for Contact will be automatically change to Controlled by Parent and this is not editable. So, if you just enable Person Account, Organization-Wide Default for Contact will be changed to Controlled by Parent, this will remove all existing contact sharing rules and manually shared contacts.

Reference:

• How can I create a report showing accounts without contacts?
• Avoid Account Data Skew for Peak Performance

Salesforce: Opportunities in Campaign

One of my users came to me and asked why Opportunities in Campaign is not tally with Lead added to that Campaign and converted into Opportunity?

Here is the cause:

• One Lead can be added to multiple Campaigns
• But only the last Campaign associated with the Lead prior to the lead conversion will be brought into Primary Campaign Source in Opportunity
• When a Lead is converted, the Primary Campaign Source in Opportunity will be populated with the last Campaign associated with the Lead prior to the lead conversion

Opportunities in Campaign are counted by the number of opportunities with the Primary Campaign Source tagged to that Campaign.

Sample screenshots:

In Campaign:

Report with report type = Opportunities

The last questions surface, how do we know which is the latest Campaign associated with a Lead or a Contact?

Easy... look at the Campaign History related list in the Lead or Contact page layout (make sure the Campaign History related list has been added) and look for Member First Associated.

For other related fields, a sample of order Lead/Contact added to Campaigns

1. Add Jeryl Ma (lead) to 1st campaign
2. Add Lia Lee (lead) added to 2nd campaign
3. Add Lia Lee (lead) added to 1st campaign
4. Add Jake Llorrac (contact) to 2nd campaign
5. Add Tom James (lead) to 1st campaign

Result:

Notes:

• Converting lead Lia Lee with creating a new opportunity will populate Primary Campaign Source in the Opportunity with "1st Campaign" because the "1st Campaign" is the last campaign for Lia Lee.
• Converting lead Lia Lee with creating a new contact will increase the number of Contacts in Campaign, but not reduce the number of Lead in Campaign.

Last update: 27-Mar-2023

Salesforce Search on Object and Chatter

In previous blog, we blog about searchable field types in Salesforce. Today we'll continue with more information on Search.

Salesforce will keeps track of which objects you use and how often you use them, and arranges the search results accordingly. Objects you use most frequent will appear at the top of the list. If you do not see the object, click Search All at link.

Options
After you search something in Salesforce, you will see Options in search result page. From here, you can restrict search result only to records you owned.

Keywords
Salesforce search is case-insensitive for the whole data.

Wildcards and Operators
You can use the * (asterisk) and ? (question mark) wildcards to refine results. Use * to match one or more characters, or ? to match a single character. For example, searching for bob jo* finds items with Bob Jones and Bob Johnson, and searching for jo?n finds items with john and joan. You can also use the AND, OR, AND NOT, ( ) (parentheses), and " " (quotation marks) operators to refine results.
Sample: johan AND (singapore OR Indonesia); please note: operator support is case-insensitive.


But, my search result still do not shown records from a custom object. Why? This is usually happened because administrator do not create tab for that object. That is why Salesforce search function will not search custom objects if the objects don't have tab. For more info related to this, read this blog Global Search Result on Custom Object.


Chatter Search
If your company have Chatter enabled, Global search also returns Chatter feed result. From above screenshot, click "Search Feeds" link. This results include all relevant public posts, including posts from people you are not following and from public groups you are not a member of. In other words, Global search return more feed information compare to if you search individual feed (Chatter home feed, record feed, group feed).

To search only from a particular feed, use the "Search in Feed" magnifying glass, if you type more than 1 word, it will search words appear together, as well as they appear separately.

Chatter Feed in Home tab

Feed in Chatter tab

Another way to search in Chatter by using Topic, it will show all post or comment tagged with the topic, even from people you are not following, or group you are not a member of.  You also can follow the topic, so when someone use that topic, it will appear in your feed.

Bookmark is another option to follow a post, so you can easily looks for post interested you from Chatter Bookmarked menu.


Reference:

• Global Search Result on Custom Object
• Wildcards and operators for search
• Find Information with Search

Last update: 14 Feb 2017

Salesforce: Hyperlink in Excel file

Out-of-the-box, a lookup field in Salesforce report is click-able, such as: you can click Account Name in Opportunity report, it will bring user to Account page layout.

But, when the report export into Excel file, the link is gone. Can we make the link stay? Yes, here we go:

1. Create a Formula Field

• Set the formula field return type = Text
• Formula field: HYPERLINK("https://xxx.salesforce.com/"& Id , "Click me", "_self") - change xxx with your Salesforce instance, e.g.: ap1, na1, na6, etc
• Do not need to show this formula field in any page layout
• Make the field visible only to profile needed.

2. Add Formula Field into Report

3. Printable View
Instead of using 'Export Details' button, advise user to use 'Printable View' button. User can delete the header and footer is not needed in Ms Excel.

This may be not the best solution, but it works with simplest effort.

Salesforce report:

Microsoft Excel:

Salesforce: Using Permission Set to Query User Permission

Permission Set is a powerful feature in Salesforce. If you are using Enterprise and Unlimited editions (including Developer edition). Using the permission set, the admin can assign additional permissions to users on top of the permission given in the Profile assigned to that user. Permission Set is set per user basis and is only to ADD more permissions, not to reduce it from Profile. Permission Set also may be given to admin users when the permission cannot be enabled in the standard System Administrator profile.

Permission Sets include settings for:
- Assigned Apps
- Assigned Connected Apps
- Object Settings, which include objects, fields, and tab availability
- App Permissions
- Apex Class Access
- Visualforce Page Access
- External Data Source Access
- Named Credential Access
- Data Category Visibility
- Custom Permissions
- System Permissions
- Service Providers

In this blog, I am not going to explain how to set up the Permission Set; you can find the overview here.

As of now, we still cannot run a report on the Permission Set and Users assignment to Permission Set. But, since the Summer '11 release (API version 22), Salesforce has introduced 2 new objects related to this: PermissionSet and PermissionSetAssignment. To make this object more powerful, in the Spring '12 release (API version 24), FieldPermissions and ObjectPermissions objects are introduced with ParentId, which points to PermissionSet.

Once you understand the architecture, you can answer all sorts of questions about your users and permission. Here is the diagram (right-click the image to see it in full size) :

Here are a few samples using SOQL to query permission set:

List all Permission Set
SELECT Id, Name FROM PermissionSet 

WHERE IsOwnedByProfile = False 

ORDER BY Name

List all Permission Set that grant extra Permission, such as Create Dashboard

SELECT Id, Label FROM PermissionSet 

WHERE IsOwnedByProfile = False AND PermissionsCreateCustomizeDashboards = True 

ORDER BY Name

List all Permission Set does not from Managed Package
SELECT Id, Name FROM PermissionSet 

WHERE IsOwnedByProfile = False AND NamespacePrefix = '' 

ORDER BY Name

List all Users with ViewAllData Permission
SELECT Id, AssigneeId, Assignee.Name, PermissionSet.Name, PermissionSet.IsOwnedByProfile
FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsViewAllData = True
ORDER BY PermissionSet.IsOwnedByProfile DESC, Assignee.Name

This query will return all Users with Permission to View All Data, either acquired from the Profile or from the Permission Set.

Compare this with the query below, where it just returns the permission from Profile only.

SELECT Id, Name FROM User

WHERE ProfileId IN (SELECT Id FROM Profile

                    WHERE PermissionsViewAllData = True)

ORDER BY Name

List all Active Users without ViewAllData Permission

SELECT Id, Name FROM User 

WHERE Id NOT IN (SELECT AssigneeId FROM PermissionSetAssignment 

                 WHERE PermissionSet.PermissionsViewAllData =  

                 True) 

      AND IsActive = True

ORDER BY Name 

So instead of pulling all users and all users with specific permission, then doing a vlookup() in Excel, we can combine the query using anti-joint query NOT IN.

Id in the main query is the same ID as AssigneeId in the sub-query, which is User Id.

List all Active Users with Transfer Record Permission
SELECT Id, AssigneeId, Assignee.Name, Assignee.Profile.Name, PermissionSet.IsOwnedByProfile FROM PermissionSetAssignment
WHERE PermissionSet.PermissionsTransferAnyEntity = True AND Assignee.IsActive = True
ORDER BY PermissionSet.IsOwnedByProfile DESC, Assignee.Name

List all Users by specific Profile and return the Permission Set assigned to that user
SELECT p.Id, p.Assignee.Name, p.Assignee.Profile.Name, p.PermissionSet.Label FROM PermissionSetAssignment p
WHERE p.PermissionSet.IsOwnedByProfile = False AND p.Assignee.Profile.Name = 'Sales Reps'
ORDER BY p.PermissionSet.Label, p.Assignee.Name

This query will return all Users with Permission Set assigned to users with Profile = Sales Reps and the additional Permission Set name assigned.

List Profile and Permission Set have read access to the Account for a User
SELECT Assignee.Name, PermissionSet.isOwnedByProfile, PermissionSet.Profile.Name, PermissionSet.Label FROM PermissionSetAssignment
WHERE PermissionSetId IN 

    (SELECT ParentId FROM ObjectPermissions 

     WHERE SObjectType = 'Account' AND PermissionsRead = True)
    AND Assignee.Name = 'Johan Yu'
ORDER BY PermissionSet.Profile.Name, PermissionSet.Label

This query will return Permission Set (and Profile if exists) that gives the user read access to the Account object.

List Profile and Permission Set that give Edit access to a Field for a User

SELECT Id, IsActive, ExpirationDate, AssigneeId, Assignee.Name, Assignee.IsActive, PermissionSetId, PermissionSet.Name,  PermissionSet.IsOwnedByProfile, PermissionSet.Profile.Name FROM PermissionSetAssignment 

WHERE PermissionSetId IN 

       (SELECT ParentId FROM FieldPermissions

        WHERE Field='A__c.Support_Staff__c' 

        AND PermissionsEdit = True)

AND Assignee.Name = 'Maria Ann'

List Permission a User has for Account and which permissions give that access
SELECT Id, SObjectType, Parent.Label, Parent.IsOwnedByProfile, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords
FROM ObjectPermissions
WHERE (ParentId IN (SELECT PermissionSetId FROM PermissionSetAssignment WHERE Assignee.Name = 'Johan Yu'))
AND (SobjectType = 'Account')
ORDER BY Parent.IsOwnedByProfile DESC, Parent.Label

This query will return user permission for an object and also tell all Permission Sets (and profiles if they exist) that give the user that permission.

Show Object Settings added to all Permission Set
SELECT SobjectType, Parent.Name, PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete, PermissionsViewAllRecords, PermissionsModifyAllRecords FROM ObjectPermissions WHERE SObjectType IN ('Account') AND Parent.IsOwnedByProfile = false

This query will return only Permission Set with additional permissions on a specific object.

Get all Non-Chatter users with Lightning Experience permission enabled
SELECT Id, Assignee.Username, Assignee.Profile.Name FROM PermissionSetAssignment WHERE Permissionset.PermissionsLightningExperienceUser = True AND Assignee.Isactive = True AND (NOT Assignee.Profile.Name LIKE '%chatter%') ORDER BY Assignee.Username


Mass Assign Permission Sets to Users
Based on PermissionSetAssignment attributes, we can use Data Loader to mass assign (and mass delete) users with a specific Permission Set. All you need to provide is AssigneeId (which is User Id) and PermissionSetId. But, you cannot update the record in PermissionSetAssignment.



Reference:

• FieldPermissions
• ObjectPermissions
• PermissionSet
• PermissionSetAssignment
• SOQL (Salesforce Object Query Language) Reference
• Using SOQL to Determine Your Force.com User’s Permissions
• Comparing Profiles and Permission Sets
• Permission Sets Best Practice: Mass Assign Permission Sets and other cool things using the API
• How to Troubleshoot User Access with SOQL (Beginner Friendly)

Last update: 9 Jul 2024