Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Salesforce Admin 110Salesforce Admin 110 

aggregate query

i have Added an auto-number field to User selecting the "Generate Auto Number for existing records" checkbox to populate the field for the existing Users.

Then use it from Apex code - i need help with apex on the following::
  • Generate say 10 random numbers between the min and max values of that auto-number field obtained using an aggregate query.
  • Query for the Users that match those numbers and also have IsActive=true.
  • Take the one 
Daniel BallingerDaniel Ballinger
Something like the following should get you started.

I used the PostalCode field in lieu of a custom field. As such I needed to move back and forth between integer and string. You'll need to adapt that based on your field type.
  
AggregateResult[] aggResults  = [SELECT MIN(PostalCode) minf, MAX(PostalCode) maxf FROM User];
integer minAmount = Integer.valueOf(aggResults[0].get('minf'));
integer maxAmount = Integer.valueOf(aggResults[0].get('maxf'));

System.debug(minAmount);
System.debug(maxAmount);

Set<string> valuesToFind = new Set<string>();
for(integer i = 0; i < 10; i++) {
    // generate random int
    integer randomIntInRange = Math.round(Math.random() * (maxAmount - minAmount)) + minAmount;
    valuesToFind.add(string.valueOf(randomIntInRange));
}

List<User> randomActiveUsers = [Select Id from User where PostalCode in :valuesToFind and IsActive = true LIMIT 1];

 
Salesforce Admin 110Salesforce Admin 110
i like your answer, however i am a newbie to apex, could you check the following trigger and check for errors please, i have no probleming saving this in developer console but alloacted user doesn't populate with a value:

trigger popallocateduser on Contact ( before update) {

 Set<String> roleIds = new Set<String>();
roleIds.add('00E25000000QO8O');
roleIds.add('00E25000000QO8T');
roleIds.add('00E25000000QO8d');
roleIds.add('00E25000000QO8i');    
    
  AggregateResult[] aggResults  = [SELECT MIN(User_Identifier__c) minf, MAX(User_Identifier__c) maxf FROM User where userroleid in :roleids];
integer minAmount = Integer.valueOf(aggResults[0].get('minf'));
integer maxAmount = Integer.valueOf(aggResults[0].get('maxf'));

System.debug(minAmount);
System.debug(maxAmount);

Set<string> valuesToFind = new Set<string>();
for(integer i = 0; i < 10; i++) {
    // generate random int
    integer randomIntInRange = Math.round(Math.random() * (maxAmount - minAmount)) + minAmount;
    valuesToFind.add(string.valueOf(randomIntInRange));
}  
    
  


    
List<User> userList = new List<User>();
userList = [select id from User where isActive = true  and User_Identifier__c in :valuesToFind  LIMIT 1];

if(userList.size() > 0){
    for(Contact con : trigger.new){
        if(con.Allocated_User__c == null){
            con.Allocated_User__c = userList[0].id;
        }
    }
}
}
Daniel BallingerDaniel Ballinger
Have you confirmed that the size of the userList is greater than 0? if the code doesn't find any active users in the range of random integers than Accocated_User__c will never be set.