(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
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...