2

Could somebody please help me on how to write a proper Bulkified trigger for the below scenario.

Please note that I am not new to the trigger Bulkification but I am just struggling to write an efficient trigger for the below particular scenario.

Description:

  1. I have a custom object called Filter__c with ten fields named Field1__c,Field2__c,Field3__c..Field10__c.
  2. For each of these fields there is a corresponding field on the Account object.
  3. Based on what values user selected for the fields on Filter__c record, I need to query matching accounts.

Problem:

Now lets say there are three records of object Filter__c are coming in the after insert trigger and each record has different values for different fields.

Questions:

  1. How do I get the list of accounts matching only two fields(Field1__c,Field2__c)
  2. How do I get the list of accounts matching all the ten fields(Field1__c..Field10__c) in the same transaction as above one.
  3. How do I get the list of accounts matching all the eight fields(Field1__c..Field8__c) in the same transaction.

Here is the sample code that I came with so please don't nitpick for any syntax or compilation errors:

trigger FilterAccounts on Filter__c (after insert){
    if(Trigger.isInsert && Trigger.isAfter){

        Map<Id,String> filterIdToFieldOneMap = new Map<Id,String>();
        Map<Id,String> filterIdToFieldTwoMap = new Map<Id,String>();
        --
        --
        Map<Id,String> filterIdToFieldTenMap = new Map<Id,String>();

        for(Filter__c filterObj: Trigger.new){
            filterIdToFieldOneMap.put(filterObj.Id,filterObj.Field1__c);
            filterIdToFieldTwoMap.put(filterObj.Id,filterObj.Field2__c);
            --
            --
            filterIdToFieldTenMap.put(filterObj.Id,filterObj.Field10__c);
        }

        Set<String> fieldOneStringSet = new Set<String>();
        Set<String> fieldTwoStringSet = new Set<String>();
        --
        --
        Set<String> fieldTenStringSet = new Set<String>();

        for(String str: filterIdToFieldOneMap.values()){
            fieldOneStringSet.add(str);
        }

        for(String str: filterIdToFieldTwoMap.values()){
            fieldTwoStringSet.add(str);
        }
        --
        --
        for(String str: filterIdToFieldTenMap.values()){
            fieldTenStringSet.add(str);
        }

        List<Account> accountsMatchingFieldOneList = 
        [SELECT Id,Field1__c,Field2__c..Field10__c FROM Account WHERE Field1__c IN:fieldOneStringSet];
        List<Account> accountsMatchingFieldTwoList = 
        [SELECT Id,Field1__c,Field2__c..Field10__c FROM Account WHERE Field2__c IN:fieldTwoStringSet];
        --
        --
        List<Account> accountsMatchingFieldTenList = 
        [SELECT Id,Field1__c,Field2__c..Field10__c FROM Account WHERE Field10__c IN:fieldTenStringSet];

        //Now how do I get the list of accounts matching the below permutations because
        // If one of the record in the transaction has values filled for only two fields (Field1__c, Field2__c) 
        then how do I get Accounts matching these conditions.
        //If one of the record in the transaction has values filled for five fields (Field1__c,Field2__c,Field3__c,Field4__c,Field5__c) 
        then how do I get Accounts matching these five fields.
        ..Now I can have so many combinations of records coming in then how do I find Accounts matching for each of those records.
    }
}
  • What is the type and size for field1 - 8? Is the match on field1-8 an exact match or can it match on only field1-3. Does the match have to be the same fields on both objects (i.e. field 1 has to match field 1 and not field one can match any of the corresponding fields) – Eric Jun 5 '16 at 23:03
  • The type of the fields are picklists. Match needs to be on same fields on both the objects, meaning field1 on filter__c object should match with field1__c on account object. – javanoob Jun 5 '16 at 23:10
  • You could potentially use a WFR to populate another field with a concatenation of all 8 fields (including those that are null) using ';' as a separator. Then you could use that as a key of sorts in your code. – Eric Jun 5 '16 at 23:12
  • @Eric , I am still thinking if this approach would work and I will post in a while if I have any questions. I need to check if this workaround works in all the scenarios. Thanks for your time. – javanoob Jun 5 '16 at 23:29
3

I had to do something similar to this once. It wasn't a lot of fun as I recall and the solution involved creating lists of lists. Your situation is actually more complex than I think you may realize.

If I understand your problem correctly and you have 10 fields that an account can match. You have more than a list of 10 to contend with, you have SUM(n) where n=(1-10) or 55 as in 10+9+8+7+6+5+4+3+2+1 = 55. There could be 10 possible single matches, 9 possible combo matches of 2, 3 possible combo matches of 3, ... and only 1 possible combo match of 10. That's how you reach the number 55.

This assumes that one could have Field1__c and Field3__c while for example, Field2__c is left empty. If that's not the case, then the solution is simpler, but still works along the same lines. The number of combinations is dramatically reduced to a total of 10.

Using your code, I believe you'd need to begin with something like this:

    integer count = 0
    map<Id,list<filterObj>>filtObjMap = new map<Id,list<filterObj>>();
    list<filterObj>fltrlst = new list<filterObj>();
    for(Filter__c filterObj: Trigger.new){
        for(integer n=1;n<11;n++){
            if(!isEmpty('filterObj.Field'+n+'__c')){
                fltrlst.add('filterObj.Field'+n+'__c');
               count++;
            }else{
               // optionally use the value of n to keep track of which fields weren't 
               // added to the list. Put them into a comma separated string & store 
               // in a map at end of each loop for later use.
            }                 

        }
        // dynamically name and create a new list from fltrlst
        list<filterObj>'fltrlist'+_count_+Now().millisecond() = new list<filterObj>(fltrlst);
        // use of millisecond() helps us create unique name for list. 
        // If necessary add minutes too.
        count = 0;
        // put list into a map can retrieve it with the size using `_count_`
        filtObjMap.put(filterObj.Id,'fltrlist'+_count_+Now().millisecond());
        fltrlst.clear();
     }

If fields are added only in order, there's only 10 possibilities and no sorting is needed. If so, you can then use the queries as you have them. Just add values in maps to a single list or set. If not, then you will need to track which fields are in each list using the "else" option and only join those values that include the same fields. Then, run your queries separately using the values from the maps. You'll need to keep track of the Ids that go with those values and query results returned from them.

"then how do I get Accounts matching these five fields. ..Now I can have so many combinations of records coming in then how do I find Accounts matching for each of those records."

You'll want to do something like the following:

For(Account a: accountsMatchingFieldTenList){
    For(Id obId: filtObjMap.keyset()){
        If(filtObjMap.get(obId).contains.a.Field1__c){
           put(aId,obId)mapidsFld1;
        }
        If(filtObjMap.get(obId).contains.a.Field2__c){
           put(aId,obId)mapidsFld2;            
        }  
          // .....

        If(filtObjMap.get(obId).contains.a.Field10__c){
           put(aId,obId)mapidsFld10;            
        }
    }
}  

// now you have 10 maps of account Ids to object Ids related to Field#__c

You can do counts on these for each AccountId, each Field# and each objId to match them up or sort them however you want.

This is the general approach without working out all the details for you.

  • Thanks for understanding the trouble I am going through :) My concern is what if we add one more field to the Filter object and account object, Then we need to rewrite almost everything. I am looking of a framework or something like that so it is easy in the future and easy to maintain. I will try your idea and see how it goes. Again thanks for your time. – javanoob Jun 6 '16 at 18:12
  • This could probably be broken out into methods without much difficulty to create reusuable code for creating the lists and maps. You'd just need to use schema calls to get the field names dynamically if you did. That would allow this to scale. – crmprogdev Jun 6 '16 at 19:08

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.