Hi all,
I'd love some guidance on the following:
I have a form [Dependants] and there can be multiple records in the [Dependants] form that link to a master form [Main Member] through a unique value that I've created "Member Number"; so a many to one relationship.
I have another form where I want to have the user see and select (one by one) all the Dependants linked to the one Main Member, however as they select one (easy to show them via lookup field), I want it to disappear from the next lookup field on the same page I have. Example: There are 3 Dependants names Harry, Sally and Tom. I'm creating 3 lookup fields on this form in question. When I select Harry in the first lookup field, I'd like to only see Sally and Tom in the 2nd lookup field. If I select Tom in the 2nd lookup field, I want to see only Sally in the 3rd lookup field.
I'm using lookup fields as it seems easy to link to the data, but I'm having a tough time achieving what I'm describing. Oh and the reason I want to do this is to ensure the user does not by accident select the same name twice (and yes, I could possibly do a check with an alert, but I thought the disappearing names would be better).
Any suggestions?