6 Replies Latest reply on Sep 14, 2011 8:08 AM by CarriePledger

    (internal SQL) query to export in excel flat file

    CarriePledger

      Title

      (internal SQL) query to export in excel flat file

      Post

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

        • 1. Re: (internal SQL) query to export in excel flat file
          rjlevesque

          First question, are you using FMP Advanced?

          Everything you mentioned filemaker can do, you just have to learn to think Filemaker. Now understand I am not saying think Filemaker and not SQL. What I am saying is you will need to learn Filemaker, and how it relates to your knowledge of SQL. Not to mention in FMP 11 it is capable of SQL commands.

          • 2. Re: (internal SQL) query to export in excel flat file
            CarriePledger

            Hi there.  Thanks, Yes, I am using advanced.  I actually know filemaker fairly well, can do some advanced scripting.  I don't know SQL that well.  I just know this is very simple with SQL and I know I am going to have jump through a ton of hoops to do it in filemaker...at least I think I will.  I'd be happy to be proven wrong.  As I understand it SQL in filemaker (the execute SQL script step at least) is only for external commands.  When I tried it I had to enter an ODBC.  Is there a way to type in an internal SQL statement? That would be great.  I'm trying a few of the plug ins now but they don't seem to work with the execute SQL script step either.

            • 3. Re: (internal SQL) query to export in excel flat file
              philmodjunk

              I don't see anything all that difficult there and it doesn't require Advanced, though I always recommend that developers get that version for their own use.

              Reports like this have been done for a long time with much more primitive versions than the current.

              You should have a separate table for patients where you have one record for each patient. Base your layout on this table.

              You can use a one row sorted portal to list the most recently dated lab for each patient and the lab results for it. With FileMaker 11, you can apply a portal filter based on a global field to specify a specific labe test for your report. In older versions, a similar relationship based approach can be used for the portal to make the desired test selectable.

              • 4. Re: (internal SQL) query to export in excel flat file
                CarriePledger

                Thank you. That would be a good option except that I have multiple situations like this and multiple tables I want to pull from.  For this two table example I have 10 different types of labs, preop and postop and I want sometimes the lowest, sometimes the max result depending on the lab.  Also, my goal is to pull the necessary data so that it can be exported to an excel file in one row per patient form, not to view it on a layout in the form of portals.

                • 5. Re: (internal SQL) query to export in excel flat file
                  rjlevesque

                  Creating and printing forms/reports of information in FMP is a breeze, let's get you on track first because the reports are easy. You shouldn't have a lot of trouble doing this, just a few layouts to switch between to present the various forms of information the ways you want it displayed.

                  • 6. Re: (internal SQL) query to export in excel flat file
                    CarriePledger

                    Hmm..I am finding it interesting that the responses so far have not actually addressed the goal of creating a limited query of the data to produce a flat file excel sheet but have rather focused on displaying the data in form style reports in very general terms.  As far as I can see a summary report is not going to meet the goal becuase I want to limit the data and I want to export it into an excel file.  Maybe I am not being specific enough.  Lets say I have three tables: Pt demographics, labs and complications. 

                     

                    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 with MRN 1, 2, 3...

                     

                    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

                     

                     

                    All I want is 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.  Therefore I do not want all of the information to pull.  I also want it all in one row per patient not in a subsummary form.  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:

                    PatientMRN   highest preop IGF lab         highest postop IGF      highest preop GH         highest postop GH          complication CSF
                    1                    1313                              22                                         23                                  9                           yes
                    2                     155                                                                            13                               44                            no   

                                                             

                    How can I pull data in a way that will make multiple limitations and result in this excel table?