function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Kenneth KimbrellKenneth Kimbrell 

Best way to handle a trigger that has data in a field that is a string of a comma separated list

My issue: I built a trigger that assumed a 1:1 relationship with Student Accounts:AAC Accounts. The problem is now that we have discovered that AAC has multiple AAC accounts per student, why? Who knows it is the craziest thing I heard. So what I did is built a trigger that adds the additional AAC Customer Numbers to a long text area field called AAC_Additional_Customer_Numbers__c. This field now has data similar to this: 

6535965716,6535965736,6535966326

My existing trigger before discovering that I would need to look at multiple AAC numbers looks like this:

trigger TGR_Payment on Payment__c (before insert, before update) {
  set<string> aac = new set<string>();
  set<id> students = new set<id>();
  map<string,id> aacStudent = new map<string,id>();
  map<id,id> studentQuote = new map<id,id>();
  for(Payment__c p : trigger.New){
    if(p.AAC_Customer_Number__c != '' && p.AAC_Customer_Number__c != null){
      aac.add(p.AAC_Customer_Number__c);
    }
  }
  if(aac.size() > 0){
    for(Contact c : [SELECT Id, AAC_Customer_Number__c FROM Contact WHERE AAC_Customer_Number__c IN :aac]){
      students.add(c.Id);
      aacStudent.put(c.AAC_Customer_Number__c,c.Id);
    }
    if(students.size() > 0){
      map<id,date> latestDate = new map<id,date>();
      for(Quote__c q : [SELECT Id, Student__c, Sales_Reporting_Date__c FROM Quote__c WHERE Student__c IN :students AND Active__c = true AND Sales_Reporting_Date__c != null]){
        if(latestDate.get(q.Student__c) != null){
          if(q.Sales_Reporting_Date__c > latestDate.get(q.Student__c)){
            studentQuote.put(q.Student__c,q.Id);
            latestDate.put(q.Student__c,q.Sales_Reporting_Date__c);
          }
        }else{
          studentQuote.put(q.Student__c,q.Id);
          latestDate.put(q.Student__c,q.Sales_Reporting_Date__c);
        }
      }
    }
  }
  for(Payment__c p : trigger.New){
    if(p.AAC_Customer_Number__c != '' && p.AAC_Customer_Number__c != null){
      if(p.Student__c == null && aacStudent.get(p.AAC_Customer_Number__c) != null){
        p.Student__c = aacStudent.get(p.AAC_Customer_Number__c);
      }
      if(p.Quote__c == null && aacStudent.get(p.AAC_Customer_Number__c) != null && studentQuote.get(aacStudent.get(p.AAC_Customer_Number__c)) != null){
        p.Quote__c = studentQuote.get(aacStudent.get(p.AAC_Customer_Number__c));
      }
    }
  }
}

Can someone please help me with how I might integrate looking at multiple AAC numbers from a comma seperated list into this existing trigger. Before, I just mapped the Contact ID to the one AAC Customer Number. So when importing payments I just got the ID of the AAC Customer Number being imported because the AAC Customer Number was the key, and that is how I created the lookup for the student record. Problem is now, I can't just look for one, I need to look for many and then still be able to determine what Contact ID the AAC Customer Number is associated with. Any ideas? Thanks!
Best Answer chosen by Kenneth Kimbrell
Abdul KhatriAbdul Khatri
To me as you mentioned the craziest thing, I see design as fundamental flaws and need to properly thought through again to make it more scalable and flexible. Anyway based on your requirement, the only way I can come up with is this.

I personally didn't like lot of things in this code keeping the apex best practices like bulkification, avoid nested for loop etc. but I am not sure how to make it without doing what I am doing here in this code. Please review, try and let me know what you think
 
trigger TGR_Payment on Payment__c (before insert, before update) {
	set<string> aac = new set<string>();
  	set<id> students = new set<id>();
  	map<string,id> aacStudent = new map<string,id>();
    map<string,List<String>> aacStudent = new map<string,List<String>>();
    List<string> aacStudentadditionalList = new List<string>();
  	map<id,id> studentQuote = new map<id,id>();
    
  	for(Payment__c p : trigger.New)
    {
    	if(p.AAC_Customer_Number__c != '' && p.AAC_Customer_Number__c != null)
        {
      		aac.add(p.AAC_Customer_Number__c);
    	}
  	}
    
    if(acc.isEmpty()) return;
    
  	for(String str : aac){
    	//for(Contact c : [SELECT Id, AAC_Customer_Number__c FROM Contact WHERE AAC_Customer_Number__c IN :aac]){
        for(Contact c : [FIND :str IN ALL FIELDS RETURNING CONTACT (AAC_Customer_Number__c, AAC_Additional_Customer_Numbers__c)][0]){
      		students.add(c.Id);
      		//aacStudent.put(c.AAC_Customer_Number__c,c.Id);
        	aacStudentadditionList = c.AAC_Additional_Customer_Numbers__c.split(',');            
            for(String accCustomerNumber : aacStudentadditionList) {                
                aacStudent.add(accCustomerNumber, c.Id);
            }       
    	}
    
    	if(students.size() > 0){
      		map<id,date> latestDate = new map<id,date>();
      		for(Quote__c q : [SELECT Id, Student__c, Sales_Reporting_Date__c FROM Quote__c WHERE Student__c IN :students AND Active__c = true AND Sales_Reporting_Date__c != null]){
        	if(latestDate.get(q.Student__c) != null){
          		if(q.Sales_Reporting_Date__c > latestDate.get(q.Student__c)){
            		studentQuote.put(q.Student__c,q.Id);
            		latestDate.put(q.Student__c,q.Sales_Reporting_Date__c);
          		}
        	}else{
          		studentQuote.put(q.Student__c,q.Id);
          		latestDate.put(q.Student__c,q.Sales_Reporting_Date__c);
        		}
      		}
		}
	}

  	for(Payment__c p : trigger.New){
    	if(p.AAC_Customer_Number__c != '' && p.AAC_Customer_Number__c != null){
      		if(p.Student__c == null && aacStudent.get(p.AAC_Customer_Number__c) != null){
        		p.Student__c = aacStudent.get(p.AAC_Customer_Number__c);
      		}
      		
            if(p.Quote__c == null && aacStudent.get(p.AAC_Customer_Number__c) != null && studentQuote.get(aacStudent.get(p.AAC_Customer_Number__c)) != null){
        		p.Quote__c = studentQuote.get(aacStudent.get(p.AAC_Customer_Number__c));
      		}
    	}
  	}
}



 

All Answers

Abdul KhatriAbdul Khatri
Quick Question
  1. Where is this Addition Customer Number Fields Exists?
  2. How you filling this Field with additional number, I mean what is the process?
Kenneth KimbrellKenneth Kimbrell

1.The field "AAC_Additional_Customer_Numbers__c" exists on the contact object.
2. The process is kind of difficult right now. But what I have done is made a report of all students with their email, phonenumber, name, and contact id. I then export the report as a CSV. I also make a report of all AAC Customers, I then export the report as CSV. Then I do a VLOOK that carries serveral conditions, first it looks at the students email and matches with the aac email if it matches it gets the Contact ID. If it fails, I then look at the mphone, if it fails, I then look at the homephone, if it fails I then look at the name and ensure no other names are greater than 1, to prevent skewing the logic when two names are the same. So this formula maps the contact ID with the AAC Customer Number. I then just import only the Contact ID with AAC Customer Number into salesforce, I built a trigger:

trigger TGR_SortAAC on Account (before update) {
    set<string> OLDaacSet = new set<string>();
    list<string> OLDaacLst = new list<string>();
    list<string> NEWaacLst = new list<string>();
    for(Account a: Trigger.new){
	    Account oa = Trigger.oldMap.get(a.id);
		if(oa.AAC_Customer_Number__pc != null && oa.AAC_Customer_Number__pc != ''){
			if(oa.AAC_Additional_Customer_Numbers__pc != null){
				OLDaacLst = oa.AAC_Additional_Customer_Numbers__pc.split(',');
				OLDaacSet.addAll(OLDaacLst);
				OLDaacSet.add(a.AAC_Customer_Number__pc);
			}else{
				OLDaacSet.addAll(OLDaacLst);
				OLDaacSet.add(a.AAC_Customer_Number__pc);
			}
			for(string s: OLDaacSet){
				NEWaacLst.add(s);
			}
			a.AAC_Additional_Customer_Numbers__pc = string.join(NEWaacLst,',');
		}else{
			OLDaacSet.add(a.AAC_Customer_Number__pc);
			for(string s: OLDaacSet){
				NEWaacLst.add(s);
			}
			a.AAC_Additional_Customer_Numbers__pc = string.join(NEWaacLst,',');
		}
		system.debug('******OLDaacSet' + OLDaacSet);
		system.debug('******OLDaacLst' + OLDaacLst);
    }
}

that simply adds the first aac number to the AAC_Additional_Customer_Numbers__pc, and then joins any additional aac numbers with the same contact ID to this list. This is how I am currently trying to control additional customer numbers. but now I am trying to decide the best way for my existing trigger on the payment object to handle looking at more than one aac number. Hope that gives you some more detail. I appreciate any help you can provide.
Abdul KhatriAbdul Khatri
To me as you mentioned the craziest thing, I see design as fundamental flaws and need to properly thought through again to make it more scalable and flexible. Anyway based on your requirement, the only way I can come up with is this.

I personally didn't like lot of things in this code keeping the apex best practices like bulkification, avoid nested for loop etc. but I am not sure how to make it without doing what I am doing here in this code. Please review, try and let me know what you think
 
trigger TGR_Payment on Payment__c (before insert, before update) {
	set<string> aac = new set<string>();
  	set<id> students = new set<id>();
  	map<string,id> aacStudent = new map<string,id>();
    map<string,List<String>> aacStudent = new map<string,List<String>>();
    List<string> aacStudentadditionalList = new List<string>();
  	map<id,id> studentQuote = new map<id,id>();
    
  	for(Payment__c p : trigger.New)
    {
    	if(p.AAC_Customer_Number__c != '' && p.AAC_Customer_Number__c != null)
        {
      		aac.add(p.AAC_Customer_Number__c);
    	}
  	}
    
    if(acc.isEmpty()) return;
    
  	for(String str : aac){
    	//for(Contact c : [SELECT Id, AAC_Customer_Number__c FROM Contact WHERE AAC_Customer_Number__c IN :aac]){
        for(Contact c : [FIND :str IN ALL FIELDS RETURNING CONTACT (AAC_Customer_Number__c, AAC_Additional_Customer_Numbers__c)][0]){
      		students.add(c.Id);
      		//aacStudent.put(c.AAC_Customer_Number__c,c.Id);
        	aacStudentadditionList = c.AAC_Additional_Customer_Numbers__c.split(',');            
            for(String accCustomerNumber : aacStudentadditionList) {                
                aacStudent.add(accCustomerNumber, c.Id);
            }       
    	}
    
    	if(students.size() > 0){
      		map<id,date> latestDate = new map<id,date>();
      		for(Quote__c q : [SELECT Id, Student__c, Sales_Reporting_Date__c FROM Quote__c WHERE Student__c IN :students AND Active__c = true AND Sales_Reporting_Date__c != null]){
        	if(latestDate.get(q.Student__c) != null){
          		if(q.Sales_Reporting_Date__c > latestDate.get(q.Student__c)){
            		studentQuote.put(q.Student__c,q.Id);
            		latestDate.put(q.Student__c,q.Sales_Reporting_Date__c);
          		}
        	}else{
          		studentQuote.put(q.Student__c,q.Id);
          		latestDate.put(q.Student__c,q.Sales_Reporting_Date__c);
        		}
      		}
		}
	}

  	for(Payment__c p : trigger.New){
    	if(p.AAC_Customer_Number__c != '' && p.AAC_Customer_Number__c != null){
      		if(p.Student__c == null && aacStudent.get(p.AAC_Customer_Number__c) != null){
        		p.Student__c = aacStudent.get(p.AAC_Customer_Number__c);
      		}
      		
            if(p.Quote__c == null && aacStudent.get(p.AAC_Customer_Number__c) != null && studentQuote.get(aacStudent.get(p.AAC_Customer_Number__c)) != null){
        		p.Quote__c = studentQuote.get(aacStudent.get(p.AAC_Customer_Number__c));
      		}
    	}
  	}
}



 
This was selected as the best answer
Kenneth KimbrellKenneth Kimbrell

As always you prevail! I had to modify your code slightly but you helped get my wheels turning. As you mentioned it is against most batch operation designs by querying inside the for loop, when the governer will only allow 100 queries. But that can easily be bypassed by reducing the data loader batch to 1. I had to do that anyways when upserting the new data and adding additional aac numbers. Considering there were multiple same contact ids it would fail if the same id was within the same batch. Was not fun doing it to 7,000+ records but we do what we got to do. Anyways the only piece I really modified of my trigger was this:

for(string str: aac) {
    list<list<SObject>> searchList = [FIND :str IN ALL FIELDS RETURNING CONTACT (AAC_Customer_Number__c, AAC_Additional_Customer_Numbers__c)];
    for(list<SObject> c: searchList){
        Contact [] con = ((List<Contact>)searchList[0]);
        students.add(con[0].Id);
        //aacStudent.put(c.AAC_Customer_Number__c,c.Id);
        for(Contact cc: con){
            accStudentAdditional = cc.AAC_Additional_Customer_Numbers__c.split(',');
        }
        for(string accCustomerNumber: accStudentAdditional) {
            aacStudent.put(accCustomerNumber, con[0].Id);
        }
    }
}