Tutorial simple medical record

Tutorial: Simple Medical Record using SQLiteBoy

In this tutorial, we will create a simple medical record system.

You can use existing database or create a new one.

Please note that extended features must be activated.

Please also note that you will need sqliteboy v0.88 or later in order to follow this tutorial.

The steps below must be done as admin user (once). For everyday operation, forms and reports should be run by standard user (created in last step).

(screenshots can be found at the end of tutorial)

1. Create table: patients
   • number of columns: 6
   • table name: patients
   • columns:
     • id: integer primary key autoincrement
     • first_name: varchar
     • last_name: varchar
     • date_of_birth: integer
     • image: blob
     • note: text
2. Create table: records
   • number of columns: 5
   • table name: records
   • columns:
     • id: integer primary key autoincrement
     • patient_id: integer
     • date_time: integer
     • user: varchar
     • record: text
3. Create form: registration
   • form name: registration
   • code:

{
      "title" : "Patient Registration",
      "data"  : [
                  {
                    "table"     : "patients",
                    "column"    : "first_name",
                    "label"     : "First Name",
                    "required"  : 1
                  },
                  {
                    "table"     : "patients",
                    "column"    : "last_name",
                    "label"     : "Last Name",
                    "required"  : 1
                  },
                  {
                    "table"     : "patients",
                    "column"    : "date_of_birth",
                    "label"     : "Date of Birth (YYYY-MM-DD)",
                    "required"  : 1
                  },
                  {
                    "table"     : "patients",
                    "column"    : "image",
                    "label"     : "Photo/Image"
                  },
                  {
                    "table"     : "patients",
                    "column"    : "note",
                    "label"     : "Note"
                  }
                ],
      "security" : {
                     "run" : ""
                   }
}

4. Create form: medical record
   • form name: medical_record
   • code:

{
      "title" : "Add Medical Record",
      "data"  : [
                  {
                    "table"     : "records",
                    "column"    : "patient_id",
                    "label"     : "Patient",
                    "required"  : 1,
                    "reference" : "select id as a, id || ' - ' || first_name || ' ' || last_name || ' - ' || date_of_birth as b from patients order by first_name asc" 
                  },
                  {
                    "table"     : "records",
                    "column"    : "record",
                    "label"     : "Record",
                    "required"  : 1
                  },
                  {
                    "table"     : "records",
                    "column"    : "user",
                    "label"     : "User",
                    "required"  : 1,
                    "readonly"  : 1,
                    "default"   : ["sqliteboy_x_user"]
                  },
                  {
                    "table"     : "records",
                    "column"    : "date_time",
                    "label"     : "Date/Time",
                    "required"  : 1,
                    "readonly"  : 1,
                    "default"   : ["sqliteboy_time3a"]
                  }
                ],
      "security" : {
                     "run" : ""
                   }
}

5. Create report: patient medical record
   • report name: patient_medical_record
   • code:

{
  "title" : "Patient Medical Record",
  "header": ["date_time", "user", "record"],
  "sql"   : "select date_time, user, record from records where patient_id=$patient_id order by date_time asc",
  "data"  : [
              {
                "key"       : "patient_id",
                "label"     : "Patient",
                "reference" : "select id as a, id || ' - ' || first_name || ' ' || last_name || ' - ' || date_of_birth as b from patients order by first_name asc" 
              }
            ],
  "security" : {
                 "run" : ""
               }
}

6. Create users. It's always a good idea to create separate user for each doctor, nurse, receptionist and cashier. All of these users should be added as standard user.

Create Patients Table Create Records Table Create Registration Form Create Medical Record Form Create Patient Medical Record Report Registration Form Medical Record Form Patient Medical Record Report (Wizard) Patient Medical Record Report (Result) Patient Medical Record Report (Result, Printer Friendly)