Validating date range

[Baylah]

Hi,

When we create a new "order" it is automatically assigened a date with an auto-entry into the "date received" field. This order may sit around for "X" number of days before it is processed, but that time(except for a highly unusual circumstance) should never exceed 25 days before the order is actually "written-up".

We have a seperate field for "date-written" that is manually entered based on the date a CSR will actually add the detail to the order. Sometimes though, through sloppy entry, a CSR will put enter a ridiculous date that throws off metric we track called "time to write up from received".

I know I can validate this field with a range, but it seems that a validation calculation would be better. The trouble is I can't figure out how to write this.

Anyone have any suggestions?

Thanks,

Steve

[J__]

You may want to provide some more details, from what I am gathering, you want to check the value in the field called date_received to determine the order has been sitting around for 25 days or more.

OK. Go to the field definitions dialog, select the field date_received (or whatever you have named it).

click the options button to display the fields options dialog.

click on the second tab in that dialog called "Validation"

under the "Require:" section, check off "validated by calculation"

a dialog should appear (calculation editor dialog)

enter the following:

( date_recieved +25 ) >= Get (CurrentDate)

this says if the date entered in the date_received field plus 25 days is greater than whatever today is (presumably the date you are entering the order), then return true ; otherwise return false.

click ok.

check off the check box next to "Display custom message if validation fails"

Inside of the text area, enter "The date received is 25 or more days old"

click ok. (this should close the options dialog)

go into browse mode and go to the layout where the date_recieved field is.

test it:

enter in todays date - there should be no message

enter 6/1/05 - should be no message

enter 25 days before - still no message ?

enter 26 days before - should see message

enter 1/1/05 - should see message

enter 12/1/04 - should see message

I don't think this is the exact solution your after, but it gives you an idea how to create a calculated field and display or do something in response to that.

hope that helps,

sincerely,

J__

[LaRetta]

Hi Steve,

Notwithstanding J___'s suggestions which are good - I'm unclear whether you wish to force the User to enter a date within the 25-day range or just know if it IS within the 25-day range. You mention an exception (highly unusual circumstance) which means you can't prohibit illegal entries (via validation) but must instead 'suggest.' And if you can only suggest, they can still mess up.

If the User truly doesn't complete the form (process) within 25 days, don't you want to know that? Do you want the actual date they process inserted or do you want the date processed to revert to within the range (last day?) even if not? Could you explain 'ridiculous' date? How will the system know?

Instead of expecting the User to type the date written (when they complete the detail), you might just do it for them. In this way, no bogus dates can appear. You could use a detail field (one that must be filled in or modified) which, by the fact that it is completed, the form is being 'processed.'

Date Received (Date)

Detail (any field which must contain data to indicate it's being written)

Date Written (Date), Auto-Enter (Do Not Replace, Do Not Evaluate if Referenced Fields Are Empty) with:

If(not IsEmpty(Detail); Get(CurrentDate))

If you want to convert the date so it's always within the range even if completed after the 25-day range, adjust the calculation to:

If(not IsEmpty(Detail);

If( DateReceived + 25 <= Get(CurrentDate); DateReceived + 25;

Get(CurrentDate)

)

)

You can use just about any criteria to determine when an order is 'written.' If the only time the form is further worked on it's being processed, you can also just use an Auto-Enter Modification Date. Well, I hope this gives you additional ideas.

LaRetta