I am trying to tidy up my app by adding the appropriate restrictions and I am not sure how to do the following:
A Worker selects his, or another Worker’s, name from the list (table Workers).
That displays the Worker Details form which includes related TimeSheet records.
He then presses New, from the related table, to add a new timesheet entry.
That displays the Timesheet Input form.
The first field is Work Date (defaults to Today() but can be changed).
The second field is AssignmentId which I want to have as a dependent dropdown based on the Work Date selected and should show all the Assignments so far raised for the selected Date Worked.
This is what I am struggling with. How do I limit AssignmentId to show only those Assignments allocated to the selected Work Date and if it doesn’t exist I want him to be able to create an Assignment?
Following is an overview of the structures in case you need to know but I’ve left it till last cause it makes the question so long:
Table Jobs - Job Id and basic Job details.
A job can require one or more employees for one or more days
Table Assignments - One assignment per day per job.
Can be created when scheduling (i.e. the scheduler will create the Assignment and possibly an entry in the Timesheet table with a starting time for each of the Workers assigned to that job);
or if not scheduled the Worker needs to be able to create an entry in the Assignment table when the timesheet is entered
Table Timesheets - One entry per employee per AssignmentId.
When an employee adds a timesheet entry he needs to select a date and, if one already exists, an Assignment.
If one doesn’t exist then he needs to create an Assignment.