9 Replies Latest reply on Apr 16, 2012 9:27 AM by GuyStevens

    Please help a time poor newbie surgical trainee!



      Please help a time poor newbie surgical trainee!


      I'm a junior paediatric surgical trainee and i'm trying to set up a filemaker database to keep track of patients I've seen and whose care I've been involved in.  I have just purchased Filemaker Pro 12 and have downloaded the associated Filemaker Go for iPad app.

      I have identified the following fields (arranged into tables that I think make sense, but am happy to be corrected):


      Demographic Table

      • Patient ID (this is the unique patient identifier)
      • UR Number (the hospital's pt identifier, but given i'll be working across multiple hospitals I will need to allocate my own identifier)
      • Last Name
      • First Name
      • Date of birth
      • Age (calculated from date of birth)
      • Street
      • Suburb
      • State
      • Country
      • Postal Code
      • Mobile phone number
      • Home phone number
      • Parent1 Last name
      • Parent1 First name
      • Parent2 Last name
      • Parent2 First name


      Admission Details Table

      • Patient ID (the same unique identifier as above)
      • Admission ID (do I need this field? - each patient needs to be able to have multiple admissions. I'm thinking of trying to make a one (patient ID) to many (admission ID) relationship)
      • Admission date
      • Discharge date
      • Ward
      • Bed number
      • Diagnosis
      • Admission history
      • Birth history
      • Past history
      • Family history
      • Social history
      • Physical examination
      • Investigation results
      • Progress
      • Plan
      • Tasks
      • (NB: from "Diagnosis" to "Tasks", these are all just simple text fields)


      Operation Details Table

      • Patient OR admission ID ?? (see next point)
      • Operation ID (do I need this field? - each patient needs to be able to have multiple operations in the one admission. I'm thinking of trying to make a one (patient or admission ID) to many (operation ID) relationship)
      • Procedure region (body region of operation)
      • Operation
      • Role in operation
      • Details of operation
      • Operation date (date field)
      • Time since operation (calculated from operation date, preferably displayed in days and weeks/months and years)
      • Complication
      • Complication severity
      • Complication details
      • Photo field (photo placeholder)
      • (NB: other than those indicated above, the other fields are text fields or simple drop down menus)
      I am getting stuck on the building of relationships between the tables.  Essentially I need to have one patient be able to have multiple admissions, and have each admssion for a given patient to be capable of having multiple operations during that admission.  Each admission ID needs to be assigned to a patient ID, and each operation ID needs to be assigned to an admission ID (and hence connected to a patient ID).  Am I being too ambitious?
      In the end, I would like to be able to use the database on my iPad at work, so I would like to create a single user interface (layout).  In the interface I envision having the Demographic Table details displayed permanently in the upper part of the layout, while Admission Details and Operation Details tables in the lower part and (preferably) in a tabbed arrangement to allow alternating between the two.
      If I can achieve that initial set up I would be absolutely thrilled and any help with pointers in the right direction would be greatly appreciated.
      Other goals for the database, but perhaps I can build these in later:
      • I would also like to be able to identify which patients are current inpatients (I'm assuming this would be based on their discharge date)
      • In the admission and operation tabs of the user interface/layout, I would like to be able to click through admissions & operations (in order of most recent to most distant; presumably by sorting records by date)
      • Be able to create reports of that include fields from all three tables (eg: list of current inpatients, operations during a particular time frame)
      I have had a crack at setting this database up, but just keep running into things I can't figure out, and surgical training unfortunately makes getting past these troubles hard!!  I really appreciate any help that can be offered.

        • 1. Re: Please help a time poor newbie surgical trainee!

          - Are you being to ambitious?

          Not at all, you haven't even scratched the surface of what you can do in filemaker. What you have seems really basic to me.

          - Do I need an Admission ID?

          Every table needs it's own ID as an auto enter serial number field. Otherwise you can't make proper relationships.

          I have a little bit of time on my hands so I'll make you a little demo file.
          I'm just downloading a demo of Filemaker 12 now to do it in the fmp12 format.

          I had a few questions: Do you have many tasks?
          Because in stead of a text field that could become a portal with the ability to have an unlimited amount of tasks.

          Also for the parents you could make a separate Table so you can input as many parents or contacts as you want.

          One tricky thing I'm thinking right now, of the top of my head. Is that you need to be carefull to link the operation to the correct admission. Because an admission is linked to a patient, but an operation is linked both to a patiënt and to an admission.

          Anyway, I'm going to have a look and see what I can come up with.

          • 2. Re: Please help a time poor newbie surgical trainee!

            The relationships were a little tricky, and maybe a little hard to figure out if you don't have a lot of Filemaker eperience.

            I made you a little demo, pretty basic.

            You can try it out and see if this is already in the good direction.

            It's pretty basic but from here on you can start adding all kinds of functionality.
            I didn't test it to the bone, but I tried some stuff and it looked like everything was working pretty well already.


            P.S. Don't mind the dummy data. I might have made some mistakes in the medical terminology.  :p

            Like I said in my previous example you can add more portals, for instance for tasks, if you need many tasks; Of for phione numbers, if you have lots of phone numbers of different types.

            • 3. Re: Please help a time poor newbie surgical trainee!

              Alternatively, if you need more space to write stuff down you could also just add more tabs and spread the fields out over multiple tabs.

              • 4. Re: Please help a time poor newbie surgical trainee!

                I would counsel you to check with your hospital (administrator, legal, advisor, etc) to see if you can legal create a database to contain sensitive private patient data. It may be illegal or you may have to follow stringent guideliness. Patients may lose confidence in a hospital that allows the uncontrolled and unprotected creation of databases of patient data. You may open the hospital to a lawsuit for privacy violations and even yourself. Why risk your future on this project?

                Even posting the question on the forum with your name might be problem.

                I've had the shivers when teachers asked similar questions about creating databases for their students to be shared with other teachers in order to point the finger at 'rotten kids'.

                Also, the hospital may have a fully vetted program you can use on the iPad and save yourself a lot of time and trouble.

                • 5. Re: Please help a time poor newbie surgical trainee!

                  Thanks for your concern Jack.  I had similar thoughts to yours when I first began planning this database.  I was cautious before embarking on this project and have checked with the correct people at my hospital and have sought medicolegal advice.  I have approval to continue with this project.  The information in this database is for me as the patient's treating doctor and no one else.


                  To DaSaint - what can I say?!  Absolutely brilliant.  You have created in a couple hours almost exactly what I had in my head.  I have been trying on and off for a couple months and hadn't even come close to what you've produced.  Thank you so much.  I have tinkered a tiny bit to make some of the data entry easier, but other than that I haven't had to do anything to it at all.  There is perfect amounts of space for typing history, examination, and investigation information, and perfect space for reminder notes in progress, plan, and tasks fields (see my little example data in the file).


                  The icing on the cake for this project would be the ability to generate a report of current inpatients "at the touch of a button".  I would want to be able to print this report to use as a worksheet during the work day.

                  I would include the following fields:

                  • Ward
                  • Bed
                  • UR Number
                  • Last Name
                  • First Name
                  • Date of Birth
                  • Age
                  • Admission Date
                  • Diagnosis
                  • Operation
                  • Operation date +/- Days since operation
                  • Investigation results
                  • Plan
                  • Tasks


                  I'd want the report to include only patients who have not been discharged (ie: discharge date field blank at time of running report), as this would save sifting through an ever growing database to find the patients that are currently 'active'.  The admission & operation information would be the most recent admission and most recent operation (ie: the current admission and the most recent operation that occurred during that admission).  I'd also want to group the records by ward, and then sort by bed number then last name within each group (this will make a massive difference to the efficiency of our ward rounds!)


                  I don't know if this is possible, but I can see in my head 2-3 buttons (similar in appearance to the "Create new admission" button) in the header of the Lay_Demographic screen - "List view of all patients" and "Create Inpatient Report" (which would run the report described above) +/- "Show records of current inpatients".  My thinking behind the last button is to be able to filter the records in the database (is this a fancy find function that I'm thinking of?) so that when I press the button it will find the records of the current inpatients so i can click through them only without having to click through all the records of the database.  It was for this reason, and for the generation of the report described above, that I was thinking I'd need to find a way of indicating the 'status' of a patient (ie: "current inpatient" vs "not current inpatient").  As I've discovered from your amazing demo file, I'm pretty sure I have no idea how to do this.


                  Here is my tinkered version of the file.  I would be forever in your debt if you could help me out with these last couple of things.  Thank you so much - you have no idea how much easier you've made my work day.



                  PS: Your medical terminology was excellent - don't sell yourself too short!  We all feel like some of those things in the complication severity drop down box from time to time!!

                  • 6. Re: Please help a time poor newbie surgical trainee!

                    Biggest problem I see is that you will be carrying around Protected patient information.

                    Check HIPPA laws, if your practicing in the United States

                    If you are in the US this info could be considered a dischargabe offense.

                    • 7. Re: Please help a time poor newbie surgical trainee!

                      I'm glad everyone is concerned about patient confidentiality - it is extremely important.  I am not practicing in the USA and I have checked local laws carefully and it is okay for me to proceed with this project.  It is essentially a private surgical logbook in electronic form.  Paperbased versions of the same have been in use in my country for decades and the same laws apply to them as to this project.  I absolutely agree with everyone's concern for patient privacy, as it is crucial to me performing my job to the best and safest standards.

                      • 8. Re: Please help a time poor newbie surgical trainee!

                        As long as the computer or IOS device is in your possession there is little problem but unfortunately they can be misplaced or stolen. So be sure to use the locking feature of IOS that requires the PIN number every time you turn it on and then an account name and password for Filemaker Pro and Filemaker Go. Set the iPad or iPhone to sleep after 15 minutes or so.

                        Using Filemaker Server and WiFi if it is available would also eliminate the storing of informatiion on the IOS device and you could share the file with other doctors and restrict access to the individiual patients by coding access to an account as is done in business for Bookeeping, Sales, Purchasing, etc. If you do this you would need to investigate security and encryption and VPN, etc.

                        Good luck and beautiful sunsets.

                        • 9. Re: Please help a time poor newbie surgical trainee!


                          Did a little update (Well, not little) today.

                          I noticed already really quick that I had been wrong to make my main layout based on the "Demographic" table because that made listing  
                          admissions pretty difficult.
                          So I changed the structure a little and made the "Admissions" table the main table in the main layout.
                          That way you can go to a list of admissions, filter it with a find and go back and forth between your "Detailed view" and your "List View"  
                          while staying within the same find.

                          That means you can go into listview and:
                          - Find only "In patients"
                          - or "in patients" on a certain ward
                          - or all patients on a certain ward...

                          And then you can click on one of them to see the detailed view and from there you can browse in those found records.

                          Just try it out, you'll see what I mean.

                          Your list view is at the same time your list of all "In Patients" if that's what you select and "Find" with the button.
                          From your list view you can print the found records on another layout that's printer friendly.
                          I'm not extatic about the layout of the Printable Report, (It doesn't look great yet) but I don't really know what you prefer in that  
                          layout. Merged fields are a little prettier but fields can have their borders active so you can write with a pen in the empty fields when  
                          you print the report.

                          It depends on how you are going to use the printed report. You could make it larger to have more room to write or smaller.

                          I also made a layout based on the "Demographics" table to show your patient data.
                          All patient data can be filled out from the "Lay_Admissions" but the other layout gives you a handy way to review the history of  
                          Admissions and Operations.
                          From the "Patient History view" you can click on an operation to see it's details in the "Lay_Admissions"

                          The idea is that you go into your "Lay_Admission" and that you hit "New Record" when a new patients gets admitted.
                          If the patient exists in your database you can just select him on the top where is says "Patient" (Yellow Box)
                          If that patient happens to be someone else with the same name or you clicked the wrong one you can click the "Brush" to clear the field.
                          With no patient selected you can start typing the patients name in the empty fields and a new patient record will automatically be created and linked. (So no need to go to the "Lay_Demographic")

                          Because the two layouts look alot alike I gave both of them a colored header. So you know where you are.

                          Another thing I was thinking about is a history of visits.
                          When you visit a patient or you do rounds you could have a button that says "Record visit" or something like that that stores the current Date and time in a patients "Visited" list.
                          That way you know what patients you have already visited etc.
                          You could integrate this in a list so all visited patients go off the list or something like that.

                          Anyway, maybe something to think about.

                          Hidden buttons that might not be clear:
                          - In the Admissions List you can click on a patients name to see that Admission.
                          - In the "Patient History" you can select an admission to see all operations and you can click on an operation to see that in "Admission  

                          I'm going to have another look at the file later. (I've been looking at this to long now, my head needs clearing :)  
                          Let me know what features you are still missing.

                          P.s. I just selected random stuff while inputting dummy data. I don't know what most of it means so I might have sheduled some bizarre  
                          operations  :)