Most Salesforce.com Administrators are often asked to perform a variety of tasks that are not easily available in the Salesforce.com User Interface or Reporting environment.  So to that end I’ll endeavor to teach you (the Salesforce.com Administrator) a few tricks of the trade, including how to use the Developer Console.

First log into Salesforce.  Then click your name and from the Dropdown select “Developer Console”

Menu

So for these examples I will use the User and Profile Objects. Here are some things you can keep and Modify for handling users.  The first is an SOQL Queries you can run that will give you a count of Users by Profile.  This is known as an “Aggregate Query”


SELECT ProfileId, Profile.Name, COUNT(Id) myCount FROM User WHERE IsActive = True
GROUP By ProfileId, Profile.Name

To execute this query you’ll select the “Query Editor” tab of the Developer Console and paste this query into the window:

Console

Next click the “Execute” button and view the Results

Console Query Results

Now if you’d like to see the Users in each profile you can use another SOQL Query to create a List by utilizing a Sub SOQL Query to filter for a particular profile like so:


SELECT Id, Name, UserType, LastLoginDate, Profile.Name FROM User
WHERE ProfileId IN (SELECT Id FROM Profile WHERE Name LIKE '%Partner%')
AND IsActive = True
ORDER BY Profile.Name, LastLoginDate

Several things to note here.  First the WHERE clause makes use of a Sub SELECT which will return the Id column from the Profiles Object for all profiles that contain the word “Partner”.  Secondly we are using the ORDER BY clause to group users for each Profile Name logically together, and then by Last Login Date.

Console Query Results - Sub Query

To reuse this query to find users in other Profiles you change %Partner% to %[name]% (where [name] represents the Profile Name) in order to get the users of a profile or group of Profiles.  For example if you wanted all users in an Administration Profile Group change it to %Admin%

Console Query Results - Sub Query - 2

So now you’ve seen how to use the SOQL Query Editor, let’s talk about something more useful…dumping the results to a text file. In order to do that you’ll need to Open the Execute Anonymous window.

Execute Anonymous Menu

Paste this code into that window



List listOfUsers = [SELECT Id, Name, LastLoginDate, Profile.Name 
                    FROM User
                    WHERE ProfileId
                    IN (SELECT Id FROM Profile WHERE Name LIKE '%Partner%')
                    AND IsActive = True
                    ORDER BY LastLoginDate, Profile.Name];

String csv = 'Id,Name,ProfileName,LastLoginDate\n';
for(User u : listOfUsers)
{
   csv += u.id + ',' + u.name.escapeCsv() + ',' u.Profile.Name + ','
        + u.LastLoginDate + '\n';
}

ContentVersion file = new ContentVersion(title = 'PartnerUsersActive.csv',
                                        versionData = Blob.valueOf( csv ),
                                        review_date__c = date.today(),
                                        pathOnClient = '/PartnerUsersActive.csv');

insert file;
System.debug('Content URL : /' + file.Id);

This will generate an Excel CSV file.  Check the Open Log option on the bottom right of the Execute windows (next to the Execute button) so when the code completes it will Open the Log file

Execute Anonymous Code

You’ll see the URL in the Log file

Debug Log

Double click on the line in the Log file and it will open up a new window with the details

URL To File

Copy the “/” and Id following it.

Then go back to the browser which will have the https://naXX.salesforce.com in the URL.  Duplicate the current tab of Salesforce.  Once duplicated Past the “/” + Id after it in the URL (example: https://na28.salesforce.com/0681A000002sAxaQAE)

This will jump you right to the content file.  Click the Download Link

Content Download

Then after you download it, delete the file from SFDC.

If you need to create a CSV file with the Aggregate results (from a SELECT Coun() query) use the following code in the Execute Anonymous window and follow the same steps as above to open the Content and download the CSV


AggregateResult [] arResults = [SELECT ProfileId, Profile.Name pName, 
                                COUNT(Id) myCount
                                FROM User
                                WHERE IsActive = True
                                 GROUP BY ProfileId, Profile.Name];

String csv = 'ProfileId, Count\n';
for(AggregateResult ar: arResults)
{
    Integer iCnt = (Integer)ar.get('myCount');
    
    String line = (String) ar.get('ProfileId') + ',';
    
    line += (String) ar.get('pName') + ',';
    line += iCnt.format() + '\n';
    csv += line;
}

ContentVersion file = new ContentVersion(title = 'ProfileUserCount.csv',
                                    versionData = Blob.valueOf( csv ),
                                    review_date__c = date.today(),
                                    pathOnClient = '/ProfileUserCount.csv');

insert file;
System.debug('Content URL /' + file.Id);

 

This will produce a CSV with the total number of users in each profile. For any queries that utilize Aggregate functions you’ll want to use this template.

[contact-form]
Filed under: Salesforce.com Programming