+ Start a Discussion
strangebiscuitstrangebiscuit 

Trigger to relate entries on two custom objects on bulk upload

I've been trying to puzzle out this problem with various roundabout methods for some time on Professional Edition, but we just upgraded to Enterprise and it seems like apex triggers would be the most effective way to implement this. I wonder if anyone could provide any advice or guidance, as I'm pretty new to apex.

 

We're trying to use Salesforce to store and organize some payroll info that is provided to us in the form of Excel spreadsheets. They are basically lists of sales which include the name and ID of the rep who made the sale. We need to upload these on a weekly basis and then be able to view the sales made by each rep. 

 

So basically we have two custom objects, "Sales" and "Reps". We upload a list of sales (in .csv format) into the Sales object each week. We want to make it so that every time a Sale entry gets created it is assigned to the Rep who made that sale (Sales has a master-detail relationship with Reps). Each sale lists the Name and ID# of the rep who made it, but not the Salesforce ID of the corresponding Rep entry. So it seems we need the apex trigger to match each Sale to the correct Rep by comparing the Name and/or ID fields in both objects.

 

Does this sound plausible and make sense? Could anyone provide any insight on how I might get started? Any help would be greatly appreciated.

 

P.S. We also need to assign "points" to each Rep based on the status of each sale that gets added...but I suppose that will come after. I've been told this might be accomplished with workflow rules, but obviously we first need to find a way of associating each Sale with the correct Rep

Best Answer chosen by Admin (Salesforce Developers) 
Satish_SFDCSatish_SFDC

Yes this is possible through triggers. But before working with triggers, i would like to suggest one more alternative. If you have a list of Rep ID's and the Salesforce ID's of these reps in an excel sheet, you could easily do a vlookup to get the Salesforce ID of the Rep in a new column in your Sale csv file. This way there is no need for a trigger.

 

Ok now the trigger based solution.

 

1. Create a trigger on the Deal to dynamically lookup the the Salesforce ID of the rep.

 

trigger DealTrigger on Deal__c (before insert){

   //This set will store all the RepId's.
   //I am assuming Rep ID's are Integers. If it is 
   //alphanumeric then it should be 
   //Set<String> repIDs = new  Set<String>

   Set<Integer> repIDs = new Set<Integer>();

   //Loop through all the new deals to be created.
   for(Deal__c deal : Trigger.new){
      //Get the rep's ID of each deal and add it to the Set
      //Note: I am assuming the API/Unique name of the Rep ID     
      //field is Rep_Id__c;
      repIDs.add(deal.Rep_ID__c);
   }

   //Now we have all the Rep ID's in the set.
   // Issue an single SOQL to get the Salesforce ID's of all the reps.
   
   Map<String,ID> mapReps = new Map<String,ID>();
   for(Rep__c repRecord : [Select ID,Rep_ID__c From Rep__c WHERE Rep_ID__c IN (:repIDs)]){
      mapReps.put(repRecord.Rep_ID__c,repRecord.ID);
   }
   
  //The mapReps has a map of all Rep ID's with Rep Salesforce ID's
   for(Deal__c deal : Trigger.new){
      ID repSalesforceID = mapReps.get(deal.Rep_ID__c);
      //This is the MD field in Deal__c.
      deal.Rep__c = repSalesforceID;
   }
   
}

 

I have not compiled and checked, so there may be a few syntax errors. Let us know if you get any errors. 

You cannot write new triggers on your production instance, so test it first on a sandbox and then migrate it later

 

Hope this helps.

 

Regards,
Satish Kumar
Please mark my answer as a solution if it was helpful so it is available to others as a proper solution.
If you felt I went above and beyond, please give me Kudos by clicking on the star icon.

 

All Answers

Satish_SFDCSatish_SFDC

Yes this is possible through triggers. But before working with triggers, i would like to suggest one more alternative. If you have a list of Rep ID's and the Salesforce ID's of these reps in an excel sheet, you could easily do a vlookup to get the Salesforce ID of the Rep in a new column in your Sale csv file. This way there is no need for a trigger.

 

Ok now the trigger based solution.

 

1. Create a trigger on the Deal to dynamically lookup the the Salesforce ID of the rep.

 

trigger DealTrigger on Deal__c (before insert){

   //This set will store all the RepId's.
   //I am assuming Rep ID's are Integers. If it is 
   //alphanumeric then it should be 
   //Set<String> repIDs = new  Set<String>

   Set<Integer> repIDs = new Set<Integer>();

   //Loop through all the new deals to be created.
   for(Deal__c deal : Trigger.new){
      //Get the rep's ID of each deal and add it to the Set
      //Note: I am assuming the API/Unique name of the Rep ID     
      //field is Rep_Id__c;
      repIDs.add(deal.Rep_ID__c);
   }

   //Now we have all the Rep ID's in the set.
   // Issue an single SOQL to get the Salesforce ID's of all the reps.
   
   Map<String,ID> mapReps = new Map<String,ID>();
   for(Rep__c repRecord : [Select ID,Rep_ID__c From Rep__c WHERE Rep_ID__c IN (:repIDs)]){
      mapReps.put(repRecord.Rep_ID__c,repRecord.ID);
   }
   
  //The mapReps has a map of all Rep ID's with Rep Salesforce ID's
   for(Deal__c deal : Trigger.new){
      ID repSalesforceID = mapReps.get(deal.Rep_ID__c);
      //This is the MD field in Deal__c.
      deal.Rep__c = repSalesforceID;
   }
   
}

 

I have not compiled and checked, so there may be a few syntax errors. Let us know if you get any errors. 

You cannot write new triggers on your production instance, so test it first on a sandbox and then migrate it later

 

Hope this helps.

 

Regards,
Satish Kumar
Please mark my answer as a solution if it was helpful so it is available to others as a proper solution.
If you felt I went above and beyond, please give me Kudos by clicking on the star icon.

 

This was selected as the best answer
strangebiscuitstrangebiscuit

Wow! I never noticed the email that there was a reply. I'm amazed and delighted to get such a thorough and detailed response. I really appreciate it and apologize for not replying to thank you earlier. 

 

I'm trying out your trigger...but I am getting a little error. My Rep IDs are in fact strings, not integers, so I did change both instances of "Set<Integer>" to "Set<String>". The only other change I made was to change "deal" to "sale" to match my Sales object.

 

The error I'm getting is: "Error: Compile Error: Invalid bind expression type of SET<String> for column of type String at line 22 column 81"

 

I'm not quite sure what's causing that. I'll copy my slightly modfied code below.

 

Thanks so much again for your help. I can't tell you how much I appreciate it. I'll make sure to keep an eye out for a reply now that I know there's someone as helpful as you on these forums.

 

trigger SaleTrigger on Sale__c (before insert){

   //This set will store all the RepId's.
   //I am assuming Rep ID's are Integers. If it is 
   //alphanumeric then it should be 
   //Set<String> repIDs = new  Set<String>

   Set<String> repIDs = new Set<String>();

   //Loop through all the new sales to be created.
   for(Sale__c sale : Trigger.new){
      //Get the rep's ID of each sale and add it to the Set
      //Note: I am assuming the API/Unique name of the Rep ID     
      //field is Rep_Id__c;
      repIDs.add(sale.Rep_ID__c);
   }

   //Now we have all the Rep ID's in the set.
   // Issue an single SOQL to get the Salesforce ID's of all the reps.
   
   Map<String,ID> mapReps = new Map<String,ID>();
   for(Rep__c repRecord : [Select ID,Rep_ID__c From Rep__c WHERE Rep_ID__c IN (:repIDs)]){
      mapReps.put(repRecord.Rep_ID__c,repRecord.ID);
   }
   
  //The mapReps has a map of all Rep ID's with Rep Salesforce ID's
   for(Sale__c sale : Trigger.new){
      ID repSalesforceID = mapReps.get(sale.Rep_ID__c);
      //This is the MD field in Sale__c.
      sale.Rep__c = repSalesforceID;
   }
   
}

 

Satish_SFDCSatish_SFDC
Can you please point out the line number 22 here? Sorry but i have a hard time figuring out the line on which the error is.

Regards,
Satish Kumar
strangebiscuitstrangebiscuit

Pardon me...line 22 is:

 

for(Rep__c repRecord : [Select ID,Rep_ID__c From Rep__c WHERE Rep_ID__c IN (:repIDs)]){

 

Thanks again for your help!

strangebiscuitstrangebiscuit

Still around? Any guidance on this error? I really appreciate the help you've given so far and am very grateful. 

strangebiscuitstrangebiscuit

I did get this resolved...in case anyone in future finds this useful I figured I'd post here that the solution to the error was to remove the parenthesis around ":repIDs" on the line:

 

for(Rep__c repRecord : [Select ID,Rep_ID__c From Rep__c WHERE Rep_ID__c IN (:repIDs)]){

 

Also, I learned after the fact that the free Dataloader.io app on the appexchange can do this Salesforce ID lookup right out of the box...so when using that there's no need for a trigger or a vlookup or any of that annoying stuff I've spent time messing around with.

Satish_SFDCSatish_SFDC
Thats great.
Thanks

Satish Kumar