• I have a custom object called employees__c. Employees__c has fields- person_id__c(external and unique id) , employee_status__c(which can have value active or inactive).
  • I have an object called log which records the employee status when they become active or inactive. This log is a child of employee(lookup) and when ever the employee status field becomes active or inactive a new log is created with appropriate incident(incident type is a field in log ,more later).
  • I have a csv file which consist of a list of employees to be upserted into salesforce.

My requirement - If the employees are in the Salesforce but not in the CSV file, then the employee_status__c field of those employees who are not in CSV file should be updated to Inactive and the employees in CSV file should be upserted into salesforce.

For example - In CSV file I have emp1,emp2,emp3 and in salesforce I have emp3,emp4,emp5.. then when upserting the csv file in Salesforce, the emp4 and emp5 should be made as inactive which would create a log with incident type field having value as employee removed. And emp1,emp2 should be inserted and a log is created with incident type field value - "New Employee". For emp3 as it is an update, if the emp2 status in CSV is active and emp2 in Salesforce is active it should not create any log as it's employee status has not changed. If changed then create the log according to employee status.

My problem- What i tried so far:

  • Created a trigger on employee and match CSV file records and Salesforce employee record to see which employees are not in CSV. Did this in before insert trigger.

  • Put the employees who were not in CSV in a list and update the employee status of those to inactive. In before trigger( This runs an update trigger also on those records)

  • Then comes after trigger where I add a log to each newly inserted employee with incident type field populated as "New Employee" Now I come to before update trigger where I get the records which has to be updated and create a log for them with appropriate incident type field value.

I am running into trouble when there is an employee both in CSV and Salesforce with the active status. In the 2nd step where I get the employees who are not in CSV, since it is a before insert trigger it doesn't have the scope of the particular record in CSV which has to be updated in Salesforce. Thus it updates the status of the record in Salesforce to inactive eventually creating a log with incident type employee removed. But when 3rd step runs it identified that the record has to be updated in salesforce thus in before update trigger it updated the status of the record to active and there by creating another log with incident type -"Employee removed"

My Need - In this case the record that has to be updated, since the status in CSV and salesforce are active there should be no log created. Please see the screenshot below on how it looks:

enter image description here

trigger CreateEmployeeLog on Employees__c (before insert,after insert, before update) {

  // Create a transaction log with Incident - New Employee , when HR feed is uploaded  
  if(trigger.isInsert && trigger.isBefore){

    List<employees__c> exsistingEmployee = [select id,person_id__c,name from employees__c];

    Map<String,employees__c> employeesFromCSVMap = new Map<String,employees__c>();
    For(employees__c empl : trigger.new){

    List<employees__c> employeeTobeMadeInactive = new List<employees__c>();
    for(employees__c empl : exsistingEmployee ){

            // employee is in CSV and Salesforce
            system.debug('employees in CSV and Salesforce********' + empl.name);

            // employees not in CSV
            system.debug('employees not in CSV but is in Salesforce ********' + empl.name);
            empl.Employee_Status__c = 'Inactive';


    Update employeeTobeMadeInactive;


  if(trigger.isInsert && trigger.isAfter){

  List<Transaction_Log__c> newtransLog = new List<Transaction_Log__c>();
  for(employees__c emp : trigger.new){
      Transaction_Log__c log = new Transaction_Log__c(name=emp.Network_ID__c,Employee_Log__c=emp.id,Incident_Type__c='New Employee'
      ,Job_Level_Name__c=emp.Job_Level_Name__c,Date_Of_Incident__c= Date.valueOf(emp.CreatedDate));
    insert newtransLog;


  // When the status of employees is updated to 'Active' or 'Inactive' create a transaction log with appropiate Incident  
    List<Transaction_Log__c> newtransLog = new List<Transaction_Log__c>();
    for(employees__c emp: trigger.new){
        employees__c oldEmployeeInfo = trigger.oldMap.get(emp.Id); 
        if(oldEmployeeInfo.Employee_Status__c != emp.Employee_Status__c && emp.IsBatchUpload__c=='true'){

           if(emp.Employee_Status__c == 'Inactive'){

           Transaction_Log__c log = new Transaction_Log__c(name=emp.Network_ID__c,Employee_Log__c=emp.id,Incident_Type__c='Employee Removed'
            ,Job_Level_Name__c=emp.Job_Level_Name__c,Date_Of_Incident__c= Date.valueOf(Date.Today()));

           }else if(emp.Employee_Status__c == 'active'){
            Transaction_Log__c log = new Transaction_Log__c(name=emp.Network_ID__c,Employee_Log__c=emp.id,Incident_Type__c='New Employee'
            ,Job_Level_Name__c=emp.Job_Level_Name__c,Date_Of_Incident__c= Date.valueOf(Date.Today()));
    insert newtransLog; 


  • Yes , I did not receive any comments for that previous topic. I will close that and keep this open. – SfdcBat Aug 19 '16 at 15:34
  • @TusharSharma The previous question did not solve my problem as there were no responses. Pls suggest how can I close the other one and keep this open as this the latest. – SfdcBat Aug 19 '16 at 15:37
  • Patience is a virtue. Your other question was only asked 10 hours ago. I can't speak for others, but that roughly corresponds to midnight in my time (during which I am generally sleeping instead of trolling through SFSE). – Derek F Aug 19 '16 at 15:42
  • I agree! Shall I close this questions then? or delete/? – SfdcBat Aug 19 '16 at 15:43
  • How are you inserting CSV in system – Tushar Sharma Aug 19 '16 at 15:46

A trigger isn't the right tool for the job here, at least not for all of the work that you're trying to do.

The reasons why a trigger doesn't work completely for this situation are:

  1. An Upsert call is just a nice shortcut.

Salesforce will determine which records need to be inserted, and which need to be updated, and then fire separate events. The trigger context variables will only contain one group or the other, never both.

  1. A trigger will only run on a maximum of 200 records in one shot.

If you're performing DML on 1000 records, Salesforce will fire your trigger 5 times. The trigger context variables will only contain records from the current trigger batch of (up to) 200.

My Advice

The only part of your trigger that absolutely needs to be moved somewhere else is the part where you determine whether or not an existing employee in Salesforce exists in your CSV file or not.

You could take care of making this determination in two different ways:

1. Do it outside of Salesforce by using the DataLoader to extract the current employees that you have in Salesforce

Use that result in a vlookup in Excel (or some other tool) to determine which records from the current csv you actually need to update.

Prepare a new csv file with only those records and the records that need to be inserted, and hand that to the DataLoader upsert instead.

2. Create a schedulable apex class, and have it scheduled to run sometime after you finish using DataLoader (say, 11pm)

A relatively simple query for employee records where CreatedDate != TODAY and LastModifiedDate != TODAY may be all you need to pick out which records need to be updated to inactive.

It may be wise to check to see if any records were updated TODAY and not created TODAY, and not mark records as inactive if this query returns 0 results (that way, if you aren't running the DataLoader over the weekend, it won't deactivate everyone).

If other users are able to update employee records, you may want to consider adding a new field to employee called LastDataLoaderUpdate__c which you would set in the CSV file that you give to the Data Loader. Making it read-only, and leaving it off of any layout, would make the data in the field quite reliable. If you go this route, I'd use LastDataLoaderUpdate__c in place of LastModifiedDate in the two queries I suggested above.

| improve this answer | |
  • Hmm perhaps we should have closed the other as a duplicate of this one rather than the other way around. – Adrian Larson Aug 19 '16 at 17:36
  • @AdrianLarson Or perhaps I could delete my answer here, and put it on the other one. I'd be fine with either. Maybe I'll turn this into a meta after I get back from lunch. – Derek F Aug 19 '16 at 17:40
  • 1
    Word. You're right though that this is a bit like hammering on a screw here. – Adrian Larson Aug 19 '16 at 17:45
  • Thanks. I do not have option to do it outside data load. So thinking about the 2nd option. – SfdcBat Aug 19 '16 at 18:57
  • @SfdcBat Why not? Is it an issue of procedure dictated by your employer? I don't think you can be granted permission to write all the records that you'd be upserting without having the readAll permission as well. You should physically be able to export the required data. Using DataLoader is already a manual step. While it would probably pay off to automate the logic to deactivate users eventually, doing it manually shouldn't take too long each time. If Excel isn't available, there are other options such as the LibreOffice suite. – Derek F Aug 19 '16 at 19:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.