Creating New Data Import Window

From ADempiere
Revision as of 03:24, 14 October 2010 by Red1 (Talk) (1. Create Importing Infrastructure)

Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.

This page is under construction --Kittiu 04:42, 12 October 2010 (UTC)


In ADempeire, there is a great way of importing data into the system using the existing Data Import module. You can read about how the Data Import works from Data Import. The great thing is that, we can always do validation to our data before the data transfer really take place.

Note.gif Note:

The data import process has two main steps. Data is read into the system and placed in a temporary table. From there it is processed and entered into the main database. The two step process helps prevent errors in the data from affecting the main database. The first step of importing the data requires a: definition of the data file that will be imported and information about where to put it in the temporary tables. This is performed by an Import File Loader and an Import Loader Format.

The only shortcoming is that, the Data Import Window available for standard version only cover some of the main data, i.e., Product, BPartner, etc. and not all data we want to import can be imported. In many case, we will want to customize our own data validation. This page will give the steps by steps on how to create a new Data Import Window.


Just to make a short example, let's say we have a huge list of Product Attribute and its values to import into the system. To make it a simple case, instead of writing a complex import window that do a mix and match of data, we will be creating 2 new Import Attribute and Import Attribute Value. Each window and its field will pretty much match with with the window we want to import data into.

1) Import Attribute, to import data to Attribute window

Import Attribute.jpg

2) Import Attribute Value, to import data to Tab Attribute Values under Attribute window

Import Attribute Value.jpg

Steps by Steps

With ADempiere's Application Dictionary framework, creating new window is easy. There are 3 major steps in creating the new Data Import window.

  1. Create Importing Infrastructure (Table & Columns, Windows & Fields, Model Calss)
  2. Create Importing Brain (Process & Parameters)
  3. Packaging for reuse

1. Create Importing Infrastructure

You can look at how to create new window at NewWindow. In our case we are going to do the same thing, except we will use it to create Importing Window.

1) Login as System, go to window "Table and Column"
  • Create new DB Table = "I_AttributeValue"
Create i attribute table.jpg
  • Click on Copy Column from Table and select "M_AttributeValue"
  • New columns will be created as of M_AttributeValue, except that it will has Key Column I_AttributeValue_ID in stead of M_AttributeValue_ID
2) As Importing Window, create more columns for
  • Mandatory columns: I_ErrorMsg, I_IsImported,Processing, Processed, ID column of target table - in this case, M_AttributeValue_ID -- (1)
  • Optional lookup columns: AttributeName -- (2)
Note.gif Note:
  1. Additional ID field (i.e., M_AttributeValue_ID) is used to save back the ID of record inserted in target table, use for reference.
  2. The preferred function of using this Import Window is that, user will only supply the Name/Vlaue field, and let the system validate for matching ID field. this is where the optional lookup field come in.
3) Create Import Table in Database
  • On any record in Column Tab, Click on Synchronize Column button.
Note.gif Note:
  • On the first time, as table is not yet crated, it will create new table and all the columns.
  • After this, if we modify it and click this button, it will only modify the current record.

1. Table Tab window, Copy columns from M_Attribute 2. Add 4 new column import => I_ErrorMsg, I_IsImported, Processing, Processed => M_Attribute_ID, (M_AttributeValue_ID, AttributeName) => + Any field we want to lookup, i.e., Attribute Name, etc. 3. Sync column to create database table 3.1 Make sure that I_IsImported do not have constraint Y/N (drop and recreate table) 4. Create Model classes


X_I_Attribute 5. Create Process Class and in window Report & Process create process and parameter 5.1 register process to Table's Button 6. Create window, re arrange as proper. 7. Register to menu 8. In Table window, link to this new Window 9. Packout

Note: For Child Table - In Column, mark M_Attribute_ID as parent link column - In Tab, Set tab level to be 0, 1 More Note: - Tab level can't be used in Import Table - All table must have ID - For I_Tables, make sure it is not null. Otherwise, it will has problem when import. Or has default value (only I_xxx_ID and isimported should be mandatory)

SQL to drop constraint ALTER TABLE adempiere.i_attribute

 DROP CONSTRAINT i_attribute_i_isimported_check;

ALTER TABLE adempiere.i_attributeset

 DROP CONSTRAINT i_attributeset_i_isimported_check;

ALTER TABLE adempiere.i_attributeuse

 DROP CONSTRAINT i_attributeuse_i_isimported_check;

ALTER TABLE adempiere.i_attributevalue

 DROP CONSTRAINT i_attributevalue_i_isimported_check;

ALTER TABLE adempiere.i_attributesetinstance

 DROP CONSTRAINT i_attributesetinstance_i_isimported_check;

Next action 1. Test Packin - OK

   - Table need to manually drop and create table - Can we use SQL in packin?
   - Permission for Process is not carried over, need to check.

2. Import Attribute Value --> Make it same window 3. Import Attribute Set, Attribute Set Instances

Testing 1. In all window, when copy, the Imported is still checked. -- OK like others. 2. About Instance Attribute flag in Attribute Set window - OK

Note.gif Note: