I will do my best to make this clear... (I have attached the a picture of the relationships window to assist)
I have a "audit" DB that has a "user" table and a "discipline" table among others. The audit layout is attached to the audit table to capture all of the audit information.
I have a user layout with a portal to a many to many join table to join the disciplines to the users.
Right now, I have a start-up script setting a global variable to the users name, and each time a new audit record is created a script places that username in the name field on the audit table. So when the user starts the audit the username is already added.
What I am looking for is a way to have the discipline drop-down filtered to only show me the disciplines that are assigned to the user. I was able to make it work for two other fields, but their relationship was direct (show 3 and audit 2 in the picture)
I am getting stuck at creating the relationship and the value list that sees the username as populated by the variable, and compares that to the disciplines linked in the join table. The join table is ID numbers. I even tried to add a username field in the join table, but it didn't work.
Thank you in advance