3

I have two different custom objects Catalogs and Products. I have a visualforce page where user can select multiple catalogs and multiple products from two different picklists respectively. I also have a junction object called Catalog-Products, a child to both catalogs and products. The selected catalogs and products are created as records for Catalog-Products Junction object. So, I have two different lists of each of those object called as CatalogList and ProductList which are captured from that mulit-select picklists (that I explained above). There might be already some Catalog-Product records with the selected product and selected Catalogs.

How can I compare both those lists with existing Catalog-Product junction object records and insert only new records?

Thanks.

Update- I am stuck with the code here

list<Catalog_Product__c> existingList = [select Id, Product__c, Catalog__c from Catalog_Product__c where Product__c IN: prodIds and Catalog__c IN: catalogIds];

//prodIds and catalogIds are Ids retrieved from selected values of Picklists 

list<Catalog_Product__c> newList = new list<Catalog_Product__c>();

 for(Id clogId: catalogIds)
 {
   for(Id prdId: prodIds)
   {
     Catalog_Product__c cpd = new Catalog_Product__c();
     cpd.Product__c = prdId;
     cpd.Catalog__c = clogId;
     newList.add(cpd);
   }
}

newList and existingList are the two lists of same Object . Need help in comparing those lists and inserting the non-duplicate values.

  • why not use upsert? - new records will be inserted, existing records just updated in place – cropredy May 5 '15 at 23:41
  • Are you using picklist field types for the Catalog and Product lists or are you querying these two objects and building the <apex:selectList /> options in your page controller? – Mark Pond May 6 '15 at 0:55
  • @MarkPond Second one i.e.., I used the <apex:selectList /> to display the data. – sam_s May 6 '15 at 0:59
3

This is a good use case for leveraging an External ID field on the Catalog_Product__c object. The value that is contained in this field will be comprised of two keys concatenated together to create a unique value that represents the junction. In traditional database terminology this is known as a "compound key" and allows you to use a few bits of information to create a unique identifier that might not exist naturally.

You can use the external id field to also enforce a uniqueness constraint (preventing duplicates) as well as greatly simplifying your code as there's no need to search for any records are already in existence. If there are records in the junction object already, they will simply be updated.

Assuming you've created an External ID field on the Catalog_Product__c object with the Unique attribute set true, which is named CatProd_ExtId__c and will have values in it that take the form "CatalogId + ProductId" (2 SFDC Id values of the parents, 30 characters in total), your save method can look like this:

public void save() {

    // the list of junction object records being inserted or updated later (upsert)
    List<Catalog_Product__c> catalogProductsToUpsert = new List<Catalog_Product__c>();

    // assuming catalogIDs are the items selected by the user
    for (Id catalogId: catalogIds) {
       for (Id productId: prodIds) {
         Catalog_Product__c cpd = new Catalog_Product__c();
         cpd.Product__c = productId;
         cpd.Catalog__c = catalogId;

         // concatenate the two ID values together to create the compound key
         cpd.CatProd_ExtId__c = productId + catalogId;

         catalogProductsToUpsert.add(cpd);
       }
    }

    // only perform the DML operation if there are records to upsert
    if (!catalogProductsToUpsert.isEmpty()) {

        // execute the upsert, specifying our CatProd_ExtId__c field as the identifier to use
        upsert catalogProductsToUpsert Catalog_Product__c.Fields.CatProd_ExtId__c;
    }
}
  • Thanks. I really liked the idea of External-field. I have just figured another solution playing with the loops but this one seems to be more efficient than mine to preserve the uniqueness of junction object. – sam_s May 6 '15 at 1:44
  • @Mark Pond - Thank you so much for the excellent solution – David Jul 29 '16 at 13:47
1

If you would rather not use an upsert, you can leverage the same compound key idea with code like this:

List<Catalog_Product__c> existingList = [select Id
                                         , Product__c
                                         , Catalog__c 
                                         from Catalog_Product__c 
                                         where Product__c IN: prodIds 
                                             AND Catalog__c IN: catalogIds];

// using a set to store the unique list of records that are already in existence
Set<String> existingJunctionSet = new Set<String>();

for (Catalog_Product__c catProd : existingList) {
    existingJunctionSet.add(catProd.Catalog__c + catProd.Product__c);
}

//prodIds and catalogIds are Ids retrieved from selected values of Picklists 

list<Catalog_Product__c> newList = new list<Catalog_Product__c>();

// iterate the lists and if the pair isn't in the set above
// add it to the list for insert
for(Id clogId: catalogIds) {
    for(Id prdId: prodIds) {

        // only add to the list if it's not in the existing set already
        if (!existingJunctionSet.contains(clogId + prdId)) {
            Catalog_Product__c cpd = new Catalog_Product__c();
            cpd.Product__c = prdId;
            cpd.Catalog__c = clogId;
            newList.add(cpd);

            // to prevent duplicates as we iterate, add this new entry to the set too
            existingJunctionSet.add(clogId + prdId);
        }
    }
}
0

If these products and catalogs are a small set of data, consider showing a custom multiselect picklist with combination of Catalog-Product available and already existing in left and right panels. You can find existing component in below link,

https://github.com/metadaddy-sfdc/Visualforce-Multiselect-Picklist

  • Sorry If I'm not being clear with the question. My problem is to how to compare these picklist values and run a duplicate check with the existing Catalog-Product junction object records but not for displaying data. – sam_s May 5 '15 at 20:08
  • oh sorry about that. For that if data volume is large, you can write an apex batch class. If data volume is less simply execute some anonymous script from developer console to deduplicate – Victor May 5 '15 at 20:10
  • Data volume is quite less, but any help from the code perspective (how to compare) is highly appreciated. – sam_s May 5 '15 at 20:14
  • That would be around 50 ~ 80 lines of code. You need to query both catalogList and productList. Also existing product-catalog records. Then iterate using a for loop of one over another for loop of other. You might need a map also to find how many duplicates are there. – Victor May 5 '15 at 20:24
  • I updated the code; Could you take a look at it please? – sam_s May 5 '15 at 23:20
0

If there is only insertion of records required rather than updating any existing records this logic also works fine just for removing the duplicate records by comparing two lists of Catalog_Product__c Object (Existing and NewList).

list<Catalog_Product__c> existingList = [select Id, 
                                         Product__c, 
                                         Catalog__c from Catalog_Product__c where 
                                         Product__c IN: prodIds 
                                         and Catalog__c IN: catalogIds];

//prodIds and catalogIds are Ids retrieved from selected values of Picklists 

list<Catalog_Product__c> newList = new list<Catalog_Product__c>();

 for(Id clogId: catalogIds)
 {
   for(Id prdId: prodIds)
   {
     Catalog_Product__c cpd = new Catalog_Product__c();
     cpd.Product__c = prdId;
     cpd.Catalog__c = clogId;
     newList.add(cpd);
   }
}
if(existingList!=null && !existingList.isEmpty())
{
   for(Catalog_Product__c cpd: existingList)
   {
      Integer i=0;
      while(i<newList.size())
      {
         if(newList[i].Product__c == cpd.Product__c 
            && newList[i].Catalog__c ==cpd.Catalog__c)
         {
            newList.remove(i);
            break;
         }
         else
         {
            i++;
         }  
      } 
    }
 }

if(!newList.isEmpty()){
insert newList;
}  

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.