The Incore Lookup Function

When loading data, you'll often need to consult with other sources to prepare your output.  An example of this is to check the data against a common reference like part numbers, customer ids, or category codes.  When this reference data exists and is maintained outside of a relational table, Pervasive Data Integrator's Lookup Functions are an excellent way to handle the data and build up a library of reusable components for future tasks.



An Incore Table Lookup Function is a RIFL file created by the Lookup Wizard.  "Incore" is suited for Excel spreadsheets.  The RIFL file contains several functions for working with lookup tables.  The most used are

• Init - A function for applying a ConnectionString to a new DJImport object
• Lookup - Using an internal DJImport object, lookup one or more fields by a key

Other functions in the RIFL file are for making adjustments to the underlying lookup file (Clear, WriteToFile).

There are 3 steps to using a Lookup function in a new map.  The Lookup Wizard will automatically add the RIFL file to the map's Code Modules, which is run from Map Designer.

1. Add to Code Modules
2. Call Init function
3. Use Lookup function

I prefer to locate my Lookup Functions in a centralized location rather than the default location which is the Pervasive installation directory.  You should be wary about mixing your code with Pervasive's as this could complicate upgrades.  To add the Lookup Function from a central location to Code Modules, navigate to View > Transformation and Map Properties.  Select "Code Modules" and find the RIFL file in one of the empty slots.

In the Transformation and Map Properties window, select the BeforeTransformation event handler and add an Execute Action.  Use the Expression Builder ("Build").  Under User Functions, find the Incore selection.  Select the Init() function.

Call one or more of the Lookup Functions in the source / target mapping.  Build an expression from one of the fields and select User Functions > Incore > the lookup.  In the templated code, replace the key / default settings with a source field (for key) and enter a default value.  I often use "" as the default value.

Lookup Functions provide a convenient way to bring a spreadsheet into your data loading.  The functions are useful because spreadsheets are very popular in the business world and it's very easy to pass one around, have project stakeholders validate it, and move it to the production environment.  And you'll be building up a library for later.