I'm starting on a solution to schedule the use of resources in our lab. Ive attached a file which attempts to use relationships and a calculation field to detect scheduling conflicts. This appears to work, but I'm looking for input on whether this is the best approach to use.

Also, I'm looking for a way to use field validation to disallow commiting a record if it conflicts with an existing record. Any ideas on how to validate this w/o the use of a script?

FileMaker Version: Dev 7

Platform: Mac OS X Panther

schedule.zip

Reed,

Your relationships to test for conflicts seems like a very good design to me, I don't see how it can be done better without an OR in the relationship creator.

For the validation, I added a validation calc on the "id" field:

conflict = 0

then turned off the "Validate only if field was modified" option in the calc dialog.

Turn off "allow override" and add a nasty custom message and you're there with no script.

I tried this validation technique as well, but it seems that the validation is based on the count() functions prior to record commitment. So it doesn't catch a conflitct if there is only one. If you change the condition to count>0 instead of >1 then it works, but if you try to edit the record, it conflicts with itself. Any thoughts on how to get around this? (Again, without the user have to execute a script.)

Dana

I didn't notice that.

You could try adding not-equal to your own schedule id in each of your test relationships and your own row won't be in your conflict-finding portals?

Thanks, I wonder why I didn't think of that?