I have been working on some custom forms recently which allowed me to look closer at the SQL populating the forms. Will the addition of the "deleted" flag in table "forms" impact the query that retrieves data in custom forms for view.php? The data retrieval function is formFetch() which is in /library/api.inc. There is no join on the forms table.
Here is the SQL:
function formFetch ($tableName, $id, $cols="*", $activity=“1”)
{
return sqlQuery ( “select $cols from `$tableName` where id=’$id’ and pid = ‘{$GLOBALS[‘pid’]}’ and activity like ‘$activity’ order by date DESC LIMIT 0,1” ) ;
}
In OpenEMR terms, an activity field usually means:
activity=1: not deleted
activity=0: deleted
If all forms have an activity field and there is a mechanism for setting it to 0 when a form is ‘deleted’, then there is probably no need to add a deleted flag field to the forms table. formFetch will not return data from a form record with the activity set to 0, unless otherwised specified by overriding the default $activity argument.
Problem is, I don’t think the code that sets the “deleted” flag in the forms table is also clearing the “activity” flag in the form-specific table.
Maybe it should, or maybe the activity flag should be considered obsolete, but either way it appears this is an omission that needs to be fixed. Unless I’m missing something.
I was the one that added the ‘deleted’ column to the Forms table because there was no other column in there to help indicate if a form was deleted. I was unaware of the ‘activity’ column in most of the individual forms. Fortunately this can easily be corrected or ignored.
Ignoring the issue would not be horrible. Each form that is deleted from an encounter is simply marked deleted in the Forms table. The associated record in the form-specific table is untouched but still accessible through the database back-end.
To fix the problem some code needs to be altered.
Personally I don’t see a strong need to alter the code. However, I’m willing to edit the code if there is a strong consensus to make such a change.
OK but as things stand now, it appears formFetch() as referenced above is broken because it does not check the forms table and thus can fetch deleted forms. So that at least needs to be fixed.
I haven’t investigated the code but, does FormFetch ever get called without a first call to the Forms table? I think most code looks at the Forms table first to see which forms are associated with an encounter. And as long as that SQL call checks the ‘deleted’ field then FormFetch will never be used to pull up a deleted form’s details.
However, this is something that certainly needs more investigation.
I’ll add it to the Tracker as a bug and reference this forum thread.