+ Start a Discussion
Akash Garg 2Akash Garg 2 

validation rule on comparing 2 different object picklist value

How can i write the validation rule in which on changing value in Opportunity Picklist value it compares the value with 2 picklist fields of custom object .
If value in Opportunity picklist is matches with the any of the 1 field of custom object then it saves record otherwise it throws the error.

I have write the following validation rule but it didn't work.

Kindly help in correcting the validation rule.
TEXT(INCO_Term__c) <> "" && 
OR( 
TEXT(INCO_Term__c) <> TEXT(Account_Sales_Area_TI__r.Inco__c) , 
TEXT(INCO_Term__c) <> TEXT(Account_Sales_Area_TI__r.Incoterms__c) 
)

 
TruptiTrupti
Could you please below:

TEXT(INCO_Term__c) <> "" &&
( AND( TEXT(INCO_Term__c) <> TEXT(Account_Sales_Area_TI__r.Inco__c) ,
TEXT(INCO_Term__c) <> TEXT(Account_Sales_Area_TI__r.Incoterms__c) )
)


***Since you want either of the values should be matched. So we need to use AND here.

Thanks
Akash Garg 2Akash Garg 2
Hi Trupti,

Its not working.
After saving anyother value, it still take the value which is coming by a Trigger at first time.

I think ISCHANGED function to be used in this. 
As this functionality is used by one of another field on opportunity whose Validation rule is as below and it also take the value from same trigger.
Payment term functionality is working perfectly.
IF(ISCHANGED(Payment_Term__c) && Payment_Term__c <> Account_Sales_Area_TI__r.Payment_Term_Optional__c && Payment_Term__c <> Account_Sales_Area_TI__r.Payment_Terms__c, true, false)

 
Akash Garg 2Akash Garg 2
This the trigger that fetch Inco term and Payment term from Account Sales Area object by Matching Account and Sales Area on oppty with Account Sales Area object. May be this will help you solving the issue.

 
private void populateInfoFromSalesArea(){

        //get list of Oppty where we have Sales Area

        //create Set of account Id
        //get all account sales area



        List<Opportunity> listOpportunityWithSalesArea = new List<Opportunity>()  ;
        Set<Id> setAccountId = new Set<Id>();
        Set<Id> setSalesArea = new Set<Id>();
        Set<String> setSalesOrg = new Set<String>();
        Map<Id, Sales_Area__c> mapSalesArea = new Map<Id, Sales_Area__c>();


         for(Opportunity opportunity : (List<Opportunity>)Trigger.New){
        
            if( opportunity.Sales_Area__c!=null && opportunity.AccountId!=null){
                listOpportunityWithSalesArea.add(opportunity);
                setAccountId.add(opportunity.AccountId);
                setSalesArea.add(Opportunity.Sales_Area__c);
            }
         }

         //query the sales area information
         mapSalesArea = new Map<Id, Sales_Area__c>([SELECT Id, VKORG__c, VTWEG__c, SPART__c, Division__c, Distribution_Channel__c, Sales_Org__c, Unique_Key__c
                                    FROM Sales_Area__c
                                    WHERE ID IN :setSalesArea 
                                        AND SPART__c!=NULL AND VTWEG__c!=NULL AND VKORG__c!=NULL
                                    ]);
         for(Sales_Area__c salesArea : mapSalesArea.values()){
            setSalesOrg.add(salesArea.VKORG__c);
         }


         if(!setSalesArea.isEmpty() && !setAccountId.isEmpty()){
            
            //List<Account> listAccount = []
            List<Sales_Area_TI__c> listAccountSalesArea = [SELECT Id, CurrencyISOCode, Account_Name__c, Payment_Terms__c, Price_List__c, Incoterms__c, SAP_Sales_Area_Number__c, Incoterms_2__c,
                                                                          SAP_Sales_Area_Number__r.SPART__c, SAP_Sales_Area_Number__r.VTWEG__c, SAP_Sales_Area_Number__r.VKORG__c, SAP_Sales_Area_Number__r.Unique_Key__c      
                                                                FROM Sales_Area_TI__c
                                                                WHERE Account_Name__c IN :setAccountId AND ( SAP_Sales_Area_Number__c IN :setSalesArea OR SAP_Sales_Area_Number__r.VKORG__c IN :setSalesOrg)
                                                                AND Payment_Terms__c!=NULL AND Account_Name__c!=null AND Incoterms__c!=null AND SAP_Sales_Area_Number__c!=NUll
                                                                AND SAP_Sales_Area_Number__r.SPART__c!=NULL AND SAP_Sales_Area_Number__r.VTWEG__c!=NULL AND SAP_Sales_Area_Number__r.VKORG__c!=NULL 
                                                            ];

            Map<Id, List<Sales_Area_TI__c> > mapAccountToAccountSalesArea = new Map<Id, List<Sales_Area_TI__c> > ();
            
            for(Sales_Area_TI__c accountSalesArea : listAccountSalesArea){
                List<Sales_Area_TI__c> listAccSalesArea = new List<Sales_Area_TI__c>();
                if(mapAccountToAccountSalesArea.containsKey(accountSalesArea.Account_Name__c)) listAccSalesArea = mapAccountToAccountSalesArea.get(accountSalesArea.Account_Name__c);
                listAccSalesArea.add(accountSalesArea);
                mapAccountToAccountSalesArea.put(accountSalesArea.Account_Name__c, listAccSalesArea);
            }                                              

            //System.debug('map'+mapAccountToAccountSalesArea);
            /*
                            While selecting Sales Area on Opportunity, match "Sales Area" present on Opportunity with the Account Sales Area" (present under Opportunity's Account). If no match is found, then look for a Account Sales Area with 
                VTWEG - Distribution Channel 10 which can match with Opportunity's Distribution Channel 1* (11, 12, 13, 14, 15, 16, 17, 18, 19) and match Sales Org.
                "SPART" - Division - 01 means do not compare Division

                VTWEG - Distribution Channel 20 which can match with Opportunity's Distribution Channel 1* (21, 22, 23, 24, 25, 26, 27, 28, 29) and match Sales Org.
                "SPART" - Division - 01 means do not compare Division

                If no match is found, then that Sales Area cannot be selected on that opportunity.
                */

            Boolean salesAreaMatched = false;
            for(Opportunity opportunity : listOpportunityWithSalesArea){
                salesAreaMatched = false;
                if(mapAccountToAccountSalesArea.containsKey(opportunity.AccountId)){
                    for(Sales_Area_TI__c accountSalesArea : mapAccountToAccountSalesArea.get(opportunity.AccountId)){
                        if(opportunity.AccountId==accountSalesArea.Account_Name__c){
                            if( opportunity.Sales_Area__c!=null && accountSalesArea.SAP_Sales_Area_Number__c!=null && opportunity.Sales_Area__c==accountSalesArea.SAP_Sales_Area_Number__c ){
                               salesAreaMatched = true; 
                            }
                            else if( (mapSalesArea.get(opportunity.Sales_Area__c)).VKORG__c!=null &&  accountSalesArea.SAP_Sales_Area_Number__r.VKORG__c!=null && (mapSalesArea.get(opportunity.Sales_Area__c)).VKORG__c == accountSalesArea.SAP_Sales_Area_Number__r.VKORG__c){
                                //if(Distri Channel left1 matches && division matches)
                                String opptySalesAreaDistriChannel = (mapSalesArea.get(opportunity.Sales_Area__c)).VTWEG__c;
                                String accountSalesAreaDistriChannel = accountSalesArea.SAP_Sales_Area_Number__r.VTWEG__c;
                                System.debug('opptySalesAreaDistriChannel '+opptySalesAreaDistriChannel+'   accountSalesAreaDistriChannel '+accountSalesAreaDistriChannel);
                                if( opptySalesAreaDistriChannel!=null &&  accountSalesAreaDistriChannel!=null && ((accountSalesAreaDistriChannel.left(2)).right(1))=='0' &&  opptySalesAreaDistriChannel.left(1)==accountSalesAreaDistriChannel.left(1)){
                                    //check division matches
                                    if( (mapSalesArea.get(opportunity.Sales_Area__c)).SPART__c == accountSalesArea.SAP_Sales_Area_Number__r.SPART__c ){
                                        salesAreaMatched = true;
                                    }
                                    else if( accountSalesArea.SAP_Sales_Area_Number__r.SPART__c=='01'){
                                        salesAreaMatched = true;
                                    }
                                }
                            }
                            

                            
                            if(salesAreaMatched)
							{
                                if(Trigger.isInsert)
								{ 
								opportunity.CurrencyISOCode = accountSalesArea.CurrencyISOCode; 
								}
                                opportunity.Account_Sales_Area_TI__c = accountSalesArea.Id;
                                opportunity.INCO_Term__c = accountSalesArea.Incoterms__c;
                                if(!String.isBlank(accountSalesArea.Price_List__c))
                                opportunity.Price_List__c = accountSalesArea.Price_List__c;
                                
                                if(Trigger.isInsert && Trigger.isBefore && opportunity.Payment_Term__c == null)
                                {
                                    opportunity.Payment_Term__c = accountSalesArea.Payment_Terms__c;
                                }
                                else if(Trigger.isUpdate && Trigger.isBefore)
                                {
                                    Opportunity tempOpty = (Opportunity)Trigger.oldMap.get(opportunity.Id);
                                    if(tempOpty.Sales_Area__c != null && tempOpty.Sales_Area__c != opportunity.Sales_Area__c && tempOpty.Payment_Term__c == opportunity.Payment_Term__c)
                                    {
                                        opportunity.Payment_Term__c = accountSalesArea.Payment_Terms__c;
                                    }
                                }
                                //opportunity.Payment_Term__c = accountSalesArea.Payment_Terms__c;
                                opportunity.INCO_Term_Location__c = accountSalesArea.Incoterms_2__c;
                                break;
                            }
                        } // account ID match check
                            
                    }//for loop ends
                }


                if(!salesAreaMatched){ opportunity.addError('No such sales area on account');  }
            }//oppty for loop ends

        }//if set not empty


         

    }// populateInfoFromSalesArea ends
TruptiTrupti
So it solved your problem.. Great! 
Akash Garg 2Akash Garg 2
Hi Trupti,

My problem is not solved. I just want to know what these lines code do exactly because of this it always get the value maintained in Account sales area against Account and Sales area on opportunity.
if(salesAreaMatched)
							{
                                if(Trigger.isInsert)
								{ 
								opportunity.CurrencyISOCode = accountSalesArea.CurrencyISOCode; 
								}
                                opportunity.Account_Sales_Area_TI__c = accountSalesArea.Id;
                                opportunity.INCO_Term__c = accountSalesArea.Incoterms__c;
                                if(!String.isBlank(accountSalesArea.Price_List__c))
                                opportunity.Price_List__c = accountSalesArea.Price_List__c;
                                
                                if(Trigger.isInsert && Trigger.isBefore && opportunity.Payment_Term__c == null)
                                {
                                    opportunity.Payment_Term__c = accountSalesArea.Payment_Terms__c;
                                }
                                else if(Trigger.isUpdate && Trigger.isBefore)
                                {
                                    Opportunity tempOpty = (Opportunity)Trigger.oldMap.get(opportunity.Id);
                                    if(tempOpty.Sales_Area__c != null && tempOpty.Sales_Area__c != opportunity.Sales_Area__c && tempOpty.Payment_Term__c == opportunity.Payment_Term__c)
                                    {
                                        opportunity.Payment_Term__c = accountSalesArea.Payment_Terms__c;
                                    }
                                }
                                //opportunity.Payment_Term__c = accountSalesArea.Payment_Terms__c;
                                opportunity.INCO_Term_Location__c = accountSalesArea.Incoterms_2__c;
                                break;
                            }