I have a set of records I would like to load into my Account object. I'm trying to write a trigger before insert/update to validate the Bus. Id and Account Name already exists in SFDC or not. Because our data is a mess, Bus. ID and Account are not unique.

We should check incoming Bus. Id against Bus. ID in SFDC, if there is a match then we check the Account Name. If both matches then no further actions needed.

If Bus. Id and Account do match then insert record into SFDC.

If Bus. Id match and Account Name doesn't then update the record in SFDC with the incoming Bus. Id.

Can some experts help with the structure of this trigger so I can follow and modify if needed?

This is what I have so far:

trigger Data_Transfer_BI_SFDC on Account (before insert){

    List<String> accountupdate = new List<String>();
    List<account> accountinsert = new List<account>();
    List<String> accountNames = new List<String>();

    List<Account> SF_Account = [select id, Service__c, Business_ID__c, name from Account order by Business_ID__c desc];

    for (Account a : Trigger.New){


  • By no further actions, do you mean that the insert should be disallowed via an error? The 3rd scenario makes no sense right now, if business Id matches, why would you need to update the Bus. Id? – greenstork Sep 18 '14 at 23:36
  • Thank you Greenstork. No further action needed means, don't include that record in the insert or update function. – user7123 Sep 18 '14 at 23:40
  • I wrote the third logic incorrectly. If the Bus. ID matches and the Account name doesn't then we update the incoming Account Name to the record in SFDC. Thanks. – user7123 Sep 18 '14 at 23:45
  • Is this intended as a one time process? – Andrew Monshizadeh Sep 19 '14 at 1:24
  • @AndrewMonshizadeh, no this will be a daily process. Thanks – user7123 Sep 23 '14 at 1:29

Sounds like you want to perform an upsert based on an external id field (Business_ID__c) and the account name.

If you could populate a custom field with these two values and set it as an external id then you could let Salesforce do the hard work for you. If the upsert matches on the resulting external id custom field then the existing record will be updated. Otherwise a new record will be inserted.

With regards to the trigger. I don't think you can convert an insert into an update on the fly. You will need to reject the records and handle them separately as updates instead.

trigger Data_Transfer_BI_SFDC on Account (before insert){

    // Find the unique Business Ids in the transaction
    Set<String> businessIds = new Set<String>();
    for (Account a : Trigger.New) {
        if(a.Business_ID__c != null) { 

    // Get the existing accounts for the business ids
    Map<string, Account> businessIdToAccountMap = new Map<string, Account>();
    for (Account a : [select id, Business_ID__c, name 
                             from Account 
                             where Business_ID__c in :businessIds]) {
        businessIdToAccountMap.add(a.Business_ID__c, a);

    // Find any of the Accounts being inserted that match an existing account by
    // Business Id and name. Reject these records.
    for (Account a : Trigger.New) {
        Account existingAccount = businessIdToAccountMap.get(a.Business_ID__c);
        if(existingAccount != null && existingAccount.Name == a.Name) {
            a.addError('Matches existing Account: ' + existingAccount.Id);
  • Will this script works if the Business_ID__c is not an external id? Data is a pretty big mess. We are trying to dedup Business_ID__c at the monment. Thanks – user7123 Sep 23 '14 at 1:33
  • You can only do an upsert on Business_ID__c if it is an external Id. The trigger above will reject any any accounts from being inserted if there is already an existing account with the same name and Business_ID__c. – Daniel Ballinger Sep 23 '14 at 1:42
  • Thank you @Daniel Balinger, after our massive clean up. I'm going to propose to make the Business_ID an external Id. Thank again. – user7123 Sep 24 '14 at 20:20
  • @user7123 Glad to help. If this answer helped consider using the upvote arrow next to the answer. If you consider it a complete answer there is a green tick. This is useful for other users as it directs them straight to the answer and removes the question from the unanswered list. – Daniel Ballinger Sep 24 '14 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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