This VMD is designed to be used with conn:merge{} to handle autoincrementing Primary Keys in a database. It is simply our demo.vmd with the Mrn (external patient identifier) field added. The Mrn field is used to identify the patient exists in the the database.
Note: Technically in “database speak” the Mrn is an Alternate Key for the patient table (but you don’t need to know this to use the code).

First setup a MySQL database to use with the demo-autoinc.vmd:
Note: You can use a different database if you prefer.

• Create a database called Test
• Use the demo-autoinc.vmd to create the tables from within Iguana
• Change the Id column to an int data type, using “MySQL Workbench”
• Set the Id column as the Primary Key, using “MySQL Workbench”

Note: we assume you are reasonably familiar with MySQL Workbench or a similar tool for MySQL.

How to use the demo-autoinc.vmd:

• Check for an external patient identifier in the inbound feed
• Do a SELECT from the patient table, to retrieve the patient Id
• If the patient exists, leave the main Id column in the table. This ensures that the conn:merge{} call will do an UPDATE for this Id
• Otherwise, if the patient does not exist, use T:remove('Id') to remove the ID column, then conn:merge{} will then do an insert, and a new auto-incremented Id will be created

See Handling tables that use an auto-incremented ID column for details.