iNTERFACEWARE™ Inc.

Defining a Table

The table grammar determines which tables will be populated in the database. You must first define each table and determine the primary key field that will be used to determine uniqueness in the database.

	The primary key field must be unique across all records for a specific table. The primary key field is typically something like a social security number, account number or another similar record identifier. If a primary key is defined but not populated during the parsing of an incoming message, no data is inserted or updated and you will get an error. However, if all the fields are not populated, no data is inserted or updated and no error will be received. In addition, each table can have more than one column specified as a primary key - these columns combine to form the primary key of that table.

To create a new table:

• Click the Table icon on the toolbar. Table0 appears in the Workspace.
• Right-click Table0, and choose Rename Table().
• Rename the table Patient. This name will be used for the name of the table in the database.
• Double-click the Patient table in the Workspace to open it.

The columns defined in the table will map exactly to the columns of the table defined in the database. Iguana will prompt you to automatically export the tables defined in the table grammar directly to the database when you finish setting up the channel that uses this VMD (this is explained later in this tutorial).

The next step is to define your table in Chameleon to store the information you want to capture. The goal of this tutorial is to capture the following data:

• Patient ID;
• First Name;
• Last Name;
• Date of Birth;
• SSN; and
• Discharged.

To define the table:

• Under the Name Column, fill it in with appropriate labels, and select the applicable data type for each item.
• Under the Key Column, check the Key checkbox for PatientID.
• Enter a description for the table. Call it Patient Demographics.

When you are finished defining the table, it should closely resemble the one below:

Patient Table Defined

Iguana functions by taking each incoming record and executing a SELECT statement for the primary key. If the key is present, Iguana executes an update query putting in the changed information. If it is not present, Iguana generates an insert query to create a new row in the database table. So, the result of receiving an A04 message for a patient whose ID is already in the Patient table is simply an update of the column values. If the patient is not already in the database, then an insert is performed on this table.