7 Replies Latest reply on Aug 2, 2015 10:26 AM by indyj

    Copy multiple record from one table into one record in another table

    indyj

      Title

      Copy multiple record from one table into one record in another table

      Post

      I have a problem I can't seem to figure out, and was hoping somebody here could help. I have a database of skeletons, that contains a table into which records were added through a portal. The table (FracturebyBone) records observations of fractures per long bone, and each observation is one record. The fields are SkeletonID, FractureID, Bone, and FractureCode - i.e. each SkeletonID will have multiple Bone records: left humerus, right humerus, left ulna, and so on. 

      I need to copy these records into a new table, (FracturebySkeleton), where each SkeletonID has only one record - i.e. the new fields should be SkeletonID and then one field for each bone (HumerusLeft, HumerusRight, UlnaLeft etc). I want to copy the FractureCode into the corresponding fields in the new table. The tables are related through the SkeletonID. 

      Is there a calculation I could use in the FracturebySkeleton bone fields that would automatically copy matching records to the correct field from the FracturebyBone table? I tried setting the fields up as calculations like this (using the FracturebySkeleton::HumerusLeft field as an example):

      If ( FracturebyBone::Bone = "Humerus Left" ; GetAsText ( FracturebyBone::FractureCode ) )

      but it only works for the first field in the table, the others remain blank. 

      Thankful for the help!

        • 1. Re: Copy multiple record from one table into one record in another table
          philmodjunk

          It's possible, but what you describe does not sound like a good design for a relational database. Keeping the date in your original table with one record per fracture would seem to make a lot more sense.

          Can you explain why you want to denormalize the data like this? Maybe we can suggest a better alternative.

          • 2. Re: Copy multiple record from one table into one record in another table
            indyj

            Thanks for the quick reply!

            I do realize that I am duplicating data. 

            The reason I need the data "transposed" like this is because I want to import it into SPSS so that I can run statistical tests by individual skeleton. In order to do that I need one record per SkeletonID, since that is how my "Cases" in SPSS are organized. I know I could just change the setup of the original  Filemaker table to avoid duplication in the database, but the old table is already used in several layouts and reports, and I would rather not redo those. The new table would not be used in the Filemaker database, other than for the SPSS export. 

            • 3. Re: Copy multiple record from one table into one record in another table
              philmodjunk

              I wouldn't change the structure of your original set up. It's a better design for a relational database. And copying the data isn't the total problem, it's also organized in your new table in a much less flexible fashion. But if you have to get it into that format for export purposes...

              What you can do is import the SkeletonID and set up auto-enter calculations that use ExecuteSQL to find not only the data by ID by by bone type from the FractureByBone table in order to copy that data into the appropriate field. Each of these SQL queries will be very similar so once you get one to work, you can copy and paste to replicate it into the other fields with just a small edit to specify a different bone.

              Come to think of it, you don't even need the added table. You could add unstored calculation fields with ExecuteSQL to reference the FractureByBone data in the original skeleton table and you could then export from that table.

              But could it be possible to have more than one FracturebyBone record for the same bone in the same skeleton? What should happen then?

              • 4. Re: Copy multiple record from one table into one record in another table
                indyj

                Thank you for the advice! There is only one record per bone, so that will not be a problem. The SQL export is probably more elegant, but I would still prefer a separate table, since I am adding fields to a large layout in table format for the SPSS exports, that contains fields from several different tables (i.e. age, sex, location, other pathologies). That way, all of the cases are updated automatically if I add records. I will play around with the ExecuteSQL to see if I can get it to work.

                 

                • 5. Re: Copy multiple record from one table into one record in another table
                  indyj

                  Ok, so I played around with it a little, but I still can't make it work (I've never used the ExecuteSQL function before). What I am getting in the HumerusLeft field (where I placed the calculation) is a return delimited list of ALL the FractureCode values, not just the matching one. How can I select only the matching record? 

                  This is what I did:

                  Let ( [ 

                  ReturnSub = "\n" ; 

                  SQLResult = ExecuteSQL ( 

                  "SELECT a.\"FractureCode\"
                  FROM \"FracturesLongBones\" a
                  INNER JOIN \"FracturesLongBonesBySkeleton \" b ON a.\"SkeletonNumber\" = b.\"SkeletonNumber\"
                  WHERE a.\"Bone\" = ?" ; 


                  "    " ; "|*|" ; 


                  "Humerus Left"


                   ) ] ; 

                   Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )


                  )

                  • 6. Re: Copy multiple record from one table into one record in another table
                    philmodjunk

                    Your Where clause needs to specify the Skeleton Number AND the bone.

                    WHERE
                    a.\"Bone\" = ? AND a.\"SkeletonNumber\" = ?" ;

                    "" ; "|*|" ; 


                    "Humerus Left" ; FracturesLongBones::Skeleton Number

                    You don't, in fact need the Join clause for this query as this now queries the fractures table directly.

                    • 7. Re: Copy multiple record from one table into one record in another table
                      indyj

                      That worked! Thank you so much!