Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jonathan Osgood 3Jonathan Osgood 3 

Multiple Record Types in SOQL

Want my soql query to find records with specific record types. OR stement returning error: expecting right square bracket, found 'OR'

Do I need to create additional soql queries to first obtain record types, then reference in existig soql?

Thanks!

Existing soql 
List<AggregateResult> results = [SELECT Account__c, 
                                         SUM(X2_1_a_Total_of_jobs_Year_0__c) mysum0,
                                         FROM Form__c 
                                         WHERE Account__c IN:accountIds
                                         AND (RecordType.DeveloperName ='Baseline Report')
                                         OR  (RecordType.DeveloperName ='Annual Report')
                                         GROUP BY Account__c]  ;

 
Best Answer chosen by Jonathan Osgood 3
James WooleyJames Wooley
The problem with using the RecordTypeInfosByName is that it references record types by their name (label) not their developer name as you were trying to do in your original SOQL. This is more likely to change that the developer name.

You could use your existing SOQL, but make sure the text you put in is actually the developer name, not the name. I imagine it is exactly what Vivek originally suggested, but with underscores rather than spaces: 
List<AggregateResult> results = [SELECT Account__c, 
                                         SUM(X2_1_a_Total_of_jobs_Year_0__c) mysum0,
                                         FROM Form__c 
                                         WHERE Account__c IN:accountIds
                                         AND (RecordType.DeveloperName ='Baseline_Report'
                                         OR  RecordType.DeveloperName ='Annual_Report')
                                         GROUP BY Account__c];
I would definitely use the RecordTypeInfosByName method when you are trying to create new records with a specific record type, or when checking a record's record type in trigger context, but for SOQL I think what you were trying to do is best.

All Answers

Vivek DeshmaneVivek Deshmane
Hi Jonathan,
Try below SQL and let me know if it works. 
List<AggregateResult> results = [SELECT Account__c, 
                                         SUM(X2_1_a_Total_of_jobs_Year_0__c) mysum0,
                                         FROM Form__c 
                                         WHERE Account__c IN:accountIds
                                         AND (RecordType.DeveloperName ='Baseline Report'
                                         OR  RecordType.DeveloperName ='Annual Report')
                                         GROUP BY Account__c]  ;

Best Regards,
-Vivek Deshmane
Jonathan Osgood 3Jonathan Osgood 3
Hi Vivek,

That clears out the error, but the soql returns 0 results. I've tried in dev console querying tool with same result. This syntax has worked for me before, but I think I may to do something like: 
  
RecTypeBaseline = [select id,Name from RecordType where SobjectType='Form__c' and Name='Baseline Report' Limit 1].id;
then reference in my existing soql.  
List<AggregateResult> results = [SELECT Account__c, 
                                         SUM(X2_1_a_Total_of_jobs_Year_0__c) mysum0,
                                         FROM Form__c 
                                         WHERE Account__c IN:accountIds
                                         AND RecordTypeId =: RecTypeBaselineReport
                                         GROUP BY Account__c]  ;

I was hoping to avoid another soql query though
 
Vivek DeshmaneVivek Deshmane
Hi Jonathan,
Use below coe to get Recordtype id without SOQL Query.
  
Id RecordTyepId=Form__c.sObjectType.getDescribe().getRecordTypeInfoByName().get('Baseline Report').getRecordTypeId();

Let me know if it works and mark best answer.

Best Regards,
-Vivek Deshmane
Jonathan Osgood 3Jonathan Osgood 3
Thanks Vivek,

The syntax was a little different, but very close. This worked:

Id BaselineRecordTyepId = Schema.SObjectType.Form__c.RecordTypeInfosByName.get('Baseline Report').RecordTypeId;
Vivek DeshmaneVivek Deshmane
Hi Jonathan,

If this helps you please mark as best answer.

Best Regards,
-Vivek Deshmane
James WooleyJames Wooley
The problem with using the RecordTypeInfosByName is that it references record types by their name (label) not their developer name as you were trying to do in your original SOQL. This is more likely to change that the developer name.

You could use your existing SOQL, but make sure the text you put in is actually the developer name, not the name. I imagine it is exactly what Vivek originally suggested, but with underscores rather than spaces: 
List<AggregateResult> results = [SELECT Account__c, 
                                         SUM(X2_1_a_Total_of_jobs_Year_0__c) mysum0,
                                         FROM Form__c 
                                         WHERE Account__c IN:accountIds
                                         AND (RecordType.DeveloperName ='Baseline_Report'
                                         OR  RecordType.DeveloperName ='Annual_Report')
                                         GROUP BY Account__c];
I would definitely use the RecordTypeInfosByName method when you are trying to create new records with a specific record type, or when checking a record's record type in trigger context, but for SOQL I think what you were trying to do is best.
This was selected as the best answer
Jonathan Osgood 3Jonathan Osgood 3
Thanks James,

This is very helpful. I went back to the oringal soql approach, but as mentioned, put in the record type api name this time! Can't beleive I missed that! Good to know I'm not crazy as I've used this approach before! On the upside, I now have a better idea of when to use apex and when to use soql. Use case matters. Here's a great Stack Exchange post I found for both plus a third, industrial-strength Force.com application using a utility class. (http://salesforce.stackexchange.com/questions/11968/what-would-be-the-best-approach-to-get-the-recordtype-id)

 
LakisLakis
@JonathanOsgood3 - hey, I wonder if you can help... in my trigger works for specific record type. I'd like to update it to work on more than one record type. How to update the below to include other record types such as "Affiliation" or "otherrecordtype"?

trigger Contact_Importance_Update on Account (before update) {

    Id recTypeId = Account.sObjectType.getDescribe().getRecordTypeInfosByName().get('Exhibitor').getRecordTypeId();
    
    List<Account> accountsToVerify = new List<Account>();

Thanks,
Lakis