7 Replies Latest reply on Sep 9, 2011 12:44 PM by philmodjunk

    Filtered Value List for Drop Down



      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.