Notifications
Clear all
Topic starter
Is there a report/ or a way to show what obs terms are used in custom VFE forms within EMR 9.8? Example like show me all forms that "SMOK STATUS" is used in.
Thanks
Posted : May 4, 2016 5:44 am
I've used a couple of ways to find info like this. I have a SQL query that looks for the obs term:
/* TERM USAGE IN A FORM*/
select Name, Description, Formxlatedef, Pefdata, Watchers from ml.formset
where (upper(formxlatedef) like '%HEMOCCULTGVN%'
or upper(pefdata) like '%HEMOCCULTGVN%' or
upper(watchers) like '%HEMOCCULTGVN%' )
and Active = 'D' order by Name
And I have used a tool called FileSeek to search the development area and look through all the TXT files that VFE generates when you build a clinical kit.
MD EMR Systems also makes a tool called OBS Analyzer which can do this and more functions.
Posted : May 4, 2016 12:49 pm
Topic starter
Do you know if there is a Crystal report to use for this?
Posted : May 5, 2016 3:45 am
Take a look at this thread https://centricityusers.com/forum/list-of-obs-terms/ . I don't know if it is exactly what you are looking for but it might help.
Posted : May 5, 2016 4:53 am
I thought this was an interesting problem. The following query was written in ACCESS SQL, and looks a little strange for the SQL programmer. The first query determines the number of occurrences of obs terms. The second query is an unlinked query using the obs frequency query on the Formset table. It returns a list of ordered obs terms and the ordered list of the forms they appear in. Please note, this interrogates only the formxlatedef field, but could be easily expanded to include pefdata and watchers fields by summing the instr() function. A more useful report would be to order by encounter type. Feel free to contact me with questions/suggestions. If there is any interest, I will write a Crystal Report. Contact me a [email protected]
Qbs Term Frequency:
SELECT dbo_OBSHEAD.NAME, Count(dbo_OBS.PID) AS CountOfPID, dbo_OBSHEAD.MLCODE
FROM dbo_OBS INNER JOIN dbo_OBSHEAD ON dbo_OBS.HDID = dbo_OBSHEAD.HDID
GROUP BY dbo_OBSHEAD.NAME, dbo_OBSHEAD.MLCODE
HAVING (((Count(dbo_OBS.PID))>100))
ORDER BY dbo_OBSHEAD.NAME;
Obs in Form:
SELECT [Obs Term Frequency].NAME, dbo_FORMSET.NAME
FROM dbo_FORMSET, [Obs Term Frequency]
WHERE (((InStr(UCase([formxlatedef]),'"'+UCase([Obs Term Frequency].[name])+'"'))<>0) AND ((dbo_FORMSET.ACTIVE)="D"))
GROUP BY [Obs Term Frequency].NAME, dbo_FORMSET.NAME
ORDER BY [Obs Term Frequency].NAME, dbo_FORMSET.NAME;
Following is a portion of the output:
| Obs Term Frequency.NAME | dbo_FORMSET.NAME |
|---|---|
| LDL | ACG Test Results |
| LDL | ACG Test Results 2 |
| LDL | ACG-Referral Letter |
| LDL | Diabetes-Type 2 Ltd Follow-up |
| LDL | EMR SLHV Quality Metrics |
| LDL | Health Maintenance Review |
| LDL | JWF Quality Metrics |
| LDL | JWF Quality Metrics Test |
| LDL | JWF Quality Metrics Tmp |
| LDL | Lab Letter |
| LDL | Lab Results to Patient |
| LDL | Lipid Q&E-CCC |
| LDL | PQRS |
| LDL | Quality Metrics |
| LDL | SLHV Quality Metrics |
| LDL | YC Referral Letter |
Posted : May 5, 2016 12:13 pm
I combined John's SQL statements into one statement and modified it to work with SQL 2008 and related versions.
SELECT q.NAME, FORMSET.NAME
FROM FORMSET,(SELECT OBSHEAD.NAME,Count(OBS.PID)AS CountOfPID, OBSHEAD.MLCODE
FROM OBS INNERJOIN OBSHEAD ON OBS.HDID = OBSHEAD.HDID
GROUPBY OBSHEAD.NAME, OBSHEAD.MLCODE
HAVING (((Count(OBS.PID))>100))) q
WHERE (((charindex(upper(q.NAME),upper(FORMSET.FORMXLATEDEF)))<>0)AND((FORMSET.ACTIVE)='D'))
GROUPBY q.NAME, FORMSET.NAME
ORDERBY q.NAME, FORMSET.NAME;
Posted : May 6, 2016 8:39 am
