12 Replies Latest reply on May 18, 2017 9:01 AM by AndreasvanHaren

    value list from an amount of text fields possible?

    AndreasvanHaren

      Is it possible to create a value list from a number of text fields?

       

      I have 14 separate text fields in my project where a user can fill in names. On another layout i would like to be able to choose any of those names from a drop down menu and for that I need a value list. Any suggestions how to do this?

       

      thanks!

      André

        • 1. Re: value list from an amount of text fields possible?
          erolst

          It sounds like it would be a better idea to have a single Name table to enter and store names, and link that as necessary to the original table(s). Then you can get your value list from a single field in a single table.

          • 2. Re: value list from an amount of text fields possible?
            TomHays

            You can create a calculated field (e.g. cAllNamesUsed) that combines the 14 separate fields into a List.  Use this cAllNamesUsed field as the source of the Value List.

             

            cAllNamesUsed =

            List(

            Name_PetCat;

            Name_PetDog;

            Name_PetBird;

            Name_Sofa;

            (and so on)

            )

             

            -Tom

             

             

            This assumes that the names are all coming from different fields in a single table.

             

            If you have names scattered into fields in different tables, your only choice may be the recommendation from erolst  to consolidate the names into a field in a single table.

            • 3. Re: value list from an amount of text fields possible?
              AndreasvanHaren

              thanks! Yes, all the text fields for the names are in the same table. I never worked with calculated fields before, so I have to check how to do this. How do I connect the fields in the calculation window? I tried placing them on their own line, and also using the AND command between the fields but that doesn't do it. in the image below, I would like to combine all the Character fields into a single list, for that I made the cAllCharacters list as you suggested. So now I need to fid the correct way combining them.

               

              Screen Shot 2017-05-18 at 13.23.14.png

              • 4. Re: value list from an amount of text fields possible?
                AndreasvanHaren

                Thanks, i will give this a try. I am still new with filemaker so I have to check on even the basics a lot. I did some Lynda.com training courses and have a slight grip o the basis though.

                • 5. Re: value list from an amount of text fields possible?
                  TomHays

                  The correct format to enter into the calculation box is

                   

                  List(AntagonistName; Character1; Character2; Character3; (continue this for all character fields) )

                   

                  To make it more readable you can break it onto separate lines.  The key is to use the List() function and separate each field with a semicolon as the input values for that function.

                   

                  List(

                  AntagonistName;

                  Character1;

                  Character2;

                  ... and so on

                  )

                   

                   

                  Looking at your table structure, you would be much better served to learn about using multiple tables.  Your data begs to have a separate table for Characters where each character is a separate record instead of having each character in a separate field.

                   

                  -Tom

                  • 6. Re: value list from an amount of text fields possible?
                    AndreasvanHaren

                    Thanks, I am getting close to what I want. I now get a drop down menu with the names of the fields I filled in somewhere else which is what I wanted, but cannot select anything from the menu, trying this gives the error message:

                    Screen Shot 2017-05-18 at 14.01.12.png

                     

                    Your other suggestion to make a separate table for characters only, is something I tried before but couldn't not get it to work.

                    • 7. Re: value list from an amount of text fields possible?
                      TomHays

                      The "field is not modifiable" indicates that the field where you are doing data entry is... not modifiable.  The Value List and the menu appears fine (maybe just appears, more on that later).

                      Examine the field that you are trying to edit using the Value List.  Is it perhaps the new, calculated field?  It shouldn't be.  The menu created by the Value List should be applied to a new field that is of type Text.

                       

                      Note that the Value List will be pulling names from all Character fields in all records.  Is that what you actually want? Is each record in this table for an independent work that has its own characters?  Characters from other records will appear in this menu.

                       

                      -Tom

                      1 of 1 people found this helpful
                      • 8. Re: value list from an amount of text fields possible?
                        AndreasvanHaren

                        "The "field is not modifiable" indicates that the field where you are doing data entry is... not modifiable.  The Value List and the menu appears fine (maybe just appears, more on that later).

                        Examine the field that you are trying to edit using the Value List.  Is it perhaps the new, calculated field?  It shouldn't be.  The menu created by the Value List should be applied to a new field that is of type Text."

                         

                        thanks, i used indeed the calculated list, so I made a new text field as you suggest, this works now as I wanted. The name appear in the menu and I can choose one from the list.

                         

                        Making a new record does indeed show the names from the other record, which is not a disaster, but it would be nice to have an empty list when starting a new record. Every record represents a new novel with its own characters. So I think I should find a different way to populate the value list?

                        • 9. Re: value list from an amount of text fields possible?
                          TomHays

                          From the info so far, I'm guessing that you have

                          • One record in "The 4 Questions" per literary work
                          • Each literary work has a list of characters
                          • The record in "The 4 Questions" has at least one field that requires you to choose a character in the literary work

                           

                          This scenario needs

                          • Table "The 4 Questions"
                          • Table "Characters"

                           

                          The 4 Questions

                          --------------------

                          questions_id  (primary key unique to each record, e.g. auto-enter serial number)

                          title

                          Antagonist

                          Protagonist

                          other_question_asking_for_which_character

                          ...

                           

                           

                          Characters

                          --------------

                          character_id (primary key unique to each record, e.g. auto-enter serial number)

                          CharacterName

                          CharacterSex

                          fk_questions_id (the id of the literary work record in "The 4 Questions")

                           

                           

                           

                          In the relationship diagram

                           

                          [The 4 Questions]   <--- = ---> [Characters]

                          where the = connects questions_id to fk_questions_id

                          You probably want to check the box on the Characters side to "Allow creation of records via this relationship".

                           

                          Put a Portal onto The 4 Questions layout containing the related field Characters::CharacterName and any other fields you want to edit in the Characters record.

                           

                          You can then add characters to the literary work by entering them directly in the Portal in the blank row.  The records in Characters will be created automatically.

                           

                           

                          You can then make a Value List to

                          Use values from field Characters::CharacterName

                          and

                          Include only related values starting from The 4 Questions.

                           

                          This will generate a Value List you can use on The 4 Questions layout.  It will be populated with the Characters that are in the Portal for that record.  It will not show characters that are on other The 4 Questions records.

                           

                           

                          -Tom

                          • 10. Re: value list from an amount of text fields possible?
                            AndreasvanHaren

                            Thanks Tom. I learned about using relationships in the Lynda course but didn't think I needed this, because my project isn't that complicated. I think it's a good idea to adjust these elements and get a better organization. Right now all my fields are in a single Table.

                             

                            André

                            • 11. Re: value list from an amount of text fields possible?
                              TomHays

                              An alternate (more elaborate) way of using the Value List and filling in fields on The 4 Questions that require character responses is to respond with the ID of the Characters record instead of a copy of the CharacterName.  The fields in The 4 Questions would then be

                               

                              fk_Antagonist_id (foreign key of Characters record)

                              fk_Protagonist_id

                               

                              These would be values of the Characters::character_id field.

                               

                              You would need to create additional Table Occurrences of Characters on your relationship diagram to connect these to instances of that table.

                               

                              Your Value List would Use values from first field Characters::character_id and also display the second field Characters::CharacterName.

                               

                              This type of structure would be used if you anticipate editing the CharacterName after using it for data entry filling in other fields on The 4 Questions.  The records would be linked so the edits in Characters would automatically show up in the fields in The 4 Questions.

                               

                              -Tom

                              1 of 1 people found this helpful
                              • 12. Re: value list from an amount of text fields possible?
                                AndreasvanHaren

                                Thanks, although the database works now as I hoped for, I will fine tune it using your suggestions. I see my first setup as the 1st draft, now I will start with the 2nd and if needed a 3rd till I am happy with the result.