Question in detail :

  • I have 20 Users {U1, U2, U3....}
  • and 5 Regions {R1, R2, R3, R4, R5}
  • and Ammount__C custom field on User.

Now those users are distributed among those regions. So i want to give them Rank (Rank__c) depends on the amount they have. for Greater the amount rank will be first. And it should be region wise. Should not consider all users at once. If One region has 6 users, then they should be ranked from 1 to 6. same thing for the next region again. Hope i am clear here.

How to achieve this?

List<User> allUser = [SELECT Id, UserRole.Name, FSLA_Role__c,Region_Name__c,Territory__c FROM User WHERE FSLA_Role__c='TSL' AND IsActive=true];

Map<String, User> mapTslRegion = new Map<String, User>();
Map<String, User> mapTslTerritory = new Map<String, User>();
for(User usr : allUser){


if(!mapTslRegion.isEmpty() && !mapTslTerritory.isEmpty()){
    List<User> lstUser = [Select id, FSLA_Role__c, Market__c, Presidential_Territory__c, Territory__c, Region_Name__c
                                  from user where FSLA_Role__c = 'FSLG' AND IsActive=true AND Region_Name__c in:mapTslRegion.keySet() AND Territory__c in:mapTslTerritory.keySet()];

    if(lstUser.size() > 0){
        List<User_Performance_Metrics__c> lstUserPerformanceMetrics = [Select id, Appointed_EA_12MM__c, User__r.Region_Name__c, User__c from User_Performance_Metrics__c where User__c in:lstUser ORDER BY Appointed_EA_12MM__c DESC];

        if(lstUserPerformanceMetrics.size() > 0){
            integer tempRank =1;
            decimal tempAppointedEA12MM = lstUserPerformanceMetrics.get(0).Appointed_EA_12MM__c;
            for(User_Performance_Metrics__c upm:lstUserPerformanceMetrics){
                if(upm.Appointed_EA_12MM__c != tempAppointedEA12MM){
                upm.Appointment_Ranking__c = tempRank;
                tempAppointedEA12MM = upm.Appointed_EA_12MM__c;
            update lstUserPerformanceMetrics;
  • 2
    What have you tried so far? There's a way to do this with a single query, but it'll help you grow more if we start from what you have (which might already be really close). You should edit your question to include the code you've tried writing so far. – Derek F May 14 '19 at 16:11

I'm going to assume that each user's region is specified on the user also.

To run the ranking and loop through users

        //Get list of all Users ordered by Amount
        List<User> users = [SELECT Id, Rank__c, Region__c, Amount__c FROM User ORDER BY Amount__c NULLS LAST];

        //Initialize each region's index to 1
        Map<String, Integer> regionRank = new Map<String, Integer>(
            'R1' => 1,
            'R2' => 1,
            'R3' => 1,
            'R4' => 1,
            'R5' => 1

        //Loop through all users to assign rank
        for(User user : users){
            //get the rank index according to the user's region using our regionRank map
            Integer rank = regionRank.get(user.Region__c);

            // assign that user's rank to the correct value from the regionRank map and then increment the rank
            user.Rank__c = rank++;

            //replace the region's rank value with the old rank + 1
            regionRank.put(user.Region__c, rank);

            //the next time a user with that region is traversed, it will assign the next rank for that region
| improve this answer | |
  • 1
    user.Rank__c == rank++; should be user.Rank__c = rank++; – sfdcfox May 14 '19 at 17:10
  • Thanks - Fixed. – Ayoola Olapo May 14 '19 at 17:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.