6 Replies Latest reply on Jul 10, 2013 12:28 PM by RedDot1

    Multiple Records Displayed in One Field



      Multiple Records Displayed in One Field


           I have seen some previous posts on this subject but they usually involve more complex situations and are hard to apply to my situation which is that I have a table with five fields:

           P1 (primary key field, a number)

           P2 (Label, text)

           P3 (Number, a number)

           P4 (foreign key field, a number)

           Concatenate (a calculation field = P2 & "  " & P3)

           I would like to create an additional field that contains a list of all the records in Concatenate that each P4 record has.  So for example if I have 6 records in this table:

           P1        P2        P3        P4

           100      ABC     789      500

           101      DEF     573      501

           102      JKL      547      502

           103      DEF     210      500

           104      JKL      589      500

           105      ABC     698      501

           I would like to create a new field that contains (if I am on the P4 record 500:

           ABC     789

           DEF     210

           JKL      589

           Or if I am on the P4 record 501:

           DEF     573

           ABC     698

           Or if on the P4 record 502:

           JKL      547

           I created a new table incidence and a self-join relationship but the new field using the List function only lists the first record for each P4 record.  What am I doing wrong?

           Thanks for any help.

        • 1. Re: Multiple Records Displayed in One Field

               You don't need a calculation field from what you have posted here. Use a portal instead of that calcualiton field and you can list as many rows of these fields as you need.

          • 2. Re: Multiple Records Displayed in One Field

                 Is it possible to copy all the data in the portal to clipboard?  Sorry, I should have mentioned that I wanted to do this so I thought that if all the data were in one field I could highlight the contents and then paste into a Word or Text document.

            • 3. Re: Multiple Records Displayed in One Field

                   With a bit of scripting, yes.

                   The script would use Go To Related Records to pull up the portal's records in a found set on a layout based on the portal's records and where the only fields present on the layout are those from which you want to copy data. Then the copy all records step will copy the data to the clipboard. The resulting data will include a tab character between data from each field and a return between data from each record in the found set.

                   There are two alternative methods that do indeed put all this data in one field from the related portal:

                   1) Use your list function, but define a calcualtion field in your portal's table that concatenates the fields you want into a single field: Field1 & ", " & Field2 &  ", " & ..... Then use List from the context of the parent table occurrence to pull this data together into a single field from which you can copy the data to the clip board.

                   2) Use an Execute SQL function call to pull this data together in a single field. This requires understanding how to set up SQL queries but can be done without needing that extra calculation in the portal table. It can, in fact, be setup without any defined relationship in Manage | Databse linking the two tables.

              • 4. Re: Multiple Records Displayed in One Field

                     Thanks very much PhilModjunk. 

                     While waiting for any answers on this question on the Forum I called Filemaker Tech Support to ask them if they would answer a question on a problem I was having with one of their Answer ID's related to this question.  I had called them earlier on the problem of copying multiple records to one field and they said I had to contact a developer or post the question on this Forum.

                     The problem was that Answer ID 2403, "How To Copy Data To One Field From Multiple Portal Records" was out of date and did not work, for me at least.  FYI They just updated the Answer ID as of Jul 09, 2013 08:54 AM PDT and in it they included an additional method at the bottom of the article using a single script step:   "Set Field [MainTable::Combine; List (RelatedTable::DesiredField)]"  I used this and it works for me.  I hope it is also useful for others.


                • 5. Re: Multiple Records Displayed in One Field

                  "Set Field [MainTable::Combine; List (RelatedTable::DesiredField)]"

                       Only lists the values of a single field--which is why I suggested 1) as a way to combine the values from multiple fields using the List function.

                  • 6. Re: Multiple Records Displayed in One Field

                         PhilModJunk, Yes, understood.  I had already seen your suggestion in one of your earlier answers to a related question and as a result used this approach with the script suggested by Answer 2403.  Thanks for your help.