1 Reply Latest reply on Feb 24, 2012 10:54 PM by philmodjunk

    Relationships and script issues



      Relationships and script issues


      I have been working with a very basic 2 table database, I need to add tables and I'm having trouble deciding on the relationships and subsequently scripts as well.

      I am a Nurse Practitioner. I have many patients, the patients have many encounters. The parent table (PATIENT)  consists of the patient info that does not change and has a one to many relationship with the child table (ENCOUNTER).  So far, so good and I have been able to create "reports" that I can print and use as progress notes, but I have to separate fields for Diagnosis1, Diagnosis 2 etc,  

       I think I need to add a DIAGNOSIS "join" table (each patient/encounter has many diagnoses) and then subsequently an ASSESSMENT table (each diagnosis has many assessments) branching off that.  I have the tables in place, each has its own primary key and I have also added the foreign keys to every table. Aside from the initial relationship (Patient table's PATIENT_ID primary key connecting to the Encounter table's PATIENT_ID foreign key) and also checked the "allow creation through this....." on the child side, I'm not sure what to connect in what direction. I have tried several different configurations of relationships with different keys (in a copy of the database) but I can't seem to accomplish what I need to do. I am missing something somewhere, several things, I'm sure.

      Currently I have a layout with portals for the child table, I "find" a patient or add a new one and to add a portal record.

       I have a script on a button that lets me enter new encounters for that specific patient (An online tech wrote it and I do not understand anything about it, he gave me a fish, but I want to learn to fish :)

       After I "find" a patient (or enter a new one), I would like to  have only that patient's diagnoses show in that field... choose which diagnoses (usually at least 4) I am addressing at that encounter and enter assessments specific to each diagnosis. 

      How do I use the inspector to narrow the values to be unique to that patient or do I need a script? ( I saw how to use the pk ID Field as a value list but I would like to take it down a notch)

       The script writing thing has me totally stumped. Completely new language.

      After I get this up and running, I will need scripts to add the "found set" of the diagnoses and their assessments within that encounter record to incorporate that data into the appropriate place in the report (in this case the report is the progress note I have to print out). 

      I have "The Missing Manual" ...just as clear as mud.

      PhilModJunk are you out there? :) 

      Thanks to Phil and/or anyone who is able to help with this.


        • 1. Re: Relationships and script issues

          Is this what you have?

          Patients---+<Encounters---+<Diagnoses----+<Assessments    (---< means one to many, + means "allow creation...")

          Patients::__pk_PatientID = Encounters::_fk_PatientID
          Encounters::__pk_EncounterID = Diagnoses::_fk_EncounterID
          Diagnoses::__pk_DiagnosesID = Assessments::_fk_DiagnosesID

          The challenge here is that you can't put a portal to Diagnoses inside the portal to Encounters so trying to list all diagnoses and assessment records on the patient layout while still maintaining the correct links is a challenge.

          One technique is to set up "master-detail" portals where clicking on or entering data in a row of the Encounters portal triggers a script that updates a portal to Diagnoses to only list diagnoses records for only that one Encounter Record. That takes scripting and either additional relationships or portal filters to make it happen and you'd need to set this up with Encoutner to Diagnoses and Diagnoses to Assessments to get two pairs of "master detail" portals.

          Another option would be to put a button in your encounter portal that brings up the encounter record on an "encounter detail" layout which has a portal to Diagnoses. A button in the Diagnoses layout would bring up a detail layout for Diagnoses with a portal to Assessments. This would be simpler to set up, would use exactly the relationships shown and would need minimal scripting. Thus it may be a better choice for you to set up as a new user, but it may be a bit less friendly to use.

          With either approach, your report may be printed from a layout based on Assessments with fields added from each of the related tables to produce a complete report. You'd perform a find on this layout to pull up just the assessments for a specified Encounter.