Really Simple Record Modification Log using v7

[BruceJ]

One of many tricks sure to come as the experience grows with v7.

To create a modification log like we used to do with self triggered look ups and such woudl be such a pain in v7 because you can't simply duplicate a table with all the fields and relationships in it. So.. I discovered an easier way.

However, my easier way doesn't go to the trouble of showing what the field contents were changed from/to. Instead, it just shows the users name, timestamp and the layout name they were in. This is enough for me right now and provides adequate satisfaction of HIPPA (medical) and other needs.

First,

create your basic fields that return the last user modification account name, and another for last user modication timestamp.

I called these: zc_personModified and zc_dateModified

Then create another text field that returns a calculation and UNCHECK "Do Not Replace Existing Value for Field".

I called this field: zc_modificationRecord

Then use this calculation:

zc_personModified & "

[dbruggmann]

Simple, but nice to know!

[Bush]

Simple and I greatly appreciate your sharing that... but.

Does it _really_ satisfy HIPAA? I mean, yes, we can answer the "who looked at my data?" question, but our data security/audit trail policy requires that I be able to record the FROM WHAT > TO WHAT data (HIPAA's big on data restoration). Ray's [nightwing] 2-field solution satisfies this requirement but an enormous kludge in that EVERY field to be tracked needs to be entered into the calculation.

I need a nice combination of these two solutions... ooh, the struggles of a Filemaker nearnovice..

[smorr]

I have been working on a modification to Ray's solution that uses a global field to specify the fields to be audited. This way fields can be easily specified without full access to a database -- or event specified different fields to audit according to different accounts -- It works quite well for less than 30-40 audit fields -- there is about a 1 second lag on a PB 500 for 80 audited fields (YMMV).

This also logs IP address(es), Layout Table Name, RecordID, and ScriptName (if any).

Fire me questions/ suggestions!

Details:

This technique uses a 4 custom functions (requires Developer): LogFeed; MergeOnKey, NotList, and TrimReturns

Note -- I haven't cleaned up my work file. Otherwise I would attach it. -- maybe on the the weekend

Function Name: LogFeed

Parameters: fieldsToLog

Calculation =

//Note  the 

[dkemme]

To satisfy HIPAA we only allow addendums instead of true edits. The results are not as pretty but makes not only HIPAA happier, but our malpractice carrier as well.

[Bush]

How do you track/differentiate something as simple as an address/phone # change, then?

[BruceJ]

Bush said:

How do you track/differentiate something as simple as an address/phone # change, then?

My Director of Health Information Services here at HCA tells me that each organization is repsonsible for how they will actually compy with HIPPA, so techniques will vary.

However, demograhic information does not fall under the same level of scrutiny and is considered a bussiness office function and therefore tracking changes isn't necessary.

At HCA (200+ hospitals) we've decided that users can not change an entry that has already been made, they can only add to it. This follows the general rule for medical records, that even an entry made in error can only be marked out and noted as an error - and then added too.

It just so happens that this simplifies the needs of tracking record changes.

When I worked with Cerner Corp on their EMR system (Millenium - PowerChart) they also decided that tracking each field change and such would result in such a performance hit that they were satisfied with simply recording who has viewed or modified a record. Meditech does it this way as well.

[Chris Cain]

This approach reminds me of something I learned about when working with PeopleSoft a few years back.

PS uses "effective dated rows" for many of its major tables. This means that even for things like addresses and phone numbers, you add a date row (record) with a new effective date, rather than changing the existing row.

The rollback capabilities are, of course, an advantage. Another, though, is that at least in theory, you wouldn't need to look up values. F'rinstance, the address could be dynamic, but it'd look up the address on the date the invoice was created, not the most current address. Another advantage is you don't have to wait for it to happen. F'rexample, you could put in next month's prices today, or enter that a customer's address will change the first of next year.

With the advent of 7, I got excited about the commit functions, thinking that perhaps I could explore eff. dated rows in FM, but as there's no reliable way to <i>prevent</i> commit, I can't come up with a user interface that wouldn't make entering this type of data a royal pain in the, um, well, you know...

Sorry for the long lead-up, but my question is this: How do you handle the "add don't edit" from a UI standpoint? Does the user have to click an "add"/"edit" button or something?

[PRP407]

Also, on the "add but don't edit" area, is only the latest entry visible or is all of it visible?

More data, please. This would solve SO MUCH for our development.

Thank you!

[BruceJ]

Also, on the "add but don't edit" area, is only the latest entry visible or is all of it visible?

More data, please. This would solve SO MUCH for our development.

You can never change another users entry. It needs to be visible and all responses visible.

When trying to navigate through HIPAA - just take yourself back to the paper medical record/chart - you'd never erase another person's entry. You'd just add to it. Likewise, you can mark through your own entry as an error or correction, but never would mark through someone else's entry.

Using this analogy - all of the responses should be viewable in the legal medical record. You may decide that info that "pulls forward" and auto fills a new field - may be only the latest entry. This is useful in responses that are likely to be static (eg a list of previous procedures, allergies, etc).

With v7's control over text formatting, you can get pretty creative and end up with some powerful solutions where users can correct their work with lines thru text, etc.

This text formatting can be real cool. I know that some facilities use different colors of ink for different shifts. Imagine progress notes where you could tell at a glance which notes were charted on days, evenings and NOC.

I just picked up a contract with a hospital system to do their complete Psych medical record system and I'm going to be using many of these features. I'm pretty excited (hence the long postings).

[PRP407]

This sounds perfect. Would you mind posting the script/calculation that you are using to achieve the different colors? It would be much appreciated.

[BruceJ]

PRP407 said:

This sounds perfect. Would you mind posting the script/calculation that you are using to achieve the different colors? It would be much appreciated.

It's rather cumbersome, but relatively unsophistocated.

I have progress notes as one field, but users enter their text in a separate field. Then, they use a button to enter the progress note. The button is what decides the text formatting.

It goes something like this (from memory):

SetField(ProgressNote; ProgressNote & & Get(HostTimeStamp)& " " & Get(AccountName)& " " &(Case(Get(HostTime)>07:00:00 and Get(HostTimeStamp)>16:00:00; TextFormat(DataEntry; 1,1,1); Get(HostTiemStamp)>=16:00:00 and Get(HostTimeStamp)< 23:00:00; TextFormat(DataEntry;1,122,1); TextFormat(DataEntry; 1,1,122))

Mine's a little more complicated as is also inputs the users discpline and treatment plan problem number. Mine also puts the account name at the end of the entry instead of the begining.

I'm sure my syntax is all screwed up in the above example, but that's the general idea. Of course the actual field that "displays" the progress note is a calc field so that the user can scroll through it, but can't change it.

[PRP407]

Thanks for posting this info. It really helped!