New CVX codes

yehster wrote on Monday, September 09, 2013:

There are new CVX codes that the current import process doesn’t handle. (143-158).

http://www2a.cdc.gov/vaccines/iis/iisstandards/vaccines.asp?rpt=cvx

and some of the existing ones might not be active anymore.

yehster wrote on Monday, September 09, 2013:

It’s kind of ugly, but here’s another demonstration of data import for the codes table.
I copy and pasted the new CVX codes from the CDC website into OpenOffice Calc and wrote the concatenate formula in column G.
Same basic approach could be used for CPT codes.

yehster wrote on Monday, September 09, 2013:

Also, many of the “short descriptions” for CVX codes are longer than the 24 character limit in the codes table, so they are getting truncated for now.

bradymiller wrote on Tuesday, September 10, 2013:

Hi,

The issue users may run into is the code_type since it may not always be 100: http://www.open-emr.org/wiki/index.php/Code_Types#Current_Official_Code_Types
Correct way to do this would be to instead insert the ct_id from row where ct_key is CVX from the code_types table for this value.

Sounds like need a fix for the codes table ‘short descriptions’ length and addition of the new CVX codes in database.sql and the upgrade sql script. Also looks like need a Active/Inactive mechanism here, which has not yet really been incorporated, that would only allow adding new ‘active’ vaccines while still allow viewing/mapping of ‘inactive’ vaccines.

-brady
OpenEMR

yehster wrote on Tuesday, September 10, 2013:

I suspect that most users do have 100 for CVX code_type and this ought to be sufficient. It’s certainly better than nothing

The “official” import script also has them hard coded to 100.

Anyone who was sophisticated enough to change the code type to something other than 100 is likely skilled enough to edit this spreadsheet as well.

Is anybody out there besides Dr. Kay actually documenting immunizations in OpenEMR?

mamassoud wrote on Tuesday, September 10, 2013:

Yes, I using immunizations to report all given vaccines. I use it to export immunizations to report to state registry, MI.
As you might know it is very complicated process to relate drugs inventory and consumables from immunizations. As well as billing codes and administrations in the Fee sheet need some automation to relate the given Immunization to the billing codes ICD9 and CPT4 + fees
Thanks for the table update.
Mohamed Massoud

bradymiller wrote on Wednesday, September 11, 2013:

Hi Kevin,

Note the cvx_codes.sql script is only used for new installs. The upgrade script is a different mechanism:


(see line 1006)

That being said, though, your spreadsheet technique is a very nice way for lay users to do these types of things.

-brady
OpenEMR

fsgl wrote on Wednesday, September 18, 2013:

Kevin did a bang up job.

Concatenation in OpenOffice Calc is no walk in the park. The Help section of the application is clear as mud regarding the function and syntax. Online tutorials are better.

Have to put on the best pair of glasses for this stuff!

This is the formula:
$C$1& “’”& A2& “’”& “,’”& B2& “,”& “1”& “);”

Output:
INSERT INTO codes (code_text, code, code_type) VALUES( ’ Comprehensive Exam, New ','92004,1);

How does one get rid of the space demarcated by the red square and add a single quote in the area shown by the red circle of the attachment?

Will this work in the .sql file?

Changed the Insert string to this:
INSERT INTO codes (code_text, code, code_type) VALUES

Changed the formula to this:
$C$1& “(”& “’”& A2& “’”& “,’”& B2& “’”& “,”& “1”& “);”

New output:
INSERT INTO codes (code_text, code, code_type) VALUES (’ Comprehensive Exam, New ',‘92004’,1);

Thanks a bunch.

yehster wrote on Wednesday, September 18, 2013:

Is there a space at the end of the Cell on line C1? That might be where the space at the square is coming from.

I think you just need another quote in the string with the second comma
Something like this

$C$1& “’”& A2& “’”& “,’”& B2& “’,”& “1”& “);”

For cleaner looking/easier to maintain spreadsheets, some people have tried Macros
http://slava-technical.blogspot.com/2010/07/openoffice-concatenate-cells-with.html

This way, one could generate the “VALUES” clause just by specifying the range of cells containing the value and not need to worry about the comma and quote stuff.

yehster wrote on Wednesday, September 18, 2013:

Dr. Massoud,
I’ve written some code for Dr. Kay which allows defining vaccination schedules (sets of vaccines for a given age). In those schedules, in addition to the CVX code, a CPT4 and ICD9 code is also associated with each entry. So if one chooses an immunization through that interface, when the save button is pressed, appropriate billing entries are also created on the fee sheet for the active encounter.

There’s no connection to inventory though, and there’s no “maintenance” interface to update the codes. (I just do it directly in the MySQL tables for now.)

I’m curious what format the MI state registry uses for immunization data. Dr. Kay is in CA, and they expect HL7 in a format that I don’t believe OpenEMR is capable of generating at the moment.

fsgl wrote on Thursday, September 19, 2013:

You are correct about a space after the left parenthesis in the Insert statement. The space after the prime was the result of not lining up the descriptions flush with the left side of the Column A.

I realized the problem with the the set of quotes surrounding the prime as you were posting.

Thank you for the link to the Macros (understood a third of it, fantastic; starting to be fun) and all the help getting the CPT import mini-article written. Mission accomplished.