9 Replies Latest reply on Jan 5, 2009 6:48 AM by tangentkid

    Value List and ID Key conundrum

    tangentkid

      Title

      Value List and ID Key conundrum

      Post

      Apologies in advance for my newbie question.....

       

      First the background: 

      I am building an invoicing/customer database that contains two tables: Company and Person.

      The Person table has fields for the person's name, contact details, the company ID for whom they work plus an ID for the person.

      The Company table has fields for the ID of the person who deals with invoices and another ID for the person who deals with deliveries.

       

      When browsing a Company record I'd like to present the user with two drop-down menus of the names of all people who work for the company to allow them to select which person deals with invoices and which with deliveries. This I can acheive by creating a relationship between the Person's Company ID field and the ID field in the Company table. A Value List can then be defined to contain only the Person Names that are related to the current Company ID. However, I don't have a field in the Company table for the invoice Person's name, only their ID. I then resolve and display their name by a relationship between the Company: Invoice Person Id and the ID for the individual in the Person table. 

       

      I can't quite figure out how best to solve this. It would appear that I need to use a drop-down menu of the Person IDs instead which, when selected, will allow the Company layout to resolve the person's name from the Person table. However this is extremely unhelpful for the user!

       

      If I were to use the Person's Name as the key instead of the ID then this would solve the problem but I'm sure that I'm going to come accross this again and again (e.g. Product Names & IDs when I build the invoicing database) so I thought I should find out how to do it properly from the outset and retain numerical IDs as keys for all tables.

       

      Thanks. 

        • 1. Re: Value List and ID Key conundrum
          Jens Teich
             Value lists can have two columns. You need person ID as left column and person name as right column. You can configure the value list in a way that dropdown menues display only the name but store the ID.

          Jens
          • 2. Re: Value List and ID Key conundrum
            tangentkid
              

            Hi Jens,

             

            That's great. Thanks very much for your help. I've tried this and it works great. Just one other questions though:

             

            The field that the user now need to click on is the ID value of the person rather then the resolved name. While this works OK it is rather odd - to click on the ID and then have a list of names pop-up. Is there any way to have the value list of names pop-up when the user clicks on the displayed name and then have the ID field update when a selection is made? Or any other ingeniuos way to acheive the effect that I'm after. If not then don't worry.

             

            Thanks again for your swift assistance!

             

            - Andy 

            • 3. Re: Value List and ID Key conundrum
              Jens Teich
                 Are you sure that your left column of the value list is the ID and the right is the name? If you check additionally the box 'show only second value' the names will be displayed for the user but the ID is stored invisibly.

              Jens
              • 4. Re: Value List and ID Key conundrum
                raybaudi
                  

                Hi Jens


                this isn't completely true...


                It depends from the way that you format the field holding the value list.

                 

                Drop-down list will show the first value, when exit the field.

                Pop-up Menu will show the second value, when exit the field.

                 

                But both ways store ONLY the value of the first field.

                • 5. Re: Value List and ID Key conundrum
                  Jens Teich
                     You are right. This is because the difference between both is that you can edit the values of drop down list. So they have to show the left value which might be edited and overwritten with a new value which is not part of the value list.

                  Jens
                  • 6. Re: Value List and ID Key conundrum
                    raybaudi
                      

                    Sorry Jens

                     

                    this is also incorrect:

                    > you can edit the values of drop down list

                     

                    It seems that you cannot edit a value of a pop-up menu, but it isn't the true.

                     

                    Try to make a choose and, while the focus is still on that field, press CANC and paste the today date. ;)

                     

                    • 7. Re: Value List and ID Key conundrum
                      Jens Teich
                         FileMaker unfortunately is very inconsequent with value lists. Radio buttons for example are supposed to show only one value but with SHIFT you can add a second value. I consider the behaviour that you describe beeing buggy.

                      Jens
                      • 8. Re: Value List and ID Key conundrum
                        swj
                          

                        So true!

                         

                        But it hasn't changed through 9 versions over some 20+ years! HaHa!

                        • 9. Re: Value List and ID Key conundrum
                          tangentkid
                            

                          Hi Jens, Ray, Daniel, Scott

                           

                          I have indeed got the Value List set up just as you describe and operation is also just as described i.e. if the field is a Drop-Down list then the First value is left on the page after making a choice, if the field is a Pop-Up Menu then it is the Second value that is displayed. How bizarre!

                          It has however allowed me to solve the problem by using a Pop-Up menu instead.

                           

                          Thanks to all!

                           

                          Cheers,

                          - Andy