+ Start a Discussion

Help With Validation Rule

I am trying to come up with a solution to prevent bad data from being saved.  I am working with Contracts and I have a custom field called AdID.  It's a text field and a Unique Identifier.  What I want to do is prevent a user from saving a contract record if the time falls between a current one with the same AdId.  For example



contract abc from 2011-04-01 to 2011-05-01 with AdId123 with contract amount $100
contract xyz from 2011-04-15 to 2011-05-01 with AdId123 with contract amount $200.



I would want a Validation Rule to prevent the saving of contract xyz since the dates overlap and contain the same AdId.  I can't think of a good way to do this.  Any help is greatly appreciated


You can achieve this using a trigger, possibly a before insert on Contract.

I don't think configuration alone provides any mechanism to achieve this kind of functionality.


Try something like this -


trigger DupCheck on Contract (before insert) {

List <Contract> lstC = new List <Contract> ();

Integer flag = 0;

Date currED = null, expED = null;

string strAdId = trigger.new[0].AdId__c;

expED = trigger.new[0].StartDate.addMonths(trigger.new[0].ContractTerm);

currED = expED.addDays(-1);

lstC = [ select id, ContractNumber, StartDate, EndDate, AdId__c from Contract where AdId__c = : strAdId];

for (Contract c : lstC)
 if ( (trigger.new[0].StartDate >= c.StartDate && trigger.new[0].StartDate <= c.EndDate) || (currED >= c.StartDate && currED <= c.EndDate) )
    if (flag != 0)
    trigger.new[0].AdId__C.addError('This ApId already exists for overlapping duration.');