8 Replies Latest reply on Sep 10, 2010 2:04 PM by Frinholp

    List fields question

    hilary12345

      Title

      List fields question

      Post

      Hi, I'm wondering if their is a built in function that returns all the field contents from each record as a list. So for example, I have a table called contacts, in the table is a field called contactName. I have three records in contacts, the first record has "Joe" in contactName field, the second has "Barb" and the last has "Sue."

      I'm not sure which function filemaker has, but I was hoping there was one that when called like so:

      exampleFunction ( contacts::contactName ), it would return:

      Joe

      Barb

      Sue

      Please let me know if filemaker has this function, and what it's called. Thanks so much!

        • 1. Re: List fields question
          Frinholp

          If you have Filemaker Pro Advanced you could write a custom function to do what you want to do or there is an example here:

          http://www.briandunning.com/cf/471

          Here is an example to do this just by scripting. I am holding the list in a variable here called $list. To store the list in a field replace Set Variable with Set Field.

          Go to Record/Request/Page [First]
          Loop
          Set Variable [$list; Value: $list &  yourtable::yourfield  & ¶

          Go to Record/Request/Page [Next, Exit after Last]
          End Loop

          Lee

          Edit: Misread question

          • 2. Re: List fields question
            hilary12345

            Thanks for your post! I don't have filemaker advanced, just filemaker pro. I had taken a similar approach that you suggested. What I was trying to accomplish was to make my script faster. I have many embedded loops in my script and I have to switch between layouts many times. I think this is what is slowing down my script.

            On an aside, this database will end up on filemaker server advanced. Is it possible my script will run faster on a server than on my mac book pro? I don't have the specs of the server so this might be a silly question.

            Thanks for your help!

            • 3. Re: List fields question
              philmodjunk

              There are two approaches you can use with regular filemaker that do not require a script.

              Define a table and link it to your existing table via a relationship that uses the X operator so that any record in this new table matches all records in your existing table.

              Then, a calculation field defined in this new table can use the List function to return your list of names: List ( YourTable::YourField )

              Use Manage Database to define a value list and specify your name field as the source of data for this value list. Now this calculation field, which can be defined in any table, will return your list of names:

              ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotes " )

              • 4. Re: List fields question
                Frinholp

                Thanks for the additional techniques Phil.

                Use Manage Database to define a value list and specify your name field as the source of data for this value list. Now this calculation field, which can be defined in any table, will return your list of names:

                ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotes " )

                If my understanding is correct the value list and calculation field would release a significant amount of processor load. The list is populated everytime a new record is commited and is up-to-date and ready everytime it is required.  I never thought of this even though I use value lists on a regular basis.

                Define a table and link it to your existing table via a relationship that uses the X operator so that any record in this new table matches all records in your existing table.

                Then, a calculation field defined in this new table can use the List function to return your list of names: List ( YourTable::YourField )

                How does this work Phil? I have never used but read about the X relationship operator in help. I understand all the relationship operators apart from X.

                All records in the left table are matched to all records in the right table, regardless of the values in the match fields. - Filemaker Help

                 This says to me every record matches all records, is this correct? If so, how does this achieve the goal of creating a list. I'm not understanding the logical flow. Could you elaborate and help me on this one please?
                Thanks
                Lee

                 

                 

                • 5. Re: List fields question
                  philmodjunk

                  I don't think you'll have a problem with option one it works off the field's indexing which takes place (with stored, indexed fields) regardless of whether you add this new calculation field-which you may be able to define as an unstored field. I'd try it out for your specific file and see.

                  The cross product operator x is a "match every value" operator in FileMaker. Thus, you need only have one record in the new table and it will be linked to all the records in your current table. Think of it this way, if you added a portal to your original table on a layout based on this new table, you'd see all the records in your original table. The List function as I specified it, can then pull the contents of a given field in your related table together as a return separated list. This option is more likely, I think, to encounter delays with large data sets than the first.

                  • 6. Re: List fields question
                    Frinholp

                    As ever Phil, always greatful for your mentorship.

                    "you'd see all the records in your original table"

                    My understanding at the moment suggests that if one field matches the same field in the related table there is a relationship created, so if there is 10 fields in the original table, there are ten cases where a relationship could be created if both tables share those same 10 common fields. Is the same as creating an = relationship on every field on every field that each table has in common. Is my understanding correct?

                    Lee 

                     

                    • 7. Re: List fields question
                      philmodjunk

                      Before we had any relationship operators (Back then all relationships were based on = logic), we'd implement this by defining a number field in one table that always stored the value 1 (often a calculation field) and then defined match field in the other field that always stored the same value 1. This "match all records" relationship is frequently useful in FileMaker systems. You still see this in some templates and many legacy systems.

                      So think of it as a relationship where the primary and foreign keys all have the same value for every record in both tables.

                      • 8. Re: List fields question
                        Frinholp

                        Thanks again Phil

                        I'm grasping that concept. So when searching for related records, all records in the linked table will be returned. I can see where this has multiple uses, especially when using a calculated field in one of the tables.

                        Cheers