Tuesday, March 3, 2015

How to create lookup views for enum types in ERP system

Problem

Quite often we have to include fields in our data model that have no lookup table in the ERP system. They are usually some kind of "Type" and they are sometimes referred to as "Enum" types in the ERP system.

They have their text values stuck somewhere inside the ERP application layer rather than in real database tables.

Also often it is not desirable to create physical tables in ERP database. And even if we could they would be separated from both the ERP system and the data model we are creating, living their own lives so to speak.

So how do we get a lookup table with the text values corresponding to the enum types?

Solution

One possible solution to this is to create a Dynamic View in Modeller and simple create the record synthetically - with no physical tables involved.

All you need is the ERP system to look up the text values you need and then enter them in the Dynamic View.

Example

Let's say we have an enum called "LineType", which has these values:

0: Item
1: Text

We can create a Dynamic View for these values with this SQL:

SELECT 0 AS LineType_ID, 'Item' AS LineType_Name
UNION ALL
SELECT 1, 'Text'

For practical purposes there will usually be more than 2 values of your enum, so just keep adding "UNION ALL... SELECT..." statements for all the values you need.


That's all. Enjoy!