8 Replies Latest reply on Apr 12, 2016 3:28 PM by macwombat

    Want to export 1 record from 1 patient that has multiple records

    DouglasGrant

      Hello,

       

      I use Filemaker Pro 12 Advanced as a proxy for an Electronic Medical Record.   I am a novice database designer and user.  I store a lot information about the patient's visit with me that ultimately ends up in a Word document that is then uploaded into the official medical record.  I am sure that my database is not normalized and my attempts to develop a normalized database have not allowed me to export information in the manner I need it. My problem is exporting 1 record of multiple records for a given patient.

       

      I have a database setup with the following relevant tables:  1) Patient Info (demographic info such as ethnicity, handedness), Referral Information (who referred the patient, for what reason, and the date of intake), 4) Medical History, and 4) score on the mini-mental status examination,  I have linked all my tables by the patient's medical record number.  In this setup, patient information is not expected to change between appointments or over the course of our relationship.  Referral information may be different each time I see a patient and the patient have different problems.  Also, medical history and mini-mental status examination score may change.  If I only have seen the patient once, then exporting the info to a Word file is no problem.  However, I do not know how I can export the basic information that does not change and all the information that is related to a specific visit.  Currently, if the patient has been seen twice, and there are two records for each of the tables except the patient table, and I export the data, only the first record is exported.  What I would rather do is find a set of records for a specific date of service and export all that information.

       

      I would appreciate any help that I may receive.

       

      Douglas Grant

        • 1. Re: Want to export 1 record from 1 patient that has multiple records
          macwombat

          Hi Douglas

           

          Are you attempting your exports from a layout based on the Patient Info table?  That will make it hard to achieve what you want.  Decide what table and record you want to be able to isolate and export.  Run your export from a layout based on that table.  You can still pull the patient information back into that layout.  You can always create extra Table Occurrences (TOs) in FM to provide yourself with different options.  Context is really important and it starts from the layout and the TO that the layout is based on.

           

          HTH.  Chris

          • 2. Re: Want to export 1 record from 1 patient that has multiple records
            macwombat

            Also, it sounds like your referral information, medical history and mini-mental status scores are all related to each other by the visit - so you might want to have relationships that include a key field to reflect that - ie. a VisitID or something like that.  You can have multiple foreign keys in your record - so each one can have a PatientID as well as a VisitID - this gives you different options to locate the records in different tables that you need.

            • 3. Re: Want to export 1 record from 1 patient that has multiple records
              DouglasGrant

              Thanks for responding Chris.

               

              So to expand a little… The way my work is structured, is a little bit different than a traditional medical appointment.  For one encounter, I usually see the patients twice, on two different dates of service. The final report is the date of the last appointment.  I have a table I call Service and on that layout, I have dates for each part of the service I provide and can bill for (reviewing their medical record, interview, interpretation of test results, report writing, and providing results back to the patient).  Each of these services varies in terms of how long I spend on each part.  I may spend ½ hour reviewing a chart today, but an 1 hour tomorrow on a different chart.  I do not have the PatientID on this page.  I guess maybe I should have a visit table or appointment table with an ID, and use PatientID, VisitID, and ServiceID.  Hopefully, that will help pull information from all the tables for each encounter.  The last table I populate is a join table that is only composed of all the foreign keys. After that is complete, I do a search for the patient I just created, and have that as the only record showing. I have set up scripts that will export the data I want exported to word.  In that script, I pull in information from all of the tables.  In most cases, if I have only one occurrence of the patient in the database, FMP exports the information just fine and it is brought into a word file without problems.  When I have more than one occurrence of the patient in the database, the join table pulls the foreign keys from the first occurrence. 

               

              Would it make sense to have a table layout that only has the variables I want to export on it, rather than having the script pull the information together?

               

              Sorry this long.  Thanks for your help.

              Doug

              • 4. Re: Want to export 1 record from 1 patient that has multiple records
                macwombat

                DouglasGrant wrote:

                Would it make sense to have a table layout that only has the variables I want to export on it, rather than having the script pull the information together?

                Hi Doug - yes it might.  Not sure what you are doing with it when you get it into Word - but the process of exporting has limitations.  When you export records the context of the export starts with the layout you are on when you export.  That layout is based on a table, so the export is based on that table.  You can export data from a related record - but as you have found out it only includes the first related record.

                 

                If you are wanting to use Word to send out letters or bills that relate to each Service then you would need to base your export on a layout that is based on the Service table - then pull the patient information back into that layout.

                 

                Another thought would be ... why use Word?  Why not create a layout for printing directly from FileMaker.  Base the layout on patient and have a portal to show the related records.  Use the print sliding functionality and the portal will only show the number of rows required - ie. if a patient has only had one related Service record the portal will print 1 row.  If the patient has 3 related Service records the portal will print all 3 rows.

                 

                HTH.  Chris

                • 5. Re: Want to export 1 record from 1 patient that has multiple records
                  DouglasGrant

                  Hi Chris,

                   

                  Thanks again for the response.  I think part of the problem is for me is that I am probably not using Filemaker Pro in a manner in which is intended.  I have tried to use FMP for the report, but I could not get it to look like what I needed it to look like.  When I say reports, I am meaning actually pages of narrative text.  So, I do not need to sum, average, or collect information to be displayed in a list, but rather, need to write a paragraph to pages based on the information.  I have become pretty skilled at using FMP to calculate Text calculations in which I select several variables and the paragraph is generated.  For the most part, I have not had a lot of export issues.  The only one that is somewhat troublesome is when I have seen a patient previously and already has data in the database.  In the past, I have deleted the previous data and input the information again.  This kind of defeats the purpose, of typing in something in one place, and being able to use that same information at different times and different places.  The WORD report is only a holding and formatting tool.  After I have the report in Word completed, I then cut and past that into and electronic medical record.  I am not sure if I could even do that step with FMP. 

                   

                  As an example of what I am talking about I have attached this screen shot.  As you can see, I have a few variables that I fill in and FMP produces the last box "Refhx".  FMP capture.PNG

                   

                  and the final product would look like:

                   

                  fmp example.PNGI don't know if it helps to see how I use FMP and Word together.  BTW, the Word document is a large Mail Merge with the FMP variables in the word template. 

                  Doug

                  • 6. Re: Want to export 1 record from 1 patient that has multiple records
                    macwombat

                    Hi Doug.  FMP is not a word processor, so it doesn't have certain advanced word processing features of Word, but it does sound like you could do what you want in FMP, which would save you time exporting to Word.  You might be able to export directly to the electronic medical record - you'd need to give us a bit more information about that before we could say - but at least you could cut and paste from FMP instead of having the intermediate step of going to Word.

                     

                    Probably you should set up a new table for your letters / correspondence.  In that table you should have a foreign key for your Patient ID to link each letter to the patient, and if letters related to individual visits with a patient, then you could have a second foreign key for the Visit ID to also link the letter to a particular visit.

                     

                    Create a layout for your correspondence with things like letterhead information etc in the header and footer.  Then create a body part with a text box - make the text box as long as you think your longest letter is likely to be - it can be 3 pages long if you want.  It looks like you've already got a field set up to aggregate your text from your various other data fields.  Insert that field as a merge field into the text box.  Set the text box  in the Inspector>Position tab to "Remove blank space by "Sliding up based on "All objects directly above" " " - with the "Also resize enclosing part" selected.  That will automatically remove blank space from the text box and slide the footer up so that shorter letters don't have a lot of blank space in them.

                     

                    HTH.  Chris

                    • 7. Re: Want to export 1 record from 1 patient that has multiple records
                      DouglasGrant

                      Hi Chris,

                       

                      I am not sure, but it may be that these filemaker functions are beyond my ability.  I did as you suggested, set up a new table, and new layout based on the new table, created a text box, and inserted the merged field, set the controls as stated.  On screen and in print, there was no adjustment in the format based on sliding  (same experience as I have had in the past). There is a huge amount of space between the end of the merged field text and the boundary of the text box, and the the next merged field does not move up, nor does it expand. 

                       

                      The other problem that I ran into is that there was no way to select and copy the information so that I could past it directly into my EMR. 

                       

                      I sincerely doubt that there is a way to export data directly into the EMR.  I work at a fairly large hospital and they have taken great lengths to prevent us from tapmpering with things.  I cannot install or delete any program or setting from my computer without IT, as everything requires administrative logins.  We use EPIC EMR.  Other than that, I am not sure what other information is needed. 

                       

                      I now have run into a new problem, and I think might be because of the different tables and layouts I have added.  Previously, exporting the record in the MER format worked well with Word.  Now, after I export the data from FMP, when I try to import it into word, all the data are combined into one field.  I did not change anything with the export methodology, or the Word File.  Word asks me if how the fields are delineated (, vs tab).  No matter what I select, the import fails.  I have tried to set up an OBDC connection, but apparently, I need the administrators password to do that.

                       

                      I am at a loss.  In trying to make this more functional, I have lost function.

                       

                      Doug.

                      • 8. Re: Want to export 1 record from 1 patient that has multiple records
                        macwombat

                        Hi Doug.  I don't have the time to look at this today (my time in Australia) - but tonight I'll do up a quick sample database for you that will show how the printing sliding features work and so forth.  Chris