1 Reply Latest reply on Apr 11, 2012 8:58 AM by philmodjunk

    Need help with a validation formula



      Need help with a validation formula


      I have two databases: The first contains records of "Events", and contains fields with workers scheduled to work at each event. Each worker is in a separate field (Worker1, Worker2, Worker3, etc.).

      The second database contains records of dates in which specific workers are unavailable. To give a quick example: If 25 workers are unavailable on June 1st, there would be 25 different records in this database with the 25 different names in one field, and the June 1st date in another field in all 25 records. 

      Is there a validation formula I could use in the "Events" database which would prevent workers from being entered into those fields if they have that Event's date listed as them being unavailable? 

        • 1. Re: Need help with a validation formula

          Each worker is in a separate field (Worker1, Worker2, Worker3, etc.).

          Not a good idea. Use separate records in a related table for each worker, not separate fields. You may want to check out the Event manager starter solution that comes with Filemaker or this demo file for ideas on how this might be done: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          Please note that the demo file is not intended to be a full up demo on event management, but since it uses event management as the model fo rmany to many relationship interfaces, it still has some techniques you may find useful.

          A validation rule using a relationship that matches by event date and worker name could be used for you fields, but this would require separate relationships for each of your worker fields. That can be done, but it produces a very complex design that will be difficult to work with.