In this Lookup filter "primary" and "Leadoffices" are picklist fields.Here profit center description is lookup field to profit center. I would like to construct query.

enter image description here

I need to Identify those records matching these filter criterias using soql query. How to do it?

Updated Query:

List<Opportunity> opList=[select id,Primary__c,Lead_Offices__c,Profit_Center__r.Lead_Office__c,Profit_Center__r.Primary__c from Opportunity where(Primary__c!=null and Primary__c==Profit_Center__r.Primary__c and  Lead_Offices__c==Profit_Center__r.Lead_Office__c )or  
(Primary__c==Profit_Center__r.Primary__c  and Primary__c!=null and 
   Lead_Offices__c==null) ];
  • 1
    Have you tried creating a SOQL query by yourself yet? Please share what your have done so far.
    – Eduard
    Aug 29, 2018 at 6:55
  • I got confuse in constructing the query Aug 29, 2018 at 6:56
  • Primary and leadoffice are picklist field?? You said values. Can you clear the confusion? Aug 29, 2018 at 7:02
  • yes.picklist fields :) Updated it Aug 29, 2018 at 7:02

1 Answer 1


This is not possible in single query. We need two SOQl and few collection elements here.

First make query on Profit center Description and store the Lead Office and Primary in two sets.

Set<String> LeadOffice, Set<String> Primary

But I suggest you to create two new formula field of type checkbox and match that condition there aas it would be easy and will work in long run as well.

After you make two formula fields its just a matter of simple SOQL. Which will look like

WHERE (Lead_Office_check = true and Primary_Check IN: Primary and Primary != null) or (Primary_Check IN: Primary and Primary != null and Lead_Office = null)

as you don't want to use any new field then your SOQL will look like

WHERE (Lead_Office IN: LeadOffice and Primary_Check = true and Primary != null) or (Primary_Check = true and Primary != null and Lead_Office = null)

and then iterate this list to make sure you get all right combinations

for(Opportunity opp: oppList) {
    if(opp.Lead_Office == opp.Profit_Center_Description__r.Leadoffices && opp.primary == opp.Profit_Center_Description__r.primary) {
   //Now here you will get final result same as your above filter
  • I dont want to create any fields its one time check . if you update your answer in collection its really usefull Aug 29, 2018 at 7:14
  • I dont want to create any fields its one time check . if you update your answer in collection its really usefull .sorry its lookup to profitcenter Aug 29, 2018 at 7:22
  • @AnnappaPH check my updated answer. Aug 29, 2018 at 7:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .