1 2 Previous Next 21 Replies Latest reply on Jul 27, 2011 8:36 AM by CaitlinM

    Description Search Script

    CaitlinM

      Title

      Description Search Script

      Post

      Hello,

      I am trying to create a script that will populate information based on a search. For this script I am working with mainly 2 tables, one that links a referring doctors first names and last names to the patient information, and then through a uniqueID for each doctor, is related to another table with all the information related to that doctor.

      Table:

      Referral Info

      Fields: 

      Referring MD ID

      MD Last Name

      MD First Name

      Phone, Email, Address, Etc.

      Table:

      Patient Info

       Fields:

      Patient ID

      Referring MD Last Name

      Referring MD First Name

      Referring MD ID

      The problem is I imported this information from Access and so I have a complete table for the referringMD with all the information, which I then assigned unique IDs to each referring MD. However in the patient information table, the last and first names got imported, but without going through each patient, I do not know how to attach the referring MD ID to the patient information.

      So far I have this script which is not working:

      Set Variable [$SearchText; Value: Get Value (Get ScriptParameter); MD Last Name)]

      If [not IsEmpty ($SearchText)]

      Freeze Window

      Go to Layout ("Referring MD List")

      Enter Find Mode

      Set Field [Referring MD:: MD Last Name; Quote ($Search Text)]

      Set Error Capture [on]

      Perform Find []

      If [Get (FoundCount) = 0]

      Go to Layout [Referring MD]

      Show Custom Dialog ["No Doctor Name Containing" & Quote ($SearchText) & "was found."

      Else If [Get (FoundCount) = 1]

      Set Field [Referring MD:: ReferringMD ID; ReferringMDID]

      End If

      Thanks for any help you can give!

      Caitlin

        • 1. Re: Description Search Script
          philmodjunk

          What expression did you use to pass a parameter to your script?

          If you didn't set up a paramter, then the first line of this script could read:

          Set Variable [$SearchText; Value:  MD Last Name ]

          Then, in the last part of your script, modifiy it to be this:

          If [Get (FoundCount) = 0]
                 Go to Layout [Original Layout]
                 Show Custom Dialog ["No Doctor Name Containing " & Quote ($SearchText) & " was found." ]
          Else If [Get (FoundCount) = 1]
                 Set Variable [$MDID ; value: ReferringMD::ReferringMD ID]
                 Go To Layout [Original layout]
                 Set Field [PatientInfo::Referring MD ID ; $MDID]
          End If

          Note: the Referring MD layout should be set up as a list value layout listing doctor names plus additional fields (Perhaps their specialty and or an address?) so that the user can select from multiple matching doctor records by clicking a button in the layout body to select a doctor. That button needs to be a script with the same steps as the above "Else IF" script steps.

          For others reading this thread, we are working out a simplified version of one of the enhanced value selection techniques from this demo file:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Description Search Script
            CaitlinM

            Phil, 

            Thanks for the script, I have it all set up, however in my layout in which all the information, patient and referring MD will be entered in on -should I display the related fields of the ReferringMD::Last Name and ReferringMD::First Name, or PatientInfo::Referring MD Last Name - Right now when I try to click into the field on the layout it will not allow me to. Both the ReferringMD Table and Patient Info (Referral Information) are related to another table that has all the biographical information of the patient, which is the platform for the Layout, and referring information is just a tab control on this layout.

            I have the Referring MD layout set-up in a list which has all their information (Phone, Address, Email, etc) fields, So per your note - I need to create a button on the list field which will be attached to the script below?

            Set Variable [$MDID; Value: ReferringMD::Referring MDID]

            Go to Layout [Original Layout]

            Set Field [Patient Info:: Referring MDID; $MDID]

            Thanks,

            Caitlin

            • 3. Re: Description Search Script
              philmodjunk

              Yes, but you have to specify the Patient Info layout instead of "Original layout".

              You should use the name fields from the Referring MD table to display  any information, such as their name, about the referring doctor on your layouts.

              The fact that you have a third table involved is new information here. I was assuming that you needed to link the Patient Info record to a Referring MD record and that your original layout base based on the Patient Info layout.

              That will complicate this implementation a bit. It will depend on the relationships you have defined and what you need displayed on the layout to determine what changes to this script or your layout that might be needed to get this to work fully.

              It sounds like you have these relationships, but I could be wrong:

              PatientInfo---<Patient_Referrals>---Referring MD

              PatientInfo::PatientID = Patient_Referrals::PatientID
              Referring MD::Referring MD ID = Patient_Referrals::Referring MD ID

              I'm using different names here to try to be clear about the intended function of each table. PatientInfo would record patient name, contact info, etc. Refferring MD lists your Referring MD's and any relevant info about each such MD. Patient_Referrals would link a PatientInfo record to a Referring MD record and serves as a "join" table so that any given Patient can have multiple referring MDs and any given MD can refer multiple patients. It appears that your layout is based on this join table. This works, but some times the same is done via a portal to the join table, placed on the PatientInfo layout, but with fields from Referring MD included in the portal row so that selecting an MD in the portal links them to the patient and also displays name and other data about that MD.

              • 4. Re: Description Search Script
                CaitlinM

                Yes those relationships are correct. I did not think the portal was the right choice, because this database is for a specialty group, so the "Referring MD" that I refer to is the MD that was the doctor who specifically referred them for their current disease. In the referral information, or "Patient_Referrals" table, there is also a field for their Primary Care Doctor, but that is unrelated to the Referring MD. I am focusing on the Referring MD because that is who our doctors correspond with and we need their information (phone, email, etc).

                Should I be using a portal instead of this script?

                Thanks

                • 5. Re: Description Search Script
                  philmodjunk

                  You'd need the script either way. Using a portal here is just an option to consider. A portal allows you to see a list of all the MD's that have referred the patient over time, but can limit the amount of data about that doctor that can easily be displayed. You are not restricted to either method, but can actually use both in the same database for different situatons and layouts.

                  I would think that the Primary Care Doctor would be recorded in Patient Info, as I would assume each patient would have one such doctor rather than different Primary Care Doctor for every disease or condition for which they were referred.

                  Your portal to Patient_Referrals can include a field from Patient_Referrals for the disease and contact info fields from Referring MD. That's just an option to keep in mind. I wouldn't try this until you get the current set up working for you.

                  Now that I have confirmation on your relaitonships, I can point out the changes that need to be made (if you haven't done so already) for it to work from your Patient_Referrals based layout.

                  The set field steps in both scripts should modify the Referring MD ID field in Patient Referrals, not patient info. In the case of the selection script for the button on the Referring MD layout, Go To Layout should go to the Pattient Referrals layout instead of Patient Info.

                  • 6. Re: Description Search Script
                    CaitlinM

                    I will make those changes and send over the final script that is put together - I just wanted to double check with you about the layouts:

                    The main layout is called "Patient Entry" and that is where all patient and referring doctor information will be displayed and entered. The two tables that relate to referring MD (Patient Referral and Referring MD) are both hidden as of right know since I would like there to only be one layout in which the patient and MD data gets entered on. So do I want the set fields and Go To Layout to specify the "Patient Entry" layout, or the Patient Referral Layout (which will not be seen by those who will enter the data. One again, the main layout "Patient Entry", has a tab specifically for the referring MD but it is the related data shown on the main layout. 

                    Thanks,

                    Caitlin

                    • 7. Re: Description Search Script
                      philmodjunk

                      Without a portal, that layout approach doesn't make it possible to list multiple referrals for the same patient. (Say a patient is referred for condition 1 by a gynecologist and for condition 2 on a different date by an Oncologist.) In this case, you'd need to base your layout on the Patient Referral table, with fields included from the other two tables.

                      However you set up your layouts, you still need the relationships to work out the same way, so you still need to modify the linking fields in Patient Referral in order to link a PatientInfo Record to a specific Referring MD record.

                      • 8. Re: Description Search Script
                        CaitlinM

                        This database is only one specialty (Oncology/Hematology) so each patient, similar to having only one PCP, will have only one referring MD - the MD that referred them for their hematological disorder. 

                        • 9. Re: Description Search Script
                          philmodjunk

                          And a patient can't be referred to you more than once by more than one doctor even so?

                          If that's the case, you don't need the join table here and you can simplify your database structure.

                          • 10. Re: Description Search Script
                            CaitlinM

                            That's correct. The referring MD only refers to the MD who referred the patient for their current hematologic disorder. So I should put the referring MD first and last name, and a field for the ReferringMD ID, in the patient info/patient entry table, and get rid of the join table?

                            So for the set fields and I want those as Patient Info table, correct?

                            I will work on making those changes to the tables

                            • 11. Re: Description Search Script
                              philmodjunk

                              I'm not quite sure I understand.

                              A patient is referred to you for a condition on April 3rd of 2010 by Dr. Smith. You examine the patient and deal with the issue. The patient is then referred to you for a different condition, but in the same specialty on July 10th of 2011 by Dr. Jones.

                              While a bit unlikely, isn't that a possible scenario for at least a few patients?

                              If so, you need that join table as one patient can now have multiple referrals.

                              IF it is not the case, put the referring MD ID field in the Patient Info table only. Do not include the referring doctor name fields, in this table, keep them in the Referring MD field where they belong. (and Do not use Referring Doctor Name fields in any other table.)

                              • 12. Re: Description Search Script
                                CaitlinM

                                In our department, the patients who get referred to us are then taken on by out doctors at our center, therefore if another disorder occurs, those will then be considered toxicities/secondary malignancies which have its own place in the database (as portals). Therefore for every patient, they will have only one referring MD because once they are referred - they become our patient due to the nature of their disease. 

                                The only problem with not including the MD names is that they are then not associated with the patient which is why I originally thought a look-up was needed. What happened was that we transferred our data to filemaker. The patient info had the referring MD last and first names associated with the patient, but no ID. On a separate table was the complete (>2000 names) of referring MDs. So the problem is if I delete the name fields in the patient info table then they will not be stored any where because right now there is no associated MD ID with the patient, only their names. So if there is a way to do a look-up of first and last names of the MDs then I could write in the MD ID in the patient info, but since our database has over 2500 patient names, it would take a long time to go through and enter the MD ID for each patient. 

                                • 13. Re: Description Search Script
                                  philmodjunk

                                  As long as the tables are related by Referring MD ID, you can access and display any fields from Referring MD on your Patient Info layout.

                                  I figured that what you report here is the case, I indicated that you should not use these field, not that you should delete them from the patient info table definition.

                                  It's possible to use a script that uses the existing name fields to update all of your existing patient info records with MD ID's in a batch operation. The possible issue you'll need to handle carefully is if you have any referring doctor records with exactly the same first and last names or any cases where the names in the patient info table do not perfectly match a name in the referring doctor table.

                                  Once you've updated your existing records, THEN, you can delete the fields from your patient info table.

                                  • 14. Re: Description Search Script
                                    CaitlinM

                                    I guess the easiest way would just to go through the patients who have a referringMD and list their MD ID in the Patient Info table and then I can work with the script from there. Thanks for your help and I'll let you know if I have any trouble with the script after I'm able to update the current MD IDs for the patient info table.

                                    Thank you,

                                    Caitlin

                                    1 2 Previous Next