+ Start a Discussion
sylar20sylar20 

using Case createdById UserRole

I am trying to select the userrole name of the user who created the case by following code but not able to get it.

It gives me an error in line 3.

 

for (Case c:accs){
User thisUser = [ select Id FROM User where Id =:c.CreatedById];
UserRole userrolle = [ select Id FROM UserRole where Id =:thisUser.UserRoleId];
string rolename = userrolle.Name;

-----------------------------------

-------------------------------

-----


}

Best Answer chosen by Admin (Salesforce Developers) 
sdetweilsdetweil

again you didn't select all the data

 

User thisUser = [ select Id FROM User where Id =:c.CreatedById];
UserRole userrolle = [ select Id, Name FROM UserRole where Id =:thisUser.UserRoleId];

 

the field name for apex is UserRole

 

so I think you wanted

User thisUser = [ select UserRole FROM User where Id =:c.CreatedById];
UserRole userrolle = [ select Id, Name FROM UserRole where Id =:thisUser.UserRole];

 

sam

All Answers

sdetweilsdetweil

you only populated the ID field,..

 

UserRole userrolle = [ select Id FROM UserRole where Id =:thisUser.UserRoleId];

 

you need to add the role name

 

UserRole userrolle = [ select Id, Name FROM UserRole where Id =:thisUser.UserRoleId];

 

sf is cool, it will allow you to get ONLY what you need.. but you have to tell it explicitly!

 

Sam

forecast_is_cloudyforecast_is_cloudy

You aren't including the neccessary fields in your SOQL queries. As the earlier poster pointed out, you need to explicitly include any field that you want to reference in the SOQL query. Also, you can get the information you need in a single query. Finally, your code is not bulkified (it has SOQL statements inside a For loop) and can run into Governor limits.

 

Try this instead

 

Set<Id> userIds = new Set<Id>();

for (Case c:accs){

     userIds.add(c.CreatedById);

}

 


User[] users = [ select UserRole.Name FROM User where Id in :userIds];

 

-----------------------------------

-------------------------------

-----


}

sylar20sylar20

I changed the code as following:

 

for (Case c:accs){
User thisUser = [ select Id FROM User where Id =:c.CreatedById];
UserRole userrolle = [ select Id, Name FROM UserRole where Id =:thisUser.UserRoleId];
string rolename = userrolle.Name;

 }

 

------

-------

 

Error:

 

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger LogEmailToActivityOnDispute caused an unexpected exception, contact your administrator: LogEmailToActivityOnDispute: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: User.UserRoleId: Class.LogEmailToActivity.UpdateActivity: line 8, column 65
sdetweilsdetweil

again you didn't select all the data

 

User thisUser = [ select Id FROM User where Id =:c.CreatedById];
UserRole userrolle = [ select Id, Name FROM UserRole where Id =:thisUser.UserRoleId];

 

the field name for apex is UserRole

 

so I think you wanted

User thisUser = [ select UserRole FROM User where Id =:c.CreatedById];
UserRole userrolle = [ select Id, Name FROM UserRole where Id =:thisUser.UserRole];

 

sam

This was selected as the best answer
sdetweilsdetweil

 


forecast_is_cloudy wrote:

You aren't including the neccessary fields in your SOQL queries. As the earlier poster pointed out, you need to explicitly include any field that you want to reference in the SOQL query. Also, you can get the information you need in a single query. Finally, your code is not bulkified (it has SOQL statements inside a For loop) and can run into Governor limits.

 

Try this instead

 

Set<Id> userIds = new Set<Id>();

for (Case c:accs){

     userIds.add(c.CreatedById);

}

 

User[] users = [ select UserRole.Name FROM User where Id in :userIds];


}


thank you , thats some pretty interesting code..

 

I have two questions

1. the Users user[] = statement, returns a 'userrole.name' field,  but that is not present in a 'User' object,

so how do you reference it?  user[index].userrole.name? 

     does this only work where the referenced type direct field is an id? (userrole= reference in this case)

 

2. the admonition of no soql in a loop..  is that explicit soql or any?

    cause to my mind the for(Case c:accs) is just language short have for a soql loop

 

still a relative novice trying to learn..

 

thanks

Sam

 

forecast_is_cloudyforecast_is_cloudy

1) Yes, you would reference a User Role name from the array with the 'user[index].userrole.name' notation. To better understand how parent-child relationships work in Salesforce please refer to the following documentation - http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm. In your case, User is the Child and UserRole is the Parent in the relationship.

 

2) You should never have any SOQL or DML statements (like updates/insert/delete) inside a for-loop. Please refer to the following article for more on how to bulkify Apex code - http://wiki.developerforce.com/index.php/Apex_Code_Best_Practices

 

Hope this helps....

sdetweilsdetweil

SOQL 'statements' in a loop.. got it..

 

tried to rewrite my trigger, and figured out the _r for my custom reference field,

but the trigger no longer works as expected.. stil researching

 

Sam

sdetweilsdetweil

here is my updated trigger.. can't figure out why neither side works..

these are both custom objects, like notes on a case. but I need control of the visible data and the date/time

on the before insert, I want to show the users company id in the view list.. and can get than from the  createdby user reference to custom field. and don't want to depend on the user entering it.

 

on the after insert. I have a trigger that updates a remote system if the 'problem' has been 'escalated' and need to wake up that trigger when this comment is saved.

 

the isapi flag on both objects is set if the remote system makes inserts or updates to either object. the triggers do not fire in this case,

cause there is no reason to send the data we just received back to the originator.

 

 

trigger commenttrigger2 on Comment__c (before insert, after insert) {
      
      // loop thru the before saved comments and make sure the creator field is set
      if(Trigger.isBefore)
      {
            for(Comment__c a:Trigger.new)
            {
                if(a.isApi__c==false)
                {
                    system.debug('new comment from sf ui'); 
                    if(a.creator__c==null)
                    {
                        system.debug('new comment from sf ui, creator not set' + a.CreatedBy ); 
                        a.creator__c = a.CreatedBy.pmfkey__c;
                    }
                }
            }
      }
      // loop thru the saved new comments and cause the problem trigger to fire
      // only of the problem is escalated already
      else
      {
            system.debug('trigger is after='); 
            list<problem__c> problems = new list<problem__c>();
            // loop thru all the comments and check the parent for escalated
            for(Comment__c a:trigger.new)
            {
              system.debug('trigger is after=' + a.related_problem__r.name); 
              if(a.related_problem__r.isEscalated__c==true) 
                {
                    system.debug('escalated problem=' + a.related_problem__r.name);                               
                problems.add( new problem__c (Id = a.related_Problem__c, isAPI__c=false));  // update status field to new
                }
            }
            if(problems.size()>0)
                update problems; 
        
      }
}

 

my old trigger works, but breaks the rules and is incomplete (I started with both before and after insert, but got sidetracked)

 

 

trigger commentTrigger on Comment__c (after insert) {
      map<id, problem__c> problems = new map<id, problem__c>();
      Problem__c o = null;
      
      for(Comment__c a:[select Related_Problem__c, isApi__c, creator__c, createdById from Comment__c where id in :trigger.NewMap.keySet()])      
      {      
          if(a.isApi__c==false)
          {
              if(Trigger.isBefore)
              {
                  if(a.creator__c==null)
                  {
                     User u=[select pmfkey__c from User where id=:a.createdbyid];
                     //a.creator__c=u.pmfkey__c;
                  }
              }    
              else
              {      
              o = new problem__c (Id = a.related_Problem__c, isAPI__c=false);
              problems.put(a.related_problem__c, o);  // update status field to new
              }
          }      
      }
      if(problems.size()>0)
        update problems.values(); 
}