0

The following gives me a list of texts which are customer numbers (CUST_NUM__c)

 List<SYSTEMS__c> lst_PartnersAcc = [SELECT CUST_NUM__c
                                             FROM SYSTEMS__c WHERE Account__c IN (Select  AccountId from AccountShare where ( UserOrGroupId IN : map_group OR  UserOrGroupId =:userId )AND RowCause IN ('Territory', 'TerritoryManual', 'TerritoryRule'))
                                             ];

I have 4 text fields in another custom object orders__c==> customer_1__c,customer_2__c,customer_3__c, customer_4__c. I'm trying to find out if **lst_PartnersAcc ** contains 1 or more of the 4 text field. My Final Aim is to find a list of orders if any matches from the string in the list.

Following is what I tried...

Attempt 1:

Set<String> tempList = new Set<String>();
tempList.addAll(lst_PartnersAcc);

Error: Compile Error: Incompatible argument type List for All method on Set

Attempt 2:

List<orders__c> orders = [select id,name from orders__c WHERE customer_1__c IN( SELECT CUST_NUM__c
                                             FROM SYSTEMS__c WHERE Account__c IN             
                                             (Select  AccountId from AccountShare where ( UserOrGroupId IN : map_group OR  UserOrGroupId =:userId )AND RowCause IN ('Territory', 'TerritoryManual', 'TerritoryRule')))
                                             ];

Error: Compile Error: Nesting of semi join sub-selects is not supported

Attempt 3

List<SYSTEMS__c> lst_PartnersAcc = [SELECT CUST_NUM__c
                                             FROM SYSTEMS__c WHERE Account__c IN  (Select  AccountId from AccountShare where ( UserOrGroupId IN : map_group OR  UserOrGroupId =:userId )AND RowCause IN ('Territory', 'TerritoryManual', 'TerritoryRule'))];

List<gew_orders__c> orders = [select id,name from gew_orders__c WHERE  GEW_SAP_Bill_to__c IN: lst_PartnersAcc OR
                                    GEW_SAP_Payer__c IN: lst_PartnersAcc OR 
                                    GEW_SAP_Ship_To IN: lst_PartnersAcc OR
                                    GEW_SAP_Sold_to__c IN: lst_PartnersAcc];

Error: Compile Error: Invalid bind expression type of SYSTEMS__c for column of type String

How should I proceed to compare 4 values to a list? Thanks in advance!

  • In your attemp 1, your are trying to convert an sObject into a String. That's doesn't work. If you want to add your field into the Set you need to do convert one by one your field into a string with : String.valueOf(theRecord.TheField) – Yann Gorvel Jan 16 '15 at 14:00
1

This should work:

List lst_PartnersAcc = [SELECT CUST_NUM__c
                                             FROM SYSTEMS__c WHERE Account__c IN (Select  AccountId from AccountShare where ( UserOrGroupId IN : map_group OR  UserOrGroupId =:userId )AND RowCause IN ('Territory', 'TerritoryManual', 'TerritoryRule'))
                                             ];
Set tempList = new Set();
for(Systems__c s : lst_PartnersAcc) {
    tempList.add(s.Cust_Num__c);
}
List orders = [select id,name from orders__c WHERE customer_1__c IN :tempList OR customer_2__c IN :tempList ETC...
                                             ];

Just needed to add the Customer Number to the set individually for each Systems__c.

| improve this answer | |
  • Just a quick clarification. what would be the type of Set? shShould it be Set <id>tempList = new Set<id>(); ? – MnZ Jan 16 '15 at 17:00
  • 1
    If Cust_Num__c is a reference (lookup or master/detail) then the Set should be Set<Id>. If it is just a text field, it should be Set<String> – Bradley Delaune Jan 16 '15 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.