0

I has objects Contracts__c with fields(Start_Date__c and End__Date__c) and related obj Actor__c. When i insert new contract i trying to check does the actor already has a contract on specified dates. I tryed before insert trigger but i am stuck. That is my quer: **SELECT** id, Name, Start_Date__c, End_Date__c, Contact__r.Name **FROM** Contract__c i dont know how to set inserted Start/End dates value to variables and compare them with all existed records.

public static void beforeContractInsert(List<Contract__c> newContracts){
  Date sDate;
  Date eDate;
}
Improve this question
1
  • 3
    Hi @SfAsker, welcome to SFSE. It sounds like you're struggling with some of the core mechanics of Apex triggers. Trailhead has some great modules to get you started. What you want to do is achievable in a trigger, but you'll need to be conversant with some ideas about bulkification and trigger mechanics. There is a declarative way to do this, though, and I'll post an answer with a suggestion in a bit.
    – David Reed
    Commented Oct 21, 2018 at 21:33

2 Answers 2

Reset to default
1

Here is a way to check it in a before insert trigger:

  1. Iterate over the List of new Contracts and get the Ids of the parent Actor in a Set.
  2. Query and fetch the existing Contract records related to these parent Actors into a list.
  3. Iterate over the existing Contracts and new Contracts using a nested for loop.
  4. Check if the new contract has a Start Date and End date clashing with the existing contract. Check the following code snippet (existingContracts is a list retrieved at step 2)

for (Contract__c existingContract : existingContracts) { for (Contract__c newContract : newContracts) { if ( newContract.Actor__c == existingContract.Actor__c && ( ( newContract.Start_Date__c >= existingContract.Start_Date__c && newContract.Start_Date__c <= existingContract.End_Date__c ) || ( newContract.End_Date__c >= existingContract.Start_Date__c && newContract.End_Date__c <= existingContract.End_Date__c ) ) ) { newContract.addError('The Actor already has a contract on specified dates!'); } } }

Improve this answer
2
  • You don't need to perform a combinatoric search (nested for loop) here. It is generally more efficient to put the existing Contracts into a Map keyed on the Actor__c value, as you can then access only the relevant existing Contracts as you loop through new Contracts.
    – David Reed
    Commented Oct 22, 2018 at 13:24
  • In that case, the Map will be in the format Map<Id, List<Contract__c>> (Actor Id vs list of Contracts) as there can be multiple Contracts per Actor. It will reduce the if condition to compare the Actor Id but we'll require second for loop to iterate over the map values. So I provided a straight-forward solution. Please correct me if I'm wrong.
    – Sanket Mandhare
    Commented Oct 22, 2018 at 13:42
0

I want to propose an alternate, fully declarative approach, since you seem uncomfortable with building the Apex out further.

What you're doing is basically defining a composite key that must be unique across all instances of Contract__c: every record must have a unique combination of Contact__, Start_Date__c, and End_Date__c.

The declarative way to do this is to create a custom field (call it, say, Unique_Key__c). Make it a Text field, length 255, required to be unique and case sensitive.

Then, build a Process or Workflow Rule on the Contract__c object. Have your process run on object creation only, and add an action to populate the Unique_Key__c field with the concatenation of Contact__c, Start_Date__c, and End_Date__c. (This would be a Formula in Process Builder).

Because of the uniqueness constraint on the field, the system itself will prevent you from creating duplicate records (where duplicate means the exact dates are the same).

The resulting error message isn't tremendously user friendly, but it gets the job done.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .