Merging duplicate charts

Developers
Rod Roark
2013-03-27
2013-04-06
  • Rod Roark
    Rod Roark
    2013-03-27

    It happens - the front desk adds a new patient, failing to notice they are already in the system.  So a client wants a way to fix it.

    Seems fairly straightforward, but detailed.  I count 49 tables that currently store a pid value or equivalent.  Logic to do the merge might go like this:

    1. Enumerate all tables.  Do an update on each that has a column named "pid" or "patient_id", just selecting on and changing the value of that column.

    2. Do the same update for these special cases:
          chart_tracker.ct_pid
          documents.foreign_id
          documents_legal_detail.dld_pid
          openemr_postcalendar_events.pc_pid

    3. Merge the subdirectories under sites/<siteid>/documents appropriately.

    4. Remove the unwanted patient_data row.

    Seems OK?  What I'm mainly unsure about is whether to make this an administrative feature in OpenEMR, or just to write a one-off perl script.  It's the kind of thing that can really screw up a database if you're not very careful.  Thoughts?

    Rod
    www.sunsetsystems.com

     
  • Paul Williams
    Paul Williams
    2013-03-27

    Most of the duplicates I've had to manually correct already have encounters created and various forms stored, usually across both patients/encounters.  So, in addition to your list, I've had to select which is the appropriate encounter to retain, and then update both the pid and encounter columns in the relevent rows of the forms table.

     
  • Rod Roark
    Rod Roark
    2013-03-27

    Good point.  I'm thinking the merge logic should pre-check for encounters with duplicate dates as well as duplicate document names, and reject the entire merge if any of those exist.

    Rod
    www.sunsetsystems.com

     
  • DrKay
    DrKay
    2013-03-28

    Rod- I'd vote for an administrative function instead of a Perl script. I think it's a feature that many users need. -James Kay

     
  • DrKay
    DrKay
    2013-03-28

    Another merging need is to be able to merge duplicate encounters. I often end up with two, sometimes three, and, rarely, four encounters for the same patient on the same date. One has the actual appointment date and time, another the vital signs, weight and height…

     
  • Rod Roark
    Rod Roark
    2013-03-29

    OK I've got code for the new feature to review/test.  This merges two different patient charts.

    https://github.com/sunsetsystems/openemr/commits/work

    Rod
    www.sunsetsystems.com

     
  • Kevin Yeh
    Kevin Yeh
    2013-03-29

    Dr Kay,
    I will work with Tony to start investigating a good way to merge encounters next week.

     
  • Rod Roark
    Rod Roark
    2013-03-29

    Another useful feature would be to fix the case where a single encounter was entered to the wrong patient.

    A heads-up to developers: This code contains some assumptions about how tables relate to patients.  If you create a new table that references patients, be careful that the relation is named "pid" or "patient_id", or else is handled as an exception in the merge script(s).

    Rod
    www.sunsetsystems.com

     
  • Rod Roark
    Rod Roark
    2013-03-30

    Devi, thanks, I never knew that was there.  It's pretty rough though, and there are about 17 tables and the scanned encounter notes it doesn't deal with.  I do like the "confirmation" feature.

    So guess I'll proceed with changes based on Brady's code review comments.  Do jump in there with your own.

    Rod
    www.sunsetsystems.com

     
  • ZH Healthcare
    ZH Healthcare
    2013-03-30

    Hi ,

    By querying the information_schema you will get the table which contain the columns name with PID or patient_id. Is there is any reason you don't you use that.  This  method will reduce the number of queries to be executed.

    Eldho
    ZH Healthcare

     
  • Rod Roark
    Rod Roark
    2013-03-30

    Eldho, thanks for the suggestion.  Performance is not an issue here so I'm not going to worry about it this time around.  A good thing to note for future projects though.

    It looks like CouchDB is an issue I need to deal with though.  Can someone tell me why it stores data in a way that depends on patient ID?

    Rod
    www.sunsetsystems.com

     
  • Rod Roark
    Rod Roark
    2013-03-30

    I've created a new branch with a new commit to handle CouchDB documents as well as code review comments. This also moves the logic for handling patient ID changes for documents into the Document class:

    https://github.com/sunsetsystems/openemr/commits/ptmerge

    Rod
    www.sunsetsystems.com

     
  • Brady Miller
    Brady Miller
    2013-03-30

    Hi,
    I am pretty sure information_schema is not always available (ie. need special access to use it); I could be wrong, but remember this issue coming up when playing around with innodb conversion a long time ago.
    -brady
    OpenEMR

     
  • ZH Healthcare
    ZH Healthcare
    2013-03-31

    Hi,

    It does not require special permission but will restrict the access right according to the connected user.

    http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

    As rod said this is not an issue here, but may be useful in some other situation.

    Regards
    Eldho

     
  • Pieter W
    Pieter W
    2013-03-31

    Now back to the merge. In DEMO VERSION DEVELOPERS I made a second client with the same birthday. Merging went as it should be. Two clients into one.

    Now there are two encounters on the same date. One from the old and one from the merged client. Only the vitals do show for the main client all others do show in the second encounter of the same day. If this is on purpose, OK if this is on purposose it is just a remark of a user.

    My conclusion is that at least the BIRTHDAY DATE has to be the same.

    Tnx for the improvements anyway!

    Pimm

    Pimm

     
  • Rod Roark
    Rod Roark
    2013-03-31

    Birth date must exist and must be the same for both patients.  SSN values must also match but can be empty.  These requirements are attempts to avoid choosing an incorrect patient by mistake.

    I didn't do anything to disallow encounters on the same day, or to merge encounters.  Yehster indicated he might work on that.

    Rod
    www.sunsetsystems.com

     
  • MD Support
    MD Support
    2013-03-31

    Merging encounters (specially on same day) is sorely needed as a standard functionality.  Many times a variation to the standard check-in process results in multiple system encounters for the same (actual) encounter.  It is then rather messy to cleanup. 

     
  • Pieter W
    Pieter W
    2013-03-31

    When everything is working as stipulated, I might include some of these comments into some WIKI page about erroneously created duplicate clients?

    My suggestion for same date encounter leave it like it is, at least it is obvious the first encounter is most likely from the original client and the second encounter is from the merged client.

    Gr, Pimm

     
  • Pieter W
    Pieter W
    2013-04-01

    Question:

    If PID, SSN and DOB all must be the same to complete merge duplicates, how can there be a mistake in the first place?

    Or…….

    Before doing the merge, you have to correct PID, SSN DOB and make them the same and be sure to correct the wrong input before the merge can be done. Can this be included in the MERGE message? So the trial and error messages will be reduced before hitting the MERGE button.

     
  • Pieter W
    Pieter W
    2013-04-01

    Yes Rod,

    you were so quick and are correct about the External-PID as it is called for the user. The external is automatically assigned if not filled by the Provider administrator.

    I will try not to post in the wrong thread again! Sorry for my wrong doings.

    Pimm

     
  • Rod Roark
    Rod Roark
    2013-04-01

    External patient ID (pubpid) is not checked by the patient merge.  pid must be different, not the same.  The page does explain about SSN and DOB - feel free to suggest changes to the instructions there.

    Rod
    www.sunsetsystems.com

     
  • Pieter W
    Pieter W
    2013-04-01

    Mea Culpa, Rod,

    the only suggestion I can make with a blush on my cheeks is:  "this utility is experimental"-part. can be taken out.

    I tried, but can't find any severe problems with the MERGE button.

    Compliments again for a job well done!

    Tnx, Pimm