4 Replies Latest reply on Jul 14, 2017 2:04 AM by guit4eva

    Unique Dropdown Selection

    guit4eva

      Hey guys,

       

      I have a table of users and a table of trades, for example:

       

      [Users]

      - User 1

      - User 2

       

      [Trades]

      - IT

      - Chef
      - Mechanic

       

      What I would like to do is have a dropdown of Trades on the User layouts - which I have now by creating a relationship based on a foreign key in Trades table - however, it requires the following:

       

      1) The trade can only be selected once (to avoid duplicate entries)

      2) Once selected, it must be removed from the dropdown

       

      Can anyone point me in the right direction to achieve this? Will be much appreciated!

       

      Thanks in advance!

        • 1. Re: Unique Dropdown Selection
          philmodjunk

          I have a file that teaches people how to do a dwindling value list, but what you describe spawns lots of questions.

           

          If I read this right, any given user may have one and only one trade and if user #1 is a "Chef" no other user may be a "Chef". If so, I don't see the purpose to two tables when a field for trades with a unique values validation can do the job.

           

          But maybe there is more to this that you have not described. The following file demonstrates a number of different conditional value lists. One of them is a dwindling conditional value list. Each example comes with detailed documentation on how to set it up.

           

          Adventures in FileMaking #1--conditional value lists

          1 of 1 people found this helpful
          • 2. Re: Unique Dropdown Selection
            guit4eva

            Thanks so much for your reply. Will definitely check out your file

             

            To clarify my question:

             

            A user can (and ideally should) have more than one trade. The problem I'm facing is that, once the user selects a trade via the dropdown - that trade is still present in the dropdown and thus can be selected again (causing duplicates), for example:

             

            [User_ID] [Trade]

            [1] [Chef]

            [1] [IT]

            [1] [Chef]

             

            Hope that makes sense?

            • 3. Re: Unique Dropdown Selection
              philmodjunk

              Then I suggest that you modify your data model.

               

              Trades----<Trade_User>-----Users (---< means "one to many" )

               

              Trades::__pkTradeID = Trade_User::_fkTradeID

              Users::__pkUserID = Trade_User::_fkUserID

               

              You select Trades for a user by creating records in Trade_User that link the current user to different trades. A portal to Trade_User with _fkTradeID placed in the portal and formatted as a drop down list or pop up menu is a simply way to get started with such a "many to many" relationship where Trade_User functions as a "join" table between Trades and Users.

               

              The dwindling value list example from my teaching file can be set up to function with _fkTrade_User in the above data model. You can also set up an auto-enter calculation on an added field in Trade_User with a calculation such as:

               

              _fkTradeID & "|" & _fkUserID

               

              so that you can set up a Unique Values field validation on it to prevent selecting the same trade for a given user more than once, but the dwindling value list precludes that from being possible.

               

              There are also ways to set up what looks like a set of check boxes with one check box for each possible Trade. Since they act like check boxes, you can't select a trade twice. At the end of the file that I shared, there's a link to a Adventures #2 file that illustrates this checkboxes technique among other data selection tricks.

              1 of 1 people found this helpful
              • 4. Re: Unique Dropdown Selection
                guit4eva

                You are a legend, thanks so much! You have some really handy tips in those two files, really appreciate you sharing them. I've got a perfect dwindling dropdown list now, woot woot!! Thanks a ton!