Dependent Dropdown to create new record if one doesn't exist

[Griff]

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.

[WillowMobileSys · Accepted solution]

Griff:

How do I limit AssignmentId to show only those Assignments allocated to the selected Work Date

This implies you have some date on the Assignment record? I’ll call it Assignment Work Date.

Assuming that the Assignment column in your Jobs table is of Ref type, you would add an expression to the Valid_If property of that column definition.

Griff:

Table Assignments - One assignment per day per job.

I’ll also presume based on the above statement that if an assignment is already assigned, it should not be shown in the dropdown. I.e. show only assignments that have the Job Work Date = Assignment Work Date AND are not already assigned to another job BUT are assigned to THIS job.

The expression would be something like (has not been tested):

SELECT(Assignments[AssignmentID], 
       AND([Assignment Work Date]  = [_THISROW].[Work Date],
                NOT(IN([AssignmentID], 
                       SELECT(Jobs[Assignment], 
                              AND([Work Date] = [_THISROW].[Work Date],
                                        [JobID] <> [_THISROW].[JobID]
                              )
                       )
                    )
                )
       )
)

Note: This is just an example based on your post. I am not perfectly clear why there would be a date on the Assignment record if you plan to re-use/re-select it on other jobs.

Griff:

and if it doesn’t exist I want him to be able to create an Assignment?

If you have Add capability turned “on” in the Assignments table you should automatically get an Add option in the dropdown (see image). It will launch the Assignment form to enter a new row and upon Save should automatically assign that item to the Assignment column in the Jobs record.

[Griff]

Awesome! Thanks. Some of your assumptions were slightly incorrect but it look very little to get it working. Appreciate your help!!!