Prevent Duplicate Child Records for Each Parent Record

Question

I am have a custom object (VIP) with a master-detail to the Contact object. The custom object has 1 picklist field (Type) and I want to make that field unique for each Contact/VIP record combination. In other words the VIP records can have the same Type value as long as they are linked to different Contacts, but that value can only appear once for each Contact.

I've tried the code below, but it prevents me from entering any value in the type field. I'm sure I'm missing something obvious to do what I need. Ideas?

trigger UpdateVIP on VIP__c (after insert, after update, after delete){
 
        Set<Id> contIds = new Set<ID>();
        
        if (Trigger.isDelete)
        {
            for(Type__c vip : Trigger.old){
                contIds.add(vip.Contact__c);
            }
        }
        else
        {
            for(Type__c vip : Trigger.new){
                contIds.add(vip.Contact__c);
            }
        }

    Set<String> setVIPList = new Set<String>(); 
        for(VIP__c objVIP: [SELECT Contact__c, Type__c
                                 FROM VIP__c
                                 WHERE Contact__c in: contIds]){
            setVIPList.add(objVIP.Type__c);
            
            for(VIP__c objVIP2: Trigger.new){
                
                if(setVIPList.contains(objVIP2.Type__c))
                {
                    objVIP2.Type__c.addError('ThisType already exists for this Contact');
                }
                else
                { Rest of trigger

Answer 1

You can try the duplicate rules of Salesforce, I'm not sure what are the limitation there, but 1 approach/trick that will work is:

1.Create unique field in the VIP object . Assume call it 'Contact_Type_Unique' . Make sure to mark the field as unique.

2.Create workflow/process buidler that populate this field with concatenation ContactId + Type.

This way anytime someone will try to create record that violate the duplicate he will get error. Drawback of the approach is that you cannot provide nice error message.

If you still prefer to use trigger, then it should be something like this:

/* doesnt seems delete relevant... {*/

map<Id, set<String>> contact_type_map = new map<Id, set<String>>();


if(trigger.isUpdate || trigger.isDelete)
    for(Type__c vip : Trigger.new){
        contact_type_map.put(vip.Contact__c, new set<String>());
    }
}


for(VIP__c objVIP: [SELECT Contact__c, Type__c
                         FROM VIP__c
                         WHERE Contact__c in: contact_type_map.keySet()]){
                         
    if(contact_type_map.get(objVIP.Contact__c).contains(objVIP.Type__c)){
        trigger.newMap.get(objVip.Id).addError('ThisType already exists for this Contact');
    }
    else{                    
        contact_type_map.get(objVIP.Contact__c).add(objVIP.Type__c);
    }
}
    

Just to clarify the above logic, the map value is set of types per each contact.

After the first vip record, map should look like

{
    'contactId1' => {'Type1'}
}

After second vip from different contact with different type it will be

{
    'contactId1' => {'Type1'},
    'contactId2' => {'Type2'}
}

After third vip record from the first contact it will be

{
    'contactId1' => {'Type1', 'Type2'},
    'contactId2' => {'Type2'}
}

Now, assume in the next iteration the vip is for contactId1 and with value Type1, then it should raise the error because the check contact_type_map.get('contactId1').contains('Type1') should return true.