1 2 Previous Next 17 Replies Latest reply on Jun 8, 2010 1:01 PM by philmodjunk

    Medical Database



      Medical Database


      Hi guys,


      I am a doctor and I'm developing a medical database solution to keep record of the database of all my patients and doing the normal stuff like fixing appointments and printing out prescriptions.


      I have designed the basic interface and there are a few things that i would like your help with.


      The first thing that I would like to do is to copy the details of the patients in various related tables, so that there is no duplication, I have developed a reception interface where all the patient details would be filled in, and after that it will display on a screen, from where the concerned doctor will click and his respective case sheet will be opened.  But in that case sheet, I have not been able to get the data entered previously.


      Any ideas on how to do it??


      Secondly, how can I enter data for multiple visits of the same patients and display a short summary of the diagnosis and treatment given in the previous visits.


      I am a doctor and not really a software programmer, so any help for a layman would be greatly appreciated




        • 1. Re: Medical Database

          It's better to avoid "copying details" from previous entries. Instead you can simply display that data from a related record.


          To better assist you, can you post an example of what kind of details you want to "copy" here? (Click the reply button in upper left to add your response to the bottom of this thread.)

          • 2. Re: Medical Database

            Dear Phil


            Thanks for you reply,  I'll try and explain everything more elaborately in this reply of mine.


            We are a family of doctors handing 4 specialities - Dental, Gynaecology, Psychiatry, Ophthalmology. 


            I have made only one database in the name of "Main". And I have made plenty of tables in various headings like Reception, Dental, Gynaecology, Psychiatry, Ophthalmology, Prescription, Appointments, Glasses Prescription, Inventory, Expenses and so on.


            "Reception" is the main registration unit, where all the basic info of a patient will be filled in. After that the receptionist will assign a doctor and department to the patient. That can be seen as a list view of the reception by the doctors, they will click on the department and the respective case sheet (Dental, Gyn, Ophth, Psychiat) will open up. I have put a script where the respective case sheets open up. (Although I haven't still found out a way to display only the patients of one particular day)


            Now I have tabbed all the layouts, wherein the first tab is the general patient info that is the same that is displayed in the "reception". like the Patient Registration No., the name, fathers name, address, age, sex, etc.  I was hoping that something could be done so that, all the general patient info can be transferred to the respective case sheets, when clicked on the "list view" of "Reception".


            Secondly, will it be possible to just put in a Registration Number and all the details of the patients coming out, like if I directly wanna give presciption to a patient.


            I hope I've explained a bit more thoroughly this time around,  Waiting for your replies.





            PS. I might require more help regarding the printing pattern of the prescription and the glasses prescription in the future, so might keep on troubling you from time to time. :)




            • 3. Re: Medical Database

              If I understand correctly, you are creating a new record in your reception table each time a patient visits your office. You should split the data you currently record in this table into two tables: Patients, Reception


              Put all the patient data such as name, gender, address, etc in the Patients table. Include a serial number field called PatientID and use it to link these records to your other tables. (I'd also create doctors table listing all your doctors with a doctor ID number as well.)


              Remove the patient detail fields you've now placed in Patients from your Reception table and add a PatientID field. Define a relationship linking the two tables like this:


              Patients:: PatientID = Reception:: PatientID


              Make Reception:: PatientID a drop down or pop up menu listing Patient ID numbers in column 1 and patient names in column 2. Add the desired patient detail fields from Patients to your Reception layout. When you select a Patient from the PatientID menu, their previously entered details will automatically appear on the layout.


              After you get this working and you get lots of patients in your database, you may need a better way to select patients. Here's a tutorial on how to search your Patients table by name, but enter their ID number into a record in your Reception table:


              Tutorial: How to use an auto-complete drop down list when selecting records related by ID number


              • 4. Re: Medical Database

                Dear Phil,



                Thanks for your suggestions,


                There are a few points though, that I would like you to take into account.


                I have already given a Patient ID no, in my table "Reception" and in the clinical specialities we have. I'll be taking the specific example of my speciality - "Ophthalmology".


                I have added the same patient id field in the ophthalmology table and related the two tables using the BMH No.


                I just wanted to know that as far  as my understanding is concerned, I'll have to enter the patient Ids in the value list. and it will be come a tedious tast putting every new patient in the value list again.


                Is there a way that the value list keeps on getting added as I have auto entered the patient Id in the reception layout. Or can I use a script in some way, so that all the fields can be taken up from the "reception Layout" on clicking at the patient Id in the Reception Layout


                Secondly, I would be thankful, if you could tell me a few basic steps, may be I will improve as I learn fm more and more, because I didn't know how to decipher Patients::Patient ID = Reception::PatientID.  Where and how do I write this in the relationships?? If its just matching the patient id field of both the tables, then I have done that.




                • 5. Re: Medical Database

                  Patients:: Patient ID = Reception:: PatientID  is just a simple way to use text to show how the fields should be matched up in Manage | Database | Relationships.


                  In Manage | value lists... you can set up a value list that gets its values from a field. Click the "values from a field" option and you can select a field such as PatientID to be the source of values in your list. You can even specify the patient's name for the second column in the value list. Now, when you create a new record that generates a new PatientID, that Patient's ID code will automatically appear in your value list.

                  • 6. Re: Medical Database

                    Hi Phil,


                    I could do the mange value list using the field as you had mentioned, but there are two problems that I am facing with these.


                    1. If the list gets too big, how am I gonna find out the patients with the particular ID,it is gonna become really tedious.


                    2. It still displays just the patient id, the rest of the information is still not ther, like the name address associated with that particular patient id.  How do I get that to work again.


                    And, if its possible, I can send you the file that I had made, so that you can have a better idea as to what I wanna do. and then you can guide me accordingly, if that isn't too much of a hassle for you.




                    Varun Baweja

                    • 7. Re: Medical Database

                      Yes, when dealing with lots of people, there a problems. Value Lists using IDs become long, and are particularly slow to load on hosted files. It seems a good way to get started, but gets more and more annoying. 


                      If you show the IDs, you cannot navigate the list using names. If you do not show IDs, but use the [x] Show values from 2nd field, then choose [x] Show only 2nd field, then there is another (fatal) problem. It can only show the 1st person if 2 people have the same name (which will happen sooner or later).


                      The best solution is I think to not use a Value List, but use a "filtered portal" (or a list view in a small window with filtering ability; more easily done in FileMaker 10). The filter could be by whatever is most useful. I would think by either ID or Last Name (type either one to narrow the results). The great advantage of using a portal is that you can put other fields (though space becomes a consideration). You can show the ID, and the full name, and at least part of the address; whatever it takes to make a positive match.


                      The disadvantage of a portal is that is either takes up some room on the layout, or requires a separate controlled window. It does not have to take up a lot of room however. It is fairly easy to narrow a list of 10,000 people down to less than 10 matches by typing 4 or 5 characters (well, more for Smith, etc., but the portal can scroll for those).


                      This is not really beginner level unfortunately, but there are examples around. I'll have a look here, when I have time. But I have to run right now. 

                      • 8. Re: Medical Database

                        Thanks Jones,


                        I need all the information here that I can get my hands on.


                        I know a lot of things are difficult in the beginner level, but that won't deter me to stop trying, hopefully one day, I'll be able to get it right.


                        If its possible can anyone of you tell me, is it possible to relate the various fields to one patient Id, so that when I enter that patient id, I can get a display of the information that I need in a particular layout.


                        If that can be done then most of my problem would be solved.


                        Thanks in anticipation

                        • 9. Re: Medical Database

                          "is it possible to relate the various fields to one patient Id, so that when I enter that patient id, I can get a display of the information that I need in a particular layout?"


                          Yes, this is a basic tenet of relational database design. Any data about a Patient can appear anywhere where you have a valid relational connection back to the Patient table, via the (unique) PatientID. Actual data should only be entered once. It should only be looked up into other tables if there is a very good reason to do. 


                          You do not need to create fields like "Patient Name" in other tables, unless you really need to store the name for historical reasons (seldom), or need it to be local for (very) fast Finds.* This is called "redundant" data, and is to be avoided whenever possible, as it may have a "maintenance" cost. (Oops, we misspelled this guy's name. We have to go find him in 6 tables and change it; rather than just having to just change it once in Patients.)


                          It is also unnecessary to create fields in other tables, which calculate the patient's name, by pointing back to the Patients table. Because, since you have a relationship, you can just put the Patient table's original field directly on the layout; comes to the same thing with less overhead. 


                          * A Find on a related field, from a child table, Patients::Last name for example, is not as fast as a Find on a local field. But it is still plenty fast, unless you have 100,000+ Patients. It kind of depends; there is often a choice here.

                          • 10. Re: Medical Database

                            Example file showing a filter on First or Last name (using global fields). This technique was from a file by John Mark Osborne (hopefully he will not mind; I put his web site URL). I've added a further test with the calculations, so it does not show anything until you type something to begin filtering (faster).


                            It also has a script trigger attached to the fields, so that it can automatically filter as you type in FileMaker 10. But it will work with earlier versions also, with tab, return or enter. 


                            It does not do anything else, as I wanted to show only the 1 technique. But I think you can guess that the next step would be a script, so clicking on a portal row would set an ID into a field. 



                            • 11. Re: Medical Database

                              Here's another option for managing large, name based value lists you can consider:


                              Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

                              • 12. Re: Medical Database

                                Hi Guys, I'm sorry I was off topic for a while because I had my exams in May, and just got over with them.


                                I shall be thankful if you guys can help me around the EMR that I'm trying to develop for my hospital.


                                First of all, when i try to enter a new record, the name and other field values remain the same even in the new record. Whats amiss here??


                                Secondly, I want to know how to associate records with the unique patient id no., like if I enter the id, the previous visits of the patient with the treatment and other stuff would show in portals.


                                Thirdly, how can I enter multiple visits in the same patient ID? and their other details.


                                Lastly (for the time being) how can I print prescriptions, with the same address and name of the patient, automatically coming on the prescription???


                                I shall be thankful for all the help that I can get.




                                • 13. Re: Medical Database

                                  Well Fenton described one possible solution and I posted a link to another. Which are you trying to get to  work for you?


                                  You'll need to set up tables and relationships correctly to get this to work for you. Set them up right and you can start a new record, select a patient by name (from Fenton's portal or my global search field with search script) and/or ID (from a drop down) and get their contact info or list of records from previous visits to show up in a portal.


                                  That first step: correct relationships and table definitions, seems to be where you're having a problem, but can't tell for sure from what you've posted so far.

                                  • 14. Re: Medical Database



                                    Thanks for your reply,  I just wanted to ask you if its possible for me to send the file over to you, so that you can see and also probably have a better idea of what I'm talking about




                                    1 2 Previous Next