auto-populate form with Rostered Start Time

[Cameron_Wilson]

Hi in my app, 
I have rosters which can be bulk uploaded by the manager, and then approved by their superior (State manager).
The roster will have the start of shift time and end of shift time.

I also have another form for staff and volunteers to sign in, lets call it the sign in form.
it currently auto populates the staff/volunteer details based on their employee details saved in another table. I would also like this sign in form to autopopulate their start time for that particular time based off the roster provided by the manager.

Rosters have individual submission for the staff member and date (Staff ID is included to identify the staff as well as a date column)
The key column for rosters is roster submission id.

I have been stuck with getting the right formula, i have tried the following:

SELECT(ROSTERS[ROSTER SUBMISSION ID], AND([_THISROW] .[DATE]= ROSTERS.[DATE]),([_THISROW] .[STAFF ID]= ROSTERS.[STAFF ID]))

Errors I get include:
1. Cannot compare Date with List in ([_THISROW].[Date] = ROSTER[Date])

2.Cannot compare Ref with List in ([_THISROW].[STAFF ID] = ROSTER[STAFF ID])

I feel as though I'm close but missing something.

If you need more details, I'll try and explain clearer or post screenshots of tables as it's only got test data.

If anyone could assist I would greatly appreciate it.

Thanks.

[TeeSee1 · Accepted solution]

Try these

 ([_THISROW].[Date] = ROSTER[Date]) ->  ([_THISROW].[Date] = [Date])

([_THISROW].[STAFF ID] = ROSTER[STAFF ID]) -> ([_THISROW].[STAFF ID] = [STAFF ID])

In your SELECT statement the table ROASTER is already identified by the first parameter.

[Cameron_Wilson]

Legend,

Thanks for the assistance, I've been stumbling on that for a while. I think I should read the Select Expression page closer next time.

Thank you very much.

Regards, 

Cameron.