6 Replies Latest reply on Jan 3, 2012 6:51 PM by DavidEnns

    Combining multiple records



      Combining multiple records


      I have an obstacle table with fields trail_ID, Obstacle_ID and Description.

      Record 1: 0001, 01, qwerty

      Record 2: 0001,02, help me

      record 3: 0002, 01, nice


      What I'm trying to do is combine all the description records from one trail into one field for printing purposes.

      trail_ID=0001 obstacle_ID=01

      trail_ID=0001 obstacle_ID=02

      result=qwerty help me

        • 1. Re: Combining multiple records

          Use the List() function.  Calculation (result is text) in your Trails table:

          Substitute ( List ( Obstacles::Description ) ; ¶ ; ", " )

          or just " " if you want only a space and no comma between them.

          • 2. Re: Combining multiple records

            Unfortunately this didn't work. Its just displaying each obstacle_ID description. Not combing multiple fields descriptions with the same obstacle_ID into one field.

            • 3. Re: Combining multiple records

              I might have misunderstood.  Here are some things to check:

              * Am I correct that one trail can have many Obstacles? 

              * You want the result per trail to display in your Trails table? 

              * In your first example, you show the same ObstacleID twice (01) and the Obstacles table should only have uniqueIDs.

              * Are they are related as:  Trails::ObstacleID = Obstacles::ObstacleID?

              * Did you create a calculation (and not a regular text field with auto-enter calculation)?

              * You do not need a TrailsID in Obstacles because Obstacles is the 'one' side ... or is this a many-to-many table?  If so, you are missing a table.

              If there is one trail with many obstacles and if you have it joined as indicated and it is type calculation (result is text) then when viewing Trails ID 0001,there will be two related Obstacles records (01 and 02) and it will work as expected.  Let us know how we can adjust it for you. :-)


              • 4. Re: Combining multiple records

                I believe I'm missing something regarding the relationships of tables so I've probably set it up wrong. But here goes. I have a layout called Trails. Within Trails I have a portal called Obstacles. Each obstacle has a separate ID which I access with a drop down menu. I  have two table (Trails & Obstacles) with a relationship between trails::trail_ID to obstacles::Trail_ID & trails::obstacle_ID to obstacle::obstacle_ID. Maybe this is the "many to many" your'e talking about. Thru hit and miss this is the only way I could find to view the different obstacles_ID. Change to obstacle 2  with a drop down menu and the rest of the record refreshes with the new information. Keep in mind I am not a database programmer so I"m thinking I need to go pick up FMP11 for Dummies :-)


                Edit: I also don't know how many obstacles there are. There could 3 or 5 or 10. Number is unknown

                • 5. Re: Combining multiple records

                  Hi David,

                  Everyone is ignorant of many things and everyone is new at everything sometimes.

                  Here is a demo showing how you should be structured.  Note that Obstacles does not hold the TrailsID and that I have added a join table where each combination of trail/obstacle is logged.

                  The number of obstacles does not matter.  The List() function takes all of that Trail's Obstacles and puts them in a list as:

                  help me

                  ... and the Substitute() changes the carriage return to a space for the file result of:  steep help me nice



                  • 6. Re: Combining multiple records

                    Thanks LaRetta!! I must have tried every combo except the one you've suggessted. And it works. Its a classic example, on my part, of making something more complicated than it is. Again thanks and I'm going to use your example.