Data Dictionary


A data dictionary identifies where specific data pieces are found in the OP database. All OP setups actually contain two database is:

  1. the GDBMED database, which contains all the text: demographic data, progress notes, labs, billing data, etc
  2. the DOCIMAGE database, which contains only scanned images and is beyond the scope of this tutorial

Since most OP Reports you’d want to write use only the GDBMED database, we’re going to assume that’s a given, and tell you what tables and fields the data you want will be found in.

Within the GDBMED database, there are over 60 data tables. Each table has anywhere from three to nearly 100 fields. Depending on the size of your practice and how long you’ve been using OP, some of the tables may have from hundreds to hundreds of thousands of records.

Although there are several dozen data tables, you will use some over and over while writing reports; others you might use occasionally; and many you will never touch at all. Here are some of the “frequent fliers”:

  • REGISTER is the table that contains demographic data fields, like LNAME, FNAME, SEX, BIRTHDAT
  • ARCHIVE_TRANSACTIONS is the table that contains most of the coding and billing data
  • CHARTNOTES contains fields relating to problem lists, social history, and allergies
  • CLAIMS1 contains additional information about claims status not found in ARCHIVE_TRANSACTIONS
  • The DIAG_TEST_NAME, DIAG_TEST_ORDER, DIAG_TEST_ORDER_HEADER, DIAG_TEST_RESULT, DIAG_TEST_RECALL tables collectively contain data regarding the Diagnostics module
  • The ESMG_DELIVERY, ESMG_ORG_MSG, ESMG_RESPONSES tables collectively contain fields of patient messages
  • The CONTACT table contains the master list of parent/guardian/relation contacts; the REGISTER_CONTACT table contains indexing information that connects the CONTACT table to the REGISTER table
  • MEDICATIONS contains fields about prescriptions
  • Several tables with the QUEST_ prefix contain survey fields
  • The SCHEDULE table contains data about future, current, and past appointment schedules

In general, we’ll use the TABLE.FIELD nomenclature to show where database pieces are found. So, when referring to where a patient’s first and last name are found in the database, we’ll say REGISTER.FNAME and REGISTER.LNAME. However, in the programming syntax, you’ll see constructions in the form TABLE[‘FIELD’], like REGISTER[‘LNAME’]. Don’t worry for now about knowing which to use.

Directly-Accessible Fields

While some data must be computed on the fly at the time it’s needed, or is referenced only via linking to another table, most data of interest in OP can be accessed directly by referencing the field.

REGISTER Fields

The REGISTER table is one of the “workhorse” tables in OP – it contains most of the information found in the Patient Register (F2) screens, including patient demographic data, parent/contact information, and notes fields.

The following is not an exhaustive list of the contents of the REGISTER table, but it lists most of the fields in the table useful for reports:

Chart piece REGISTER field Notes

OP chart number

REGISTER.PATNO

Chart number is an index in many tables, including ARCHIVE_TRANSACTIONS.PATNO, SCHEDULE.PATNO, CHARTNOTES.PATNO, MEDICATIONS.PATNO, REFERRALLETTER.PATNO, ESMG_ORG_MSG.PAT_ID, others.

Patient’s name

REGISTER.LNAME, REGISTER.FNAME, REGISTER.MI

To get a calculated field with “first last,” create a DBCalc variable and set its value to:

Value := ARCHIVE_TRANSACTIONS[‘FNAME’] + ‘ ‘ + ARCHIVE_TRANSACTIONS[‘LNAME’];

For the “last, first MI” convention, you can use:

Value := ARCHIVE_TRANSACTIONS[‘LNAME’] + ‘, ‘ + ARCHIVE_TRANSACTIONS[‘FNAME’] + ‘ ‘ + ARCHIVE_TRANSACTIONS[‘MI’];

Patient’s home address

REGISTER.ADDRESS, REGISTER.ADDRESS1, REGISTER.CITY, REGISTER.STATE, REGISTER.ZIPCODE, REGISTER.ZIP4

Patient’s date of birth

REGISTER.BIRTHDAT

Stored as mm/dd/yyyy.

Patient’s sex

REGISTER.SEX

Stored as single character (M, F, U)

Patient’s primary phone number

REGISTER.HPHONE

Patient’s home phone number, and used to identify “social family.” Stored as aaa-ppp-nnnn.

Patient’s VFC status

REGISTER.SICKVNO

Stored as an integer: 1 = Medicaid, 2 = self pay, etc.

Patient’s PCP

REGISTER.DOCTOR, REGISTER.ADDR_ID

DOCTOR = Stored as login initials (SKB)

ADDR_ID = Stored as staff ID number (104)

Patient status

REGISTER.STATUS_PAT

Stored as all caps: ACTIVE, TRANSFERRED, DISCHARGED, VISITOR, etc.

Medical home location

REGISTER.LOC_ID

1 for Primary Location, 2 for Secondary Location, etc.

Parent/ guardian information

See Parent/guardian contact information fields below.

Registration date

REGISTER.DOACCT

These two fields are calculated from elsewhere in OP and are not editable from the F2 Register screen.

Last visit date

REGISTER.BILLTWO

Language

REGISTER.MAIN_ LANGUAGE, REGISTER.OTHER_LANGUAGE

ENGLISH, SPANISH, etc.

Ethnicity

REGISTER.ETHNICITY

Default (blank), 1 = Hispanic, 2 = Not Hispanic

Race

REGISTER.RACE

1 = American Indian/Alaskan, 2 = Asian, 4 = Black, 8 = Hawaiian/Pacific, 16 = Caucasian

Combinations are added together (e.g. Asian/Black = 6)

To get entire list, see “Code Tables”.

Insurer

REGISTER.INS_CARRIER_CODE, REGISTER.INS2_CARRIER_CODE, REGISTER.INS_CARRIER_CODE_3

The patient’s primary, secondary, and tertiary insurance codes (two or three alphanumeric letters.) If the patient doesn’t have a secondary or tertiary insurance, the field is blank.

Guarantor

REGISTER.BILL_TO

An integer (1, 2, 3, or 4) reflecting which of the first four contacts is the responsible party to receive the bill

Guarantor ID number

REGISTER.GUARANTOR_ID

The unique ID number of the guarantor (the same as the BILL_TO person). This unique ID of contacts/family members is also used in both REGISTER_CONTACT.CONTACT_ID and CONTACT.ID.

Billing method for guarantor

REGISTER.BILLING_METHOD

MAIL, HOLD, CCONFILE, or EMAIL

Total patient balance for the patient

REGISTER.COPAYBAL

This is the child’s family-responsibility balance due from all unpaid line items/all dates of service. This may not be the same as the family balance if there is more than one child in the family.

Total insurance balance for the patient

REGISTER.CURRBAL

This is the child’s unpaid insurance balance from all pending line items/dates of service, from all insurance companies.

Last billing date

REGISTER.BILLDATE

The date of the most recent paper HCFA printing, electronic claims submission, or statement printing for this account

“Miscellaneous notes” in Register

REGISTER.REFERDBY

“Billing notes” in Register

REGISTER.BILLING_NOTE

“Referred by” in

Register

REGISTER.PAT_REF_BY

Parent/Guardian Contact Information Fields

Field

Parent 1

Parent 2

Parent 3

Parent 4

Parent’s first name

PORGFNM

PORG2FNM

P3FNM

P4FNM

Parent’s last name

PORGLNM

PORG2LNM

P3LNM

P4LNM

Parent’s address

P1ADD1, P1ADD2, P1CITY, P1ST, P1ZIP

P2ADD1, P2ADD2, P2CITY, P2ST, P2ZIP

P3ADD1, P3ADD2, P3CITY, P3ST, P3ZIP

P4ADD1, P4ADD2, P4CITY, P4ST, P4ZIP

Parent’s Social Security #

P1SSNO

P2SSNO

P3SSNO

P4SSNO

Parent’s home email

P1EMAIL

P2EMAIL

P3EMAIL

P4EMAIL

Primary phone

P1HPHONE

P2HPHONE

P3HPHONE

P4HPHONE

Mobile

PAGER1

P2CPHONE

P3CPHONE

P4CPHONE

Work phone

P1BPHONE

P2BPHONE

P3BPHONE

P4BPHONE

Lives with patient? (stored as Y or N)

P1LWP

P2LWP

P3LWP

P4LWP

Relationship (stored as all caps, truncated: OTHER_REL, GRANDPAREN, etc)

P1RELATIONSHIP

P2RELATIONSHIP

P3RELATIONSHIP

P4RELATIONSHIP

Which Contacts count as a “parent or guardian”?

Melissa Testpatient is technically neither a parent or guardian of this child – she’s “Other relative” – but in OP, she’s listed as “parent/guardian #3.”

To be one of the “parents” in these fields, the contact must be listed with number 1, 2, 3, or 4 – not just be one of the first four listed. Melissa is listed with sort order “#3,” which makes her parent #3. Thus, for this child, REGISTER.P3FNM will be ‘Melissa.” Also, you’ll notice that the fourth entry in this list is Dustin Testpatient, but his number is 5, so he is not parent #4.

To copy Dustin’s information to the parent 4 slot, click the Fix Sort # button.

Some of this information is duplicated in the CONTACTS table. To link CONTACT to REGISTER, you can join:

REGISTER.PATNO = REGISTER_CONTACT.PATNO
REGISTER_CONTACT.CONTACT_ID = CONTACT.ID

(Note: REGISTER_CONTACT.CONTACT_ID is not the same as REGISTER_CONTACT.ID)

ARCHIVE_TRANSACTIONS Fields

The ARCHIVE_TRANSACTIONS table contains nearly all the billing and data used for financial reports: productivity by rendering or billing provider, charges, CPT frequencies, billed diagnosis frequencies, payments received by insurance company, and much more. As you might expect from the title, ARCHIVE_TRANSACTIONS does not contain transactions which have not yet been daysheeted.

ARCHIVE_TRANSACTIONS uses a “double entry ledger” field which often confuses the uninitiated. Let’s use the following hypothetical to illustrate:

  • On 3/1/14, provider #104 sees patient #3000 and bills CPT 99213, for which the practice charges $95 to insurance company BLU.
  • On 3/24/14, insurance company BLU allows a total of $70 for the charge. It adjusts off $25, pays $40, and says the patient’s coinsurance/responsibility is $30.
  • On 4/8/14, the practice receives and posts patient #3000’s check for $30.

The ARCHIVE_TRANSACTIONS entries for these transactions would look something like this:

TRNSXNO

PATNO

DATE1

CPTCODE

CHARGE

PAYMENT

ADJUSTMENT

COPAYRECD

TXNOPAID

489300

3000

03/01/2014

99213

95.00

0.00

0.00

0.00

0

492847

3000

3/24/2014

1

0.00

40.00

24.00

0.00

489300

494609

3000

04/08/2014

2

0.00

0.00

0.00

30.00

489300

Highlights of the table:

  • Each transaction – a single line item charge, a payment (either patient or insurance), or an adjustment (either patient or insurance) has a unique “transaction number.”
  • In the patient #3000 example above, we’re examining a single claim (with a single CPT code), but it had three transaction lines associated with it: the initial charge transaction (489300), an insurance company payment/adjustment (492847), and a patient payment/adjustment (494609). But all three of these transaction lines would have the same claim number.
  • Some of your practice’s claims might have twenty or thirty transactions associated with them, if they have multiple CPT codes, are paid or adjusted by multiple insurance companies, and/or if they contain corrected or voided line items.
  • From within OP, you can see transaction numbers (red box) by double-clicking from within the Claims tab of a patient’s F12 Account.
  • Transaction numbers are different from claim numbers (blue box).
  • The CPTCODE field tells us what kind of transaction has been recorded:
  • Transactions which contain a standard 5-character CPT code (e.g. 99213, 96110, A4606) in the CPT field are charge transactions. They contain a nonzero CHARGE amount but zeroes for PAYMENT, ADJUSTMENT, and COPAYRECD – even if we expect the insurance company or family to pay for this line item.
  • Transactions which contain a 1 in the CPT field are insurance-side transactions – either insurance payments, insurance-side adjustments, or insurance-side voids/corrections. They will always have a zero CHARGE amount but contain nonzero PAYMENT and ADJUSTMENT fields.
  • Transactions which contain a 2 in the CPT field are patient-side transactions – payments, patient adjustments, or patient-side voids/corrections. Like CPT 1 transactions, they will always have a zero charge amount but contain nonzero COPAYRECD and/or COPAYADJUSTMENT fields.
  • Transactions which contain the characters PLA in the CPT field and are associated with patient number -5 are provider-level adjustments (associated with ERAs).
  • TXNOPAID is always 0 for charge transactions, but for payment and adjustment transactions, this field is the original charge transaction number.
  • DATE1 is the “official date” you might expect for each transaction: For a charge transaction, it’s the date of service. For a payment or adjustment transaction, it’s the payment or adjustment date (green oval.)



ARCHIVE_TRANSACTIONS field Description

DXCODE1, DXNAME1, DXCODE2, DXNAME2, DXCODE3, DXNAME3, DXCODE4, DXNAME4

The ICD diagnosis code(s) and the ICD code description(s) used for that CPT line item

CHARGE

UNITS x UNITCHARGE

UNITS

Units billed

UNITCHARGE

Charge per CPT unit

COPAYCHARGE

The amount of the CHARGE that is set to patient responsibility. This amount is all patient responsibility for this line item – not just a copay, but also deductible, coinsurance, not covered. COPAYCHARGE can either be sent on the front end, like a copay known to be $25, and/or when the remittance comes back, and an additional $34.58 goes to the patient’s deductible.

OTHERTRXPAYADJMNTS

The total insurance-side payments and adjustments so far applied to this charge line item. If CHARGE is the same as OTHERTRXPAYADJMNTS, then the insurance balance on that line item is $0 and there was no patient-side responsibility.

OTHERTRXCOPAYMNTS

The total patient-side payments and adjustments so far applied to the patient responsibility of this charge line item (COPAYCHARGE). If COPAYCHARGE is the same as OTHERTRXCOPAYMNTS, then the patient-side balance on that line item is $0. (The sum of these is an easy way to compute a patient’s total balance.)

POS

Two-digit place of service code: 11 = office, 21 = hospital, 22 = outpatient hospital, etc

LINE1, LINE10A, LINE10B, LINE10BST, LINE10C, LINE11D, LINE15, LINE16A, LINE16B, LINE20, LINE20A, LINE22A, LINE22B, LINE23

HCFA 1500 claim lines

MOD1, MOD2, MOD3, MOD4

Modifiers for CPT codes (if applicable)

SUP_ADDR_ID

Supervising physician’s OP ID number (if applicable), 0 (if not)

The following fields are only populated for payment/adjustment transaction rows:

ARCHIVE_TRANSACTIONS field Description

METHODPAY

Type of transaction: EF, CK, PC, AD

CHECKNO

The check number (when METHODPAY is EF or CK)

ADJ CODE

For insurance- or patient-side adjustments, a nonzero code indicating the adjustment reason. (Charge and payment lines usually have 0 in this field, but may also have codes.) The codes are listed in OP Utilities -> Manage Codes -> Code Table – Adjustment Reason.

(Note: this is NOT the same code set as the similarly-named HL7 Code Groups Claim Adjustment Code table found in OP Utilities -> Manage Codes -> HIPAA Code Tables.)

For more information on linking the correct code description to ADJ CODE, see “Code Tables”.

For insurance-side payments (CPTCODE = 1), the dollar amount applied to the transaction.

ADJUSTMENT

For insurance-side adjustments (CPTCODE = 1), the dollar amount adjusted on the transaction.

COPAYRECD

For patient-side payments (CPTCODE = 2), the dollar amount applied to the transaction.

COPAYADJUSTMENT

For patient-side adjustments (CPTCODE = 2), the dollar amount adjusted on the transaction.

TXNOPAID

The reference number of the transaction that this payment or adjustment is being applied to,

TXBALANCE

The negative sum of PAYMENT and ADJUSTMENT, to show how much the claim balance has changed. That is, if a payment line includes a payment of $32 and an adjustment of $17, TXBALANCE will show -$49.

COPAYBALANCE

The patient-side equivalent of TXBALANCE: the negative sum of COPAYRECD and COPAYADJUSTMENT.


  • Note that these are not necessarily the same as the dates you entered the transactions in OP. The field A_DATE1 contains the actual date/timestamp that entries were posted (orange highlight).

This system may seem “clunky” at first glance but actually allows for more sophisticated reporting than a “simpler” table might. It’s worth learning to navigate.

ARCHIVE_TRANSACTIONS includes the following fields:

ARCHIVE_TRANSACTIONS field

Description

PATNO

Patient’s chart number

CPTCODE

The CPT code (for charges), 1 (for insurance payments/adjustments), 2 (for patient payments/adjustments), and PLA (provider-level adjustments)

CPT DESCRIPTION

For charges, the description of the CPT code (e.g. “OFFICE/OUTPATIENT VISIT, EST”). For payments, adjustments, a brief description (e.g. “PAYMENT,” “PATIENT PAID”). Voided lines start with VOID, e.g. “VOID ADJUSTMENT.

TRNSXNO

The unique transaction number

INVOICENO

The 6-digit OP claim number that the transaction is associated with

INS_CARRIER_CODE

The 3-character insurance carrier that this transaction is associated with (even for patient-side transactions.)

INSURED_ID

The insurance ID associated with INS_CARRIER_CODE

INSURED_ID_OTHER and INS_CARRIER_CODE_OTHER

The secondary insurance carrier and insurance ID, if applicable, associated with this claim.

STAFFINIT

The OP login initials of the staff member who posted the transaction.

S_ADDR_ID

The OP user ID of the staff member who posted the transaction (the same person as STAFFINIT.)

DRINIT

The OP login initials of the billing provider for the transaction

P_ADDR_ID

The OP user ID of the billing provider (the same person as DRINIT.)

REND_ADDR_ID

The OP user ID of the rendering provider

LOC_ID

The location ID associated with the transaction. Defaults to 1 for single- location practices.

ARCHIVE_FLAG

Whether an archived transaction is still considered valid (1) or has been voided (2). Single-digit integer. In most reports, it’s important to exclude rows where ARCHIVE_FLAG = 2 to exclude voided/erroneous transactions.

A_DATE_1

The date-and-timestamp when the entry was posted originally.

DAYSHEET_DATE

The date on which the transaction was daysheeted and appeared first in the ARCHIVE_TRANSACTIONS table.

INSPERRCENTPAID

The daysheet number on which this transaction was daysheeted and appeared first in the ARCHIVE_TRANSACTIONS table. To recreate the charges, applied payments and adjustments in a daysheet, you can search for all transactions in ARCHIVE_TRANSACTIONS with a particular daysheet number.

The following fields are only populated for charge transaction rows:

ARCHIVE_TRANSACTIONS field

Description

DXCODE1, DXNAME1, DXCODE2, DXNAME2, DXCODE3, DXNAME3, DXCODE4, DXNAME4

The ICD diagnosis code(s) and the ICD code description(s) used for that CPT line item

CHARGE

UNITS x UNITCHARGE

UNITS

Units billed

UNITCHARGE

Charge per CPT unit

COPAYCHARGE

The amount of the CHARGE
that is set to patient responsibility. This amount is all patient
responsibility for this line item – not just a copay, but also
deductible, coinsurance, not covered. COPAYCHARGE can either be sent on
the front end, like a copay known to be $25, and/or when the remittance
comes back, and an additional $34.58 goes to the patient’s deductible.

OTHERTRXPAYADJMNTS

The total insurance-side
payments and adjustments so far applied to this charge line item. If
CHARGE is the same as OTHERTRXPAYADJMNTS, then the insurance balance on
that line item is $0 and there was no patient-side responsibility.

OTHERTRXCOPAYMNTS

The total patient-side
payments and adjustments so far applied to the patient responsibility of
this charge line item (COPAYCHARGE). If COPAYCHARGE is the same as
OTHERTRXCOPAYMNTS, then the patient-side balance on that line item is
$0. (The sum of these is an easy way to compute a patient’s total
balance.)

POS

Two-digit place of service code: 11 = office, 21 = hospital, 22 = outpatient hospital, etc

LINE1, LINE10A, LINE10B, LINE10BST, LINE10C, LINE11D, LINE15, LINE16A, LINE16B, LINE20, LINE20A, LINE22A, LINE22B, LINE23

HCFA 1500 claim lines

MOD1, MOD2, MOD3, MOD4

Modifiers for CPT codes (if applicable)

SUP_ADDR_ID

Supervising physician’s OP ID number (if applicable), 0 (if not)

The following fields are only populated for payment/adjustment transaction rows:

ARCHIVE_TRANSACTIONS field

Description

METHODPAY

Type of transaction: EF, CK, PC, AD

CHECKNO

The check number (when METHODPAY is EF or CK)

ADJ CODE

For insurance- or
patient-side adjustments, a nonzero code indicating the adjustment
reason. (Charge and payment lines usually have 0 in this field, but may
also have codes.) The codes are listed in OP Utilities -> Manage
Codes -> Code Table – Adjustment Reason
.

(Note: this is NOT the
same code set as the similarly-named HL7 Code Groups Claim Adjustment
Code table found in OP Utilities -> Manage Codes -> HIPAA Code
Tables
.)

For more information on linking the correct code description to ADJ CODE, see “Code Tables”.

PAYMENT

For insurance-side payments (CPTCODE = 1), the dollar amount applied to the transaction.

ADJUSTMENT

For insurance-side adjustments (CPTCODE = 1), the dollar amount adjusted on the transaction.

COPAYRECD

For patient-side payments (CPTCODE = 2), the dollar amount applied to the transaction.

COPAYADJUSTMENT

For patient-side adjustments (CPTCODE = 2), the dollar amount adjusted on the transaction.

TXNOPAID

The reference number of the transaction that this payment or adjustment is being applied to

TXBALANCE

The negative sum of PAYMENT
and ADJUSTMENT, to show how much the claim balance has changed. That
is, if a payment line includes a payment of $32 and an adjustment of
$17, TXBALANCE will show -$49.

COPAYBALANCE

The patient-side equivalent of TXBALANCE: the negative sum of COPAYRECD and COPAYADJUSTMENT.