eForm Magic Part C – Enhanced Database Operations

Documentation Copyright © 2023 by Adrian Starzynski under the Creative Commons Attribution-Share Alike 3.0 Unported License

Preface

This article is in the series of articles on creating eForms in OSCAR. Advanced techniques for making eForms in OSCAR

Enhanced Database Tags

Like with measurements, these oscarDB’s can both read and push to update the database. To allow a field to be updated in the database, the <databaseap> tag
(in resources/oscar/eform/apconfig.xml) for each field must include an <ap-insql> tag.

The apconfig.xml file is changed by your OSP on the back-end. All the eForm tags are stored in this file and it instructs OSCAR what database tags are available and what database values they lookup. Anything in the database can be created into an eForm database tag!

The <ap-insql> tag allows: ${value} (value of the entered field),${demographic} (demographic number), ${provider} (provider number), and ${fid} (eform id).

In the eform, the <input> tag that you want to be able to update must include an oscarDBinput attribute, like this:

<input type="text" name="test" oscarDB=patient_nameL oscarDBinput>

Users must check the “Update fields in database” checkbox on the eForm in order to update the fields on the form in the database. Otherwise, they’ll just be saved as part of the eform; the query won’t be executed.

Users can click on the “Refresh DB Fields” button to update all of the tags with an oscarDB attribute with the most recently available data from the database.

All updates will be recorded in the security log as a DatabaseAP “edit” action, which includes the query that was executed.

These oscarDBs cannot be directly called (i.e. oscarDB=<tag>). Similar to measurements, they should be called in special formats so as to include extra parameters.

oscarOPEN=”eFormname”

oscarOPEN="eFormname"

This will allow to open another eForm by name. The eForm must exist and be active in your OSCAR eForms list and the name must match exactly for this to work. The following is a button that will open the eForm ‘test’:

<input type="button" name="a_name_is_required" oscarOPEN="test" />

The above oscarOPEN is useful if you need to link to another eForm from within an eForm. For example, you may have a referral form that requires the practitioner to input the patient’s PHQ9 score – you could put a button that will oscarOPEN the PHQ9 eForm for the practitioner to calculate the score.

oscarDB=e$first#{var_name}

Parameter: {var_name} = (name of eform field)
Description: This ap retrieves the value of field {var_name} from the same eform which is first submitted. Only value of the same eform (same fid) is retrieved, therefore eforms using this ap must have a field {var_name}. Deleted eform data (fdid status=0) is excluded.

For example if you want to retrieve the value of the first patient which is stored in the field “name” for whom a copy of the same  eForm is saved you would use:

oscarDB=e$first#name

oscarDB=e$last#{var_name}

Parameter: {var_name} = (name of eform field)
Description: This ap retrieves the value of field {var_name} from the same eform which is last submitted. Only value of the same eform (same fid) is retrieved, therefore eforms using this ap must have a field {var_name}. Deleted eform data (fdid status=0) is excluded.

For example to retrieve the value of field “resident” in the same eForm which is last submitted:

oscarDB=e$last#resident

oscarDB=e$count#{var_name}

Parameter: {var_name} = (name of eform field)
Description: This ap counts the total number of non-empty field {var_name} in all the same eForms. Only value of the same eform (same fid) is retrieved, therefore eforms using this ap must have a field {var_name}. Deleted eform data (fdid status=0) is excluded.

For example to retrieve the total number of fields “bp” which are filled in all the same eForms:

oscarDB=e$count#bp

var$value={var_value}

Parameter: {var_value} = (value of eform field)
This ap works together with e$count to count the total number of field {var_name} which has the value {var_value} in all the same eForms.
It has no effect when put together with e$first or e$last.

If {var_value} contains space, it must be quoted (“…”).
{var_value} can be a static value or be dynamic, see *Dynamic var_value* for detail.

For example to count the total number of field “resident” which has the value “Cheng, Ronnie” in all the same eforms:

oscarDB=e$count#resident var$value="Cheng, Ronnie"

ref${var_name}

Parameter: {var_name} = (name of eform field)
This ap works together with e$count to count the total number of a certain field in the same eForms which contains another non-empty field {var_name}.
It has no effect when put together with e$first or e$last.

For example to count  the total number of field “resident” in all the same eForms which also have the field “observer” filled:

oscarDB=e$count#resident ref$observer

ref${var_name}={var_value}

Parameter:
{var_name} = (name of eform field)
{var_value} = (value of eform field)

This ap works together with e$count to count the total number of a certain field in the same forms which contains another  field {var_name} with the value {var_value}.
It has no effect when put together with e$first or e$last.

If {var_value} contains space, it must be quoted (“…”).
{var_value} can be a static value or be dynamic, see *Dynamic var_value* for detail.

For example to count the total number of field “resident” in all the same eForms which also have field “observer”=”Cheng, Ronnie”:

oscarDB=e$count#resident ref$observer="Cheng, Ronnie"

eform$name={eform_name}

Parameter: {eform_name} = (name of another eForm)

This ap works together with all e$ to retrieve values from another eForm instead of the eForm in use.

NOTE: If more than one eForms have the same name, only the first one (smallest fid) is referenced.

For example to count the total number of field “resident” in all the “Teaching Field Note” eforms which also have the field “observer” filled):

oscarDB=e$count#resident ref$observer eform$name="Teaching Field Note"

oscarDB=o${table_name}#{other_id_name}

Parameter:
{table_name} = appointment/patient
{other_id_name} = (name of the alternative id)

Description: This ap retrieves alternative id from the other_id table.
Other_id table stores alternative id for patient demographics and appointments. Mapping to more tables can be created in the future.
For patient demographics alternative id, this ap references the demographic no of the current eForm.
For appointments alternative id, it references the appointment no parameter passed when calling eForm from the appointment screen.
NOTE: Appointments alternative id won’t work if calling eForm from “Search demographic”.

For example to retrieve an appointment alternative id named “mc_number”:

oscarDB=o$appointment#mc_number

*Dynamic var_value*

var_value can be set to reference a field value in the eForm. To use this, simply write a field name and surround it with a pair of brackets {}.
NOTE: The var_value reference is made once when the eForm is loaded. Therefore the field being referenced must have an initial value (value=… or oscarDB=…). User input to the referenced field will NOT change the var_value.

Example to count the total number of field “patient” in all the same eForms which has the value of field “patient” in this eForm:

<input name="patient" type="text" oscarDB=patient_name>
<input name="total_encounter" type="text" oscarDB=e$count#patient var$value={patient}>

Summary Template

Summary templates are defined in an eForm to write a summary of the eForm content into Oscar message, eDoc or eChart when the eForm is submitted.

The template must be placed in comment <!– … –>

Special value tags are used to extract field values from eForm.

Template Format

<!--
<template>
    <document belong=provider|patient>
        <docdesc>{document description}</docdesc>
        <docowner>{document owner}</docowner>
        <content>{content}</content>
    </document>

    <message>
        <subject>{subject}</subject>
        <sendto>{recipient1,recipient2,...}</sendto>
        <content>{content}</content>
    </message>

    <encounternote>{content}</encounternote>
    <socialhistory>{content}</socialhistory>
    <familyhistory>{content}</familyhistory>
    <medicalhistory>{content}</medicalhistory>
    <ongoingconcerns>{content}</ongoingconcerns>
    <riskfactors>{content}</riskfactors>
    <reminders>{content}</reminders>
    <othermeds>{content}</othermeds>
</template>
-->

Description: Document template has a “belong=” attribute. If belong=provider, it is written as a provider document. If belong=patient, it is written as a patient document. The default is belong=provider.

The <docowner> entry must be a number. It is a provider_no/demographic_no. The default is the eForm form_provider/demographic_no.

Message template <sendto> entry (ie recipient) must be one or more provider_no. There is no default to this entry.

For all the eChart templates (<encounternote>, <socialhistory>, etc.), a link to the eForm is added to the eChart alongside the summary.

Value Tags

Value tags can be used anywhere in the summary templates, as long as it suits the format requirements.

Value tags come in 2 types: $t{…} and $te{…}

$t{…}

Put a field name inside the bracket. The field value will be filled-in when eForm is submitted.

If the field name does not exist in eForm, the tag will be written as it is to the summary.

$te{…}

These are “eForm attribute” tags. They can be one of the following:

$te{eform.name|eform.subject|eform.patient|eform.provider|eform.link|eform.html}

In place of $te{eform.html}, the complete html of the eform will be written, with the action statement removed.

In place of $te{eform.link}, a link to the eform will be written and the eform name will be shown.

NOTE: Tag $te{eform.link} does not work in message content. Also, it’s not necessary in echart templates because a link will already be in place.

Example:

eForm “TESTING” source html:

<html>
<input type="text" name="resident" value="Cheng, Ronnie">
</html>
<!--
<template>
 <encounternote>
This is the eform $te{eform.link}, it belongs to resident $t{resident}.
 </encounternote>
</template>
-->

Summary Template Output:

This is the eform TESTING, it belongs to resident Cheng, Ronnie.

(*The word TESTING is a link to the eform)