MEDITECH DR Tip: Data Dictionary Search with Possessives

Some time ago we published a handy tip providing you with a stored procedure you could use to hunt for things in MEDITECH DR data structure(s) — by table name, by NPR DPM, etc. — so you could get down to writing some reports.

With the impending removal of possessive fields from MEDITECH C/S, MAGIC, and MT 6.x NPR-based DR modules (and yes, this may have already happened to you), you will need to go and fix oh-so-many SQL queries. When you do that, you’ll need to go and figure out which dictionary table you need to join in.

After doing two of these, we thought…“NPR data definitions note which fields are possessives, and they point to the proper DPM from which to get the dictionary data. Wonder if that same information might be in the DR…”

Great news…It is! So we boldly plunged in and built a solution that uses two familiar tables and then two obscure tables from your MEDITECH DR:

SysDrColumns
SysDrTables
NprSegmentElements
NprSegmentPhysicalSubscripts

That code (written against C/S 5.67) required all sorts of convolutions, derived tables, calculated row numbers, and what-not to work. A big part of it also required a whopping assumption about which table, from an NPR dictionary DPM structure that had multiple child segments, was the “main” or top segment…So it worked, mostly, and we were good with that.

The output of the search now includes two new columns (when run in mode = 0): POSSESSIVE_ELEMENT and POSSESSIVE_JOIN. So if you search for AdmVisits, for example, and then look at the results for some of the fields that point to a dictionary, you get:

When you are fixing reports, you can just copy the POSSESSIVE_JOIN code and paste into your query (and adjust as needed), before swapping out the possessive fields themselves.

But then we went to implement the same code on a MT 6.15 instance — but we found that those two obscure tables were empty. We thought that might pose a problem, but…wait a minute…there were some new tables that had the info we needed. In fact, from the new tables, we didn’t have to go all the way round the mountain. The new query was much simpler, and faster, and more accurate. The new version used just:

DrTableMain
DrTableColumns

And got the same information as the complicated way — with the critical exception that in DrTableColumns there is a field called:

DrTableColumns.EleForeignKey

Which stores the internal TableID of the dictionary “main” segment that you want. We still have to make two assumptions, however. Every DR table’s primary key has SourceID to start with, and the field we want to bring into the JOIN is the second element (which is almost always the first subscript in the NPR segment).

But what about M-AT?

Well…umm…yes…there’s M-AT! Things are both easier and harder on the M-AT side. In the MT6 schema, nearly every field that points to a dictionary has a field name constructed in such a way as to give you a clue about which dictionary table you need:

RegAcct_Main.RegistrationType_MisRegTypeID

Which is good…but we want our data search query to cough up that JOIN code from M-AT, too. So that took a bit of digging, during which we found that you can jump from the column record for a field pointing to a dictionary by using the ColumnPointer_FocObjID field, and then look for the matching DrTable_Main entry that has KeyLevel = ‘D’ and DefiningRecord = ‘Main’.

Which then gets us something like:

LEFT JOIN Livefdb.dbo.MisRegType_Main ON ( MisRegType_Main.MisRegTypeID = RegAcct_Main.RegistrationType_MisRegTypeID AND MisRegType_Main.SourceID = RegAcct_Main.SourceID )

Which works! Depending on how you like your table aliasing in SQL, you will want to clean up the default output, but it’s better than starting from scratch with every missing possessive.

Not Every DR is Equal

It is sad, but true, that each DR implementation is its own beast. This is particularly the case with this tip. What seems to be the case is that if you’ve gotten to CS 5.67 or MT 6.07 or MT 6.15 — and your possessive fields have vanished — then it looks like the DrTableMain / DrTable_Main and DrTableColumn / DrTable_Column tables are populated and up-to-date and have the fields filled in that let the simple version of the queries work:

• CS 5.67 : CS_IatricDataDefQuery.sql
• MT 6.15 : MT6_IatricDataDefQuery.sql

But before that, when the possessive fields are still kicking around, then these helpful tables are often empty, or missing key data. If you have these two DTS(es) — CS DR 6068 and CS DR 6142 — then an IL of the tables should set everything straight.

In which case…it’s a good thing we spent all that time writing the hard version of the query!

• Pre-Possessive Removal_IatricDataDefQuery.sql

These need testing!

If you need more help…

The Report Writing team can help you fix reports, create new ones, make old ones faster…Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss additional resources to support your team!