Validation quandry

[merkaba22]

Schedule table as follows;

Date

Name

Time -- entered military style, for example, 0900, 0930, etc.

Duration -- either "1" for 30 minutes; "2" for 60 minutes.

Time II -- Case (Duration = "2";Time ( Left ( Time ; 2 ) ; Right ( Time ; 2 ) + 30 ; 0 ); "") .... so, for example, if Time = 0900 and duration is "2", "0930", etc. -- "0930" and is formated by hhmm, 24 hour, zero before hour, no "separator", etc. to match the format of Time.

Validation for Name required:

Name value for a pre-existing record with same Time/Date is not used for a new record at the same Time/Date;

or

Name value for a pre-existing record with Time II/Date is not used for another record with matching Time/Date.

Ie, if Bill has a pre-exisitng 60 minute appointment at 0900 on July 1, 2010, then a validation message will appear when an when "Bill" is entered into Name for a new record on July 1, 2010 with a Time of either 0900 or 0930.

Self-join with Schedule table with Date=Date, Time=Time and Time = Time II

or

Date=Date, Time ≠Time and Time ≠Time II

Self-join::Name = Name

Seem to validate in all circumstances and I am stumped.

Thanks in advance:)

[comment]

Time -- entered military style, for example, 0900, 0930, etc.

What type of field is it?

And same question about Time II.

Edited July 1, 2010 by Guest

[merkaba22]

Time and Time II are "time" fields

[comment]

Are you sure? If you enter "0900" into a Time field formatted the way you have described, it will show "90000" (900 hours, 0 minutes).

[merkaba22]

Time is a time field, leave data formatted as entered .. actual data is entered from a value list comprising, 0700, 0730, 0800, 0830, 0900, 0930, etc.

[comment]

If you leave it as entered, you are only hiding the problem - the field still contains a time of 900 hours (duplicate the field on the layout and format the duplicate as hh:mm:ss to see this). So you have a start time of 900:00:00 and end time of 9:30:00. There's not much you can do with the data until you fix this.

[merkaba22]

Well, I suspected it might be something like this but since this is a large legacy DB and there a scores of records with Time entered by the VL, I am not sure at all what the "fix" might be and if the validation ideas are correct otherwise;

Do you have a solution in mind?

[comment]

With valid data you could define a self-join of the Schedule table as:

Schedule::Name = Schedule 2::Name

AND

Schedule::Date = Schedule 2::Date

AND

Schedule::StartTime < Schedule 2::EndTime

AND

Schedule::EndTime > Schedule 2::StartTime

AND

Schedule::ScheduleID ≠ Schedule 2::ScheduleID

then validate by calculation =

not Schedule 2::ScheduleID

Note:

1. This assumes no events cross midnight;

2. You should really use PersonID instead of name.

[merkaba22]

That's great -- but the $64,000 question is how does one turn the entered data in Time into "valid" data in these circumstances;ie. since noone would want a VL of times listed as 9:30:00, etc.?

Edited July 1, 2010 by Guest

[comment]

Well, you could use a calculation field (with Time as result type) behind the scenes (similar to the Time II field but without adding anything).

Alternatively, you could replace the field contents with the calculated result, and use an auto-entered calculation to change entry of "0930" into "9:30".

[merkaba22]

Thanks Comment -- this is very interesting and I will attend to this today:)

[merkaba22]

Ok -- set up the relationship based on Date, Name and Schedule_ID as directed above using:

StartTime (using leave data formatted) as a time field auto-calc: Time ( Left ( Time ; 2 ) ; Right ( Time ; 2 ) ; 0 )

and

EndTime (using leave data formatted) as a time field auto-calc based on duration:

Case(ride_duration_calc = 2; (Time ( Left ( Time ; 2 ) ; Right ( Time ; 2 ) + 60 ; 0)); (Time ( Left ( Time ; 2 ) ; Right ( Time ; 2 )+30 ; 0 )))

So that if Time = 0900, then StartTime is "9:00:00" and EndTime (where duration is "2") is 10:00:00;

Validation for Name:

not Schedule_2::Schedule_ID

And still it seems to validate no matter the entry -- what did I overlook?

[merkaba22]

Still don't see it -- tried other relationships and calcs in the validation, but somehow, today, at least, this escapes me.

[comment]

Post your file (preferably, a simplified copy).

[merkaba22]

Thanks Comment, you've been great -- I made a test using your concept and it works -- I will troubleshoot why the main file does not.

Not sure how to attach a FM7 file of the working test model.

Edited July 2, 2010 by Guest

[merkaba22]

The reason my main file is not working using your model is that the records in question are portal records and share the same ScheduleID -- need a new model or create a "record_ID" and run a script through all the portal records and assign a unique value to each ....

[merkaba22]

That did it:)

Much appreciation back!