8 Replies Latest reply on Oct 7, 2015 8:07 AM by Mike_Mitchell

    Maintaining Data in a Specific Field


      Greetings, experts!
      I have what I hope will be a quick question and a "duh" moment for me. We have a database that is used for clinical notes, an EHR. In our database, we have a form that's used for psychology treatment plans. Initially, the psychologist does a treatment plan and indicates it will take them 20 "sessions" with a patient to address a problem. We've written the system so that it beautifully then counts down each subsequent visit (of the appropriate type) as #20, #19, #18 and so on. This works well.

      The issue is that within each of these subsequent visits is the field "remaining # of sessions", and it accurately reflects the sessions you have left in the treatment plan. Therefore, if I initially specified 20, and I'm on the second visit, it shows "18" visits left in this field. The problem comes in when you look back on previous notes. The field is "live" and therefore, at any given time, reflects the number of visits left- but NOT the number of visits left when that particular note was completed. I hope that makes sense.

      An example:

      I request 20 sessions with Joe. I see Joe 2 subsequent times, and my field reflects 18 sessions remaining. I see him again, and that new note reflects 17 sessions remaining (all is well). However, when I open up the note that I completed for visit #2, it, too, reflects 17 sessions remaining- because it's live and that's how many are actually left now. It used to say 18 sessions left, until I did another one. Now, if I print a copy of the 2nd note, the # of remaining sessions are actually wrong......


      What field can I put in it's place that takes a snapshot of how many sessions were left *in that visit*, that doesn't change as the countdown does?


      I'm guessing this is something simple I'm just overlooking, but for the life of me, cannot work my brain around it.


      Thanks in advance!!

        • 1. Re: Maintaining Data in a Specific Field

          Hello, Rachel.


          You're probably not going to like this answer, but I'm going to suggest to you that you remodel the database to split out your sessions into a separate table. Each session should be a separate record. (This is an example of database normalization; you might see that term mentioned here and there on the forum.) You can script it in such a way that the user can't create or delete any of them, so the specifying of the number of sessions is done up front and can't be changed, but by holding your sessions as separate records, you solve the issues you're having.


          Here's why: Each session becomes a snapshot in time because it's a unique record. Nothing changes on that record when you enter information in a different record. Each session is a separate entity or "thing" in the database, and by making them separate records, you prevent them being blended with one another.


          Hope that helps. Please don't be shy about asking any questions.



          • 2. Re: Maintaining Data in a Specific Field

            Sounds like you just need to change the field so it's not a calculation, but rather a number. Then however you are creating the record for each session, when you create that new record, calculate the value and stick it in that field.


            We'd probably need more details on how you're currently calculating that field's value to make a more precise recommendation.

            • 3. Re: Maintaining Data in a Specific Field

              Kind of sounded like she already had a "clinical notes" table that was playing the role of a "sessions" table. Unless they are entering in more than one "note" record per-session. Given how the counter currently works though, it sounds like the count of sessions is coming from a count of records from somewhere though, so I'm not sure a complete remodel is needed.

              • 4. Re: Maintaining Data in a Specific Field

                Hi Mike!
                Thanks for the incredibly quick response. If I understand correctly, we do have it set up so that each note is it's own unique record. The field itself (# of remaining sessions) pulls from a "Patient" table, while each note is in a "Visit" table. Perhaps this is the issue..... All other fields in the note, but certain fields we "pull in" from the patient table. For example, the patient's last name is pulled in from the patient table, and appears in all of the records in the visit table. If we were to change the patient's last name after 10 visits, it would change those 10 historical visits to the new last name as well. As I type this, I am realizing the larger scale of the issue, as I wouldn't want that to happen either.


                So, what I've got are certain fields that exist in the patient table, that change in the visit (note/ record) each time they are updated. Is there a way to create a reference to what existed in the patient table at the time the note for the visit table was created? Hopefully that makes sense....

                • 5. Re: Maintaining Data in a Specific Field

                  Okay, I guess I misread the issue. But if you do have it modeled such that each visit is a unique record, it's relatively easy to get a "remaining number" of visits set up. Depending on when you want that value to update, what I would suggest is that you script the decrementing of the value based on those criteria. For example, if you don't create the visit record until after the visit is completed, you can just write a script that creates the new visit record and decrement the value as part of that script. Or, if you have some status field or the like that indicates, "We're done!", you can just add decrementing the count to a script that closes the visit.


                  As far as freezing the value of a field, you can do that by echoing the data down to the related table into a separate field via either a lookup (simpler) or an auto-enter calculation (more flexible). Which one you use will depend on when you want the value to update. A lookup updates based on the relationship between the tables, which usually means it updates when the related record is created and any time the key field or fields are touched (which is usually never). An auto-enter calculation can be updated based on changes in other fields. Of course, you can always write a script to refresh the field under whatever conditions you want.





                  • 6. Re: Maintaining Data in a Specific Field

                    I'd have a field called "Number of Visits" in the "Patients" table. This is where the "20" would go.


                    Then I'd have a field called "Visit Number" in the "Sessions" table. This would start with "1" for the first visit, "2" for the next, etc. These could be auto-entered as = Max ( "Visit Number" ) + 1, as seen from the "Patients" table.


                    Then in the "Sessions" table I'd have a calculation field called "Visits Remaining" = "Number of Visits" – "Visit Number".

                    • 7. Re: Maintaining Data in a Specific Field

                      Thanks, guys! Incredibly helpful (and so quickly!)- I am fairly positive these suggestions solve the issue, but as things go, I got pulled off in another direction momentarily. I'll try these out this afternoon and let you know what worked best.


                      Thanks again for all the help!


                      (How did people do their jobs before the internet existed?!!)

                      • 8. Re: Maintaining Data in a Specific Field

                        We banged our heads against the keyboards for six or seven days until an answer magically appeared.