How to create a validation rule referencing two lookups

Question

I would like to create a validation rule or similar that controls if a record already exists with the same two lookup values, that the start date has to be the day after the end date of the previous record. Each lookup has a total of 28 combinations so A - b,c,d,e,f,g. B -a,c,d,e,g,g etc

There is also a master-detail relationship with another field which has to be the same value for the above relationship.

I'm struggling to come up with the rule.

Answer 1

Here is a sketch of what your trigger will do

trigger YourTrigger on ObjectWithLookups__c(before insert) {
    Set<Id> idsForLookupA = new Set<Id>();
    Set<Id> idsForLookipB = new Set<Id>();

    for (ObjectWithLookups__c record : Trigger.new) {
        idsForLookupA.add(record.LookupA);
        idsForLookupB.add(record.LookupB);
    }
    List<ObjectWIthLookups__c> existingRecords = [
        SELECT Id, LookupA, LookupB
        FROM ObjectWithLookups
        WHERE LookupA IN :idsForLookupA
        AND LookupB IN :idsForLookupB
    ];
    Set<String> uniquePairs = new Set<String>();
    for (ObjectWithLookups__c existingRecord : existingRecords) {
        uniquePairs.add(existingRecord.LookupA + '-' + existingRecord.LookupB);
    }
    for (ObjectWithLookup__c newRecord : Trigger.new) {
        if (uniquePairs.contains(newRecord.LookupA + '-' + newRecord.LookupB)) {
            newRecord.addError('This combination already exists');
        }
    }
}

You'll have to modify it for your objects/fields, and this should also follow the trigger/handler pattern