Understanding Record Tables

Each Record Table in the Data Dictionary describes the fields that exist in each record in a file.  The table below describes the field information, which is displayed in each column of a Record Table.

Note:  To see a list of Record Tables for the software module, refer to the Data Dictionary Overview.

 

Record Table Columns

Field Name

Lists the name of the field.

Notes:

  • The field name is required for the report or project that you build using a third-party application such as Crystal Reports™.

  • (Key) next to a field name indicates that this is the specific field of information used to access a record(s) in a Record Table or file. For example, the Account Number is the key to the Patient file.

Type

Defines what type of data the field contains.  This information may be helpful when planning your project.

  • decimal:  Indicates a numeric field with digits to the left and right of the decimal place where x is the number of digits to the left of the decimal and y is the number of digits to the right of the decimal.

  • int:  Indicates a four-byte integer value that supports numbers from -2147483648 to 2147483647.

  • varchar:  Indicates an alphanumeric field that contains a string of text.  A text string can contain any combination of characters including letters, words, numbers, and spaces.

  • time:  Indicates a time value.

  • date:  Indicates a date value.

Length

Lists the maximum field length.

Notes:

  • x:  Indicates a STRING field length or a numeric without decimal places.

  • x,y:  Indicates a decimal numeric field with digits to the left and right of the decimal place where x is the number of digits to the left of the decimal and y is the number of digits to the right of the decimal.
    Example    :  (4,2) means 4 digits to the left and 2 digits to the right of the decimal, a total of 6 digits.

  • DATE:  Indicates a field formatted as a date.  Date field lengths can vary depending on the method for storing the century.

  • TIME:  Indicates a field formatted for time.

 

Linking (Joining) Record Tables

There are times that you will need to link information from one record table to create the common threads needed to retrieve the information you want.  Below are some examples of commonly used table links.

Record Table Columns

Linking Patient Files

gmguar

Account_ID

gmpat

Account

gmpatin

Account

gmpatin2

Account

Linking Service/Procedure Files

gmsdte

Account _ID, Ticket_Number_ID

gmspro

Ticket_Number_ID

Linking Payment Files

gmpay

Ticket_Number_ID, Account

Linking Location File

gmmisc_location

substring (gmmisc_location.Location_ID,2,2)

Linking Insurance File

gmmisc_insurance

substring (gmmisc_insurance.Insurance_ID,2,5)