Validation by Unique Value based on record Creation Date
I'm creating a database that will record information for employee work hours for the duration of a specific project. The database is comprised of two tables: the "personnel" table (with pre-entered info such as name, employee number, etc, for about 500 potential employees ) and the "work detail" table (where data such as start time, finish time, job description, etc, will be recorded each day of the project duration). Of the 500 available employees, some will work for the entire duration of the project, others will skip some days, and others may never work at all. Data will be entered in the "work detail" table each day of the project to reflect which employees worked that day, how long they worked for, etc.
I would like to validate the data entry for an employee as it is called into "work detail" from the "personnel" table whereby, for any given record creation date (i.e., work day), an employee can only be entered once. The "unique value" option in the field option dialog won't work for me since an individual employee will often work on more than one date and the information recorded in the database will span a period of several months. I would also like to include a custom message with an option to override the validation if possible.
Relevant field names are: "employee_name", "employee_id", and "date" (auto entered from creation date).
What would be the easiest way to do this? Thanks so much for any help. Sorry if this description is too long, I'm very new to this as you can probably tell!
PS Beginner, Using Filemaker Pro 10 Advanced with Windows Vista