June 24, 2009

Look Up Record Type IDs With Just One Query

As you should already know, Salesforce governor limits will only allow you to execute so many queries per transaction - but sometimes you just have to look up certain information in the database to do your work. For example, what should you do if you need to look up three different Account record types in a trigger?

Certain less-experienced programmers might do this:

ID shipperRecordType = [SELECT Id FROM RecordType WHERE Name = 'Shipper'].Id;
ID carrierRecordType = [SELECT Id FROM RecordType WHERE Name = 'Carrier'].Id;
ID vendorRecordType = [SELECT Id FROM RecordType WHERE Name = 'Vendor'].Id;
It's quick, it's easy -- and it's three queries. Sure, you have a deadline and you're in a real hurry, but doing it the right way takes about five minutes longer and won't cause too-many-query errors down the line.

There's a bunch of issues with this code:

  • You aren't querying by SObjectType. There can be other record types in the database with these same names and an error will be thrown if there are too many records or not enough records returned.
  • These queries do not have a LIMIT on them. Generally, you always want to have a LIMIT on your SOQL queries so you can more appropriately respond to all of the different ways a query can fail. (There are exceptions to this rule, of course.)
  • What if these record types don't exist (yet)?
  • You are querying by Name instead of DeveloperName. I've seen too much code fail unexpectedly because the client decided to change the label on a record type.
Try something like this instead:
// Note one query, total.
ID shipperRecordType;
ID carrierRecordType;
ID vendorRecordType;
for (RecordType rt :
   [SELECT Id, Name 
      FROM RecordType 
     WHERE SObjectType = 'Account'
       AND DeveloperName IN ('Shipper', 'Carrier', 'Vendor')])
{
   if (rt.DeveloperName == 'Shipper') shipperRecordType = rt.Id;
   if (rt.DeveloperName == 'Carrier') carrierRecordType = rt.Id;
   if (rt.DeveloperName == 'Vendor') vendorRecordType = rt.Id;
} 
// Always remember to check for system-level data that you assume
// will be there.
if (shipperRecordType == null || carrierRecordType == null || 
    vendorRecordType == null)
{
   // See previous post about custom exception classes.
   throw new util.customException('Cannot find Account record type; contact administrator');
}

3 comments :

  1. Thank you, simple but useful info. Fixed my error.

    ReplyDelete
  2. Thanks, this was helpful, however I think there's a mistake in your code. The SOQL query should be

    SELECT DeveloperName
    FROM RecordType
    WHERE SObjectType = 'Account'
    AND DeveloperName IN ('Shipper', 'Carrier', 'Vendor'

    You never look at the Id or Name fields in the code, so you don't need to query those. But if you don't query DeveloperName, you will get this error:

    "SObject row was retrieved via SOQL without querying the requested field: RecordType.DeveloperName"

    ReplyDelete