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.
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.
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?
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.
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 (
FROM \"FracturesLongBones\" a
INNER JOIN \"FracturesLongBonesBySkeleton \" b ON a.\"SkeletonNumber\" = b.\"SkeletonNumber\"
WHERE a.\"Bone\" = ?" ;
" " ; "|*|" ;
) ] ;
Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] )
Your Where clause needs to specify the Skeleton Number AND the bone.
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.
That worked! Thank you so much!