10 Replies Latest reply on Dec 29, 2014 1:44 AM by berts

    How to use multiple fields from one record in a value list

    berts

      What do is the case:

       

      Table: language

      Records: one

      Filelds: Up to100, from field a001 ~ a010 ......... z001 ~ z005 + a field "id_language"

      Field type: Gobal

      Field Content: looked up and one field "id_language" which gets a variable ($$id_language) entered by a start up script.

      Relationships: 6

       

      What I am looking for is a way to fill value list with a range of fields from the only record in the table "language". However the Value list options don't allow more than one field to be used from a particular table.

       

      I need something similar to the "Use value from field" option in "Edit value lists" but than like this: Use value from fieldS, instead of field

       

      The purpose is to create a value list to choose from in the right language in a couple of tables.

       

      Any idea's?

       

      Thanks in advance

       

      Bert Stolker

        • 1. Re: How to use multiple fields from one record in a value list
          mikebeargie

          Bert,

           

          A lot of developers get around this by having a global field somewhere that they write/update the values to at a certain time, then use that field for the value list values.

           

          With ExecuteSQL() it's easy to make this context free (you can use it on any layout), EG:

           

          Set Field [ table::global ; List ( ExecuteSQL("SELECT DISTINCT(field1) FROM table1" ; "" ; "" ; "") ; ExecuteSQL("SELECT DISTINCT(field2) FROM table1" ; "" ; "" ; "") ; ExecuteSQL("SELECT DISTINCT(field1) FROM table2" ; "" ; "" ; "") ; etc... ) ]

           

          You can use a script trigger to update the value list field at specific times, such as file open, layout load, file close, record save, etc..

          • 2. Re: How to use multiple fields from one record in a value list
            Mike Duncan

            Hi Bert,

             

            You could add another field and set it's auto enter options to populate with the values you want to show, delimited with returns. Then base your value list off that field instead. Would that work?

             

            Mike

            • 3. Re: How to use multiple fields from one record in a value list
              filemaker@i-absolute.com

              Use UNION maybe is better

               

              "SELECT DISTINCT(field1) FROM table1 UNION SELECT DISTINCT(field2) FROM table1 UNION SELECT DISTINCT(field1) FROM table2"

               

              Cheers,

              Fabio

               

               

               

              Set Field [ table::global ; List ( ExecuteSQL("SELECT DISTINCT(field1) FROM table1" ; "" ; "" ; "") ; ExecuteSQL("SELECT DISTINCT(field2) FROM table1" ; "" ; "" ; "") ; ExecuteSQL("SELECT DISTINCT(field1) FROM table2" ; "" ; "" ; "") ; etc... ) ]

               

              • 4. Re: How to use multiple fields from one record in a value list
                mikebeargie

                I’ve actually found that UNION can degrade performance in some cases, and using List() and multiple statements is faster.

                 

                Six to one, half dozen to the other…

                • 5. Re: How to use multiple fields from one record in a value list
                  Mike Duncan

                  I would think using the executesql function at all may be overkill for this. They say there is only ever one record in the table, so an auto enter with just the fields you want to populate it with may be easier to read/maintain.

                  • 6. Re: How to use multiple fields from one record in a value list
                    mikebeargie

                    Yep, I realize that now, I was doing a literal interpretation of the question “how do I combine multiple fields for a value list”. (This week’s definitely been a coffee kick week).

                    • 7. Re: How to use multiple fields from one record in a value list
                      berts

                      Hi Mike, Mike and Fabio,

                       

                      Thanks  for the answers, unfortunately, I have to do it in Filemaker solely, external scrips are not allowed.

                       

                      In Filemaker I tried field repetitions to solve the issue, didn't work because some of the Value lists are using two fields showing only one in the list.

                       

                      I'll post the solution when I solved the issue.

                       

                       

                      Thanks

                       

                      Bert stolker

                      • 8. Re: How to use multiple fields from one record in a value list
                        keywords

                        You could create a concatenated field that links together the contents of other fields as a text string, and then use that field in your list. I have done this to give an address written as a comma separated line, for example.

                        • 9. Re: How to use multiple fields from one record in a value list
                          user19752

                          It may be simple if you make records for every language, like if you use only one language for the table.

                          ex.

                          When a value list have 3 values, you make 3 records in a table.

                          If you need 4 languages, you make 3 by 4 = 12 records.

                          • 10. Re: How to use multiple fields from one record in a value list
                            berts

                            Dear All,

                             

                            The way I got conditional, two languages value list working is as follows:

                            - Create a table "Language", one record per language

                            - Create an ID field per langurage in "Language"

                            - Add the fields for each word needed in the value list

                            - Create a field ID language with the required language for each record and present in the lay out in which the value list is going to be used for this example: Table " Actual Table"

                            - Use that ID language to create a relation Language - Actual Table

                            - Create a table "Value Lists"

                            - Create a base ID which is the same for each record that will be used in the value list, as base for the relation between "Actual Table" and "Value Lists" (this allows you to have re-use some values)

                            - Create an ID for each value

                            - Create a calculated text field (Case ( ..=..;...) that uses the ID for each value to calculate the content of that fiele for each record

                            - Create the three above in "Actual Table" where the base ID should be a global field since it will be the same for all records

                            - Create the relation Value List - Actual Tabel based on the base ID

                            - Create a value list based on the ID field for each value, showing records fromt he second field (the actual value) only

                            - In the actual layout add the field ID value with a drop down list based on the value list above.

                             

                            It is a lot of work, but it does the job.

                             

                            Thanks for your input.

                             

                            Bert Stolker