I have a complicated trigger that I am trying to bulkify but I don't know how to go about it. We have two main objects, Prescriptions and Study Participants. When a new Prescription is added through Informatica we have only a few pieces of information to find the right Study Participant to attach it to. We have a Participant Code (created from the first digit of first name and last digit of last name plus the day, month and year of birth) and we have a Study. Each participant can be in multiple studies so the trigger has to find the right study to attach to.

We also have an issue with the Study being wrong on some of the Prescriptions so we have to look at the fill date compared to the date enrolled for those studies.

Here is my code. It works but it hits governor limits. Any suggestions appreciated.

    trigger Attach_Prescriptions on Prescription__c (before insert, before update) 

    for(Prescription__c a :Trigger.New) {

    List <Study_Participant__c> TREAT2 = [select id,Name,Participant__c,Study__c, Provider__c,Study_Provider__c, Date_Enrolled__c from Study_Participant__c where Name =:a.Participant_code__c and Study__c =: 'a00A000000dMIvSIAW' and  Date_Enrolled__c <:a.Fill_Date__c  ];             
      List <Study_Participant__c> TREAT = [select id,Name,Participant__c,Study__c,Provider__c,Study_Provider__c, Date_Enrolled__c from Study_Participant__c where Name =:a.Participant_code__c and Study__c =: 'a00A000000cRWxXIAW' ];   
       List <Study_Participant__c> Named = [select id,Name,Participant__c,Study__c,Provider__c,Study_Provider__c, Date_Enrolled__c from Study_Participant__c where Name =:a.Participant_code__c and Study__c =:a.study__c and Study__c !=:'a00A000000cRWxXIAW' and Study__c!=:'a00A000000dMIvSIAW'];  
            for (Study_Participant__c d: TREAT2)

            if  (a.Study__c =='a00A000000cRWxXIAW' && TREAT2.size()>0 ) {


     If(a.Study__c =='a00A000000cRWxXIAW' && TREAT2.isEmpty() && TREAT.size()>0){ 


      If(a.Study__c !='a00A000000cRWxXIAW' && Named.size()>0)// && a.Participant_code__c==d.Name && a.Study__c ==d.Study__c){
                a.Study_Participant__c= Named.get(0).id;

     else if (TREAT.isEmpty() && TREAT2.isEmpty() && Named.isEmpty())
                a.Study_Provider__c = 'a05A000000Jqa3s';

  • well, you have (1) soql inside of for loops ..you need to make multiple passes and save results in maps (2) hard-coded IDs which create deployment issues
    – cropredy
    Jun 16, 2016 at 21:25

1 Answer 1


I spent way more time on this then I should have. Granted I do not know your org so this is the best I can do with what you provided. Also a bunch of your logic was off so I did what I could to simplify it.

You need to look at my comments on the query and fill in any other parts I made assumptions on.

trigger Attach_Prescriptions on Prescription__c (before insert, before update) 
map<String, list<Study_Participant__c>) LinkMap= new map<String, list<Study_Participant__c>){}; // Map that will serve as lookup
Study_Participant__c[] TreatmentsQ = [select id, name, study__c... from Study_Participant__c where ...]; // Get all the Study Participants -- You'll want to find a way to reduce this list with a where clause

//this will create the map to find a list of Study_Participant__c's
    for(Study_Participant__c s: TreatmentsQ){
     if(LinkMap.get(s.Name) == null){
       LinkMap.put(s.name, new list<Study_Participant__c>());

        for(Prescription__c a: Trigger.New){
        list<Study_Participant__c> temp = new list<Study_Participant__c>(LinkMap.get(a.Participant_code__c )); // this is the same as saying if Name = Participant_code__c now you have a list of Study_Participant__c's that have that Participant_code__c
                        if(temp.size() > 0){ //if they were in a study

                            for(Study_Participant__c sp: temp){
                                if(sp.study__c == a.study__c && sp.Date_Enrolled__c  < a.fill_date__c){ 
                                    // if the Prescription__c.study__c == Study_Participant__c.study__c and  Prescription__c.Date_Enrolled__c < Study_Participant__c.fill_date__c date then fill that list value
                                    // IE if the Prescription__c.Study is "a00A000000dMIvSIAW" and the  Study_Participant__c.study__c is also "a00A000000dMIvSIAW"  it will check to make sure the dates are right same and fill in that info. 

                                    a.Study_Participant__c= sp.id;
                             else if(sp.study__c == a study__c){
                               //Do same logic as above if the studies match but fill dates don't?

                        //there's no Name matching Participant_code__c
                         a.Study_Provider__c = 'a05A000000Jqa3s';

  • 1
    +1 for the "way more time" (which was my initial reaction when I saw OP)
    – cropredy
    Jun 17, 2016 at 0:35
  • @sfdcfox -- please explain... There's 1 query in my code above and it's executed when the trigger begins before the for loop.
    – AlphaBravo
    Jun 17, 2016 at 11:05
  • Thanks so much @AlphaBravo. Your example is exactly what I needed! Jun 17, 2016 at 19:32
  • @AlphaBravo I think I just misread the code. You might want to clean it up for future visitors.
    – sfdcfox
    Jun 17, 2016 at 20:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .