Eliminate the Need for Virtual Tables

The AP Invoice eForm product for Perceptive Content offered by Lexmark Enterprise Software provides for a streamlined data entry process for many Accounts Payable departments.   One pain point many people have experienced is its use of the “Virtual Tables”, a method used by this Perceptive eForm for storing Vendor, GL, and PO data.  While these tables have satisfied the requirement of allowing the eForm to store and retrieve data for years, they aren’t perfect.  Some of the issues we see include heavy resource usage and slow query speeds.

Status Quo

So what’s wrong with Virtual Tables?  What IS a Virtual Table?  There are a number of reasons that these tables cause headaches for both users and administrators.  At its core, a Virtual Table record is just a Project or Folder created inside of Perceptive Content, with additional data stored in the custom properties of that Folder.  For example, a purchase order record will create a Folder with the PO Number as the name, and store the amount, date, vendor, and other information in the custom properties.


So Why Is This Bad?

Virtual Tables cause a number of problems, and it gets a lot worse as the number of records scale up.  Let’s break it down:

  • The database is competing against itself.  When you add, remove, or retrieve virtual table records, it is impacting the same database you are relying on to speedily provide images and their data.  This creates a relationship where, as performance of the eForm improves, the overall system performance is made worse.
  • Overall system performance is greatly impacted.  Because of how custom properties are stored in the database, you could be adding hundreds of millions of rows to a table that documents also retrieve information from.  This can and does impact the whole system, even outside of AP.
  • Updating Virtual Tables is slow.  With Virtual Tables, you are forced to use the iScript API to add and remove records.  When you get into the millions of records this can take many days to complete!
  • Keeping a duplicate set of data.  Even worse, you are now maintaining another set of data from your financial system.  Because of the slow pace at which data is mirrored into the Virtual Tables, delays occur in the invoice processing which costs the department money.

There Is A Solution

We have discovered a way around all of these obstacles.  New functionality allows us to create a custom connector.  Implementing a custom connector exposes a number of benefits:

  • Use existing Data Sources.  New functionality allows us, for the first time, to configure the AP eForm to look up data directly against an external database or web services.
  • Users get work done faster.  The speed at which the form operates is noticeably faster to end users.  Shaving seconds off of each invoice is critical at the end of the day.
  • Less time maintaining the upload.  Administrators have less overhead in maintaining the data transfer – once the views are set up on the financial system, they do not need to be touched.
  • Get your upgrades done on time.  System and AP eForm upgrades go much faster as the need for a full purge and reload of the virtual tables is completely eliminated.  This can save days worth of time during critical maintenance windows.
  • Gain synergy with Intelligent Capture.  If Intelligent Capture is being used, the views created for the Capture implementation can be used for the eForm connector.  This not only allows for a swifter set up, but now the two systems share one data source.

John Marney
ISTS Lead Consultant