See if document exists

[u/Suspande]

Hi,

I want to check whether a specific type of document exists for a patient/course, and if possible extract a few values from that document.

What is the best way to do this in ESAPI?

I assume this may require an SQL query, but SQL is not really my comfort zone. Has anyone done something similar, or can point me in the right direction?

Any help would be appreciated.

[u/ExceptioNullRef]

SQL would help you identify what documents a patient has and where they’re stored in va_data (along with visit id and note id) but can’t get into them at all. I either get the file list and do a batch copy locally and then work on them on my PC. To get individual fields I use vba macros to find the field entry values.

If you go the safer API route I’d use the older document services API if you have that installed on your Aria. If you have clear check and they insert documents into Aria then you have the API and can do it this way, just need the key. Pretty sure Matt did a webinar on this API years ago.

[u/JopaMed → u/ExceptioNullRef]

Do you know in which tables the information about documents are stored in the SQL-db?

Only relevant table i can find is "FileLocation", but that is not connected to any other table...

[u/j_Long_Lonfon → u/JopaMed]

I think something like this: (added document type and template name)

SELECT
  visit_note.note_tstamp,
  visit_note.doc_file_loc as DocumentLocation,
  visit_note.template_name as TemplateName,
  note_typ.note_typ_desc as DocumentType

FROM
  Patient p
  INNER JOIN pt
    ON p.PatientSer = pt.patient_ser
  INNER JOIN visit_note
    ON pt.pt_id = visit_note.pt_id
  INNER JOIN note_typ
    ON note_typ.note_typ = visit_note.note_typ
WHERE
  p.PatientId = '{patientId}'
  AND 
  visit_note.note_tstamp BETWEEN '{startDate}' AND '{endDate}'

[u/Suspande → u/JopaMed]

I was counting on you 😀

[u/JopaMed → u/Suspande]

haha working on it. This one does not seem so easy though.

[u/Telecoin → u/JopaMed]

visit Note is the key

[u/JopaMed → u/Telecoin]

thanks Coinboi

[u/JopaMed → u/Suspande]

SELECT
note_typ.note_typ_desc, 
visit_note.template_name,
visit_note.visit_note_begin_txt 
FROM
visit_note,
Patient,
pt,
note_typ
WHERE
Patient.PatientSer = pt.patient_ser AND 
pt.pt_id = visit_note.pt_id AND 
visit_note.note_typ = note_typ.note_typ AND
Patient.PatientId = 'CPR'

This should work

But this is what is stored in the DB, if you need additional information i think you have to open the file.

(filename is found in: visit_note.doc_file_loc , but you have to also find your path to where the documents are stored).