1 2 Previous Next 21 Replies Latest reply on Feb 6, 2014 9:16 AM by philmodjunk

    Portal record display



      Portal record display


           Hi everyone!

           I'm trying to organize the display of the portals I am using and need a bit of help.

           I have several tables, storing patient data.  I have linked them with several ID fields. Each patient has an ID (PatientID), and each patient may attend for any number of injuries or may attend several times over a period of years (EpisodeID) and each episode will likely have several visits (VisitID).  The relationship display is attached below.  This works pretty well, but here is where I need a little help.

           I have a layout for the therapist that is based on a tab control for easy of navigation.  The tabs are Demographics, Subjective History, Objective Assessment and Progress notes (there will be others later for reports, etc.)

           The demographics tab doesn't require multiple instances as any patient demographics need to be current, and we don't care able previous info.  Not so for the other tabs, however.  So in the Subjective History, I created a portal, so I have multiple instances (i.e.. I can take a history from the patient, then if he injures another area or comes back 2 years later, take a different one).  Each portal record increases the EpisodeID by 1.

           BUT....now, when I go to enter my physical testing of the patient in the Objective Assessment tab (which is currently not a portal, but I am guessing I have to change it to one), I have no way of selecting what EpisodeID this assessment is stored under, nor any way of bringing up an old one to chart on (I know when I turn this into a portal, it will display all of them, but I need to be able to ensure the EpisodeID being used in the Objective Assessment is the same as the Subjective History, without relying on the therapist to do anything-needs to be automated.

           Add to that, the fact that in the Progress Notes tab, I need the progress notes ONLY from a specific EpisodeID, not every progress note.

           I tried a button that could be clicked on the Subjective History Tab that would set the EpisodeID within the portal record to that displayed in the portal record edit box, but it did not work.

           I am thinking the Progress notes issue could be solved by filtering records in the portal by EpisodeID, but still have no way to change that EpisodeID and am note sure if I should, given that the EpisodeID links all of the other data.  Do I need another field that could be adjusted or can I just do it with a control?

           Am a bit confused, so hope I am making sense here.




        • 1. Re: Portal record display

               If you set up this relationship:

               Demographics::__kp_PatientID = Assessment::_kf_PatientID AND
               Demographics::_kf_EpisodeID = Assessment::_kf_EpisondeID

               You can set up a portal to assessment that will record data for only one Episode (One specific record in SubjectiveHistory).

               There are a number of ways that you might use to enter a value into Demographics::_kf_EpisodeID--one way being to set it up with a conditional value list of the current patient's Episodes.

          • 2. Re: Portal record display

                 Hmm, ok.   I had not set up an EpisodeID in the Demographics table and had set the primary key EpisodeID in the SubjectiveHistory table because the Demographics didn't need varying episodes, but the Subjective, Objective and other Tables did.  Subjective will always be the starting point for the therapist (the Demographics will likely be entered by admin staff).  

                 Are you suggesting I should create a new field in Demographics with a foreign key to the primary key in the SubjectiveHistory table? 

                 So haven't I already got the PatientID relationships? (GeneralObjectiveAs::__kf_PatientID looks linked to Demographics::__kp_PatientID in the relationship table)

                 Finally, in each of the foreign keys in the other tables, I have them fully indexed AND the values are automatically set to the value of the SubjectiveHistory table EpisodeID flied through a lookup.  Should I have automatically valued the foreign keys, or will the relationship do that for me?  I'm wondering if the automated value of the foreign key might override any setting of the EpisodeID i might do with a value list (awesome idea!)

                 Thanks so much


            • 3. Re: Portal record display

                        Are you suggesting I should create a new field in Demographics with a foreign key to the primary key in the SubjectiveHistory table?

                   Yes. That's why is used the _kf naming convention for that in my example.


                        So haven't I already got the PatientID relationships? (GeneralObjectiveAs::__kf_PatientID looks linked to Demographics::__kp_PatientID in the relationship table)

                   Yes, but if you want to link your data to a specific Episode while entering data from your tab control layout based (I assume) on Demographics, you'll need that added match field.

                   This is often done with "master detail" portals where a button in one master portal ( To subjective History ), can set an fk field in order to limit the records in a detail portal (To an assessment, for example) for just the Episode clicked in the first portal. For more on the concept, see: Need layout solution for nested portals...

              • 4. Re: Portal record display

                     Ok, I got that done, but ran into a problem.  I am now getting an error when I try to enter data into the GeneralObjectiveAssessment tab of my layout (representing the table of the same name).  Error reads: This field cannot be modified until __kf_EpisodeID is given a valid value.  This is very odd because the relationship hasn't changed.  Moreover, PopOver controls that bring up other tables within this one all work fine.

                     I'm wondering if I've set up things wrong, perhaps.  What I have done is I set the "__kf_Episode ID" to be Auto Entered with the value from __kp_EpisodeID" from my SubjectiveHistory Table.  "__kp_EpisodeID" in that table is valued from the GetActiveRowPortal command (the number will increase gradually and avoids my previous problem of the EpisodeID increasing despite which patient I am charting under---the Serial Auto-Enter option was what I used before but when I created a new patient, it did not reset.)  

                     When I changed the __kf_ values to try to display various episodes, it just changed them in the active record and they became mixed up.  SO I defined a global variable that would then hold the episodeID I wanted to view records from.   But I don't think that was the right way to do all of this...

                     Did I make one, or several wrong turns here?  I am attaching my new relationship diagram.  I notice everything is a Many to Many relationship...should that be the case?  I had thought that I would have PatientID being a one to many, then EpisodeID being one to many (ie. having VisitID (grandchild) nested within EpisodeID (child) nested within PatientID (parent).  It doesn't look like I've done that and not sure how to get back there.

                     I am hoping that, slowly, sunlight will dawn on marble brain :)

                     Thanks for all the help!


                • 5. Re: Portal record display

                       That won't work. If you check the actual field, You'll find that it is empty. Even if it entered a value, it would "lock" your data to just that one episode. You need an interface design that lets the user select an episode as the "context" for the data you are then entering that is relevant only to that episode. If you review the link provided, one way is to set up a button in the SubjectiveHistory portal that uses set field to copy the EpisodeID from that portal record into the kf field needed for this relationship.

                       The other option that I have suggested is to format the field as a value list where you select the "episode" for which you want to enter data for the current patient. This can be a conditional value list from just the SubjectHistory records related to the current patient. From what I see here, the first option is probably the better one to set up here.

                  • 6. Re: Portal record display

                         Ok, I think I'm nearly there.  The only thing going wrong now, is that when I go to create more than one GeneralObjectiveAssessment, the EpisodeID remains the same.  It doesn't seem to change the EpisodeID when I select another portal row in the Master portal...just keeps using the first created one.  My Script is as follows: 

                         Set Field [Demographics::__kf_EpisodeID; SubjectiveHistory::__kp_EpisodeID]
                         Commit Records/Requests []
                         So despite the fact that I've still got something wrong, for my learning, can you tell me if this is what I've done so far (confirm that I sort of "got it").
                         I created a __kf_EpisodeID field in the Demographics table to act as a "holder" for my selection of the Episode ID from the __kp_EpisodeID in the SubjectiveHistory table.  Then I've written the script to set the __kf_EpisodeID from that primary key.  Because of the relationship you had me create from the Demographics Table to the GeneralObjectiveAssessment table (Patient ID AND EpisodeID's related) then the GeneralObjectiveAssessment portal displays the portal contents based on both PatientID and EpisodeID.
                         Does that describe what I've done in layman's terms?  I'll have to do this again when I set up reports and probably a few other things (will need to based those on the episodes so just want to make sure I understand the mechanics I've done and the logic behind it.  I understand the Master Slave, but it's the relationships that tend to confuse me a bit.
                         Any ideas what I screwed up to make the EpisodeID not work when I run the script from the button on the Master Portal?
                         Thanks again!
                    • 7. Re: Portal record display

                           I would put _fk_episodeID somewhere on your layout so that you can see it and check to see if that value is actually being changed. What you don't describe in your most recent post is what method you are using to perform the above script nor what method you are using to create a "GeneralObjectiveAssessment".  The two ways that this method can fail is if the script is not being performed when it is supposed to be performed or something is causing the focus on the clicked portal row to be lost before Set Field can copy over the data.

                           You are correct in how that added match field is intended to work, but you may not actually need to use that same setup for all of your reports. Often, you can set up a report by basing it on the portal's table instead of your main "parent" table. This pulls up your data in a way that can be much more flexibly formatted than using a portal on a layout based on the parent table. To do this need to perform a find on that child table layout to get just the records you need for that report--which could be all records with the same EpisodeID in many cases.

                      • 8. Re: Portal record display

                             Oh, ok.  I will try to describe better.

                             I have 4 tabs in a single layout, each representing 4 different tables.  

                             Over everything, I have a header which displays the First and Last Name and Date of Birth of the patient record selected in the Demographics section (just used a Merge Field for this to display only)

                             Demographics is just a simple tab control with the fields laid out for data entry.  

                             Subjective History is a tab with a Portal inside (my Master portal) to allow me to create multiple Episodes for the patient selected in Demographics.  I have set up a button, as you suggested, that will run the script I posted.  The button is within the portal (so it recurs in each portal row--therefore multiple buttons) . I have a field display in each portal row displaying the EpisodeID associated with that row.  I do note, as per your response, that when I click the button, the active row deselects (curious as to why that happens as it exists within the active row....)

                             The next tab is Objective Assessment (which is associated with mainly the GeneralObjectiveAssessment table, but also contains PopOver controls that allow entry into 2 other tables (NeuroAssessment and ROM).  These are all contained within a portal as well (which is my slave portal basically), allowing multiple entries, intended to be based on the EpisodeID.

                             Finally, I have a Progress Notes tab associated with the ProgressNotes table which is intended to allow multiple entries within each EpisodeID as well and organize them to display only those with a selected episode. (Haven't gotten there yet, as the process will likely be identical to one we are walking through now, i assume.  Which is why I wanted to make sure I understand well :))

                             I placed a Merge FIeld box in the header to display the contents of<<GeneralObjectiveAssessment::__kf_EpisodeID>>.  It displays nothing, so I guess you are right, it is not updating the field.  I changed my script to the following:

                             Set Field [GeneralObjectiveAssessment::__kf_EpisodeID; SubjectiveHistory::__kp_EpisodeID]

                             Commit Records/Requests []

                             And run that from the button I described above.

                             Does that explain my setup better, I hope?

                             Thanks so much again, 


                        • 9. Re: Portal record display

                               But the field that your script is supposed to modify is NOT GeneralObjectiveAssessment::__kf_EpisodeID. It should be updating Demographics::_kf_EpisodeID. This is the crucial match field that needs to be kept updated either via button click or script trigger.

                               And any other portals to other tables will not automatically be limited to a given episode unless the correct relationships are then set up linking back to Demographics. Each such portal's underlying table needs to be linked to demographics by both patient and episode ID as I have previously described.

                          • 10. Re: Portal record display

                                 I had done that, however it also did not work. It probably didn't work for another reason and I didn't  change back. 

                                 I will change it back as soon as I get home.

                                 I guess what I am not grasping is how the episode ID in demographics Will change the general objective assessment portal, given that my foreign key in that table is defined by looking up the subjective history primary key.  Was that the correct way of defining my foreign key?  And if it was, then how does changing the demographics foreign key alter this?

                                 sorry if these are stupid questions, but I've learned so much from asking you questions and this is the only one so far I haven't understood. 

                            • 11. Re: Portal record display

                                   I see this relationship in your relationship graph:

                                   Demographics::__kp_PatientID = GeneralObjectiveAssessment::_kf_PatientID AND
                                   Demographics::_kf_EpisodeID = GeneralObjectiveAssessment::_kf_EpisodeID

                                   Assuming that you have not changed this relationship, this relationship means that for the current Demographics record, only those GOA records where both the PatientID AND EpisodeID values exactly match will appear in the portal. If you create a new record in the portal, these two match field values from Demographics will be automatically copied into the corresponding match field in the new GOA record.

                                   The first thing to confirm is that this is still the relationship that you have defined. The second thing to check is that Demographics::_kf_EpisodeID is getting the correct value under all circumstances. Putting a copy of this field outside the tab control so that you can see it no matter what tab you have in front would be a very useful way to check and confirm that this is happening.

                              • 12. Re: Portal record display

                                     Ok, I made the switch back to the correct script definitions and confirmed that the relationship

                                Demographics::__kp_PatientID = GeneralObjectiveAssessment::_kf_PatientID AND

                                Demographics::_kf_EpisodeID = GeneralObjectiveAssessment::_kf_EpisodeID

                                are present and correct (although on the Edit Relationship dialogue, the GOA is on the left and the Demographics on the right...I didn't think that matters.  On that dialogue, I have Allow Creation and Dele Related Records checked ONLY on the GOA side, not on the Demographics side.  Sort is NOT checked.

                                I also placed a Merge Field on the header to display the Demographics::__kf_EpisodeID.

                                So when I create several SubjectiveHistory records in the Master Portal, then click the button to run the script, 2 things happen.  The Demographics::__kf_EpisodeID changes to the correct one (yah!) and the active portal row deselects (Huh?).

                                Then I move to the Objective Assessment tab and, confirming the header still has the correct EpisodeID, I create a new Objective Assessment.  I have a field to display the GeneralObjectiveAssessment::__kf_EpisodeID value of the slave portal, within each row.  

                                It defaults to the lowest episode number for that patient (ie. the first episode created for that particular patient).  ???

                                The GOOD news is that, if I go and change the Current EPisodeID (ie, I go back to Subjective History and select a different EPisode ID and click the button to make it current), all of the GOA records I created do not display in the slave portal until I go back and change the current EpisodeID to the initial number by clicking the button again!

                                So I know the script works, I know the portals are set up right...there is one last piece to the puzzle for me as to why it won't use the current EpisodeID.  

                                So here's my thought, and you can tell me if I'm out to lunch:

                                I think in the GOA, I need to make my GeneralObjectiveAssessment::__kf_EpisodeID field in the table autofill with the lookup from Demographics::__kf_EpisodeID.  Currently, it autofills with a lookup from SubjectiveHistory::__kp_EpisodeID.

                                Am I right?

                                *Fingers crossed*


                                • 13. Re: Portal record display

                                       It should not have any auto-enter setting. The relationship set up will handle the needed auto-entry of the correct ID without your needing to specify anything for it in field options. Just leave it a plain number field with no extra settings. (And the look up you specified is what is causing the new records to revert to the earliest ID.

                                  • 14. Re: Portal record display

                                         Ok, got it.  Funny, the Filemaker Training Series teaches you to do a lookup on a foreign key...probably just for learning purposes, I suppose, but I see the problem now!  

                                         All corrected and all working absolutely perfectly!  AND best thing is I understand it all too!  That is the true miracle :)

                                         Thanks so much.  You have really helped me understand these relationships much better.  I still have a lot to learn, but I'm even more eager now that I've had some success!


                                    1 2 Previous Next