Staff and Projects scenario
I have a scenario where I want to be able to select a member of staff from a dropdown and apply their name to a specific project. I'm performing this in a portal based on the project table. I need their PK ID to appear on the project table as a FK ID so I can use it for other joins further down the line.
What's happening is that the staff name is populating to the field in question, so to the user everything would look ok. However, I'm having issues getting the staff ID onto the project table. Simple right?
I would have thought that in the value list set up I'd use values from Staff:PK, also display values from Staff:Name and Show only values from second field.
For some reason this is not working for me. The dropdown correctly displays the names but upon clicking away the staff name is replaced instead by the staff ID.
What I need is both the StaffID (FK) and Staff Name to appear on the project table.
EDIT: One solution which would help further down the line on other work would be to simply get the FK of the staff member as a variable and Set Field with this value on the Project table. However, I cannot find out for the life of me figure out how to tell Filemaker to go fetch the FK from the staff table for the previously selected staff member.
So far I've got Set Variable [$StaffID;Value;Projects:StaffID] based on the logic that it would look at the selected staff (now listed against the project) and go fetch the FK. This isn't working obviously.
This seems too simple so I must be missing something dumb!
Thanks in advance