Trigger - Lookup field value insertion

Question

I am using dataloader to import data into salesforce. As it is live synchronization, there is a problem with direct insertion of lookup field values. For this reason I am writing trigger before insertion to validate and insert.

I have a invoice__c custom object and it has accounts lookup field. For lookup field, receiving input value as account name. I need to get id of the account name from accounts object and insert into invoice__c account lookup field.

I am new to the Apex code and tried few lines of code. I am able to validate and stuck in id retrievel and insertion into lookup field.

Could someone help me out. And correct me if this approach is correct for multi trigger operation.

     Trigger AccountInsert on invoice__c(before insert) {

         for(invoice__c invoiceVal : Trigger.new) {

         List<invoice__c> oldAcc = [select id, Name from Account where Name 
         = invoiceVal.Account__c];

         if(oldAcc.size()>0) {

             invoiceVal.name.addError('Account is existing');

          }
        else {
             invoiceVal.name.addError('Account is not existing');
        }
    }
 }

Answer 1

This fails the basic principle of bulkifying. You should always follow the aggregate-query-update pattern, which looks like this:

trigger GetAccountForInvoice on Invoice__c (before insert) {
  Set<String> accountNames = new Set<String>();
  Map<String, Id> accountNameToId = new Map<String, Id>();
  // Aggregate
  for(Invoice__c record: Trigger.new) {
    accountNames.add(record.Account__c);
  }
  // Query
  for(Account record: [SELECT Name FROM Account WHERE Name IN :accountNames]) {
    accountNameToId.put(record.Name.toUpperCase(), record.Id);
  }
  // Update
  for(Invoice__c record: Trigger.new) {
    if(record.Account__c != null) {
      Id accountId = accountNamesToId.get(record.Account__c.toUpperCase());
      if(accountId != null) {
        System.debug('Account was found');
      } else {
        System.debug('Account was not found');
      }
    } else {
      System.debug('Account name field was blank');
    }
  }
}

Answer 2

Given that the trigger order of operation validates foreign keys (lookup field) prior to executing before triggers, an approach where you add data other than a valid Id in your lookup field really isn't going to work.

A more typical approach is to either (a) pull down your data for the linked object and do matching in Excel; (b) match using an Upsert operation in the data loader against an External ID field on the linked object; or (c) use a tool like Apsona or DemandTools to do more sophisticated matching.

If you're set on using the trigger approach, you'll need to bring in the account names in a different field than the lookup field. Additionally, your trigger here is not bulkified and will immediately suffer from poor performance and limits errors. It will be very difficult to build a trigger-based matching function that works effectively given the platform's constraints.

Answer 3

Thank you so much @sfdcfox for your detailed code and quick response. It helped a lot and almost working.

Need help in few blocked areas,

1. accountId always getting null and fall into "Account was not found" condition
2. By using toUpperCase() got the below error, Method does not exist or incorrect signature: void toUpperCase() from the type Id (After removal, the code is executing properly. Is it required since it is working now?)
3. Do I need to consider data loader data here or can insert only lookup field id. Please suggest. In above code insertion is not happening.

4. There are few more lookup fields available in this object. Is writing multiple triggers in similar way is recommended? i.e, dealer, sales

    trigger GetAccountForInvoice on Invoice__c (before insert) {
      Set<String> accountNames = new Set<String>();
      Map<String, Id> accountNameToId = new Map<String, Id>();
       // Aggregate
       for(Invoice__c record: Trigger.new) {
           accountNames.add(record.Account__c);
       }
       // Query
       for(Account record: [SELECT Id, Name FROM Account WHERE Name IN 
       :accountNames]) {
          accountNameToId.put(record.Name.toUpperCase(), record.Id);
       }
       // Update
       for(Invoice__c record: Trigger.new) {
         if(record.Account__c != null) {
         Id accountId = 
         accountNameToId.get(record.Account__c.toUpperCase());
          if(accountId != null) {
            System.debug('Account was found' + 'Account ----' + record.Account__c);
           accountNames.add(record); // doubt on insertion part
           } else {
           System.debug('Account was not found' + 'Account ----' + 
           record.Account__c + '--' + accountId);
           }
         } else {
         System.debug('Account name field was blank');
       }
     }
     insert accountNames;
    }