validation to allow only 2 records with same text field value

Question

I need help in following code-

I have Custom field on Account as Custom_Id__c(Text 255). Scenario is you can create only 2 accounts with same value in Custom_Id__c field.

example - Account 1 - custom_Id__c= 'abcd'
          Account 2 - custom_Id__c= 'abcd'
          Account 3 - custom_Id__c= 'abcd' - Must show error'

below code works fine for single record but not for mulitple.

public with sharing class AccountTriggerAction {

    /*
        Method Name - validateAccount
        Description - This method validates if there are Two Accounts are present with Same Custom Id. If yes
                      then this will throw an Error and will not allow User to Add Third Account with Same Custom Id
        Param - List<Account> - List of New Accounts
    */
    public static void validateAccount(List<Account> newAccounts){

        //Map of Custom Id to its Related Account List
        Map<String, List<Account>> mapOfCustIdToListOfAccount = new Map<String, List<Account>>();
        List<Account> lstAccount = new List<Account>();

        for(Account account:newAccounts){
            mapOfCustIdToListOfAccount.put(account.Custom_Id__c, Null);
        }

        //Fetching Old Accounts which are present in database with Same Custom Id which we are inserting
        List<Account> oldAccounts = [SELECT Id, Custom_Id__c FROM Account 
                                     WHERE Custom_Id__c =: mapOfCustIdToListOfAccount.Keyset() LIMIT 50000];

        if(oldAccounts.size()>0){
            for(Account objAccount: oldAccounts){
                if(mapOfCustIdToListOfAccount.containsKey(objAccount.Custom_Id__c)){
                    lstAccount.add(objAccount);
                }
            }
        }
        for(Account newAcc : newAccounts){
            if(lstAccount.size() == 2){
                newAcc.Custom_Id__c.addError('Only Two Accounts are allowed with Same Custom Id -- '+ newAcc.Custom_Id__c);
            } 
        }
    }
}

Answer 1

You could do this a lot easier simply by using an aggregate result query instead:

Set<String> allIds = new Set<String>(), maxIds = new Set<String>();
for(Account record: newAccounts) {
  allIds.add(record.Custom_Id__c);
}
for(AggregateResult result: [
    SELECT Custom_Id__c customId
    FROM Account
    WHERE Custom_Id__c = :allIds
    GROUP BY Custom_Id__c
    HAVING COUNT(Custom_Id__c) > 2]) {
  maxIds.add((String)result.get('customId'));
}
for(Account record: newAccounts) {
  if(maxIds.contains(record.Custom_Id__c)) {
    record.Custom_Id__c.addError('There are more than 2 accounts with this Id.');
  }
}

Note here that we're using the database to come up with our tallies, so make sure that this is called in an after-insert/after-update trigger, not a before-insert/before-update trigger.

As an aside, I'm pretty sure your original problem was == 2, which should have been > 2 (you want to allow two, but no more).

Answer 2

This is in addition to what sfdcfox has answered above with a different approach using more configuration utilizing platform's Duplicate Management feature and least customization (and based on the approach discussed in the comments on his answer) targeted to mitigate any governor limits that you may hit in trigger.

1. Create a custom field (which may not be visible on your layouts when users are creating records), say "Allow Duplicate" on your Account object
2. Set the default value of this field to false
3. Now using Duplicate Management, create a Matching Rule as Account: custom_Id__c EXACT MatchBlank = TRUE and activate the rule
4. Now create a Duplicate Rule, using the Matching Rule above with a condition in filter using the allow duplicate field as: Account: Allow Duplicate EQUALS False and activate the rule
5. Create a Custom Setting and have a value in it populated with value 0

6. Finally implement the logic similar to one below in your trigger:

   Custom_Setting__c cs= [SELECT id, counter__c FROM Custom_Setting__c WHERE name = 'the-name' limit 1];
   if(cs.counter__c < 2) {
       Account a = Trigger.new[0];
       a.Allow_Duplicate__c = true;
       cs.counter__c = counter__c++;
       update cs;
   }
   

A bit of explanation:

This approach uses the platform's Duplicate Management feature along with a combination of custom label and simple trigger logic which keeps track of how many records are allowed to be created with exactly the same value on your custom field. In the trigger, you keep a track of the counter, and if the limit is not yet reached, you set the custom field (allow duplicate) used in the duplicate rules to be true, thus signifying that this record can be created without the duplicate rule to be triggered. And during the course, you update the counter. Once the counter reaches the limit of 2 (in your case), the logic of duplicate rules gets triggered, because you never override it to false.

A key thing to note here is that duplicate rules fire only after the trigger operation, and thus you can utilize this approach. Also, if the logic finds the counter to have been exceeded, no other trigger operation on the record or any soql/dml is further required.