My clinical rule editor in 4.1 is buggy

system · October 13, 2011, 12:03am

rpl121 wrote on Thursday, October 13, 2011:

I’ve tried to put in some clinical rules similar to A1C for diabetes by modeling my entry after what is already in the program. For some reason, I have trouble getting the rules to enter properly into the relevant tables. I notied that my table rule_target in stock form had 80 entries, many of which were exact matches in all columns. I figured it shouldn’t be that way.

Furthermore, I had a hard time displaying the interval value with command line statements in MySQL and then discovered that one of the columns in the table is named interval, and that this is a reserved word in MySQL. Could this be a problem?

Ronald Leemhuis

system · October 13, 2011, 5:53am

bradymiller wrote on Thursday, October 13, 2011:

Hi,

Regarding use of interval(mysql reserved word). Good practice to surround elements in your sql statements with backticks (ie. `interval`), which then avoids any issues. Note the CDR engine does this in library/clinical_rules.php. Alternatively you can also use the full name (ie. rule_targets.interval) to avoid issues, which is done in the CDR admin GUI (code at main/super/rules) .

There should be 50 rule_target entries by default (note many may look similar, but they are all different). If have over that many with replicated rows, then may be useful to paste them here to try to isolate the issue.

-brady

system · October 13, 2011, 10:16am

rpl121 wrote on Thursday, October 13, 2011:

Thanks, Brady. Using the backtick in my query allowed me to count the distinct values in the rule_target table. As suspected, I had 80 values only 50 of which were distinctly different in all columns.

I repopulated the table with the 50 distinct values. Now the rule editor does not appear buggy. I’ll let you know if it does not work properly in actual use.

Ronald Leemhuis MD

system · October 18, 2011, 12:23am

rpl121 wrote on Tuesday, October 18, 2011:

Turns out, I’m still having some (not as much, though!) trouble with the rule editor. I figured for sure I messed up the tables with all my playing around, but it turns out the latest development version online has the same problem saving rules properly. One thing I learned is that if one makes an sql dump file on a clean installation of openemr, one can save that file and cut and past sections into a new sql file which can be “sourced” to recreate whatever tables you want in virgin form.

To make new rules I figure I can go to the underlying tables and add the right information in strategic places, but I would rather make my efforts contribute to the community effort rather than just fix my own installation. I am willing to incorporate official Medicare rules for diabetes care in addition to the rudimentary ones already incorporated, but I want to be in tune with the community so that I’ll be contributing to a shared effort.

Any ideas on how to do that?

Ronald Leemhuis MD

system · October 18, 2011, 1:01am

tmccormi wrote on Tuesday, October 18, 2011:

Actually providing importable SQL rule sets would work great! Just need to make sure you don’t assume primary key, so that can be imported no matter what has already been added.

Tony

twitter: @tonymi2

system · October 18, 2011, 4:23pm

bradymiller wrote on Tuesday, October 18, 2011:

Hi,

The rules can be built manually without the CDR editor, which is probably the best way to build rules that we include in the codebase (note the CDR editor uses numbers to identify added rules, while the rules included in the codebase use descriptive labels; this was done purposefully so we could continue to include new or improved rules without worrying about conflicts with rules that users create). Check out the format used in mysql to build rules. For example, following the ‘Pneumonia Vaccination Status for Older Adults’ rule has following elements:

table `clinical_rules`:
INSERT INTO `clinical_rules` ( `id`, `pid`, `active_alert_flag`, `passive_alert_flag`, `cqm_flag`, `cqm_nqf_code`, `cqm_pqri_code`, `amc_flag`, `amc_code`, `patient_reminder_flag` ) VALUES (‘rule_pneumovacc_ge_65’, 0, 0, 1, 0, ‘’, ‘’, 0, ‘’, 0);
-Note the rule_pneumovacc_ge_65 element here, which is used to identify this rule elsewhere

table list_options:
INSERT INTO `list_options` ( `list_id`, `option_id`, `title`, `seq`, `is_default` ) VALUES (‘clinical_rules’, ‘rule_pneumovacc_ge_65’, ‘Pneumonia Vaccination Status for Older Adults’, 570, 0);

table rule_filter:
INSERT INTO `rule_filter` ( `id`, `include_flag`, `required_flag`, `method`, `method_detail`, `value` ) VALUES (‘rule_pneumovacc_ge_65’, 1, 1, ‘filt_age_min’, ‘year’, ‘65’);

table rule_target:
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES (‘rule_pneumovacc_ge_65’, 1, 1, 0, ‘target_database’, ‘::immunizations::cvx_code::eq::33::ge::1’, 0);
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES (‘rule_pneumovacc_ge_65’, 1, 1, 0, ‘target_database’, ‘::immunizations::cvx_code::eq::ge::1’, 0);
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES (‘rule_pneumovacc_ge_65’, 1, 1, 0, ‘target_database’, ‘::immunizations::cvx_code::eq::109::ge::1’, 0);
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES (‘rule_pneumovacc_ge_65’, 1, 1, 0, ‘target_database’, ‘::immunizations::cvx_code::eq::133::ge::1’, 0);

table rule_reminder:
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES (‘rule_pneumovacc_ge_65’, ‘clinical_reminder_pre’, ‘week’, ‘2’);
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES (‘rule_pneumovacc_ge_65’, ‘clinical_reminder_post’, ‘month’, ‘1’);
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES (‘rule_pneumovacc_ge_65’, ‘patient_reminder_pre’, ‘week’, ‘2’);
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES (‘rule_pneumovacc_ge_65’, ‘patient_reminder_post’, ‘month’, ‘1’);

table rule_action:
INSERT INTO `rule_action` ( `id`, `group_id`, `category`, `item` ) VALUES (‘rule_pneumovacc_ge_65’, 1, ‘act_cat_treat’, ‘act_pneumovacc’);

table rule_action_item:
INSERT INTO `rule_action_item` ( `category`, `item`, `clin_rem_link`, `reminder_message`, `custom_flag` ) VALUES (‘act_cat_treat’, ‘act_pneumovacc’, ‘’, ‘’, 0);

Once you follow a couple already existent rules, you will begin to see how to add additional rules. To add a rule, probably most important thing is settling on a id name, so rec. providing your suggested rule, rule_id name along with what you plan to filter for and target for.

Also, to get more insight into the CDR engine, check out the wiki page we used while developing it here:
http://open-emr.org/wiki/index.php/Clinical_Decision_Rules#Notes_and_Issues

hope this helps get you started,
-brady

system · October 18, 2011, 5:09pm

bradymiller wrote on Tuesday, October 18, 2011:

Hi,

Also, one more element that I forgot in list_options table pertaining to above rule:
INSERT INTO `list_options` ( `list_id`, `option_id`, `title`, `seq`, `is_default` ) VALUES (‘rule_action’ ,‘act_pneumovacc’, ‘Pneumococcal Vaccine’, 60, 0);

-brady

system · October 18, 2011, 5:40pm

rpl121 wrote on Tuesday, October 18, 2011:

Thanks, Brady, for summarizing all the necessary information for a clinical rule. I was planning to dig into all the tables to see what was necessary, but there it is. I think the best approach to writing rules is this:

1. See what Medicare officially requires for diabetes, CV disease etc - one condition at a time.
2. Categorize each rule into types and find an existing rule of similar type.
3. Come up with labels and names consistent with previous naming conventions and methods
4. Modify the SQL code that enters the data for the existing rule using a text editor so it should work with the new rule. Test each rule one at a time and revert to the previous state if things get messed up.
5. Once we learn how to do this reliably we can start entering information more intensively.

The key will be to have templates made up. Maybe I can find something useful to cut and paste from a mysqldump file.

Ronald Leemhuis MD

system · October 18, 2011, 5:55pm

bradymiller wrote on Tuesday, October 18, 2011:

Hi,

Sounds good. There’s a clear summary of what the current rules do here (see ‘9) Currently supported Standard Rule/Plan sets on installation’) :
http://open-emr.org/wiki/index.php/CDR_User_Manual

Will probably be a good idea to document above process on a new wiki page and place it here on the wiki:
http://open-emr.org/wiki/index.php/CDR_Engine

A good place to start is the discrepancies between the current rules and the Medicare suggestions. If the rule has only a minor difference, then may be able to fix the original rule. If more than minor then could make a new rule with a _mc appended to end of name id etc.and redo the rule. And, of course, if the rule does not even exist yet, then simply create the rule. Also note, something I didn’t mention, is there is a cool plan mechanism, which will group rules into plans utilizing the following tables:
clinical_plans
clinical_plans_rules

Knowing how to manually create the rules also allows some added functionality, like looking for procedure filter/targets and placing in plans (these functions are not yet in the CDR editor).

-brady

system · November 13, 2011, 1:46am

rpl121 wrote on Sunday, November 13, 2011:

It appears that the clinical rule editor as currently configured is very strict in matching diagnoses or medications for inclusion. For example, INR testing reminders are created if one of the medications listed is exactly “coumadin” or “warfarin.” The engine scans for a diagnosis of “diabetes” or any of a long list of very specific ICD9 codes such as “ICD9:250.00” If the diagnosis is listed as “Type 2 diabetes” it is not recognized.

I’m thinking that it may be possible without much trouble to make the code look for similarities or use wild cards in the inclusion rules like “ICD9:250*” or “*diabetes” and to make sure it is not tripped up by upper/lower case differences. Any thoughts on this?

Ron Leemhuis

system · November 13, 2011, 8:22am

bradymiller wrote on Sunday, November 13, 2011:

Hi,

Is it getting tripped up by upper/lower case differences? (it should not be)

Also, the ICD:250 codes are in a sql query as so ‘LIKE %ICD9:250%’, so should just need to add ICD9:250 and you will get the desired ICD9:250* effect.

if curious, here’s the function doing the diagnosis codes in library/clinical_rules.php:

// Function to check for lists item of a patient
//  Fully customizable and includes diagnoses, medications,
//    allergies, and surgeries.
// Parameters:
//   $patient_id - pid of selected patient.
//   $type  - type (medical_problem, allergy, medication, etc)
//   $value  - value searching for
//   $dateTarget - target date. blank is current date.
// Return: boolean if check passed, otherwise false
function exist_lists_item($patient_id,$type,$value,$dateTarget) {
  // Set date to current if not set
  $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
  // Attempt to explode the value into a code type and code (if applicable)
  $value_array = explode("::",$value);
  if (count($value_array) == 2) {
    // Collect the code type and code
    $code_type = $value_array[0];
    $code = $value_array[1];
    if ($code_type=='CUSTOM') {
      // Deal with custom code type first (title column in lists table)
      $response = sqlQuery("SELECT * FROM `lists` " .
        "WHERE `type`=? " .
        "AND `pid`=? " .
        "AND `title`=? " .
        "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " .
        "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,$code,$dateTarget,$dateTarget,$dateTarget) );
      if (!empty($response)) return true;
    }
    else {
      // Deal with the set code types (diagnosis column in lists table)
      $response = sqlQuery("SELECT * FROM `lists` " .
        "WHERE `type`=? " .
        "AND `pid`=? " .
        "AND `diagnosis` LIKE ? " .
        "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " .
        "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,"%".$code_type.":".$code."%",$dateTarget,$dateTarget,$dateTarget) );
      if (!empty($response)) return true;
    }
  }
  else { // count($value_array) == 1
    // Search the title column in lists table
    //   Yes, this is essentially the same as the code type listed as CUSTOM above. This provides flexibility and will ensure compatibility.
    $response = sqlQuery("SELECT * FROM `lists` " .
      "WHERE `type`=? " .
      "AND `pid`=? " .
      "AND `title`=? ".
      "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " .
      "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,$value,$dateTarget,$dateTarget,$dateTarget) );
    if (!empty($response)) return true;
  }
  return false;
}

(Note the 1st and last sql-query are for the diagnosis(or meds or allergies) titles and that the 2nd query if for diagnosis codes.

-brady

system · November 13, 2011, 5:24pm

rpl121 wrote on Sunday, November 13, 2011:

Thanks, Brady.

I confirmed what you said about the ICD9 codes (i.e. “diagnosis”) matching using the LIKE command. If I change a diagnosis code in the lists table to something like ICD9:250.00adfljfljlfjjflfhfk, it matches and works properly because the 250.00 is in the clinical rules.

However, this behaves differently with the “title,” as the code cited above requires an equality rather than a LIKE command. I think I’ll try making it a LIKE statement because then any narrative diagnosis that has the consecutive letters diabetes would qualify. That would be a big plus.

Also, I think the stock configuration of clinical rules should have fewer diagnosis codes. There does not seem to be a reason to compare diagnoses to 250.00, 250.01, 250.40 etc etc etc when you could just use the common digits of the highest branch of the ICD9 tree.

Shouldn’t the official code use a LIKE command for the titles as well?

Ron Leemhuis

system · November 13, 2011, 5:44pm

rpl121 wrote on Sunday, November 13, 2011:

I tried changing one line to substitute a LIKE for an =. However, it does not seem to accomplish the desired effect.

Old line: "AND `title`=? " .

New line: "AND `title` LIKE ? " .

It matches diabetes but not diabetes2 - I can’t figure out why not.

Ultimately I’ll want it to match anywhere in the field, not just at the beginning.

Hmm…

Ron Leemhuis

system · November 13, 2011, 9:15pm

yehster wrote on Sunday, November 13, 2011:

http://dev.mysql.com/doc/refman/5.1/en/pattern-matching.html
Dr. Leemhuis,
Here’s some info on mysql syntax with the LIKE operator, but basically to match either diabetes or diabetes2
you need to do

AND LIKE "diabetes%"

which will only match if the diabetes is at the beginning (a faster search)
or

 AND LIKE "%diabetes%"

which will match if diabetes appears anywhere in the string.

the % is a wildcard indicating zero or more matches in your string.

system · November 13, 2011, 9:22pm

rpl121 wrote on Sunday, November 13, 2011:

Thanks, yehster. I’ve played around with that sort of syntax but haven’t yet gotten it to work for the “Custom” diagnosis codes, which are just narrative titles. The ICD9 diagnoses seem to work well with the “LIKE” command.

One thing I noticed was that the Custom codes are listed as Custom::diabetes, for example in the rule_filter table. The code cited above searches for “CUSTOM” in capital letters. The code started to behave better, but still not right, when I changed “CUSTOM” to “Custom” in the code. I’ve been trying to figure out whether any of the variables may be carrying along invisible trailing characters such as spaces or CRs or something like that to make the “LIKE” command not work even though the = command does.

The puzzling thing is that if I have a narrative diagnosis of diabetes (with no ICD9 codes), the = command works but the LIKE command does not - even when I keep the diagnosis unchanged. Hmm…

Ron Leemhuis

system · November 13, 2011, 9:48pm

bradymiller wrote on Sunday, November 13, 2011:

Ron and everybody,

Good point on the 250. vs 250.xx issue. Note there is a check for ICD9:250 in all the diabetes rules, whcih basically means all the other ICD:250.xx checks are simply draining performance from the engine. So, I think we can remove all the ICD9:250x codes (this should be a straightforward myswp DROP that could incorporate in the upgrade (and even the patch), which will stop lots of needless checks. Does this sound reasonable as making it a TODO (note we are rather overwhelmed right now with TODOs

Also, mysql is generally case-insensitive (unless use BINARY collation, which is only done in the translation tables in openemr). So, diabetes and DiAbEtes should both work as titles. Also, follow the code above, to see what happens if you change CUSTOM to Custom; the if ($code_type==‘CUSTOM’) { will now be false, and you will now be searching for the Custom:diabetes in the coding fields.

For LIKE to work as you want, rec subsituting it (in both the first and third query above) and changing to %.$code.% in first query and to %.$value.% in the third query. We could attach this behavior to a setting in Administration->CDR, since other may find it useful. Can also make it dependent on the cetegory of the list item (ie. medical_problem, medication, allergy).

-brady

system · November 13, 2011, 9:50pm

bradymiller wrote on Sunday, November 13, 2011:

clarifcation above:
changing to “%”.$code."%" in first query and to “%”.$value."%" in the third query

system · November 13, 2011, 10:05pm

rpl121 wrote on Sunday, November 13, 2011:

Thanks again, Brady. I have been trying things like this, but I hesitated to put regular parentheses within regular parentheses so tried single parens only. Will try this. The other thing I tried was to reverse the ? and the `title` because I am not sure that A LIKE B is the same thing as B LIKE A, and we want the “%” things added to the `title` not the ?.

Will give it a try.

Ron Leemhuis

system · November 13, 2011, 10:12pm

bradymiller wrote on Sunday, November 13, 2011:

Specifically,

Change(query 1 and 3):

"AND `title`=? ".

to

"AND `title` LIKE ? ".

Change (query 1):

" array($type,$patient_id,$code,$dateTarget,$dateTarget,$dateTarget) );

to

" array($type,$patient_id,"%".$code."%",$dateTarget,$dateTarget,$dateTarget) );

Change (query 3):

array($type,$patient_id,$value,$dateTarget,$dateTarget,$dateTarget) );

to

array($type,$patient_id,"%".$value."%",$dateTarget,$dateTarget,$dateTarget) );

-brady

system · November 13, 2011, 10:23pm

rpl121 wrote on Sunday, November 13, 2011:

My head is spinning at the moment. I tried everything but changing the arrays, which I bet will help a lot. I tried regular quotes, single quotes, left handed quotes and generally got syntax errors. I tried reversing the ? and the `title` business. Reversing the ? and the `title` may be what is necessary because I was able to confirm that A like B is not the same as B like A and what we want is going to be the reverse of what is written.

Also, it appears that the CUSTOM thing IS case sensitive because it is in a PHP domain rather than MySQL domain - just a thought. In any case, changing it to Custom made a difference.

Another thing I tried was to change rules_filter value for diabetes from Custom::diabetes to diabetes alone (and back).

My hunch, Brady, is that you’re close to fixing this.

Ron Leemhuis