11 Replies Latest reply on Mar 26, 2012 11:54 AM by philmodjunk

    Creating multiple treatment episodes for one patient - Help!

    FrancisHall

      Title

      Creating multiple treatment episodes for one patient - Help!

      Post

      Hi,

      As part of my consultancy module at University I am building a product in Filemaker Pro that facillitates the recording of patients at a hospital.

      I've had no previous experience using filemaker but I am progressing steadily - however there is one big problem with the system that I have been unable to fix.

      A crucial requirement of the system is the capability for ONE patient to have MANY treatments.

      My patient table has an NHS number primary key which links to a treatment table (The idea is that each patient has only one NHS number, yet within this they can have many treatment ID's) From here the treatment ID link to all the other tables containing information about specific treatment.

      The Problem

      I have tried a whole bunch of methods, from scripts, to making the Treatment a calculated value (I tried using a function to count how many NHS number entries there were, if none Treatment ID = 1 and if more than one then the previous treatment ID + 1) this just sets the treatment ID to 1 permanently.

      I then tried making the Treatment ID an amalgimation of NHS number and the date of diagnosis. This works however it replaces ALL entries with the same NHS number instead updating the current one (Example - I have one patient with NHS number 100, I create a new entry of the same patient with NHS number 100, change the date of diagnosis, and it updates both entries with the new treatment ID)

      ANY help that you can offer on this would be hugely appreciated. No one uses Filemaker on my course and so it is impossible to get help there, although apparently my tables are set up correctly.

      If you've got this far; thank you so much for taking the time to read this and I hope someone out there can help me on the path to enlightenment!

        • 1. Re: Creating multiple treatment episodes for one patient - Help!
          philmodjunk

          Open Manage | database | Fields.

          Define a new number field in the Patient Table. Use field options to specify that it be an auto-entered serial number. Keep your NHS field as a simple data field in Patients, but do not use it as your primary key. Since this number must be entered from outside the database, a data entry error can create problems that we avoid by using the internal serial number as the primary key.

          Switch to the Treatment table and add a matching number field. Often, it's useful to name the primary key: __pk_PatientID and the foreign key in the treatment table as: _fk_patientID.

          Now click the relationships tab and drag from __pk_PatientID to _fk_patientID to link these two tables in a relationship.

          Double click this newly created relationship line and select "allow creation of records via this relationship" for the treatment table. This option isn't strictly necessary, but makes it easy to add new treatment records for an existing patient record in a portal to the treatment table.

          Click OK to exit manage | database.

          Enter layout mode, select your patient layout and use the portal tool to draw a rectangle on your screen and setup a portal to Treatment on your Patient layout. Add the fields you find useful to this portal.

          Now you can add as many treatment records as you want in the portal by entering data into the first empty row in the portal. fileMaker will copy the value of __pk_PatientID into the _fk_patientID of the new portal record for you.

          This is the simplest method for working with multiple related records. It is not the only option. I suggest you use this method to get started as getting it to work will confirm that you have successfully created the needed relationship between the two tables. You can always explore other methods once you have the basics working for you.

          • 2. Re: Creating multiple treatment episodes for one patient - Help!
            FrancisHall

            I've tried this and it's certainly a step in the right direction - thank you!

            It is successfully letting me creating multiple treatments. However when I go to the search page (Patient layout) to search for a patient, it is only displaying one entry in the portal, I'm not sure why..

            I was hoping there would be a way to search for a patient through NHS number or Name etc (Got this set up), and once you have bought up a patient with say an NHS number of 100 with 5 treatments, it would be great to then have a drop down box that lets you select a treatment and then that displays the relavent details. Is it possible to acheive this?

            • 3. Re: Creating multiple treatment episodes for one patient - Help!
              philmodjunk

              It is successfully letting me creating multiple treatments. However when I go to the search page (Patient layout) to search for a patient, it is only displaying one entry in the portal, I'm not sure why.

              You need to describe that in more detail. What mode are you in? Find or browse. I can se how the portal could appear to have just a blank row if you are in find mode but not show only a single record with actual data in the row. On this layout, when you enter layout mode and select layout setup, what is selected in the "show records from" layout?

              I was hoping there would be a way to search for a patient through NHS number or Name etc (Got this set up), and once you have bought up a patient with say an NHS number of 100 with 5 treatments, it would be great to then have a drop down box that lets you select a treatment and then that displays the relavent details. Is it possible to acheive this?

              This is possible and done all the time, but I'm not sure exactly what you are attempting to do. Do you want to create treatment records that describe treatment performed only for the current patient or does a treatment record describe a treatment regimine that might be selected for any number of patients?

              • 4. Re: Creating multiple treatment episodes for one patient - Help!
                FrancisHall

                Thanks for all the advice. The database relates to cancer care.  We are trying to record detailed infomration about Head and Neck cancer.  The treatment of one episode of disease will require a great deal of data to be recorded.  If a pt then develops a recurrence or a new primary cancer, then we will need to record all of the same information again, but relate it to the recurrence episode ie create a second batch of the same data.  we will then need to relate that back to said patient.  when searching for the information, we would like to be able to search by pt name, that search then displaying the fact that there is data relating to a primary disease and also a recurrence. 

                In answer to your sepcific question - Currently the button that navigates to the search feature contains a script that enters browse mode (the layout is related to the patient table). From here we can search records via NHS number/Name etc. This will populate the fields taken from the patient table, however the portal will only display the top row of data in the form view (however two entries are showing up in table and list views).

                Each Patient may have multiple unique treatments, every treatment will be unique and related only to that one patient.

                I have attatched a screen shot of the relationships if it helps.

                • 5. Re: Creating multiple treatment episodes for one patient - Help!
                  philmodjunk

                  we would like to be able to search by pt name, that search then displaying the fact that there is data relating to a primary disease and also a recurrence.

                  That can be done by navigating to a layout based on the patient table. YOu enter find mode, enter all or part of the patient's name in the name field(s) or you enter the NHS number into the NHS field. Then you perform the find to see what patient records match that data. Such searches of the database can be done by hand or with a script designed to make the search more user friendly.

                  however the portal will only display the top row of data in the form view (however two entries are showing up in table and list views).

                  That does not sound like you have a portal on your layout. Portal's are not visible when you are in table view. If you are sure that you used the portal tool (using it opens up a dialog box called portal setup...), make sure that your fields are fully enclosed by the boundaries of the portal. If a portion of the field extends outside the portal boundaries, you'll only see data from the first portal record instead of all of the related records.

                  I have attatched a screen shot of the relationships

                  That's a great idea but I can't see any such screen shot. Make sure that your screen shot is a file of type jpg, gif or png. Files of any other file type appear to upload, but never become visible as part of the posted message. A screen shot taken of your layotu while you are in layout mode may also be helpful.

                  • 6. Re: Creating multiple treatment episodes for one patient - Help!
                    FrancisHall

                    I beleive I did set up the portal correctly and it was displaying the corresponding result to the patient we search for, however we still can't bring up a selection box to choose which treatment to view for one patient.

                    I've taken a few more screenshots and converted them to GIF files so hopefully they will work this time.

                    • 7. Re: Creating multiple treatment episodes for one patient - Help!
                      FrancisHall
                      /files/aedefb3312/Screen_shot_2012-03-16_at_14.36.18.gif 1280x800
                      • 8. Re: Creating multiple treatment episodes for one patient - Help!
                        FrancisHall

                        The Care Spell ID was originally the Treatmeant ID (before I implemented your suggestion) and it will be used to identify different treatment episode for one patient (would be great to have a drop down box where you can select treatment 1,2 or 3 etc and upon selecting this it will display the relavent disease information)

                        • 9. Re: Creating multiple treatment episodes for one patient - Help!
                          philmodjunk

                          The design looks execellent. You indicated that you could only see one record at a time in the portal. That should only be the case if there is only one record in DAHNO Care Spell Identifier with a value in _fk_patientID that matches the value of _pk_PatientID in the current patient record.

                          Let's take the two issues on one at a time:

                          however we still can't bring up a selection box to choose which treatment to view for one patient.

                          Your relationship matches by patient ID. Thus, the portal will list all records with a matching PatientID to select and see a specific record in the portal would require either a different relationship, a portal filter, or a button in the portal row that you'd click to bring up that record on a layout based on DAHNO Care Spell Identifier (which could be a new window that pops up if you want to get fancy).

                          I'm not sure if that's what you want here or not, but it can be set up that way if you need it. What you ask, though could be a just a different way to say:

                          would be great to have a drop down box where you can select treatment 1,2 or 3 etc and upon selecting this it will display the relavent disease information

                          So let's deal with that issue first and you can then tell me if I have understood you correctly or not with regards to the first statement that I've quoted here.

                          In layout mode, select the DAHNO Care Spell Identifier::Care Spell ID field and use the Control Style drop down in the Inspector's data tab to change it from edit box to either drop down list or pop up menu. (You can try each out and see which you prefer.) Then click the pencil icon next to the "values from" drop down to bring up Manage | Value lists. Create a new value list and select the "use values from a field" option. This brings up another dialog box where you can select Diagnosis::Care Spell ID for field 1, click also include values from and then select a name or description field from Diagnosis for field 2. Don't select any other options in this box just yet. Now return to browse mode and try creating new records in the portal by selecting a value from the drop down or pop up. Given your relationship, you can add any fields from Diagnosis to this portal row and they will automatically display data from the selected record when you enter or select a value in the Care Spell ID field.

                          Once you have that working, you can experiment with some of the other options possible for your value list. If you ensure that the name or description field that you use for field 2 always stores unique values, you can click a control that hides the ID number when the value list deploys in your drop down or pop up. With that option selected, a pop up menu will display the matching name or description instead of the number when you exit this field. With the drop down list, you'll see the ID number when you exit.

                          • 10. Re: Creating multiple treatment episodes for one patient - Help!
                            FrancisHall

                            Phil, thank you for taken the time to help me.

                            I have tried implementing your suggestion but unfortunately it hasn't worked for me - the drop box still only displays one value (even when the table view lists multiple entries)

                            Given time/resource constraints, it is looking like we will have to scrap what we have done so far and go for a simple one table system :( as we can't justify spending much longer on this one problem.

                            • 11. Re: Creating multiple treatment episodes for one patient - Help!
                              philmodjunk

                              It's your database, but I doubt that a one table system will do what you need here.