11 Replies Latest reply on May 27, 2009 3:47 PM by lenek

    Showing specific records in a portal?



      Showing specific records in a portal?




      I'm new to this and utterly confused and really grateful for any help.  Really sorry if this is a long post, I'm not sure how much to include to make sense!


      My husband is a Dr and as he works out of many locations, thought it would be useful to keep his records in a database so he has them to hand.


      It started off ok.  Basically to summarise


      I have a table - patient record, that stores the main data such as name, number address and all that stuff


      Then I have a table - patient visit - that each time he has a new visit with a patient it stores the patient number, date of visit and gives each visit a unique id


      Linking to that I have tables that hold the data for investigation results, proceedures done, diagnoses made.


      He can view the historical data for each patient via a portal when he sees them so is happy


      So far so good.  Until I get to the patient medication, and I've been playing with this for 2 weeks now getting nowhere! And have just about had enough.


      What he needs to do is be able to see the medication for that patient only, for the last visit that they had only.  No further back historical data - literally what they were taking the last time he saw them


      and then be able to say 'keep that one', 'alter that one' and have those changes shown in a new record which relates to the patient visit number for the patient.


      As each patient can be on many drugs - I'm presuming that the patient visit id number is key to this as that's the only unique thing that says at that visit, these were the drugs.  However I just can not work out how to show this on the screen in front of him, when he would be effectively in a new patient visit for the same patient entering data.


      Does that make any sense?  If not I'll happily try and clarify or confuse you further!  And can anyone help?



        • 1. Re: Showing specific records in a portal?
             Suppose he says 'stop taking this one' - do you still want to keep a record of it somewhere in the patient's history? Same question about doubling the dosage, for example - do you want the previous dosage to remain available?
          • 2. Re: Showing specific records in a portal?



            Yes!  He needs to be able to keep the old record so that should he want to know what someone was taking last June - then he can look that up.


            But create a new record for that visit having the last record in front of him and with as minimal typing things out again as possible to save mistakes (and time)


            I was just playing around wondering if I was on the wrong track thinking portals and instead should be thinking reports - but I'm really muddling around and seem to spend more time doodling on a piece of paper than actually making progress :-)

            • 3. Re: Showing specific records in a portal?
                 Well, then obviously you need to create a new record for every medication that needs to be taken from the current visit and on - whether it's new, continued or changed.

              How you want to do this is largely a matter of preferred workflow: for example, you could display the previous visit's meds in a portal, with a button for 'Continue' (which would duplicate the record and relate it to the current visit).

              Or you could start by duplicating all of the previous visit's meds, and provide buttons for 'Edit' and 'Stop'.

              • 4. Re: Showing specific records in a portal?

                Hi thanks for that - but it's what comes before that I'm stuck with


                I've tried this so many different ways and am going backwards and getting myself more confused :-)


                It's how to get a portal to show only drugs prescribed at the last visit for a given patient


                rather than all drugs prescribed at any point for that given patient in date order (or infact in any order)


                once I've got that up on screen, I can worry about the next bit


                Hope that makes sense


                • 5. Re: Showing specific records in a portal?

                  Assuming each prescription has a VisitID foreign key field, you need a relationship and a calculation field to fetch the ID of the previous visit of the current patient. The relationship is a self-join of Visits:


                  Visits:: PatientID = Visits 2:: PatientID


                  Visits:: VisitID > Visits 2:: VisitID


                  Sort the related records from Visits 2 by VisitID, descending. Then define a calculation field cPrevVisitID =


                  Visits 2:: VisitID


                  and use it to establish a relationship to the prescriptions table.




                  There are other ways to do this, but they're rather difficult to explain in words, and one cannot attach file in this forum.

                  • 6. Re: Showing specific records in a portal?

                    Hi again and thanks!  The relationships are *so* much help.  That's what I was doing wrong :-)


                    Can I push my luck and ask for a bit more explanation on the calculation field to get the last visit 


                    I did say I was new to this and have no idea (though I promise I do have a very large book right next to me)


                    And thank you - I am so grateful, I really have been faffing with this for 2 weeks before I admitted defeat and came here for help

                    • 7. Re: Showing specific records in a portal?

                      lenek wrote:

                      Can I push my luck and ask for a bit more explanation on the calculation field to get the last visit

                      I am not sure what is there to explain. The calculation does very little - it's the relationship that makes only "sibling" visits related, and the sort order of the relationship that makes the last visit become the first related record. The calculation just reaps the result of this work.




                      I was a bit distracted when I wrote my  previous post, so make sure to note the edited version.

                      • 8. Re: Showing specific records in a portal?

                        Sorry - I am feeling very thick!  I'm not getting this.


                        I've come out of my main database and have just set up tables and relationships to try and get this working, rather than confuse myself with all the other fields in the proper one


                        So - I have


                        Table - patient record, contains primary key - patient id


                        Table - visits, contains primary key - visit id and foreign key - patient id


                        Table - drugs, contains foreign key - visit id, field for drug name 


                        I have relationships as follows


                        Table patient record, field patient id = table visits, field patient id


                        Table visits, field patient id = visits 2 field patient id


                        Table visits visit id > visits 2 visit id


                        I have then created a layout for visits 2 and sorted that in descending order by visit id


                        I have then created a field in table visits which I've called last visit and that is a calculation where last visit = visits 2 :: visits id


                        (I haven't got as far as the relationship with drugs yet, I was just trying to get the last visit up!)


                        and have then created a layout to show the patient id and last visit


                        but instead of showing the last visit, it's showing the first time that patient visited


                        So - patient XYZ has visit numbers 1, 5, 9 and 10 - it is showing his last visit as being visit 1 


                        So - I know I'm doing something wrong, and I'm really sorry for being thick, but I haven't got a clue what!

                        • 9. Re: Showing specific records in a portal?

                          Ignore that last post! 


                          I now have last visits :smileyhappy: Thank you so much


                          Now comes the rest, but at least I've managed something today 

                          • 10. Re: Showing specific records in a portal?

                            There should be only ONE relationship between Visits and Visits 2, with two predicates.

                            You don't need a layout for Visits 2. You need to specify the sort order for the related records from Visits 2 within the definition of the relationship.


                            The calculation will work on a layout on Visits - each visit will calculates the ID of the preceding visit. If you want to do this on a layout of Patients, then another method needs to be used.




                            P.S. I'd suggest you rename your core tables to Patients, Visits and Drugs, or something equally reasonable - calling a table "patient record" is very confusing, IMHO.

                            • 11. Re: Showing specific records in a portal?

                              Hi again


                              Yes I've got it - thanks!  Tomorrows task will be the next step but I have last visits so this is a step forward


                              Re the table names - I just called them that for the sake of playing around testing this 


                              in the proper (hopefully soon to be finished database) we do indeed have more sensible table names


                              I'll probably be asking for help tomorrow, but now you've got me this far, I should be able to get a bit further before I have to ask again