1 2 Previous Next 19 Replies Latest reply on Feb 16, 2011 11:08 PM by Fagreement

    list box dispaly text and store ID

    Fagreement

      Title

      list box dispaly text and store ID

      Post

      I have a form that is perfect except I have a list box to a related table/value list.

      I want to store the serialID in this field but show the text of the value list and not the ID. is there a way to do this?

        • 1. Re: list box dispaly text and store ID
          Sorbsbuster

          When defining the value list tick the box to show values from another field, and then tick the option to only show the values from the second field.

          • 2. Re: list box dispaly text and store ID
            Fagreement

            that's what I did. but the problem with this solution is that if i show the value from the 2nd field it's this value that is stored while I need to store the 1st one.

            to summarize I nee dto show the 2nd field but store the 1st one.

            • 3. Re: list box dispaly text and store ID
              Sorbsbuster

              The ID is the first field, and the text description is the second field?  Sorry - I've never seen that behaviour before.  Every time I've used that feature it correctly enters the ID.  (Although I always place the associated field of text decription in front of the ID field, so it looks like it accepted, stored, and displayed the text field.)

              • 4. Re: list box dispaly text and store ID
                Fagreement

                Sorry but the basics of a relational database is whenver you have a main table and a value list table you store the ID in the main table and not the text, cause whenver you want to change the description you change it in one place (value list table) and not on each record of the main table.

                I'm a developer since 1999 (but new on filemaker) and it was always like that in all the DBMS.

                • 5. Re: list box dispaly text and store ID
                  raybaudi

                  "if i show the value from the 2nd field it's this value that is stored"

                  No, the value stored is always the one of the first field, the value of the second field is only shown.

                  • 6. Re: list box dispaly text and store ID
                    Sorbsbuster

                    "whenever you have a main table and a value list table you store the ID in  the main table and not the text,"

                    I know that.  The main table does indeed store only the ID.  But that isn't much use to the user - they can't remember the IDs.  So after the pop-up has been used to select the correct ID in a nice friendly manner (from the pop-up list with the text in it) I display the text field on the layout, as the related field, from its related table, related by the ID number just entered. As far as the user's experience goes, they only ever picked from, and now see, user-friendly text.

                    • 7. Re: list box dispaly text and store ID
                      Fagreement

                      I'm totally with you: "So after the pop-up has been used to select the correct ID in a nice  friendly manner (from the pop-up list with the text in it) I display the  text field on the layout, as the related field, from its related table"

                      but if i dsclay the text on the screen it's the text that is stored in the main table and not the ID. I want to store the ID and display the text.

                      • 8. Re: list box dispaly text and store ID
                        raybaudi

                        "I want to store the ID and display the text"

                        This is exactly what you just have now !
                        Infact, on exit that field, you'll see the ID, not the text ( if the field is formatted as drop-down list ); if the field is formatted as pop-up menu, you'll see the text but still it stores the ID. 

                         

                        • 9. Re: list box dispaly text and store ID
                          Sorbsbuster

                          Raybaudi is totally correct.  You are under-estimating Filemaker's developer-friendliness.

                          • 10. Re: list box dispaly text and store ID
                            Fagreement

                            thank you Sorbsbuter / raybaudi, I really consider your help. But it's not the case with me.

                            what i have now is that when I exit the drop down list box I still have the text, and in the main table the text is stored and not the ID.

                            • 11. Re: list box dispaly text and store ID
                              Sorbsbuster

                              I just tried it again: works for me perfectly, when I set up the value list as:

                              First Field - RelationshipToSecondTable:: FieldID

                              Second field - RelationshipToSecondTable:: FieldText

                              Show only values from second field.

                              I see the text to pick from, but it stores the ID.

                              • 12. Re: list box dispaly text and store ID
                                Fagreement

                                Sorry, what do you mean with

                                First Field - RelationshipToSecondTable:: FieldID

                                Second field - RelationshipToSecondTable:: FieldText

                                • 13. Re: list box dispaly text and store ID
                                  philmodjunk

                                  Sorry, what do you mean with

                                  First Field - RelationshipToSecondTable:: FieldID

                                  Second field - RelationshipToSecondTable:: FieldText

                                  When you define the value list, you first specify a "table" from which to select the field for each column. In Sorbsbusters post, RelationshipToSecondTable is the name of that table. In FileMaker terminology, we call that a "table occurrence" as it refers to one of the "boxes" in your Relationship graph. FieldID would then be the name of the FieldID field and FieldText would be the name of the more user friendly text field.

                                  By any chance have you defined your field to use a Pop Up Menu instead of Drop Down List?

                                  When you specify a pop up menu and hide column 1. The field displays FieldText after you exit the field, even though the value of FieldID is what is entered into the field. You can check this by making a copy of this field, placing it next to your original field but switch its format back to "edit box". When you select a value from the pop up menu, you'll see the ID value in the Edit box copy, but the pop up menu displays the text. Just keep in mind that this field really stores the ID, the text you see is just a display feature of Pop Up menus.

                                  • 14. Re: list box dispaly text and store ID
                                    Fagreement

                                    Hi everyone,

                                    what i did it's simple:

                                    main table: object.

                                    I Have a field in this main table named: categoryID

                                    occurrence table: Category

                                    I Have 2 fields in this main table named: categoryID, categoryDESC

                                    these 2 tables are related via categoryID.

                                    in my layout I insert Object.categoryID

                                    it's a drop down list box, use value from category table.

                                    1st field categoryID, 2nd categoryDESC. include all values. choose field from 2nd field.

                                    Now Browse my layout. I click on the deop down list box => list of categoryDESC. fine.

                                    I select a value. great, in the table categoryID is stored. BUT when I exit the field I see categoryID displayed, and not the categoryDESC. so it's not comprehensbile to the user.

                                    I repeat, what I want is display text int hte field and store ID in the table. is it possible in FM. it seems not.

                                    thanks in advance

                                    1 2 Previous Next