Display error message if same record exist with combination of some fields?

Question

I have taken three fields from case object called AccountName, IntegrationTo__c and SfType__c (checkbox).

I have created one case record with a specific combination of three fields.

For the second time I create a record with that combination, then display error message only if SfType__c is true in first record otherwise allow for record creation.

trigger AvoidDuplicate on Case(before insert)

{    

    set<string> newNameSet = new set<string>();
    newNameSet = new set<string>();

    set<string> newEmailSet = new set<string>();

    set<string> dbNameSet = new set<string>();

    set<string> dbEmailSet = new set<string>();

    for(Case newLead : trigger.new){

        newNameSet.add(newLead.Account.Name);

        newEmailSet.add(newLead.IntegrationTo__c);

    }

    for(Case dbLead : [select id, Case.Account.Name, IntegrationTo__c from Case where IntegrationTo__c IN: newEmailSet OR Case.Account.Name IN: newNameSet]){

        dbNameSet.add(dbLead.Account.Name);

        dbEmailSet.add(dbLead.IntegrationTo__c);

    }

    for(Case newLead : trigger.new){

        if(dbNameSet.contains(newLead.Account.Name) && dbEmailSet.Contains(newLead.IntegrationTo__c))

            newLead.addError('You are inserting Duplicate Case');

    }

}

i wrote this this not checking the account field name and integration__c values only with combination of account and integration__c shows error

Answer 1

you don't need a trigger to achieve this.

1. create a text field and mark it as unique
2. do a workflow / process with a field update action on Case creation to update this text field with the combined value of the 3 fields.
3. then the next time you try to insert another case with the same combination in those 3 fields, the unique constraint on the field will automatically prevent it.

---

UPDATE - an example formula for your field update to combine the 3 fields could be simply concatenating them

Account.Name & IntegrationTo__c  & IF(SfType__c, 'TRUE', 'FALSE')

like @Adrian mentioned, this will work when all 3 fields are mandatory / required and will have values all the time.

if any of the fields can be blank, then you need to consider expanding the formula with null checks and inserting default values in case of blanks, etc..

Answer 2

@Siva:It will check once

trigger CaseDuplicateAcc on Case (before insert, before update) {

 List<case> caselist = [select id,accountid,integration__c,account.name,Check__c from case];

  for(case c:Trigger.new)
  {
    for(case cc: Caselist)
    {
      if(c.Check__c == false)
      {
      if(c.account.name==cc.account.name || c.integration__c == cc.integration__c)
      {   
         c.adderror('Already exist same account name and integration in case object');
      }
      }
    }
  }

}