5 Replies Latest reply on Sep 12, 2011 11:42 AM by philmodjunk

    Merge data from many records to one record

    JoshHyman
    JoshHyman Sep 8, 2011 3:29 PM

      Title

      Merge data from many records to one record

      Post

      Hi there,

       

      I am a little stuck on this any help would be greatly appreciated.

      The database we are using has records for performers positions in a circus show, each record has the fields...

       

      Performer name

      Show number

      Act name

      Position

      Timestamp

       

      Our team leader is very excel based and wants me to build a layout that shows all acts in the show across the top (please see attached image) and each performer down the list (also showing the last five shows) The cell turns red if the performer has done the act.

      My question is..... how can I combine the values of one field, in many records, for one performer, in one show, into one record so i can achieve this style of layout.

      For clarification the current records we use look like the below..

       

      Please Note the sample data and the attached image do not match

       

      Record Number      Performer Name       Show number               Act

      1                              Aaron                           20                        Flip   

      2                              Aaron                           20                        Run

      3                              Aaron                           20                        Clown

      4                              Aaron                           21                        High Wire

       

      The record that I wish to make would look like the below

      Record Nuber           Performer name         Show number         Flip        Run        Clown       High Wire     

      1                                Aaron                       20                            Yes       Yes       Yes          No

      2                                Aaron                       21                            No        No        No           Yes

       

      Any help or pointers you could provide would be greatly appreciated.

      Cheers

      J

      Screen_Shot_2011-09-09_at_10.05.24_AM.png

      • 176 Views
      • Tags:
        • 1. Re: Merge data from many records to one record
          philmodjunk
          philmodjunk Sep 8, 2011 3:40 PM (in response to JoshHyman)

          Are you using fileMaker 11?

          If so you do not need that added table.

          This can be a list type summary report with filtered portals to organize the data into columns.

          Using your screen shot for a reference. The first two rows (the performer's name and the column headings) would be a sub summary part "when sorted by Performer".

          You'd then place the performance date formatted to show day of the week in column 1 of the body, leave space for the gap below the performer's name and then add 8 one row portals to your table, each with a filter expression to filter only for a specified act.

          Your portals' relationship would look like:

          Table::PerformerID = Table 2::PerformerID AND
          Table::ShowNumber = Table 2::ShowNumber

          I am using a performerID serial number here as PerformerNames are both non unique and subject to being changed where this is not the case with an ID number. (and should come from another table with one record for each performer.)

          • 2. Re: Merge data from many records to one record
            JoshHyman
            JoshHyman Sep 8, 2011 4:01 PM (in response to JoshHyman)

            Great thanks,

             

            That works a treat.

            Is there then any way to limit the results I get in the sub summary part to only show the last five shows for each performer??

            Thanks very much for your help

            J

            • 3. Re: Merge data from many records to one record
              philmodjunk
              philmodjunk Sep 8, 2011 4:04 PM (in response to JoshHyman)

              Yes, you should be able to perform a find to limit your found set to only those shows.

              • 4. Re: Merge data from many records to one record
                JoshHyman
                JoshHyman Sep 11, 2011 2:02 AM (in response to JoshHyman)

                Thanks a lot for your help.

                I now have that working my only issue now is that when I define my find, I end up with many records for each show.

                Is there a way to limit the find so it only shows one record per show??

                I have tried constraining the found set to not show duplicates but it appears each record is unique.

                Cheers for any sugestions

                J

                • 5. Re: Merge data from many records to one record
                  philmodjunk
                  philmodjunk Sep 12, 2011 11:42 AM (in response to JoshHyman)

                  We need to make a small change to this part: Quoting from an earlier post: Using your screen shot for a reference. The first two rows (the performer's name and the column headings) would be a sub summary part "when sorted by Performer".

                  Make the part of the layout where these portals are located a sub summary part when sorted by your Performance field and remove the body part from your layout altogether.

                  This approach will then "condense" all your individual performance records down to one row per performer per performance. (Make sure that you sort your records both by performer and then by performance.)