Notifications
Clear all
Topic starter
We would like to know which OBS terms are in which FORMS. I'm guessing that there's a junction table to facilitate this many-to-many relationship (because an HDID can appear in multiple FORMs, and a FORM contains multiple HDIDs).
What is the best way to get the exact relationship? We've seen approximate approaches like the link below, but would like to be more precise. -Will
https://centricityusers.com/forum/formxlatedef-field-in-formset-table/
Posted : October 6, 2016 7:18 am
This is not simple. I've done this in Access, more than willing to share the table relationships and the linking diagram, along with SQL code. Please contact me directly, [email protected]
Posted : October 6, 2016 7:48 am
Actually, I fibbed. The query is no complex but the logic is non-intuitive. The basic approach is to construct a table of obsterm frequencies in you practice, you could set a minimum filter on count if desired. Construct an unlinked query to table FORMSET looking for present of the obsterm in formset.formxlatedef. Please note change case to upper case in both fields, looks like this: InStr(UCase([formxlatedef]),'"'+UCase([Obs Term Frequency].[name])+'"') with Where <>0. Also, where clause on formset.active="D" I haven't implemented this in Crystal. Good luck.
Posted : October 6, 2016 7:58 am
One thing to keep in mind - obsterms can be referenced in multiple locations and via multiple methods. For location examples, they can appear in the XLT, XLW, and EFM files making it cumbersome to capture. Add in text components that serve as function libraries and you have a huge landscape to canvas.
Complicating matters further, obsterms can be referenced dynamically by short name only (as in an array as CCC and some custom forms often do), making it danged near impossible to capture every one via a 'report driven' approach.
The best solution is to maintain a document for each obsterm used in your EMR, something I suspect you are attempting to create. Unfortunately, catching up will be a terribly laborious process as you audit each form and its related code (if you want it done right)...
Posted : October 6, 2016 8:31 am
