+ Start a Discussion
WPCMSWPCMS 

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!

 

SteveMo__cSteveMo__c

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

WPCMSWPCMS

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.

SteveMo__cSteveMo__c

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()  

or 

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

 

 

 

 

SteveMo__cSteveMo__c

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

WPCMSWPCMS

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?

SteveMo__cSteveMo__c

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

WPCMSWPCMS

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.

 

Thanks!

SteveMo__cSteveMo__c

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

WPCMSWPCMS

The only field to close this out is Stop_Date__c.

WPCMSWPCMS

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.

SteveMo__cSteveMo__c

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

 

AND(
NOT(ISNEW()),
ISCHANGED(Start_Date__c)
)

 

 

That should close the loophole.  

 

PS.  You owe me a beer!

SteveMo__cSteveMo__c

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

WPCMSWPCMS

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? 

SteveMo__cSteveMo__c

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