Package Guidelines

This page contains the package guidelines for OpenVPMS as per the date this file was updated.  Where a package mentioned here has been superseded by its maintainer we recommend the use of that new package unless specifically mentioned here.

Java

Oracle Java 8.x - Java Runtime Environment. (ie JRE)

Alternatively 

Oracle JDK 8.x

OpenJDK 8.x 

Apache Tomcat

Tomcat 7.x

OpenOffice

OpenOffice 4.x

Apache MySQL

Mysql 5.5.x 

Mysql Connector for Java 5.x

Additional helpful but not required Software

Mysql Notifier 1.x

MySQL Workbench

JasperSoft Studio 6.x

Windows

Linux

sudo apt-get install mysql-server phpmyadmin tomcat6 tomcat6-admin libmysql-java

sudo nano /etc/php5/apache2/php.ini

sudo ln -s /usr/share/java/mysql-connector* /usr/share/tomcat6/lib/

sudo nano /etc/default/tomcat6

sudo nano /etc/tomcat6/tomcat-users.xml

sudo /etc/init.d/apache2 restart

sudo /etc/init.d/tomcat6 restart

sudo nano /var/lib/tomcat6/webapps/openvpms/WEB-INF/classes/log4j.properties

sudo apt-get install openoffice.org-headless openoffice.org-writer openoffice.org-draw

sudo nano /etc/init.d/openoffice.sh

sudo chmod 0755 /etc/init.d/openoffice.sh

sudo update-rc.d openoffice.sh defaults

sudo /etc/init.d/openoffice.sh start

MySQL Configuration - Linux (my.cnf) or Windows (my.ini):
[mysqld]
character-set-server=utf8
default-collation=utf8_unicode_ci

[client]
default-character-set=utf8

	Linux/Windows:
cd <openvpms_tmp>
cd db
mysql -u root -p
mysql> drop database openvpms;
mysql> source createdb.sql;
mysql> use openvpms;
mysql> source db.sql;
mysql> quit;

	Linux:
cd <openvpms_tmp>
cd bin
./dataload.sh setup

Windows:
cd <openvpms_tmp>
cd bin
dataload.sh setup

	Linux with IBM WebSphere Application Server Community Edition:
export GERONIMO_OPTS=-Djava.awt.headless=true

Linux with Apache Tomcat:
export CATALINA_OPTS=-Djava.awt.headless=true

	Linux:
soffice.bin -headless -nofirststartwizard -accept="socket,port=8100;urp;" &

	Linux/Windows:
mkdir <openvpms-tmp>/docimport <openvpms-tmp>/docarchive

	Linux:
echo "cd <openvpms-tmp>/bin/" > /etc/cron.hourly/openvpms-docimport.sh
echo "./docload.sh --byid --source <openvpms-tmp>/docimport --dest <openvpms-tmp>/docarchive --verbose" >> /etc/cron.hourly/openvpms-docimport.sh
chmod 755 /etc/cron.hourly/openvpms-docimport.sh

Mac (OSX)

Quick guide to using openVPMS in OSX with mamp

Requirements:

1. openvpms installation files
2. MAMP (for mysql database)
3. apache tomcat6
4. Connector/J
5. libreoffice

Download OpenVPMS Installation Files

Download the latest OpenVPMS Installation files. Create a folder within your home folder to unzip the release into.

The following assumes a user named user: Move the package to ~/user/openvpms and unzip the Installation Pack. This should create a ~/user/openvpmsopenvpms-release-x.x.x folder with the necessary files to install OpenVPMS.

Download and install MAMP

Installing MAMP provides a really easy way to configure and run MySQL. Download from http://www.mamp.info/en/downloads/

The free MAMP is fine, you don't need the paid pro version.

Download tomcat6

Download tar.gz core binary distribution of Apache Tomcat version 6 from http://tomcat.apache.org/download-60.cgi

Unpack it and then move it to your home directory so it will be in ~/user/apache-tomcat-6.x.x/lib/

Connector/J

Download the connector/J which is needed to connect openvpms to the database. You then extract it and copy the mysql-connector-java-x.x.x-bin.jar to:

1. ~/user/openvpmsopenvpms-release-x.x.x/lib/
2. ~/user/apache-tomcat-6.x.x/lib/

Libreoffice

Download libreoffice and install it

Setting the variables and paths

open a terminal window and edit your .profile file eg. type:

 nano .profile 

add the following to your .profile file

 export JAVA_HOME=/Library/Java/Home

export CATALINA_HOME=/users/user/apache-tomcat-6.0.35

export PATH=/Applications/LibreOffice.app/Contents/MacOS:$PATH 

Installing a fresh installation

Start the mysql database using mamp. Using phpmyadmin (installed with mamp) go the import tab and navigate to ~/user/openvpmsopenvpms-release-x.x.x/db/createdb.sql

Import it and then navigate to ~/user/openvpmsopenvpms-release-x.x.x/db/db.sql and import it as well. 

Now in a terminal window cd to ~/user/openvpmsopenvpms-release-x.x.x/bin

run the setup by typing

 ./dataload.sh setup 

Copy the openvpms.war from ~/user/openvpmsopenvpms-release-x.x.x/webapps/ to ~/user/apache-tomcat-6.x.x/webapps/

Starting and stopping tomcat

Hopefully now you are ready to go. I installed this a long time ago so to the best of my knowledge I haven't missed any necessary configuration. To start and stop tomcat cd to ~/user/apache-tomcat-6.x.x/bin and then run 

 ./startup.sh 

or 

  ./shutdown.sh

using the terminal

Once it starts up you should be able to access openvpms at http://localhost:8080/openvpms/app

Adding gzip compression to tomcat to improve speed.

Gzipping can result in a significant speed improvement in downloading of files from the tomcat server and is easily enabled in tomcat. When the client (browser) makes a http request it tells the server if it has the ability to deflate. If so the server can gzip files such as xml, css and javascript before they are sent to the client. Significant speed increases are seen in the OpenVPMS web application especially when served from a remote server.

To enable this feature edit the connector in tomcat's server.xml file and add the compression settings as below:

Connector port="8080" maxHttpHeaderSize="8192"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="xxxx" acceptCount="100"
connectionTimeout="20000" disableUploadTimeout="true" useBodyEncodingForURI="true"
compressableMimeType="text/html,text/xml,text/css,text/javascript,text/plain" compression="on"
compressionMinSize="2048" noCompressionUserAgents="gozilla, traviata" />


If you are using firefox you can use the Yslow plugin to check if gzipping is enabled or not. Yslow can also be used to tell you the speed prior to and following enabling compression.

Auto-capitalisation

Since version 1.4, I have been getting frustrated with this function taking capitals out of acronym like FIV (changing it to Fiv) when editing product names or adding few products or fees. Can it be turned off in some areas (it doesn't exist when writing client histories for example).
Glen
Greenvale

Configuring Your New Installation

Setting Up openVPMS: Configuration: Your First Steps.

Home Page

CONTENTS

• Adding Australian Towns
• Clinic Details
• Set Up Scheduling
• Set Up Worklist

1. Adding Towns and States (all of Australia)

In Explorer, navigate to the openvpms-release-1.n\bin folder, open Command Prompt (Shift-Right-Click and select Open Command Prompt Here) and run the following command:

dataload.bat ../import/data/postcodesAU.xml

You will see a series of processing messages.......

.... and be returned to the Command Prompt when the dataload has completed.

Make sure you haven't added any states or suburbs first as this may cause duplicate errors and the load would fail.

Close the Command Prompt window when you're done.

2. Adding Your Clinic Details

In openVPMS, click Administration (1) | Organisation (2) | Find (3) and select Practice (openvpms practice)(4).

Click Edit, delete "openvpms practice" and type in your clinic's name (6), click Contacts (7) | Add (8) and enter your clinic's address (9). Press Apply (10).

Click on Phone Number (11), enter your clinic area code and number (12), and click Apply (13).

Click on Taxes (14), select Gst in left column and click on arrow to move into "Selected" column (15). Click Ok (16) to save changes and complete this section.

Click Administration | Organisation | Main.

In the same manner as decribed above, set up location and phone number.

Click on Practice | Add | Click on Binoculars | double-click on your hospital name.

Select your default printer from a drop-down box (this should be a printer installed to the server on which openVPMS resides either as a local or network printer).

If you're going to send email from openVPMS, you'll need to place the address of your ISP's smtp mail server here (as shown above). Look in the configuration of your email client (Outlook, Thunderbird, etc) for "outgoing mail server" for the server address. You'll need to add these to this screen: place your ISP's smtp server at Mail Host and your email address and passwords in the appropriate fields, as below:

Click Ok to save changes and complete this section.

3. Setting Up Scheduling

Click on Administration (1) | Organisation (2) | Select (3) | All (4) | Find (5).
Double-click on Main Appointment Schedule (6).

Click on Edit.

Fill in start and end times in 24 hour format (eg 0900), select slot size, and click OK.

Once again, click on Administration | Organisation | Select | All | Find .
Double-click on Main Surgery Schedule.

Fill in start and end times in 24 hour format (eg 0900), select slot size, and click OK.

Create a new Schedule View called Schedule View.
Click on Administration | Organisation | Select | Schedule View | Find .
You'll see No results to display.
Click on New.

A new window pops up.
Type in Schedule View (1).
Click Add (2) | Binoculars {Search}.
Double-click on Main Appointment Schedule (4).
Window will close. Press OK (5).

Click Edit (1) | Add (2) | Binoculars {Search} (3).
Double-click on Main Surgery Schedule (4).
Window will close. Press OK (5).

Click on Administration | Organisation | Select | All | Find .
Double-click on Practice Location (Main Clinic).

Click Edit. A new window pops up.
Click Edit (1) | Add (2) | Binoculars {Search} (3).
Double-click on Schedule View (4).
Window will close. Press OK (5).

Log out, then log in again.
Click on Workflow | Scheduling and you should be able to add, modify and remove appointments.

4. Setting Up Work List

Click on Administration | Organisation (1).
Click on New (2).
Click on Work List View (3).

Click New (1). A new window pops up.
Type in Work List View (2), click Add (3) | Binoculars {Search} (4).
Double-click on Main Waiting List (5).
Window will close. Press Apply (6).

Repeat the steps above, this time double-clicking on Main Hospital List, then click OK.

Your resultant screen should look like this:

Click on Administration | Organisation | Select | All | Find .
Double-click on Practice Location (Main Clinic).

Click Edit. A new window pops up.
Click Work List Views (2) | Add (3) | Binoculars {Search} (4).
Double-click on Work List View (5).
Window will close. Press OK (6).

Log out, then log in again.
Click on Workflow | Work Lists and you should be able to add, modify and remove clients on and off lists.

Home Page

---

Compiled 10 August 2011 by Yuri Sos from many forum posts

Creating a Demo/Training Version of openVPMS on a Windows 7 Computer

CONTENTS

• Turn Off UAC
• Create Demo Database
• Prepare openvpms-demo
• Set Schema Privileges in MySQL Administrator
• Change Background Colour To Orange
• Restart PC And Test

Introduction

This tutorial describes step-by-step preparation of an alternate demo version of openVPMS for training purposes, allowing you to run the system without compromising your main installation.

You should be logged into the PC where openVPMS resides using an account with Administrator privileges.

1. Turn Off User Account Control (UAC)

UAC is on by default on Windows 7 machines. Ostensibly to stop malware changing your system files, it gets in the way of doing ANY changes within the Program files folder and its sub-folders. Disabling UAC will not make you computer instantly vulnerable and, in the vast majority of cases, provides no more protection than a decent firewall and anti-virus sofware. If you leave UAC enabled, you will need to deal with messages such as this - even if you ARE the Administrator:

Ok, let's start. Click Start | Control Panel | User Accounts and Family Safety | User Accounts | Change User Account Control Settings. Drag the slider all the way down to the bottom.

Click OK, OK. A restart message will appear.

Restart the computer.

2. Create Demo Database

I assume you know how to, and have performed a recent backup of your openVPMS system using MySQL Administrator.
Start MySQL Administrator and select Restore from the menu option at left.

 

 
Click Open Backup File (1), select your file (2), and click Open (3):

 

 
Click on Another schema and type in openvpms-demo (4), check Create database.. (5), select File Charset of latin1 (6) and click Start Restore (7):

 

Once the operation is complete, click Close and exit MySQL Administrator.

 

 

3. Prepare openvpms-demo

Open Computer, browse to C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps (n.n is your version number). Click on openvpms folder and press Ctrl-C then Ctrl-V. You may see an Administrator message. Click Continue.

Right-click on openvpms - copy, select Rename and rename folder to openvpms-demo:

In Explorer, browse to browse to C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps\openvpms-demo\WEB-INF folder (1), right-click on web.xml (2) and select Edit (3).

The file will open in Notepad.exe. Near the top of the file (lines 10-13) you will see this block of text:

<context-param>
<param-name>webAppRootKey</param-name>
<param-value>openvpms</param-value>
</context-param>

Change the openvpms in the highlighted line to read openvpms-demo, thus:

<param-value>openvpms-demo</param-value>

Save and close the file.

 

Still in Explorer, browse to the classes folder. Right-click on hibernate.properties and select Open:

Click on Select a program..... and press OK:

Select Notepad, un-check Always use ..... and click OK:

Look for the line hibernate.connection.url=jdbc:mysql://localhost:3306/openvpms

and change to

hibernate.connection.url=jdbc:mysql://localhost:3306/openvpms-demo

Save and close the file.

4. Set Schema Privileges in MySQL Administrator

Start MySQL Administrator, log in and click on User Administration (1). Click on Schema Privileges tab (2), click on openvpms (3) and click on openvpms-demo (4):

Click on double left arrows (5), then click on Apply Changes (6):

Close MySQL Administrator.
 

 

5. Change Background Colour To Orange

You'll want to change the background colour to orange so that you and your staff will know whether you're working in the real thing (green) or the demo/training version (orange).

Browse to C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps\openvpms-demo\WEB-INF\classes\org\openvpms\web\resource\style folder. Right-click ob default.stylesheet and select Edit.

Select Notepad, un-check Always use .... and click OK:

Press Ctrl-H (or Edit | Replace) and change 339933 to FF9900, selecting Replace All.

Save and close the file.

6. Restart and Test

Restart your computer.

Open Firefox and type in http://localhost:8080/openvpms-demo/app. Log in as admin/admin and you should have openVPMS in orange ready for you to use for training.

To start OpenVPMS-demo on another PC on the network, you will need to type in http://IPv4_address:8080/openvpms-demo; in example below, http://192.168.1.101:8080/openvpms-demo:

Hope this helps.

---

Created 20 July 2011 (Author: Yuri Sos)

Reminder Setup

Hi,

We are just setting up a new range of reminders and it's been ages since I've worked in this area so I'm a bit rusty. A couple of questions

Cancel Interval: If this is set a for example 2 years. What happens after two years?

Senstivity Interval: Is this to do with the reminder bells appearing? Can someone give me a refresher here?

Sandra.

Setting Up A Scheduled Task in Windows 7

Setting Up A Scheduled Task in Windows 7

This tutorial is essentially an appendix to the tutorial "Automatic Document Importing into OpenVPMS" as the method for setting up the Task Scheduler is different in Windows 7 to that of Windows XP.

Click Start | Control Panel (1) | System and Security (2) | Schedule Tasks (3):

Click Create Basic Task (4):

Enter a Name(5) and Description (6) for the task, and click Next (7):

Click Daily radio button (8) and click Next (9):

Select Start date (usually left as Today) and a time (10) and click Next (11):

Click the Start A Program radio button (12) and click Next (13):

Copy this command line complete with quotes and paste into the Program/Script field (14):

C:\OpenVPMS\current\bin\docload.bat --byid --source c:\importfolder\in --dest c:\openvpms\importfolder\archive --verbose -o --regexp "^[a-zA-Z]{0,2}(\d+).*"

Copy and paste this line into the Start In field (15).

C:\OpenVPMS\current\bin\

Note: once you've pasted in these two parameters, you will need to edit these lines as per the table below:

Once this has been done, click Next (16 in image above).

This message will appear - click Yes (17):

Click to check Open the Properties... box (18) and click Finish (19):

To fine-tune frequency of operation, click on Triggers tab (20) and then Edit (21):

Select how often (22) you want to run the scheduler and for how long (23) and click OK (24) :

You're done.
 

If you need to edit this task, click on Start | Control Panel (1) | System and Security (2) | Schedule Tasks (3) | Active Tasks (25), click on OpenVPMS DocLoader (26)....

.... and click on Properties (27):

You'll then be at the window as per image above at (20) and (21).

Setting up electronic deliveries from Provet

By using the electronic supply chain interface it is possible to receive deliveries electronically from Provet. The setup requires a fair bit of configuration but once setup is a massive time saver as deliveries no longer need to be manually entered.

After following these setup instructions you will also be able to order electronically from Provet using OpenVPMS.

Before doing this you need to setup the system with product supplier codes which match with the Provet codes.

If you edit a product you will see the supplier tab.

Select this and expand the Provet entry if it exists. If not add a new supplier (Provet). The critical information that needs to be entered is the reorder code which needs to be matched with a Provet code and the package size.

This can be done manually or you could get an implementer to do it in bulk for you. I contacted my rep and gave her a csv list generated using one of the reports in OpenVPMS of all products and then she added the Provet codes to each product for me. I then went through and manually updated the information.

Once that is setup you need to setup the connection to Provet. Go to suppliers > information and edit your Provet entry. The information is entered under the stock location. The service URL is https://vethub.provet.com.au/openvpms/registryservice.svc?wsdl

The other account information (password, etc) can be obtained via your provet account manager.

That completes the setup.

When the customer service staff enter your delivery you receive an email. In general I find that this occurs before the order arrives in most cases. When a delivery needs to be entered go to the suppliers > delivery workspace press the check inbox button (if it's not already showing). Then press the find button to refresh your screen. The invoices arrive as in progress deliveries. Edit the appropriate invoice and make any necessary adjustments. (Eg. Delete the lollies entry). Any products without Provet codes such as new products will need to be matched up with the product on your system but assuming you did all he hard work before this should be minimal. Once you have confirmed all is correct finalise the delivery.

Updating OpenVPMS to 1.2-Beta-1

This page outlines the required steps to upgrade a 1.1 release OpenVPMS installation to release 1.2-Beta-1.

Updating OpenVPMS to version 1.1

This page outlines the required steps to upgrade a 1.0 release OpenVPMS installation to release 1.1.

Upgrading to OpenVPMS version 1.3

This page outlines the required steps to upgrade a 1.2 release OpenVPMS installation to release 1.3.

Upgrading to OpenVPMS version 1.4

This page outlines the required steps to upgrade a 1.3 release OpenVPMS installation to release 1.4.

Upgrading to OpenVPMS version 1.5.1

Upgrading to v1.5.1 from v1.4

1. Download the OpenVPMS installation files from here. Unzip the installation pack onto your OpenVPMS server: this should create an openvpms-release-1.5.1 folder.
    
2. Back up your OpenVPMS database.
    
3. Stop Tomcat:
   1. Right-click on Computer;
   2. Click Manage;
   3. Click Services and Applications | Services;
   4. Click on Apache Tomcat n.n;
   5. Click on Stop.

   
    

4. Navigate to db folder. Open Command Prompt (Ctrl-Shift-Right Click):

   
    

5. Start the mysql command line client and enter the following commands highlighted in yellow (don't leave out the semi-colon):
   mysql -u root -p. Enter your password. Mysql prompt appears.
   mysql> use openvpms;
   mysql> source migrate-1.4-to-1.5.sql;

   
    

6. Allow command to run then quit;.

   
    

7. Copy connector.jar
   from c:\openvpms-release-1.4\lib
   to c:\openvpms-release-1.5.1\lib.

   
   This file should already be in your Program Files\Tomcat n.n\shared\lib\ folder.
    

8. Browse to bin folder and open command prompt (Ctrl-Shift-Right Click):
   Run archload. Close window when complete.

   
    

9. Navigate to C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps.
   Select and delete both the openvpms folder and the the openvpms.war file.

   
    

10. Copy .war file from c:\openvpms-release-1.5\lib
    to C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps:
    
     
11. Close everything. Restart Tomcat. Start openVPMS.

    
     

Upgrading to OpenVPMS version 1.6

Upgrading to v1.6 from v1.5.1

1. Download the OpenVPMS installation files from here. Unzip the installation pack onto your OpenVPMS server: this should create an openvpms-release-1.6 folder.
    
2. Back up your OpenVPMS database.
    
3. Stop Tomcat:
   1. Right-click on Computer;
   2. Click Manage;
   3. Click Services and Applications | Services;
   4. Click on Apache Tomcat n.n;
   5. Click on Stop.

   

    

4. Copy connector.jar from c:\openvpms-release-1.5.1\lib
   to
   c:\openvpms-release-1.6\lib.

   

   This file should already be in your Program Files\Tomcat n.n\shared\lib\ folder (Tomcat v5 or lower) or Program Files\Tomcat 6.0+\lib\ folder (Tomcat v6 or higher).

   
    
5. Browse to bin folder and open command prompt (Ctrl-Shift-Right Click):
   Run archload. Nothing is visible on the screen, but you'll notice lots of disk activity. After a minute or so, the prompt re-appears when the batch file has completed. Close window.

   

    

6. Navigate to C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps.
   Select and delete both the openvpms folder and the the openvpms.war file.

   

    

7. Copy .war file from c:\openvpms-release-1.6\webapps
   to
   C:\Program Files\Apache Software Foundation\Tomcat n.n\webapps:

   

    

8. Close everything. Restart Tomcat. Start OpenVPMS: there will be a delay the first time you start OpenVPMS as Tomcat rebuilds its webapps openvpms folder; then it all works perfectly!

   

    

9. Click on the subscribe link to email OpenVPMS for your subscription registration key (use this link either to subscribe, or, if you already have a subscription, to obtain your key).

   When you receive your registration key,download it to your hard drive.
   Click on Administration | Organisation | Practice (1,2,3).
   Click on Subscription (4) then Upload (5).
   Browse to your saved subscription key (6) and Send it to OpenVPMS (7).

   

    

10. Your details are now embedded into OpenVPMS. Click OK to close this window.

    

     

11. You will see your subscription status whenever you log onto OpenVPMS.....

    
    .....or whenever you click on Help.

    

Customer Alerts

The customer notes area in OpenVPMS has been modified to split notes into notes and alerts.  This was done to allow more detailed display of customer alert information in the customer summary section as well as to make customer alerts work the same way as patient alerts.  These changes are detailed in the following JIRA request  and you can see an example of the new customer summary display in this screenshot.

The previous Alert button has been replaced with specific coloured and prioritised buttons which can can clicked to get more detailed information.  These alerts are also displayed while creating and editing appointments.

During the upgrade to 1.5 the database update script finds all the Customer Note Category lookups that are associated with Customer Notes that have the Alert flag ticked and then creates new Customer Alert lookups for these.  The lookups have their Priority field set to High and their Colour set to Red.  It then migrates all the existing Customer Notes that have alerts to be Customer Alerts and deletes them from the Notes.

In order to setup the colours and priorities of these new Customer Alert Type lookups you need to go into Administration -> Lookups and edit the newly created lookups to your desired settings. 

You are of course free to add additional Customer Alert Types.

From a user perspective they will need to told that they need to now make a decision in Customer -> Notes & Alerts which they will create.  Only alerts will be displayed in the customer summary and new appointment screens.

Patient Alerts

In conjunction with the Customer Alert changes noted previously, release 1.5 has made some changes to the existing Patient Alerts feature.

The previous Alert button has been replaced with specific coloured and prioritised buttons which can can clicked to get more detailed information.  These alerts are also displayed while creating and editing appointments.

From a implementation perspective the only required task is to modify the existing Patient Alert Types to set their colour and priority. Again this is done in Administration -> Lookups as was the case with Customer Alert Types.

From a users perspective little changes except they do not need to click the Alerts button anymore.

Default View Highlight

You can set the default highlight for specific Schedule and Work List views as per Jira Entry.

To setup the default highlight edit the Schedule or Work List view from Administration -> Organisation as shown in this screen shot.

Templates

There are a number of template changes that effect general printing and the behaviour of printing.

Print Copies

Wherever you see an interactive print dialog in OpenVPMS you will now notice a Copies selector.

You can set the default copies for any specific template in the Administration -> templates workspace using the new Copies field.

If the Template Printer is setup to be interactive you will get a chance to modify this at the time of printing.  If not then this will be the number of copies that will be printed automatically.

Preferred Print Mode

A new template option now allows you to control how printing certain templates behave during checkout and also  when documents are created during invoicing certain products.

The new Preferred Print Mode field allows control over whether documents are printed immedaitely when created during charging, are printed at check-out or only printed manually.

The following outlines the behaviour of the three options.   

Immediate. The document will be printed immedaitely when invoice is saved. If the template is set to interactive then it will prompt with the standard printer dialog which you can Skip if required. If you Skip  the document will then be presented at check-out.

Checkout. The document will be presented at Check-out using the standard check-out printing dialog and will be automatically ticked. This is the default settings as this is the behaviour for all previous releases.

Manual. The document will not be printed immediately and will be presented at Check-out by unticked.  Typcailly for forms that the user wishes to manually print or only occassionally print at check-out.

Interactive Reminders

Reminder Types and Product Reminder Links can now be set to display a new reminder dialog when reminders are generated through charging.

The Reminder Type Interactive flag is used as the default for any new Product Reminder interactive settings.  That is if you link a reminder to a product the setting for Interactive for the link will be determined by the setting in the Reminder Type.  This will not effect existing links just any new links created.

In the product you can nominate whether you want a specific reminder to be interactive or not overriding the original setting in the reminder type.

The default setting in non interactive so, from an implementers perspective, you will only need to modify those products that have reminder type links that you want to change to be interactive.

When set to interactive the users will get a new reminder dialog appear when they select the product with the default due date set to the interval defined for the product.  They can accept or override this date.

Medical Records Improvements

By far the most significant User Interface and operational changes is Medical Records editing.  These changes will be evident in the Patient ->Medical Records and the Check-In and Consult workflows. The changes are described in detail in the Direct Editing and Streamline Visits Item JIRA's.

This screen shot shows you the Consult workflow initial Edit Medical Records dialog.  You will note the screen is actually the summary medical records screen and you can do all the normal filtering, scrolling and reviewing functions on this screen as you would do in the Patients - >Medical Records summary page.

If you want to add a new entry just click on the New button and the following New Medical Record dialog will appear with the default entry being a Note.

When you want to edit a specific medical record entry there is now no longer a need to select the appropriate visit, click Edit and then select the visit item to edit from the table (i.e note, investigation etc).  Just double click on the entry on the summary screen and a edit dialog will appear as is shown below for a note.

All of these changes are designed to streamline editing Medical Records and to circumvent the locking issues when check-out and medical records editing happened simultaneously.

Charging Improvements

Although there are no new charging configuration options there has been a number of significant improvements to charging.  These are primarily designed to streamline the charging and are outlined in this JIRA.

On entering the new invoice you will first notice that a new invoice entry has automatically been added and is ready to enter the patient or product.  

You will note the additional buttons at the bottom of the invoice dialog.  These allow you to save the invoice and simultaneously set the invoice to the desired status, Completed or In progress.  The Ok button is still available allowing you save as normal leaving the status unchanged.

Next and Previous buttons and keyboard shortcuts have been added to allow you to easily move between invoice entries.

The Add button is disabled when you have a invoice entry that has not been completed (No Patient and/or Product selected). 

Previously if you tried to Save the invoice and there was a entry without a Patient and/or Product you would get a validation error and have to enter the information or delete the entry.  These entries are now automatically removed.

Macro Improvements

A popup Macro selector has been implemented as per this JIRA.

Also Macros can be used in Letter input fields as per this JIRA.

The Macro screen can be displayed in any text box field using the Alt-M keyboard shortcut.

Workspace Improvements

A number of workspaces in OpenVPMS have been changed to streamline bulk editing and viewing.  These changes are detailed in this JIRA.

The workspaces that have changed are:

• Products -> Information
• Administration -> Organisation
• Administration -> Types
• Administration -> Lookups
• Administration -> Templates
• Administration -> Users
• Administration -> Roles

The idea is to allow the user to create a list then work from that list to edit the individual items. This is contrary to the current situation where selection of a product etc is done through the SELECT button. This means that the list dissapears after a selection is made and needs to be re created to make another selection. Here is an example using the Product -> Information workspace.

Double clicking on any table entry will automatically take you to a standard edit dialog for that entry as shown below.

You will note there are two(2) new buttons on the bottom of the dialog, Next and Previous. These allow you to move backwards and forwards through the previous filtered list making performing mass editing or viewing of details significantly easier.

Selection Ok or Cancel on the edit dialog will return you to the filtered list either saving or cancelling any changes made on that record respectively.

Should you edit something and not Apply the change before using the Next or Previous buttons the following dialog will appear giving you the option to Save, Revert or Cancel. 

Stock Control Improvements

Stock control has been improved by allowing stock reordering preferences to be setup against specific products and stock locations as per this JIRA.

This in conjunction with a new Stock Reorder report can be used to generate a report of products that need reordering.  This new report can be found in the Shared Resources area here.

Electronic Ordering and Deliveries

The OpenVPMS components of first release of the ESCI (Electronic Supply Chain Interface) project are included in version 1.5.  In summary this includes the ability to send Orders electronically, receive electronic confirmation from the supplier that the Order was received, and receive electronic Delivery/Invoice information from the supplier.

In order to take advantage of these new features your  current supplier needs to implement a set of standard ESCI services.  Many Australian suppliers are in the process of developing these based on documentation and tools the ESCI project has already provided.  When implementing these services you will work closely with your supplier to configure OpenVPMS for these electronic services.

From a practice and a user perspective the process is quite straight forward.

When you create an order and the supplier for the order is configured to send electronically, when the order is Finalised it will automatically sent to the supplier electronically.

If this is successful then a dialog will be displayed otherwise if connection cannot be made or there is an error in the order an error message will be displayed and the Order will remain In Progress. 

You would have noticed in the Order screen shot above a new button Check Inbox.  This button is available in both the Supplier ->Orders and Supplier -> Deliveries workspaces. This button will check all suppliers which are configured for electronic communications in your system and look for electronic Order Responses or Deliveries.  It will process any found and generate messages in the Workflow -> Messages workspace as shown below.

When the supplier has processed the Order you will receive an Order Accepted or Order Rejected  message and it will be linked to the details of the Order it relates to as shown above.  The status of the Order in the Supplier -> Orders workspace will be modified accordingly.

The Order Invoiced message tells you you have received an Delivery notification. The details of the delivery are attached.  Deliveries can be for one or more full or partial orders or not associated with a order at all.

You will find the received electronic Delivery in the Suppliers -> Deliveries workspace marked as In progress.  You can then confirm this against the actual physical delivery and modify and Finalise as appropriate.  On finalising stock and any price updates will occur.

From an OpenVPMS implementation perspective in order for OpenVPMS and the supplier to successfully interact electronically any Product Supplier information needs to include enough information for the supplier and OpenVPMS to understand which product is being ordered and correctly ascertain the number being ordered or delivered.  In order to do this it is imperative that each product supplier has key supplier reorder information and the correct package description and units. 

From an ordering perspective this provides the supplier with a product code they can recognise and the type of package and size you are after.

From an delivery perspective it allows OpenVPMS to ascertain the product being delivered and how to update the stock levels correctly for the number of selling units delivered.   this is especially important if the Delivery is not associated with an electronic Order. i.e. a phone order where you are receiving an electronic delivery notification.

We suggest you work closely with your supplier(s) to implement these new features.

Patient Age Improvements

In the previous version the format of the patient age display in the Patient summary panel and the Patient Information screen was hard coded into the application.  This release allows you to customise this to suit your practice needs. 

Also the patient age at the time of each visit displayed in the Medical Record summary tab is now shown next to the visit details.

In order to customise the display you need to create a Duration Formats lookup in Administration -> Lookups and associate one or more Duration Format lookups to it as shown is this screen shot.

Each Duration Format shown in the list tells OpenVPMS whether to show years, months, weeks or days for any age up to and including the interval and units selected.  It doesn't matter in what order you add the formats as OpenVPMS will order by ascending interval and select the first interval it finds that is greater than or equal to the patients age. 

In the above example this means patients aged up to 3 months old will display in weeks and days, patients greater than 3 months old and up to 1 year old will display in months and weeks, patients greater than 1 year old and up to 3 years old will display in years and months, and patients greater than 3 years old will display in years only.

Although typically you wouldn't create more than one Duration Formats lookup you can and then you can select which one to utilise by associating with the Practice in Administration -> Organisation as shown in this screen shot.

Once a duration formats lookup is associated with the practice that format will be used to display patient ages.  If no Format is specified in the Practice (the default) then the internal default patient age formatting is used (as per version 1.4 and prior).

SMS Features

This release includes the ability to generate SMS messages from the Customer summary panel and when viewing Contacts in the Customer Information workspace as shown below.

The Send SMS button will only appear if the SMS configuration has been completed as described below and the Customer has one or more phone contacts that have the Allow SMS flag ticked.

When the Send SMS button is clicked a dialog is dsiplayed allowing you to enter an SMS message and also, if there are more than one phone contacts that can SMS, allow syou to select the contact to use from a drop down list.

Please note the SMS mesage field displasy the number of charaters remaining in the message up to the limit of 160.  You are free to utilise macros in the message field.  Also implemented are some new macro tags which will allow you to automatically display customer and patient details.  For example the following macro

 concat('This is a test message to ', openvpms:get($customer,'name'), ' about their pet ', openvpms:get($patient,'name')) 

will display

 This is a message to Abbotsford, Dan and Debra about their pet Maple.

You can use this feature to create many standard message macros suitable for your own practice.

SMS Configuration

OpenVPMS uses email to send SMS's so the selection of SMS provider is restricted to those providers that provide an Email to SMS gateway service.  Fortunately most providers do provide this service so your choice is large allowing to shop on service and price rather than being restricted to one or only a few SMS providers.

In this release we have provided three(3) different SMS configuration options.  Two(2) of these are for popular SMS providers, SMMSGlobal and Clickatell.  The third configuration option is Generic and can be used for nearly any other SMS provider.

You will need to sign up with an SMS provider and setup your account and specific preferences with them before configuring OpenVPMS.  Each provider will have their own specific setup requirements.

To add a SMS configuration firstly goto Administration -> Organisation and click New and select the appropriate SMS configuration option from the list.

SMS Global

The SMS Global configuration screen is shown below.

Country Prefix:  This will be added to the front of the number to internationalise it.

Area Prefix: This will be removed from the front f the number to internationalise it.

From: Must match the sending email address settings when you setup email preferences in your SMS Global account. You can restrict which email address can initiate sending SMS's in the SMS Global account preferences on the SMS Global web site.  Within these settinsg you can also stipulate if any SMS replies are sent to the sending or another email address.

Clickatell

Clickatell has a more detailed configuration and is generally more secure as users need to know a few more details in order to send SMS's through their account.

User Id:  Your Clickatell account user id.

Password: Your Clickatell account password.

API Id: Provide when you create account.

From:  The from addres Clickatell require.

Reply To:  The address any SMS responses are sent to.

Generic

The setup of the generic SMS configuration is beyond the scope of ths document.  We suggest you utilise the Implementers forums to request any information about setting up the Generic SMS configuration for your SMS provider.

Note:  In each of the configuration screens you have a test section which allows you to test the setup by entering a telephone number and message to send and clicking Send SMS.  We suggest you use this to test both the SMS configuration and succesful receipt of email responses.

Practice Setup

Once you have created your specific SMS configuration you need to link it to the Practice as per this screen shot.

Notes from an RxWorks conversion

This is the beginning of a set of notes from my experiences in doing a conversion from RxWorks to OpenVPMS.  This is not intended to be a polished document, but rather a set of notes on things I have come across.  I will start this as one page, but as time goes on, I expect it will be restructured into a more accessible and logical form.

First some background: I know a lot about RxWorks and am familar with traditional relations databases, but this is the first time I have played with an entity-relationship database.  The system I am converting runs 24/7 and hence I need a conversion process which is either fast enough to be done in say 3 hours, or can be pre-converted and then use catch-up/synchronise processing.

Dataload or Kettle plugin?

In case you didn't realise there are two ways of loading data into the OpenVPMS database, the dataload utility (which processes XML formatted data), or the OpenVPMS Loader plugin for Kettle.

The dataload utility has its place, and I use it to load a practice-tailored setup file that contains all the basic data including lookups, postcodes, etc etc.

However dataload does not have the performance required to load a large dataset.  For this you need the Kettle plugin.  (In my enviroment, Kettle can load 14000 customer records in 2 minutes, dataload needs an hour.)

Data conditioning

OpenVPMS provides very tight data validation through the use of lookup tables.  RxWorks is far looser, and although it has lists for various items, it is not mandatory that the data entered matches whats in the lists.  This means that any conversion from RxWorks needs to do a reasonable amount of data cleanup work.  Fortunately Kettle is a good fast powerful tool for doing this. (The 2 minutes for 14000 customer records includes all the processing to correct the suburb names [found in either the RxWorks ADDRESS3 or ADDRESS2 slots], the titles, the account types, etc etc.)  

SQL snippets

Because of the entity-relationship design, SQL queries are not as simple as with a traditional database.  Put another way, it's not obvious how you have the write the query.  Hence this collection of snippets.

SQL to show lookups
select lookup_id, name, code from lookups where arch_short_name = "lookup.suburb";
select lookup_id, name, code, arch_short_name from lookups where arch_short_name like "lookup.%";

Differences

Below are a number of differences that may need to be considered during conversion.

Appointment duration:  RxWorks uses a start-time, duration setup; OPV uses start-time, end-time.  Hence in general one calculates the end-time by adding the duration to the start-time.  However, RxWorks will not let the appointment cross the midnight boundary, but OPV will.  Hence to mimic RxWorks, you need to terminate any appointment that ends in the next day at midnight.

Negative invoices:  RxWorks allows the total invoice amount to be negative, OPV does not.  Hence, its tempting to simply convert these negative invoices into credits.  However, if you need to link clinical event line items to invoices, then you cannot do this if the invoice containing the item has become a credit.  The solution is to split the RxWorks negative invoice into a credit holding the negative items (which will normally be a discount line item) and an invoice holding the other items.

Standard labels:  RxWorks has a concept of standard labels, OPV does not.  Hence your product conversion code needs to generate specific label text for those products which use a standard label.

No temperature:  RxWorks has fields for the patient temperature and weight; OPV has only weight.  So unless you want to invent a temperature archetype and the support for it, you need to record the RxWorks temperatures in the Visit Notes field.  You also need to educate the staff to record the temperature in the Visit notes. 

Notes overflow:  RxWorks uses a memo field (ie a huge text field) to hold visit notes; OPV provides 5000 characters.  Hence your conversion code needs to be able to split long RxWorks visit notes in multiple OPV visit notes.

Summary/Reason: Rxworks also uses memo fields for other things, notably the visit summary; OPV's visit Reason field is 50 characters long.  My solution was to put the first 50 characters in the Reason field, and if some were lost, to put the whole summary in the first line of the visit notes prefixed by "Summary:".

Other overflows:  In the case of other overflows (such as the RxWorks Transaction Text going in the OPV Invoice Notes), I just discarded the excess - on the basis that although it is critical to keep the all of the medical history, the financial stuff is less important and can afford to lose text from the end of the invoice notes.

Discard old data:  It is quite reasonable to discard some old information.  In my case we discarded all counter sales over two years old, all quotes/estimates over 12 months old, all appoinment data over two years old, and all data pertaining to clients and patients whose records had been deleted.  (RxWorks does not have full referential integrity and hence in some tables there will be records containing patients and/or clients who have been deleted.)

Database size:  In these days of terabyte disks, one really does not have to worry about space occupied by the database.  However the OPV database is much larger that the RxWorks database.  Firstly the OPV database structure requires more space (I suspect about 50% more) for everything except the attachments.  However, since in OPV, the attachments live inside the database, this adds hugely to the size.  Moreover, most of the attachements are incompressible jpeg files.

This leads us to the offsite backup problem.  Currently a zipped copy of the RxWorks database is FTP'ed offsite each night (around 150MB worth).  The 5GB of attachments are copied to a removable disk once a week and this is held offsite.

With 5GB of attachments inside the OPV database, the nightly FTP is not going to work, and I do not currently have a solution to this problem. [Although the site is in Hong Kong where it is common to gigabit bandwidth available, in our our building, the uplink only runs at some 150Kbps.]

Conversion strategy

As I indicated at the beginning I need a conversion strategy that will work with a 24/7 business environment.  I now know that we should be able to convert the system with about a 3 hour data-entry lockout.  Hence the plan is as follows:

• shut down RxWorks and make a copy of the database
• restart RxWorks telling the night staff that they can use it to look up patient records, but that if they do any data entry, this will be lost
• convert everything except the >2year old visit and financial data (takes under 3 hrs)
• bring OPV up and tell the staff that they can do anything except look at >2year old records, and all attachments
• initiate the conversion of the >2year old data (takes about 2 hours)
• start genbalance running (takes about 45 minutes), and docload running (takes ???)

Note that implicit in the above is the fact that one can use OPV while the various load utilities (ie Kettle, genbalance, and docload) are running.

That's it for the moment.  I may add more later and I will come back and fill in the ???? bits.

OpenVPMS Loader plugin for Pentaho Kettle

This documentation was generated as a result of using OpenVPMS 1.5 and Kettle 3.2

The OpenVPMS Loader Kettle plugin allows mass data to be loaded into the OpenVPMS database.
Since OpenVPMS uses an entity-relationship architecture, rather that the traditional relational database design, one needs a tool that understands the archetypes used by the system. Also, because one does not have the table record keys present in a relational database, there is the potentional problem that having say loaded customer Fred into the system, one cannot at a later time, load in Fred's pets - because Fred's identity number is not known (because it was generated internally by the loader). The Loader plugin provides for this by allowing you to specify an ID field that can be used at a later time to refer to the record previously loaded. Thus Fred's customer record can be loaded with say ID 'C004236' (with 004236 being his client number in some other database), and this ID can be used later when loading Fred's pets. This 'external-key to internal-id' data is recorded in the etl_log file in the database.

The Loader also automatically generates lookup data. Say that the customer archetype defines a field 'title' where the contents must be present in the title lookup table, then without auto lookup generation, you would have to first check the data set to be loaded to see what values are used for the titles (eg Mr, Ms, Dr, Professor, Mde, Doctor) and then create the title lookup table with these entries. With auto lookup generation, this is not necessary and the Loader will create the necessary entries for you.  However, you do have the problem that if the incoming data quality is poor (with titles like Mr, MR, Mr. and M R) then you will end up with all these in the lookup table - and you will have to use the Replace facility to clean things up.  Hence pre-cleaning of your data is recommended (and Kettle is a very good tool for doing this).

The Kettle Transform step definition window contains the following: (obvious things like the OK button are omitted)

ID Field Name: the name of the field which you want to use as your 'external-key'. This field can be left empty, but you will not be able to later refer to the records being loaded.

Batch Size: this set the number of records loaded before a commit is done. Increasing the number increases the performance at the cost of losing data if a abort occurs. So setting it to say 99999999 will ensure that only one commit is done at the end of the run, but if anything goes wrong nothing will be loaded because all records added will be rolled back out as a result of the transaction failure.

Actually - although the above is logical, its not true. Benchmarking has shown that 50 to 200 is a reasonable optimum for performance.

Generate Lookups: The checkbox is left from previous versions - lookup generation is now always enabled. See OVPMS-1240.

Skip Processed: This checkbox tells the loader what to do about pre-existing records with the same 'external-key'. ie if you have loaded Fred's record in a previous run, what happens when you try to load it again.
If checked, a row that has been previously processed successfully will be skipped.
If not checked, all rows will be reprocessed. Care needs to be taken as this can result in duplicate objects being created.

The following can be set for each field:
Field Name: the name of the field in the step's input record

Map To: The entry here identifies the field to be loaded.
For simple fields its the archetype name and field name, for example <party.customerperson>firstName.
For collections it will have a format like <party.customerperson>contacts[0]<contact.location>address where the index [0] defines this as the first contact.
For cases where you wish to refer to a specific identifier it will have a format like $CLID<party.customerperson>contacts[1]<contact.phoneNumber>telephoneNumber - here CLID is the name of the input field that contains the 'external key' of the customer record loaded earlier for which you wish to set the 2nd contact to be the specified phone number.

Note that the indexes are not absolute indexes - ie its not as though the collection has a set of numbered slots. The indexes serve only to separate the entries for the loader.  Hence if we use the second form above (where we are specifying a contact for the customer referenced by $CLID) then the loader will just add this contact to to the customer - and if the customer had 6 contacts already, this will be the 7th, not the second as the [1] appears to indicate.   Put another way, the ONLY time you need to use other than [0] as the index is when you happen to be loading multiple members of the collection in the one loader step such as in the example below.

Exclude null: This controls what happens when the incoming field has a null value.
If this is set to N, then a null input will be set on the output field, replacing any default value.
If this is set to Y, then a null input won't be set on the output field, and the default value will be set. If there is no default defined for the field, it will be set to null.
Hence this setting determines whether or not the default value is set for fields whose incoming value is null.

Note that in the supplier example above, the Y's set for the contacts mean that if a field (eg oFax) is null then no fax contact will be created. This means that one does not have to worry about having separate loader steps to cater for cases where some of the data is missing.  You just code for thr full data set as above, and rely on Exclude null=Y to omit the fields for which you have no data.

For these cases one uses code like the following in a preceding Javascript step:
  var oFax = ""; // init fax number to empty/null
  if (SupplierFax != undefined) { //if fax set in data record
     oFax = SupplierFax; //set it
  }

 Value: If you leave this blank, then the data from the incoming field is put into the 'map to' field. However, you can enter expressions here. These will normally operate on the incoming field data in some way as to produce a different value that is put into the 'map to' field. The incoming field data is referred to using '$value'. There are two common formats used here when we are processing fields that have lookup constraints:

a) the <archetype>$value form of the mapping is used when the value refers to an object identifier (see examples below), and implies that the item being referred to must have been previously loaded by the loader.

b) the <archetype>nodename=$value form is used when the value refers to something to be looked up. NOTE that at the current time (1.5.1) the lookup will not be successful if the value you are trying to match contains spaces. See OVPMS-1258

Exclude default objects: As of OpenVPMS 1.5, the "Exclude default objects" column is ignored - you can set it to Y or N. See OVPMS-1239 for details.

Value Examples:

Mapping using nodename= (here we are loading a customer and as part of this setting the account type)

Mapping using values: (here we are connecting previously loaded customers to previously loaded patients)

Error messages:
The Loader plugin is not very good at error checking. a) Kettle's 'Verify this Transformation' checker will not always reveal errors in your entries; b) when an error occurs during the run, the error message is likely to contain little useful data.  However, you may find the following helpful.

Message: "Row doesnt contain value: {0}" errors on every row
Meaning: "You twit, you have specified a field name here that is not one of the input fields - check your spelling and capitalisation - also use Get Fields to grab the names of the incoming fields".

Message: "Unexpected error null:" as follows (where 'OpenVPMS Loader.0' is the step name:
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) : Unexpected error: null
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) : java.lang.NullPointerException
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) :  at org.openvpms.etl.load.RowMapper.mapValue(RowMapper.java:192)
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) :  at org.openvpms.etl.load.RowMapper.map(RowMapper.java:161)
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) :  at org.openvpms.etl.load.Loader.load(Loader.java:115)
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) :  at org.openvpms.etl.kettle.LoaderAdapter.load(LoaderAdapter.java:110)
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) :  at org.openvpms.etl.kettle.LoaderPlugin.processRow(LoaderPlugin.java:118)
OpenVPMS Loader.0 - ERROR (version 3.2.0-GA...) :  at org.openvpms.etl.kettle.LoaderPlugin.run(LoaderPlugin.java:187)
Meaning: you have mis-spelled one of the Map To entries

Message: "Failed to retrieve object" as follows (where nnn is the row id and XXX is the value you are tyring to lookup)
OpenVPMS Loader.0 - Failed to process row nnn: Failed to retrieve object: <lookup.customerAccountType>XXX
Meaning: either you have used the 'object identifier' form  rather than the 'code' form (see Value: above) or the object you are referencing really does not exist. See also etl_log below.

Boolean fields

Kettle understands boolean fields.  OpenVPMS has boolean fields.  However, fields being processed by the Loader should not be in boolean format but rather integer or numeric/precision 0.  If you try to load a field like <party.customerperson>active with field typed as boolean, then it will always be set to False.  You have to use a Select Fields (or other) step to change the meta data to Integer.  Then the field will get loaded correctly.  1 = true, 0 false.

Tricks of the trade

dataload integration  If you load data with the dataload utility, then although it uses ID's to link the data being loaded, these IDs are not added to the etl_log file and thus cannot be used by the Kettle loader.  However, there is no reason why you cannot inject records into the etl_log file to allow you to link to the records that you loaded using dataload.  The attached etl_users.ktr illustrates this.  It grabs the user information loaded earlier by dataload and creates the necessary etl_log entries so that when using Kettle to load say visit data, it is easy to reference the associated users and clinicians.

separate steps for linking Because of OPV's entity-relationship structure, is it very common to need to link things together.  One of the most basic is the need to link patients to customers. While you might think - load the customers first, then as I load each patient, I will link it to the appropriate customer.  This doesn't work - as you attempt to create the link between the customer and patient, although the customer connection will appear to work, the patient end will not.  What you need to do is use one transform to load the customers, then one to load the patients and finally a third to build the links between them.  Below is an example of the mapping in the Loader transform step used to do this.  Note that the ID Field Name for this step is oCRN.  oCRN can contain anything that is different on each row - I used the row number of the record from the RxWorks Patient-Owner file I was loading. oCID is the ID of the customer loaded earlier and oPID the patient ID.

SQL editing Kettle uses SQL to pull data from the database you are converting.  However, it has very very rudimentary facilities to let you develop the required SQL queries.  In my case (converting from RxWork's Access database), I found it far easier to build the query in Access, then display the SQL equivalent and then copy and paste it into the Kettle Table Input step.  Because of the join limitations of Access, in some cases you will have to build a prior query in Access (so that the SQL you use ends up using both the RxWorks tables and the prior query you created). 

Debug info  I found it very useful to have a Text File Output step at the end of the transform which dumped every field from every record/row into a CSV file.  When there were errors, this enabled me to look both at the rows in which there were errors, but also at the data in the rows which did not cause an error.

Abort step  I also found it useful to have an Abort step as the very last step.  During development, you can limit the number of rows processed.  When all is working, you just disable the link to the Abort step and all data will be processed.  (And because of the 'Skipped Processed Records=Y' setting, the 50 rows at the beginning that loaded with no error will be skipped automatically.)

etl_log  An understanding of the etl_log file structure is useful. [More correctly its a table - 'file' betrays my age.] If you use the MySQL Workbench or equivalent you will find that it has three fields of interest (as well as others):

row_id - this contains the value from the field set as 'ID Field Name'

archetype - this contains the archetype being loaded (eg entityRelationship.patientOwner in the example above)

loader - contains the name of the Loader step.  This is useful in figuring out which transform/step resulted in this record (sorry 'row').

Hence a useful query is something like  'select * from etl_log where archetype="entity.investigationType" order by row_id;'

Note that since the lookups are done on the basis of archetype/row_id there absolutely no problem in using the same ID's for two different archetypes.  There is a major problem if you use the same ID more than once for the one archetype. [There will not be any problem as the data is loaded, but if you ever need to reference it, you will get 'refers to multiple ...' errors.]

Product varieties OPV divides products into three varieties, medication, merchandise, and services, each with its own archetype.  This means that when loading product data, you need to use three different OPV Loader steps, and steer the processing to use the appropriate one (see below).  Hence your processing needs to have some method of deciding on the variety of the product.  Bear in mind that only medications can have labels, hence if there is some product that you think of as merchandise, you will need to class this as medication if you need to be able to print a label for it. (The example I found was a shampoo, which required a 'for external use only' label.)

Need to Split Processing  In some cases where you need to process medications, merchandise, and services it may be necessary to split the transform.  That is, although you might think that its possible to use a transform with the above design with three separate loader steps, if you do this, you will get problems with Kettle running out of heap space.  The solution is to clone the transform into 3, with each just processing one product variety.  The problem seems to occur if the loader needs to update multiple records.  For example the mapping given below (in which I am setting the product type for products loaded by an earlier transform) caused heap overflows if I used the above '3 loader steps in one transform' design, but ran happily when I split it into three transforms, one for each of service, merchandise and medications.

Reading the archetype  As you are developing transforms you will want to refer to the archetypes. Don't using the Export facility on the Administration|Archetypes screen - it generates a very complete but overly complex adl file.  If you do want to look at a file, grab a copy of the adl file from the <OPV-HOME>\update\archetypes\org\openvpms\archetype folders.  Alternatively, look at the archetype via the Administration|Archetypes screen.  Note however, that if you use View, you will not be able to see the full archetype.  Specifically, if the archetype has a node that links to multiple archetypes (eg participation.product's entity node), then if you use View, you will only be shown one.  To see all four you need to Edit the archetype.

More Kettle tips and tricks

This note serves to document things I have learnt using Kettle (3.2). 

What Java? Kettle does not appear to function correctly with a 64-bit JVM.  It does happily run with Java 8.  If you do want to run Kettle (with a 32-bit JVM) and Tomcat (with a 64-bit JVM) on the same system, this is perfectly possible. My experience is on a windows system so I configured Tomcat using C:\Program Files\Apache Software Foundation\Tomcat 7.0\bin\Tomcat7w.exe to explicitly tell it where to get its JVM (in my case C:\Program Files\Java\jre1.8.0_45\bin\server\jvm.dll) and I set the path environment variable to have C:\Program Files (x86)\Java\jre1.8.0_45\bin at the beginning.
 

Installation  To expand on the recipe given elsewhere:

Download and unzip Kettle.  From version 1.4 we are compatible with version 3 of the PDI so you can download version 3.2 - BUT not yet (at version 1.6) compatible with version 4 of the PDI. 

Unzip the OpenVPMSLoader.zip file found in the import/plugin folder in the openvpms distribution.  This should be unzipped into plugins/steps folder in Kettle.
NOTE: Kettle 3.2 ships with version 2.0.6 of the Spring framework, whereas the OpenVPMS Loader plugin requires Spring 3.x.  Hence you need to:
1. backup your Kettle installation directory
2. remove libext/spring/spring-core.jar
3. copy the OpenVPMS jars to libext/spring: 
         cp plugins/steps/OpenVPMSLoader/*.jar libext/spring/
4. Start Kettle

Failure to do the above will result in a "No application context specified" error when the OpenVPMS Loader plugin starts.
 

Kettle is a really neat powerful tool.  You may find the following will save you some time.

Log size  The internal log is not of infinite size. If something goes wrong and you have an error on every row, the earlier records will be discarded.  Also after an overflow situation like this, Kettle seems to be unable to fully clear the log.  That is, you can clear it, and it appears to empty, but when you start a new transform/job the old stuff re-appears.  To fully clear the log after an overflow, stop and restart Kettle.

External log  You can specify the log file for a job or transform.  If you specify it for a job, then the settings apply to all the transforms in the job (unless you provide log settings for them).  Note that the records are batched out into the log - the internal log runs ahead of the external one, and in the event of a crash (most commonly a java heap overflow), you will not get all records in the external log.

Minimum logging = no errors  If you set the logging level to Minimum, then if your transform has errors, these will not show in the log.

Can detach and park  If you have a job that consists of a number of jobs and/or transforms done in sequence, you may wish to re-run the transform omitting some of the transforms/jobs.  The easiest way to do this is to delete the hop at each end of the string you don't to run, then move this out of the way, and rejoin things so as to omit the transform/jobs you don't want to run. See below.

Always turn off Lazy Conversion  Some of the input steps have a 'Lazy Conversion' checkbox.  Do not tick it - using this leads to peculiar error messages.

Trim memo fields  Some databases support a long text field called 'memo'.  If you are processing this data, and you want to log it via the Text File Output step, make sure that you trim back its lenght.  Left unaltered the field lenght is over a million characters and this will cause a heap overflow.

No registry start  It is possible to have Kettle crash leaving its repository containing a screwed up transform.  This may lead to Kettle crashing on start.  The fix is to tick the 'No Repository' box on the startup screen.  Then you can can connect to the repository once Kettle has started. 

Performance Herewith tips to get the maximum performance out of Kettle:

1. Give MySQL lots of buffer space (I run innodb_buffer_pool_size=3G).
2. If possible put the database on an SSD - perhaps not wise for production, but for the conversion phase, the faster the disk, the better.
3. Where possible run transforms/jobs in parallel.  Since the basic pattern of work is 'compute in Kettle, write to MySQL', it turn out that running two jobs in parallel has little effect on the run-time of each. Three or four is possible given suitable hardware.  Running multiple jobs is just a matter of creating a job containing multiple sub-jobs - like the picture below. Right-click on the Start element and select "Launch next entries in parallel".  Note that the complete job does not finnish until all the sub-jobs have finished. You will need to think about what things can and cannot be run in parallel.  You can also split the load of one type of item (eg invoices) into multiple parts with each part loading a separate date range.
   
4. Don't think that the OpenVPMS Loader 'Batch Size' parameter should be large. Experimentation shows that things go faster as it gets smaller: eg 500~112 minutes, 200~107 minutes, 50~96 minutes.
5. If at all possible run kettle and MySQL on the same machine.  For production you will want MySQL (and Tomcat) running on a dedicated server, but for conversion its better to run all on one system.  [Currently I am running two invoice loads in parallel and this is resulting in over 3.3MB/sec of network I/O between Kettle and MySQL.]

The following points all apply to the 'Modified Java Script Value' transform step

Var's not required  You only need to use "var" to define a variable if you need it in the next step.  If its just an internal work variable, then don't use var.

Always turn off compatibility mode  Always disable the compatibility mode. Compatibility should only be set if you have transforms from the earlier versions of Kettle.

Replace may need escape  If you use the replace function, be aware that the second argument (string to look for) is in fact a regular expression, and thus that any regex meta characters will need escaping.  Futhermore, since in javascript \ needs escaping, you need to double the backslash. Hence, to replace periods (.) by spaces you need  replace(str,"\\."," ") and to replace backslashes themselves you need to quadruple them, ie replace(str,"\\\\","x") will replace \ by x.  However, since the 'look for' string is a regex expression, you can use replace() to replace multiple characters at the same time. 
Hence replace(str,"[\\-\\* ]","x") will replace every minus (-), asterisk (*), and space character by 'x'.

Regex is peculiar  If you want to use the str2RegExp function, be aware that the required format of the regex string is not obvious.  Firstly you MUST specify string start (^) and string end ($), and you MUST double every backslash. For example the string to look for any-chars9999 9999any-chars  OR any-chars99999999any-chars (ie phone numbers) is:  "^.*(\\d{4} \\d{4}).*|.*(\\d{8}).*$".

Be careful of plus  Javascript uses the + operator to both add numbers and concatenate strings. It also has no specific variable typing facility, and can treat things that you think are numbers as strings. The expression (v1 + v2) may concatenate the two variables.  If you need these to be treated as numbers either force the second to be converted to a number by using the Number() function [ie write the expression as (v1 + Number(v2))], or use the fact that the minus operator forces the variable to be treated as a number [ie write the expression as (-(-v1 - v2))].

Another Kettle sample

We run a ranking system for our customers - the top 5 by sales are classified as Platinum, ranks 6 though 25 as Gold, and 26 through 100 as Silver.  We have Platinum, Gold and Silver customer alerts (with appropriate colours).

Although we have a report which lists the current customer ranks and what they should be, our staff were not getting the time to do the updates.

To automate the process I wrote some Kettle stuff that implements the following logic:

For each customer in the top 500 by sales volume over the last 180 days, calculate rank as platimum/gold/silver/none then:

If new rank = old rank
   If rank not = none
       update alert reason to 'Checked on date/time'
    Endif
    Exit
Else  (ie rank changed)
   If old rank not = none
       Set end date on existing alert to today, and status to COMPLETED
   Endif
   Create new plat/gold/silver alert
Endif

The attached zip file (renamed to .txt so I can attach it) contains the job and transforms.

The tricks are:

• empty the etl_log file to clean out any old stuff
• create a entry in the etl_log for the user 'sys' - who will be the author of the alerts
• for each top 500 customer create an entry in the etl_log
• both the above steps are necessary so that in the OpenVPMS Loader step we can create the required links to the author and customer.

Even if you don't want to create alerts this code illustrates how to use Kettle to load stuff which references existing data in the system.

Note that I considered writing some SQL to do the complete job, but decided that it was beyond my competance.

Using Kettle to load a product file

I happened to get involved in helping someone load product data from their supplier. (Their initials are CK and hence the file and transform names.) 

You will probably have realised that loading OpenVPMS product data is not just a simple matter of loading up a flat file.  To get things set up correctly not only do we need to link products to their supplier, but we need to set the Product Type (because a lot of the reporting in OPV has selection via product type, and because the product type also defines the invoice order and (optionally) discounts.

You may have also understood that OPV divides products into medications, merchandise, and services, and that our product loader has to cope with this.

Why Kettle?  There is also the dataload tool, and its quite possible to use dataload to load product data.  However, if you are starting from a data file provided by the product vendor, you need to process this in the xml format required by dataload.  You almost need Kettle to do this processing, so you may as well use Kettle to do the load the data - it certainly gives us flexibility and powerful processing facilities.

If you look inside the attached zip (it has been renamed to ck.zip.txt so that I could attach it) you will find 5 kettle transforms and the kettle job that includes them (so you can run the job rather than the five transforms).  There is also an extra transform (see below) than can be used to update prices at a later time. [Note however, that with the advent of the product export/import in OpenVPMS 1.7 this is not really useful.]

Also in the zip are two csv files. CK-supplier contains the single supplier, CK-products contains the products.  Note that this is a severely truncated and editied product set.  The orginal one contained some 7500 items and has been truncated to protect the supplier's commercial information.

The csv file was generated by loading the supplied text file into Excel and then manipulating it, adding the extra columns, and saving it as a CSV file.  So if you want to use these transforms to load your own supplier's data, then you need to process this to match the format of this product file (or modify the transforms).

I also added extra columns as follows:

ProdClass - a single letter to indicate the type of product G=goods=merchandise; M=medication, S=service.  If you add your own supplier's data, you can start by tagging everything as G, but you will need to go through and mark the things that should be medication as M. [Recall that medications have a DrugSchedule and a dispensing labels and instructions.]  You can also add service items like Consults.

SupplierNo - this is set to 1 for every item.  If you include products from another supplier, put 2 here and the add the corresponding line to CK-supplier

Markup - this is the markup index - see the code in the Process step of the CK Products transform, and adjust as you need.

Species - put the species here for items that are species specific. Instructions in the Process step of the CK Products transform.  You can leave this column all blank if you want.

FixedPrice - this column is here if you want to add service products, or if you want to put a 'flag-fall' price on some goods or medications. [eg putting a fixed price of $10 on a can of dog food with a unit price of $3 means that 10 cans cost $10+10x$3 = $40.]  No problem if you leave blank.

UOM - unit of measure for the item.  If you have previously set up the 'Units of Measure' lookup, then you can use the appropriate codes here. No problem if you leave blank.

DrugSched - for the scheduled drugs, put their schedule here (ie S4, S8 etc).  Note that although everything has a schedule (ie dog food is S1 - non-dangerous), only medications have a slot for the schedule.  This means that if there is some item which you think of as merchandise, but it has a schedule that you need to record, then you Must make the item a medication.

ProdType - the Product Type for the item.  What is set here needs to match the Name field in the CK_productTypes.csv file. 

The transforms are as follows: 

• CK Supplier loads the supplier(s) from the CK_supplier.csv file.
• CK Load Product Type loads the product types from the CK_productType.csc file.
• CK Products loads the products and their prices.
• CK Product Supplier links products to suppliers and adds in the supplier specific information.
• CK Set Product Type sets the product type for each product.

There is also  a CK New Prices transform.  This uses the same product input file (for simplicity sake) but just updates the prices. [Remember that in OPV a price update does NOT replace the previous prices – it just add the newer ones with the new date.  Hence these supersede the old one, but you can see what the old prices were. This happens because the prices are ‘collections’. If you updated a simple field (like Name) then the new data will replace the old.]

Problems:

a) I have not set all the available datafields

b) you will see that I set both the product Name and Printed Name to the name from the Description field in the data file.  OPV has done its 'properCase' processing of the Name field, but the standard archetype does not set properCase for the Printed Name field.  If you want the name on invoices etc to come out the same as the proper cased name, delete line 2 of the mappings in each of the OPV Loader steps so that Printed Name does not get set (and will default to what is in the Name field).

c) you will have to adjust the folder names set in the xxx.csv steps.  You will find that they are all set to " C:\Users\Tim\Documents\HKG Operations\OpenVPMS\Kettle\"

d) naming convention:  all variables generated by the Process step are prefixed by 'o'.  Hence fields like DrugSched come from the input file and have not been altered.

Using the Openvpms Docloader

Openvpms has a number of methods to enter data or information into your Openvpms MySQL instance.  To convert large data sets the OPENVPMS Kettle Plugin for Pentaho Data Intergration version 3.2 should be used, however it is possible to use the dataloader and also the document loaders to enter some data.  The dataloader parses a properly structured XML file and loads it into the database, creating the correct linkages as it does so.

The DocumentLoader loads patient or customer documents or attachments into the Database.  This article will summarize the available options when using the document loader. Further details explaining how to implement an automatic loader that can load lab results is details here.

http://www.openvpms.org/documentation/automatic-document-importing-openvpms

This will cover the arguments we can use with the docloader

--byid (-i) or --byname (-n)  You must use one of these options otherwise the docloader fails.

1.--byid (-i)  Using this options looks for an investigation ID to attach the file to.  If you specify byid you can also specify

--source (-s)  the source directory to load from  (default ./)

--dest  (-d) the destination to archine processed documents

--regexp   a java regexp statement to allow correct parsing of the document name to extract the ID

Default Value = [^\\d]*(\\d+).*

--overwrite (short flag -o )  if used  allows you to overwrite existing documents rather than add a new one.

--recurse (short flag -r) if set  that will scan the source recursively

--type(-t) I believe you can specify the archetype shortname you are targetting.  If not set it will default to Patient Investigations. The archetype short name. May contain wildcards. ie act.*Document*

--context (-c) Default ./applicationContext.xml Please rememeber the .bat or .sh usually sets this to ../conf/applicationContext.xml.  So usually you should not set it.

--verbose (-v) Verbose Logging..Logs verbose message to the log or console.

--failonerror (-e) fails on any error. Used for testing purposes usually.

2. --byname (-n) Using this option Load files by matching their names with document acts"

You can only set source, destination, recursive and type when this option is used. Overwrite, and regexp are ignored.

iReports - tips and tricks

NOTE that this is a first draft and may contain errors - if you see any, please comment.

The following may help when you start trying to edit or create reports for OpenVPMS.

Version: although iReports is now up to version 5, for the current version of OpenVPMS (1.6) you need to use iReports 3.7.6.

Manual: you may find that http://jasperreports.sourceforge.net/JasperReports-Ultimate-Guide-3.pdf is useful.

Concepts: OpenVPMS uses two types of 'reports' - those that you run as Reports and which use SQL to extract data from the database, and those that are invoked by various document Print requests - and for these the OpenVPMS code provides the data to the report. For the SQL ones you can use the Preview function with iReports to test your report.  For the others, you cannot (unless you are clever and dummy up the required data) so testing is a matter of uploading your new/modified report into the appropriate OpenVPMS report template, and then printing an invoice (or whatever).

Data Source: in order to use the preview function, you have to tell iReports what the data source is.  When you start iReports, the initial display (after you dismiss the 'new version available' message box) looks like the following:

As it says, step 1 is to create the database connection.  You need to create a "Database JDBC Connection". You can see in the top left, I have previously created what I called "OpenVPMS - Local". Clicking the 'Report Datasources'  button to the left of this shows the available data sources.  Below is what I have with the 'OpenVPMS - Local' one (my default) expanded.

If you don't set up the datasource, then by default you will be using iReports 'empty datasource' and your report preview will show nothing.

---to be continued---