date validation calculation

[wizcoder]

I need help with date validation calculation.

I have fields itemID, start date and end date.

ItemID can be duplicated.

I need to make sure current start date and end date range do not crash with preious item's

I have another set of dates start date and end date form a relationships, the check need to

validate with this a well.

[CobaltSky]

Hmmn... - this looks a tiny bit familiar?!

To validate based on date range in the way you've described, you will first have to establish a calculation that reflects the range of dates between the start and end dates entered, then a relationship that matches the calc to itself.

Examples of calculations that can be used for such a purpose can be found in the range relationship demos at:

http://www.nightwing.com.au/FileMaker/demos5.html#d5

- or using the technique pioneered by Mikhail Edoshin, as described at:

http://www.onegasoft.com/tools/smartranges/

In order to isolate the ranges as they relate to the item ID, it's likely that you'll have to construct your range calc to concatenate the ItemID with the range values. Howsoever that may be, once you have an appropriate range relationship in place, you will be able to specify validation-by-calculation options for both the start and end dates using a formula along the lines of:

not Count(RangeRelationship::RangeCalc)

to incorprate a second check (ie via a second relationship) simply set up a second range-based relationship (eg to the other file) and then add a further test to the validation calc expression. Eg:

not Count(RangeRelationship1::RangeCalc) and not Count(RangeRelationship2::RangeCalc)

- which will cause validation to fail if the start date, the end date or any of the dates in-between overlap with any existing periods already entered into either database.