Hi. How can I filter a drop down list by another value in the same row? I can achieve this with 2 drop downs and using a 'dependent drop down' - but I want to have the 1st value as readonly in the row?
As an example, I have a table of Rotas as follows:
ROTA_ID RotaName OnDuty
1 Rota1
2 Rota2
And a list of Rota Members:
ID ROTA_ID Name
1 1 John
2 1 Fred
3 2 Dave
3 2 Sid
Now I would like to have my edit page for the Rotas table have a drop down list to chose the member on duty, but do not want the Rota_ID or RotaName fields to be editable, they would be set at initial add. So if I was editing row 1/Rota1 then the only options for the OnDuty member should be John or Fred.
How do I achieve this? It seems like it should be simple but cannot figure it out. It basically needs a custom WHERE for the dropdown that refers to an existing value in the same row, but I'm not sure you can include that in the WHERE?
Thanks.
Jim.