Database App Request

[Kumaki]

Wondering if anyone could whip up an app which refers to a database, perhaps a PortaBase database...

The app would need to have an input bar with three options:

ICD9

ICD10

Name

You would type in in the field what you wanted, and then it would search the database and display the requested item.

The line item displayed should then list all three values.

This is so that I can have a handy little reference I can carry with me in my work to know Billing Codes.

Likewise an app that would do the same, but only needs two entries:

CPT

Name

This would refer to a database as well...seperate from the first database.

Anyone in the medical field would know what this was for and why I want it.

In short it would be a pocket appendix of medical billing codes.

This way if I happen to need to look up the ICD9 code for Hypertension (401.9) I could type in "Name" Hypertension...and it would come up with the name (Hypertension) as well as the ICD9 and ICD10 codes for it.

Or if I need to look up a routine office visit (99213) in the CPT filed...I could type in the name and get the code.

Likewise it needs to reverse, so that I can enter a code, and find out what it relates to...for example, in the ICD9 database...

If I entered in the "ICD9" field 290.0 - then it would return the ICD9 code 290.0 the ICD10 equivalent...and the Diagnosis, in this case, 290.0 refers to "Senile Dementia, Alzheimer Type"

Could someone whip up a simple app that would do this?

I will, myself, build the actual database. Just need the app as a shell which references the database.

I'd name one Database "ICD"

the other "CPT"

I do not even know what file extension PortaBase saves as.

At any rate, I can then add or change my database as I need. I just need the app that will access the database and display the wanted info.

I hope someone who would help me is clear on what I'm looking for.

 

[thatgui]

What format is the database in ?

If it is a sqlite db, you could use the SQLite Manager Addon for Firefox in combination with a simple (reusable) sql script (can help you with that). It won't be as comfortable as a dedicated GUI, but better than nothing if no one steps up to do the job.

 

[Kumaki]

I haven't made the database yet.

I don't really know how to tell what format it is in anyway, I just know file extensions.

Using the thing in Firefox...wouldn't it require an Internet connection? Would prefer it not requiring an Internet connection...since it would be pulling only from an internal database, anyway.

 

[Binky]

Firefox will read local files perfectly happily - use 'file://'  followed by a a path to your file or directory.

 

[Kumaki]

But I also need the ability to look up and have the thing jump to the right entry....and to look up by any of three different indices.

Could this be done?

 

[thatgui]

No, the plugin is made to work with local files, Firefox just provides the "runtime environment". The best way to find out wether it works or not would be to just try it => install the plugin and try to open the database you created

 

[thatgui]

But I also need the ability to look up and have the thing jump to the right entry....and to look up by any of three different indices.


Could this be done?

The number of 'parameters' to look for is variable - in the script they would be variables at beginning of it.

But the output would be more of a filter, so there would be no "jumping" to the right entry, but a list of entries that correspond to the given indices

 

[Kumaki]

So let me understand what you are saying, thatgui...

Let's say my database includes the following lines...

290.0 E5543.65 Senile Dementia, Alzheimer Type

401.9 A543.32 Hypertension

585.9 H5436.97 End Stage Renal Disease

And a whole bunch of other stuff in between those line entries.

The first field there is the ICD9 Code

The second field is the ICD10 Code (those I just made up, I didn't feel like looking up the right numbers)

The third field is what the actual diagnosis is, in words.

Now, suppose I want to search by ICD9....so I enter in, say 338.4 (which is in the database, but not in the example entries above) Does this give me just the entry for 338.4 which happens to be Chronic Pain Syndrome....or does it jump me into a list where I can pick from a number of choices that are close?

Now suppose I wanted to search by name, instead....so I type in Anasarca. same question....

Incidentally, Anasarca would be ICD9 782.3

The point is...I need the thing I am using to bring back all three values, whenever I type in one of the values. Preferably as one line item or as a full screen entry.

The reason I want this particular thing...I am a medical biller/coder...and these codes are used for billing medical insurance. It HAS to give me all three values and this is why:

ICD9 is being replaced by ICD10 in the near future, perhaps as soon as 2014.

ICD9 I pretty mnnuch know, but still would like a handy reference to it, because there are codes I do not use frequently.

If it helps anypne to understand...ICD9 is set up kinda like a Dewey Decimal System...each "Chapter" is the first three numbers (to the left of the decimal) and point to broader areas, for example most heart/cardiovalscular stuff is in Chapters 401-434. The two numbers to the right of the decimal are modifiers that refine the code, some codes do not have them, for example 496 is COPD, 486 is Pneumonia. Other codes have only one number to the right of the decimal, as in 401.9 being Hypertension.

What would REALLY be sweet would be for me to be able to put in the Chapter...and then have all the possible modifiers come up, for example, I type in 401 - and get back a list of 401.0, 401.1, 401.2 etc.

Now, ICD10 throws a monkey wrench into this, because it's new, and contains four times as many codes as the old ICD9. and no one is going to memorize all the codes. Hence you remember the Chapters...and then reference that Chapter to find you specific diagnosis. This code has to be correct on the insurance forms, or claims don't get paid.

Anyway, I'm rambling a bit and probably giving a bit more info than is really needed, but...this is why I need to be able to do this, so I need to know how capable any possible solution is....and how do I create a database that will work for this?

 

[skeezix]

Use a text editor, and Find .. Easy why complicate with a poorly done DB?


Jeff

 

[Kumaki]

Use a text editor, and Find .. Easy why complicate with a poorly done DB?

Jeff

I suppose. But I really need the data formatted nicely.

Does PortaBase have a FIND function that could be used that way?

 

[thatgui]

What would REALLY be sweet would be for me to be able to put in the Chapter...and then have all the possible modifiers come up, for example, I type in 401 - and get back a list of 401.0, 401.1, 401.2 etc.

Something like that would require a dedicated GUI, using a rather simple sql script would only allow things like column ICD9 contains or equals 290

I suppose. But I really need the data formatted nicely.


Does PortaBase have a FIND function that could be used that way?

you can use the one condition filter (magnifier icon, STRG+F) for that, choose the column you want, choose the method of comparison and put in a value

 

[Kumaki]

budda budda budda budda...

I am really helpless with this stuff when you talk tech.

I seriously don't know what you even just said.

I need simple instructions like

Press Button A.

Press Button C.

Don't Press Button B - that one will explode the computer...

 

[thatgui]

there are two "types" of filters in portabase, both of them can be utilized to search the database:

• permanent filters
  These are filters that you set beforehand and activate if they are needed, they are not editable on the fly. A use case could be that you use them to show only a certain chapter. To accomplish this do the following:
  - Use either the Menu : "Filter" => "Add" => press the "Add" button in the newly opened dialog, or the toolbar: Filter icon => press the "New Filter" button in the newly opened dialog, and then the "Add" button
  - now choose the column you want to search in the topmost combobox e.g. ICD9 if you want to search for a chapter in the old codes, or leave "any text column" selected if you want so search in all columns
  - In the combobox below choose the comparison type. For a ICD9 chapter search you should choose "begins with". You could also choose "contains" in that special case: If you want to search for the "401" chapter "begins with" will make certain that only rows will be shown where the first three characters equal "401" => 401.0, 401.1, 401.2, etc., using "contains" could bring up other rows as well like 400.1.401 (I know, that won't be the case here)
  - Last but not least, put in the value you want to filter e.g "401"
  - Press ok to close
  - You should see the created condition now in the list e.g. "ICD9 begins with "401""
  - You can add additional filter rules now to further "detail" your filter - maybe you want only entries of chapter 401 with diseases for a certain organ, you would add a second rule "Diagnosis contains "heart"". This would filter the database for all entries where the ICD9 field begins with "401" and the Diagnosis field contains the word "heart"
  - last but not least choose a name for the filter e.g. "blabla Chapter 401 blabla"
  - to activate the filter use either the "Filter" icon in the toolbar or use the menu: "Filter" => entries below "All Rows"
• quick filters
  They work the same way as permanent filters, but are for one time use only, and can only contain one condition. Use case would be a quick search, like you want the ICD9/10 code for hypertension

These Filters are also "stackable". So you could use a permanent filter first and then apply additional filters like another permanent or a quick filter. The problem here is that you can't remove certain filters while others should be left active. if you want to see the whole database again use the menu "Filter" => "All rows"

 

[Kumaki]

OK, that helps me a bit.

I guess I'm gonna need to just play with it a bit and see what I get.

 

[cube48]

It's really easy. 3 columns and search filter.

Import into PortaBase can be done from a .csv file.

 

[Kumaki]

Looks good. I haven't gotten to playing around with this just yet, because IK am working on finishing up a different project...so I'm not yet sure how to set up the filters, but I seem to have some good instructions for it up above in this thread. So I should be able to figure it out.

The thing I need, of course, is the ability to search by any of the the columns, by my choice...and jump to where I need to be.

Now, here's the thing...it should not be too hard to search all columns for an exact match - IF that can be conditioned on finding the exact match in the beginning of the data cell only.

Because ICD9 codes tend to be three digits, then a decimal, usually, but not always, the first three digits are numeric. there ARE a few Alpha, but not many.

ICD10, on the other hand, from what I have gathered so far on looking into it...seems to ALWAYS start with an Alpha charatcer, then numeric characters.

Diagnoses, of course, will be all alpha characters. So, searching should not be too tough.

But what I need, really, is to be able to jump to the near-right place in my database, and then scroll up and down if needed. I say this because there will be, if fully completed, about 140,000 different data lines.

 

[cube48]

The thing is that in PortaBase the search means filter. You can't scroll up and down to neighboring records after you've applied the filter. It just filters and displays those lines that you've specified in filter dialog. But if you filter smart you'll get nicely narrowed list which can be scrolled then. I would suggest set all column field types as 'text' as your id numbers are neither integers nor regular decimals. As said above you can se the searched string as 'contains', 'equal to', 'starting with' and 'not equal to'.

 

[Kumaki]

Right.

What I was asking was this?

Suppose I want to look up Cellulitis - which, in ICD9 is chapter 662

Now, the two digits AFTEr the decimal specify things, like on hand, on foot, on leg, etc.

Now, if I filter to show 662

Does it show me a list containing

662.1

662.2

662.44

662.54

662.8

etc.

Yeah, I know I would not jump from Chapter to Chapter that way - the scrollable list would be all codes starting in 662.

If I filered to find Cardiovascular stuff, in Chapters 401-436....I would have to filter it starting 4 to get them all....or starting 41 if I wanted all chapters from 410-419....and so on.

Is this basically right?

 

[cube48]

Yes, that's right. You can also create saved filter views, name them and combine several single filters together into one view.

 

[apink]

for what it's worth:


Using sqlite this would be fairly straightforward to implement using the command line. Once the database was established a couple of commands would suffice:


(Let's assume the table is called billingCodes, with collumns ICD9, ICD10 and Name)


In a terminal window:


$ sqlite3 <database> "select * from billingCodes where ICD9 like 662;"


would give you all entries where the ICD9 number started with 662.


$ sqlite3 <database> "select * from billingCodes where Name like '%renal%';"


would give you all entries where the name contained the string "renal"


You could put these sqlite3 commands into script files, then call them with different search strings.


ex: Make a file called "searchName" that looked like


#/bin/bash


sqlite3 <database> "select * from billingCodes where Name like '%$1%';"


Then make the command executable by typing


$ chmod 755 searchName


Then if you run the command


$ ./searchName renal


you get all entries where the name contained the string "renal" (renal is the first argument in the "searchName" command so it is replaced for $1 in the script command.)


Once you get this running ok put the "searchName" file into a directory like /usr/bin and you can just run


$ searchName renal