Import Validation Helper
From ADempiere ERP Wiki
Page Status: In Progress
Link to sf.net forum discussion
Overview of Data Import Validation
In ADempiere, for importing data from a legacy system, normally we turn to the Data Import module (&Menu > System Admin > Data > Data Import).
The following picture depicts the concept of how this module works.
If you have been working, and digging into this module before, you will know that the most important part is the validation of raw data --> to data that is valid to ADempiere.
For example, when importing Product, one of the required data is "Product Category". In the Import Product window, you will see 2 fields:
When run Import Product, the "Product Category Key" will be validated (using SQL) to get a valid M_Product_Category_ID for "Product Category".
Product Category Key = "TILE" --> Product Category = "Tile" (M_Product_Category_ID = 1000001).
All these looks OK, it work as it should be, but what is my problem?
My personal problem about Data Import in ADempiere
Reasons for this Import Validation Helper is due to the following problems I face:
Problem 1: Incompatible source of data
For that case, that you have control over the source data, it is fine. We can create that CSV file with the valid "Key". But I my case, I need to reuse product data (CSV file) from other system,
- Which may not use the same category key as Adempiere, i.e., they use "TL" --> I need to map it to "TILE" before upload to Import Product table.
- Which may need to regrouping, i.e., their group "TL", "TT", "TB" --> I need to map all of them to "TILE" group in ADempiere.
And this can't be done automatically in ADempiere's Import Process.
Problem 2: Cumbersome in writing a lot of validation routine in Import Process
If you look in to the code, i.e., ImportProduct.java, you will notice that the first portion of code (in doIt() function) is dedicated for Validation. I.e., for validate M_Product_Category_ID, we have
sql = new StringBuffer ("UPDATE I_Product i " + "SET M_Product_Category_ID=(SELECT M_Product_Category_ID FROM M_Product_Category c" + " WHERE i.ProductCategory_Value=c.Value AND i.AD_Client_ID=c.AD_Client_ID) " + "WHERE ProductCategory_Value IS NOT NULL AND M_Product_Category_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck); no = DB.executeUpdate(sql.toString(), get_TrxName()); log.info("Set Category=" + no);
A big portion of code in import process, are dedicated for validation.
If using the existing Import Window that already done with ADempiere, it is fine. But when it come to create my own import window, or adding more fields to validate, it is time consuming and error prone.
To address the above problem, I created an Import Validation Helper class (and a data mapping window) that will help mapping data / validate for valid ID even before it is sent into Import Table (when process Import File Loader).
Import Validation Helper
- Reduce validation code required in Import Processes. Make it configurable here.
- Make ADempiere data import tools, more flexible to use. Especially with different incoming sources.
The configuration of how data will be mapped / validated will be configure in the DataMapping window.
At the moment of writing, we can only map from OldValue (source) to NewValue (Adempiere). But once completed, this mapping window should address the following requirement
- Map from old value to new value, from string/number <-> string/number
- Map using wild card
- Map to constant value, in case source column is not available or not valid.
- Map by looking to the corresponding table and return valid ID.
- Map by looking at the default value specify in corresponding table. I.e., getting default warehouse in case of (or in any case) from Warehouse table.