+ Start a Discussion

Validation Formula: Modifying the ISNEW to everytime a record is edited

We currently have a formula that does not allow the user to enter a start date less than the current period. The problem is, this formula below works with the ISNEW. Users are figuring out that if they save the new record, the next time they edit they can put in any date they want. How can I get this formula to work evertime the record is edited?



And(If (year(Start_Date__c)*100+month(Start_Date__c)<year(today())*100+month(today()),True, False),isnew())


Thank you in advance!


Steve :-/Steve :-/

Which object are you doing this on and how are you defining what a "period" is?


This is on a custom object. The current period is basically the month we are in or higher.


We are currently in May 2011. You can add any date that is within the May 2011 date range or June 2011, Aug 2011, etc. If you try to enter 4/30/11 it should prevent you.

Steve :-/Steve :-/

Just to rule things out is there a reason why you don't evaluate the Start Date and compare it to TODAY() and throw an error if it is


Start_Date__c < TODAY()  


Start_Date__c < (TODAY() - 30) 

or some thing like that? 

If you don't already have one you could throw a custom field in there to exempt Closed, Completed, Inactive records from the VR





Steve :-/Steve :-/

Are you all set or do you still need help with this?


I didn't get notified of your previous suggestion earlier.


I can't use if less than TODAY.


Even though today is May 26th, they should be able to enter a start date of 5/1/11 but not 4/30/11, but once today is June 1st, they can't enter any May 2011 dates anymore.


They can enter any start date as long it is the current month.


Is this starting to make sense?

Steve :-/Steve :-/

So basically any day within the current month is okay, but not the previous month?


Yes, and they can also pick any day in the future needed. It is just locking them out of the previous month we are concered about.



Steve :-/Steve :-/

Do you have another field in place that closes the record?  Like an End Date, Status, Open/Closed, etc?


The only field to close this out is Stop_Date__c.


There is a Status__c but it a formula based on the start and stops dates. The values returned are Active, previous, expiring, and future based on the two date fields.

Steve :-/Steve :-/

Okay, give this a try.  Leave your original VR in place as-is, and add a new VR like this





That should close the loophole.  


PS.  You owe me a beer!

Steve :-/Steve :-/

Are you all set with this or do you still need help with anything?  


Hey there! I am relooking into this project again. 


The validation you entered above will not work as they may enter one date in the current month but it may get pushed back to another date.


I know...this sounds simple but it is not!


Any other ideas? 

Steve :-/Steve :-/

Give me 6 or 7 months and I might be able to come up with a fix for you.