AnsweredAssumed Answered

(internal SQL) query to export in excel flat file

Question asked by CarriePledger on Sep 12, 2011
Latest reply on Sep 14, 2011 by CarriePledger


(internal SQL) query to export in excel flat file


I realize FM isn't going to let me use SQL but I am wondering what other people do in this situation.   This seems like such a basic action for a database but I can't see how filemaker can do queries (except super basic ones) without using SQL.

I'm about ready to give up and go back to using a flat file or just switch to access but then that would be a lot of extra work.  :) 

All I want to do is pull out data per person so it is all in one row so we can run statistics on it. For example, I have two tables Patients and Labs. For each patient there are multiple labs done on different days. The same lab type is repeated at each followup.  For the lab table it would look like this (with PT ID as the key)
PatientID timepoint    lab             date      result
1              preop     IGF       21-Jun     1111
1               preop    GH       21-Jun     23
1              preop     GH      30-Jul      21
1               preop    IGF     30-Jul      1313
1              postop     IGF     23-Aug  22
2              Preop       IGF      22-Jul     142
2               preop       IGF      1-Aug     155
2                postop     GH      2-Sep     12

I want to pull (in addition to other fields in these and other tables) so that it looks like the one below so I can export it into excel. If I used SQL I think I would in general run join PTID  Max(result) where timepoint = preop and lab = IGF etc. I could then just build a button to run the query and then copy the data to excel. I tried some of the SQL plug-ins but I can't figure it out in filemaker. There didn't seem to be a way to just execute a query with the plug ins it seemed that you had to do it by creating a new table and using the SQL inside fields...? 


Patient highest preop IGF lab         highest postop IGF
1               1313                           22
2               155


I could probably come up with some super complicated way of doing this using the find features and scripting in filemaker but I need to do this same type of query many times with multiple tables and it seems like it should be very simple.  There has to be something I am missing.  Can anyone help??

I can't get the tables I pasted to display correctly in this message...