Filtered Value List for Drop Down
I'm trying to develop a clean method for a drop down list to assign a sales rep to a customer account. I don't want to do a pick list pop up type layout, and I really like type ahead.
I would like any account to be able to also be a sales rep by way of having a sales rep code (2 letters) assigned to it. If there is no sales rep code, then the account is not a sales rep. Note: I would be willing to have a true / false field "is sales rep" if that is required.
The sales rep code will be the link / relationship between the account and it's sales rep (another account). This is so that I can use type ahead features as opposed to using a foreign key linked to the account id.
Also, if the sales rep code changes, I want the change to be reflected automatically in the account which is using that sales rep. (referential integrity)
How do I set up a relationship(s) so that I can have a value list which only shows non-empty sales reps? I'm guessing I need some self joins since there is really only one table I'm talking about here (accounts). The problem I am having is that I am so used to using access and a simple SQL query, where in filemaker I think I need to make this happen via relationships.