5 Replies Latest reply on Sep 6, 2011 10:27 AM by philmodjunk

    Using the foreign key to access large drop down menus

    user14040

      Title

      Using the foreign key to access large drop down menus

      Post

      I have been trying to figure out how to write a script that will let me use the original field  to access large drop down menu instead of the foreign key.

       

      Example:

       

      I have a large customer list and customer equipment list in a drop down menu what I do now is use a barcode system with the foreign key to access equipment fast.

       

      On my layout I have a text field set as the foreign key, as long as I know what the foreign key is everything works perfect, I enter the foreign key and the exact machine pops up, what i want to do is change from using the foreign key and use the equipment serial number, so instead of using a foreign key of 10 I could instead use a serial number of 97898FT1.  

       

      I am thinking I have to use a global field but not sure how to do it, does anyone have any ideals.

       

      Thanks

        • 1. Re: Using the foreign key to access large drop down menus
          LaRetta_1

          Create a value list using values from field.

          Select the ID in the left pane and also 'show values from second field' selecting the serial number in the right pane.  Below, enable 'show values only from second field.'

          Attach a pop-up menu instead of a drop-down list and point to this value list.  It then displays the serial for selection but inserts the ID into the equipment ID field.

          • 2. Re: Using the foreign key to access large drop down menus
            user14040

            I have the drop dowb menu working fine, the problem is the amount in the dropdown menu.  Using the foreign key in the FSR table enable me to go directly to the serial number in the drop down menu  once a customer is chosen.

             

            FSR table links to Customer and Equipment table

            The foreign key that is generated in the FSR table makes it easier to choose what equipment I want in the drop menu.  What i want to do is not use teh foreign key but use serial number to access the menu.

             

            FSR foreign key is 10 represents serial number 987654OK in the machine table

             

            What i want to do instead is type in the serial number 987654OK instead of the foreign key of 10  to access the serial number of the drop down menu.

             

            I tried adding a global key in the FSR table and creating a relationship between global field and serial number in the machine table but that did not work when I added new records it did not show any data, it also cleared out the serial field on the previous records. 

            • 3. Re: Using the foreign key to access large drop down menus
              LaRetta_1

              We need to see your file.  Upload it somewhere (a free site such as 4shared.com if nothing else) and then provide a link to it here. 

              • 4. Re: Using the foreign key to access large drop down menus
                user14040

                FSR Table 
                Customer Table 
                Machine Table 

                On the image attached top left it say Facility this is my Customer Table 

                Below on the right it says serial number field is blue. Right above the Serial number is a field with the number 1 this number is also right next to the serial number it is the FSR table foreign key. 

                What i do is first choose the Facility [Customer] which give a list of only that customers equipment. The tan box with the number 1 in it 
                I type in the foreign key in this case the number 1 without using the 
                drop down menu. When I put the number 1 in the tan box it shows me the right serial number [95864fd]. 

                What I am trying to do, if it is even possible is to type the serial number in the tan field 95864fd instead of the number 1. 

                 

                foreign key

                • 5. Re: Using the foreign key to access large drop down menus
                  philmodjunk

                  Sometimes a scripted approach provides a more userfriendly option for dealing with large value lists while maintaining correct use of primary and foreign keys in you relationship.

                  This demo file demonstrates several such options:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html