function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
V100V100 

Trying to count Accounts owned by User

I have created the following BulkApex to count the number of accounts owned by users.

 

This works, however it does not work for our larger account owners where it hits governor limits. To combat this i have put in a LIMIT 5000, however we have many owners with more than 5k accounts and i would like to get an accurate count.

 

        List<User> users = new List<User>();
        Set<ID> UserIDs = new Set<ID>();
        for (Sobject c : scope) {
                UserIDs.add(c.Id);
        }       
        Map<ID, User> usersWithAccounts = new Map<ID, User>([select Id, Accounts_Owned__c  FROM User WHERE  Id in :UserIDs]);
        Map<ID, Account> acctsCount = new Map<ID, Account>([select Id, OwnerID FROM Account WHERE OwnerId in :UserIDs LIMIT 5000]);
        
        for (User acct : usersWithAccounts.values()) {
            Set<ID> conIds = new Set<ID>();
            for (Account con : acctsCount.values()) {
                if (con.OwnerID == acct.Id)
                    conIds.add(con.Id);
            }
            acct.Accounts_Owned__c = conIds.size();        
        }
        update usersWithAccounts.values();

    }
 global void finish(Database.BatchableContext BC){
    }
}

I have tried to do variation on 

Map<ID, Account> acctsCount = new Map<ID, Account>([select Id, OwnerID FROM Account WHERE OwnerId in :UserIDs LIMIT 5000]);

 

To use COUNT(), COUNT(ID) instead of the size so it will run for all users.

 

I have taken this script from another similar process for counting contacts on the account, so that is the basis for the way it has been contstructed, I am very new to Apex.

Any hlep much appreciated.

 

Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC

I did this quickly, but seems to compile and work in a test environment at last...there are ways to make more efficient but this gives you an idea...

 

Batch Apex class:

 

global class AccountTotals implements Database.Batchable<sObject>,Database.Stateful{
public String query;

global Map<Id,Decimal> umap = new Map<Id, Decimal>();

global AccountTotals() {
//Create County Map so we can update County Totals by # of Donors
For (User u : [Select Id from User where isActive = TRUE]) { 
umap.put(u.Id, 0);
}
}

global database.querylocator start(Database.BatchableContext BC){
return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<sObject> scope){

for(sObject s : scope) {

Account a = (Account)s;

if (umap.containsKey(a.OwnerId)) {
Decimal cu = umap.get(a.OwnerId) + 1;
umap.put(a.OwnerId,cu);
}
}
}

global void finish(Database.BatchableContext BC){

List<User> lmap = new List<User>();

for (Id smap : umap.keySet()) {
lmap.add(new User (Id = smap, Accounts_Owned__c = umap.get(smap)));
}

update lmap;

}
} //end class

 

 

You can execute it in the sys console with this:

 

AccountTotals at = new AccountTotals();
at.query = 'Select Id, OwnerId from Account';
ID batchprocessid = Database.executeBatch(at);

 

 

 

 

 

All Answers

BritishBoyinDCBritishBoyinDC

I think I would take a slightly different approach...

 

Make the batch stateful - that way it will remember the count we'll do as the batch progresses...

 

In the initial constructor, build a map of Type <Id, Integer> , and populate that map's key with every user in the system, and set the value to zero for each integer.

 

Now just pass every account you have into the batch and for each account, you can get the value from the map, and increment it by one the owner of that account.

 

In the finish, loop back through the map, and update the user records with the final count
V100V100

BritishBoy

Thanks for you repsonse. I follow the logic of what you are suggesting, but cant really put that into apex.

Any chance of a sample for me to work through. Only managing to alter other code at the moment, rather than start from scratch.

thanks
BritishBoyinDCBritishBoyinDC

I did this quickly, but seems to compile and work in a test environment at last...there are ways to make more efficient but this gives you an idea...

 

Batch Apex class:

 

global class AccountTotals implements Database.Batchable<sObject>,Database.Stateful{
public String query;

global Map<Id,Decimal> umap = new Map<Id, Decimal>();

global AccountTotals() {
//Create County Map so we can update County Totals by # of Donors
For (User u : [Select Id from User where isActive = TRUE]) { 
umap.put(u.Id, 0);
}
}

global database.querylocator start(Database.BatchableContext BC){
return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<sObject> scope){

for(sObject s : scope) {

Account a = (Account)s;

if (umap.containsKey(a.OwnerId)) {
Decimal cu = umap.get(a.OwnerId) + 1;
umap.put(a.OwnerId,cu);
}
}
}

global void finish(Database.BatchableContext BC){

List<User> lmap = new List<User>();

for (Id smap : umap.keySet()) {
lmap.add(new User (Id = smap, Accounts_Owned__c = umap.get(smap)));
}

update lmap;

}
} //end class

 

 

You can execute it in the sys console with this:

 

AccountTotals at = new AccountTotals();
at.query = 'Select Id, OwnerId from Account';
ID batchprocessid = Database.executeBatch(at);

 

 

 

 

 

This was selected as the best answer
forcedotcomforcedotcom

How come you don't just run a summary report?
V100V100

Thanks BritishBoy work perfectly

 

 
V100V100

A summary report wouldn't help as we want to be able to to manage users depending on the volume of accounts they own
forcedotcomforcedotcom

Right I see - well looks like you've got it sussed anyway :)