query and export as flat file to excel

[clp8b]

I have only been working on filemaker and then access databases for a few years and know little about querying. I hope this is right place to post this. Sorry if it is not. What I am finding is that I can't get my data to export the way I want under the relationship structure of FM. I feel like what I am doing is fairly simple but I haven't been able to figure it out and am thinking of just moving it to a flat file structure since that is how my boss originally had it set up.

All I want to do is pull out data 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.

Patient 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 this and I can export it into excel. I think I would this in general using Max(result) where timepoint = preop and lab = IGF etc if I used SQL. I could then just build a button to run the query and then copy it to excel. I tried some of the SQL plug-ins but I can't figure it out in filemaker.

Patient highest preop IGF labhighest postop IGF

1 1313 22

2 155

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

Edited September 12, 2011 by clp8b

[LaRetta]

If I understand correctly, switch to the Labs child table and perform the export. You can still add additional fields from Patients onto your export map. It will produce flat file (one line for every lab result). It will NOT however, produce a left outer in that, if there is a patient without lab results, that patient will not be included in the export.

[clp8b]

Thanks but no that doesn't help. If I do that I will simply get the top table with everything. I want to query the highest number in the result column when timepoint = preop and type =IGF. I want to get the bottom table I list as the result.

[comment]

See if this helps:

ExportMax.zip

[clp8b]

That is good for a one type of lab example. Ideally I want to be able to pull a few different labs, sometimes the max, sometimes the min and then also combine other tables doing similar processes. I would also want all of the data from the Patient table on one row (instead of having multiple rows per person and/or having rows with blanks). Here is a better, more specific, example.

The patient table has an identifier as medical record number (MRN) and a field called diagnosis with the options of NFA, Cushing's and Acromegaly.

The labs table has fields timepoint (preop, inhouse, postop), date, lab type (IGF, GH, cortisol, ACTH, TSH), on meds(yes/no), result (numerical value)

The complication table has timepoint, date, complication (CSF leak, hypopit, headache), subcomplication

The patient table has patients:

MRN diagnosis Gender

1 NFA Female

2 NFA Male

3 Cushing's Female

The lab table in filemaker has the following:

PatientID timepoint lab date result

1 preop IGF 21-Jun 1111

1 preop ACTH date 44

1 preop GH 21-Jun 23

1 preop TSH date 25

1 preop GH 30-Jul 21

1 preop cortisol date 10

1 preop IGF 30-Jul 1313

1 postop IGF 23-Aug 22

1 postop cortisol date 9

1 postop GH date 9

1 postop TSH date 11

1 postop GH date 6

2 preop cortisol date 2

2 preop GH date 13

2 Preop IGF 22-Jul 142

2 preop IGF 1-Aug 155

2 postop GH 2-Sep 12

2 postop GH date 44

2 postop cortisol date 11

2 postop TSH date 2etc

3 postop TSH date 3

3 preop IGF date 222

Let's say I only want to pull data from NFA diagnosis patients with a column for highest IGF at preop, the highest GH at preop, the highest IGF at postop and the highest GH at postop. I would want it all in one row per patient. I would also add for example, a column for positive for CSF leak from the third table, but it is the same concept as what I want to do for the labs.

The exported excel file would look like this:

MRN Gender highest preop IGF highest postop IGF highest preop GH highest postop GH complication CSF

1 Female 1313 22 23 9 yes

2 Male 155 13 44 no

[comment]

I would also want all of the data from the Patient table on one row

This would require each value to be a field in the Patients table (a calculation field, naturally). To get the highest value of certain type only, you would need to have a filtered relationship for each type. It's all possible, but it needs to be put in place in advance; it cannot be be improvised on the spur of the moment.

[clp8b]

OK. I could make a bunch of table aliases. I think I will go with that for now. It just seems like running a sql script would be more efficient. Thanks.

[RalphL]

Before you add a bunch of table aliases take a look at what you can do with SQL. I modified Comment's file by adding 2 global fields and a calculation field. The calculation uses the SQL Runner plugin. The SQL part should work in an SQL plugin.

ExportMax2.fp7.zip