Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Merry SMerry S 

Compare list (from a split multi-select picklist) to records in database

I have a trigger that looks at a multi-select picklist on a custom object, and then splits the vaules and creates records on another object. The issue that I have is that it will create multiple new records since there is no logic to see if a record already exists.

On the Account_RoadMap__c object I have a field named Product_Planned_to_Purchase__c, it is multi-select. When this field is updated I want it to create a record for each product created on the object Account_RoadMap_Product__c related to both the Account Roadmap and the Account. However, if there is already a record on the Account Roadmap Product that matches what is in the multi-select picklist, I dont want it to create another record.

I have tried comparing lists - but I am just not sure how to get there. Any help would be much appreciated. 
  
trigger AutoCreateRoadMapProduct on Account_RoadMap__c (after update) {
    List<Account_RoadMap_Product__c> products = new List<Account_RoadMap_Product__c>();

    //For each roadmap processed by the trigger, add a new  

    //product record for the specified product.  

    for (Account_RoadMap__c newRM: Trigger.New) {
        if (newRM.Product_Planned_to_Purchase__c != null) {
            // split out the multi-select picklist using the semicolon delimiter
            for(String product: newRM.Product_Planned_to_Purchase__c.split(';')){
                products.add(new Account_RoadMap_Product__c(
                        Product_Name__c = product,
                        RoadMap__c = newRM.Id,
                        Account_Name__c = newRM.Account_Name__c));
            }
        }
    }
    insert products;
}

 
David ZhuDavid Zhu
I would add a fomular field called RoadAndAccount __c on Product_Planned_to_Purchase__c to concatenate RoadMap__C and Account_Name__C.

Then before adding products, delete all existing matching products.


trigger AutoCreateRoadMapProduct on Account_RoadMap__c (after update) {

    List<Account_RoadMap_Product__c> products = new List<Account_RoadMap_Product__c>();
    List<string> R_A = list<string>();

    //For each roadmap processed by the trigger, add a new 
    //product record for the specified product. 
    for (Account_RoadMap__c newRM: Trigger.New) {
        if (newRM.Product_Planned_to_Purchase__c != null) {
            // split out the multi-select picklist using the semicolon delimiter
           for(String product: newRM.Product_Planned_to_Purchase__c.split(';')){
                products.add(new Account_RoadMap_Product__c(
                        Product_Name__c = product,
                        RoadMap__c = newRM.Id,
                        Account_Name__c = newRM.Account_Name__c));
                        R_A.add(newRM.Id + newRM.Account_Name__c);
            }
        }
    }
    delete [select id from Account_RoadMap_Product__c where RoadAndAccount in :R_A];
    insert products;

}
Merry SMerry S

Unfortunately I cannot do it that way - there is other information on the Account_RoadMap_Product__c that will be updated after it is created - so deleting them each time would not work.

 I have been playing with part of that you gave me - I added the new list - and then had the newRM.Id + newRM.Account_Name__c as you did in your example. I also created a new field on the Account_RoadMap_Product__c which holds the concatenated RoadMap__C and Account_Name__C, as you suggested. However, I was thinking there must be a way to compare the two lists and only insert the difference. But - still no joy. See updated code below:

I did have to update the code a little so that it would check to see if the Product_Planned_to_Purchase__c field had changed, not just checking to see if it is not null as it was creating new records at every save (should have seen that coming!).
  
trigger AutoCreateRoadMapProduct on Account_RoadMap__c (after update) {
    List<Account_RoadMap_Product__c> products = new List<Account_RoadMap_Product__c>();
    //For each roadmap processed by the trigger, add a new  
    list<string> R_A = new list<string>();
    
    
    //product record for the specified product.  

    for (Account_RoadMap__c newRM: Trigger.New) {
          Account_RoadMap__c oldRM = Trigger.oldMap.get(newRM.Id);
			String O = OldRM.Product_Planned_to_Purchase__c;
			String N = newRM.Product_Planned_to_Purchase__c;
			if(O != N){
            // split out the multi-select picklist using the semicolon delimiter
            for(String product: newRM.Product_Planned_to_Purchase__c.split(';')){
                products.add(new Account_RoadMap_Product__c(
                        Product_Name__c = product,
                        RoadMap__c = newRM.Id,
                        Account_Name__c = newRM.Account_Name__c));
						R_A.add(newRM.Id+' '+newRM.Account_Name__c);
						
            }
        }
    }
 
    insert products;
}