Difference between revisions of "Creating New Data Import Window"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
m (Example Scenario)
m (Overview)
 
(18 intermediate revisions by the same user not shown)
Line 1: Line 1:
<span style="color:red;"><b>This page is under construction</b></span> --[[User:Kittiu|Kittiu]] 04:42, 12 October 2010 (UTC)
 
 
 
==Overview==
 
==Overview==
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.
+
In ADempeire, the recommended way to import data is through Data Import Process. 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.
 +
 
 +
You can read about how the Data Import works from [[Data Import]].
 +
 
 +
The only problem is that, the Data Import windows comes with standard version of ADempiere do not cover all kinds of data. What if we want to import other kind of data? Or may be to modify the existing one?
  
''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.''
+
;This page will give you steps by steps guide on how to create a new Data Import Window of your own.
  
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.
+
--[[User:Kittiu|Kittiu]] 09:33, 17 August 2011 (UTC), [http://www.ecosoft.co.th ecosoft]
  
 
==Example Scenario==
 
==Example Scenario==
  
As an easy example, let's say we have a huge list of Product's attribute values to import into the system. We will be creating a new '''Import Attribute Value''' window to do the task. For ease of understanding, its fields will be pretty much matching with the window we want to import data to.
+
To be an easy example, let's say we have a data list of Product's '''Attribute Values''' to import into the system. Note also that each Attribute Values will be linked to the existing '''Attribute Name''' already exist in the system.  
  
[[File:Import Attribute Value.jpg|500px|Import Attribute Value window, to import data to Tab Attribute Values under Attribute window]]
+
As ADempiere do not provide Import Attribute Value data import window out of the box, we will want to creating a new '''Import Attribute Value''' window to do the task.
 +
 
 +
For demonstration purposes, we will be creating an Import Attribute Value window that pretty much matching with the Attribute Value tab we want to import data into.
 +
 
 +
[[File:Import Attribute Value.jpg|700px|Import Attribute Value window, to import data to Tab Attribute Values under Attribute window]]
  
 
'''Note:''' In real life case, we might design a complex import window that validate data and import into different tables in one go.
 
'''Note:''' In real life case, we might design a complex import window that validate data and import into different tables in one go.
  
==Create New Data Import Window==
+
==Creating New Data Import Window==
With ADempiere's Application Dictionary framework, creating new Data Import Window is easy.
+
#Create User Interface (Table & Columns, Windows & Fields, Model Calss)
+
#Create Business Logic (Process & Parameters)
+
  
 
===Create User Interface===
 
===Create User Interface===
Line 32: Line 35:
 
*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
 
*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) Create more columns for Data Import table
+
;2) Create required columns for Data Import table
 
*'''5 Mandatory columns:''' ''I_ErrorMsg, I_IsImported,Processing, Processed,'' ID column of target table - in this case, ''M_AttributeValue_ID'' -- (1)
 
*'''5 Mandatory columns:''' ''I_ErrorMsg, I_IsImported,Processing, Processed,'' ID column of target table - in this case, ''M_AttributeValue_ID'' -- (1)
 +
**Set all columns in this table '''not''' mandatory, except 2 columns --> Table's ID column and I_IsImported
 
*'''Optional lookup columns:''' ''AttributeName'' -- (2)
 
*'''Optional lookup columns:''' ''AttributeName'' -- (2)
:[[File:i_attrvalue_columns.jpg|500px]]
 
 
{{Note|
 
{{Note|
 
#Additional ID field (i.e., M_AttributeValue_ID) is used to save back the ID of record inserted in target table, use for reference.
 
#Additional ID field (i.e., M_AttributeValue_ID) is used to save back the ID of record inserted in target table, use for reference.
#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.
+
#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.  
 
}}
 
}}
 +
:[[File:i_attrvalue_columns.jpg|500px]]
  
 
;3) Create Import Table in Database
 
;3) Create Import Table in Database
 
*On any record in Column Tab, Click on '''Synchronize Column''' button.
 
*On any record in Column Tab, Click on '''Synchronize Column''' button.
{{Note|
 
 
*On the first time, as table is not yet crated, it will create new table and all the columns.
 
*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.
+
*Note that if the table created, this button will only alter the current column.
}}
+
*Drop of IsImported column in the database table, since it only allow 'Y' and 'N'. (later, we will use 'E' as well)
 +
<pre>
 +
ALTER TABLE adempiere.i_attributevalue
 +
  DROP CONSTRAINT i_attributevalue_i_isimported_check;
 +
</pre>
  
 
;4) Create Interface and Model Classes for new table
 
;4) Create Interface and Model Classes for new table
 
*In your Eclipse project, run [http://www.adempiere.com/index.php/NewWindow#Generate_Model GenerateModel class] to create Interface and Model for this table. These models will be useful when we refer to data in this table in Business Logic.
 
*In your Eclipse project, run [http://www.adempiere.com/index.php/NewWindow#Generate_Model GenerateModel class] to create Interface and Model for this table. These models will be useful when we refer to data in this table in Business Logic.
 
*In our case, we are creating I_I_AttributeValue.java and X_I_AttributeValue.java. Note that our Entity Type is 'RJC', so the arguments are,
 
*In our case, we are creating I_I_AttributeValue.java and X_I_AttributeValue.java. Note that our Entity Type is 'RJC', so the arguments are,
:<pre>
+
<pre>
:C:\<target folder>\
+
C:\<target folder>\
:org.compiere.model
+
org.compiere.model
:'RJC'
+
'RJC'
:'I_AttributeValue'
+
'I_AttributeValue'
 
</pre>
 
</pre>
*Copy and paste these 2 files under org.compiere.model package.
+
*Deploy this 2 files to '''org.compiere.model''' package.
  
 
;5) Create new Window
 
;5) Create new Window
Line 71: Line 78:
 
**Bottom part: Process Button, Is Processed CheckBox
 
**Bottom part: Process Button, Is Processed CheckBox
 
**Lookup Name fields same line as its ID fields. For example, in this case, Attribute Name (AttributeName) is next to Attribute (M_Attribute_ID).
 
**Lookup Name fields same line as its ID fields. For example, in this case, Attribute Name (AttributeName) is next to Attribute (M_Attribute_ID).
:[[File:import_attributevalue_fields.jpg|500px]]
+
:[[File:import_attributevalue_fields.jpg|350px]]
  
 
;6) Register new window to Menu
 
;6) Register new window to Menu
Line 80: Line 87:
 
Now if you login again as admin user, you should be able to see the new window. You can test creating new records and save it.  
 
Now if you login again as admin user, you should be able to see the new window. You can test creating new records and save it.  
  
Next step, we will look at how to create ImportAttributeValue Process, so that clicking at Process Now button will validate and import data into target table.
+
'''Next step, we will look at how to create ImportAttributeValue Process, so that clicking at Process Now button will validate and import data into target table.'''
 +
 
 +
===Create Business Logic===
 +
 
 +
All Process Class for Data Import windows are in package '''org.compiere.process''', all classes start with Import<XXX>. Now we want to create our own ImportAttributeValue.java Process.
 +
 
 +
;1) Create JAVA process
 +
You can see example from package '''org.compiere.process''', or use the example provided here [[File:ImportAttributeValue.java]]. Although some Import Process as you can find from the package are relatively complex, they share the same pattern with our simple example. Here is what it does,
 +
#'''protected void prepare()'''
 +
#*Get the input parameter, in most cases are Client and/or Organization, Delete Old Imported?, Is Validate Only?
 +
#'''protected String doIt()'''
 +
#*If user select Delete Old Imported, then delete old imported records from previous time.
 +
#*Reset importing records ready to start validation (set IsImported = 'N')
 +
#*Data Validation and Data Lookup
 +
#**Most of the logic goes here to ensure data integrity before the real import take place.
 +
#**Data Validation can be as complex as we wanted, i.e., check whether the record exist, check duplication, etc. For invalid record, add Error Message (and set IsImported = 'E')
 +
#**Data Lookup can be as automated as we want, i.e., getting ID of Attribute from Name, getting the default value, etc.
 +
#*For valid data (IsImported = 'E'), do the real import to target table(s)
 +
#**Then set Is Imported = true, Processed = true
 +
#*And finally, return the number of successful / unsuccessful import
 +
 
 +
;2) Create new Process in ADempiere
 +
*Login as System, got to window "Report & Process"
 +
*Create new Process = "Import_AttributeValue"
 +
*Set Classname = "org.compiere.process.ImportAttributeValue"
 +
:[[File:import_attributevalue_process.jpg|500px]]
 +
*In Parameter tab, set parameters based on what are required. In this case, Client , Delete Old Imported? and Is Validate Only?
 +
*Note that, we can use "Copy from Report and Process" button in the first tab, in case, the same already exists.
 +
:[[File:import_attributevalue_parameter.jpg|700px]]
 +
 
 +
;3) Register this new process to "Process Now" button.
 +
*Login as System, go to window "Table and Column"
 +
*Open the newly crated Table "I_AttributeValue"
 +
*On column "Processing", set Process = "Import_AttributeValue"
 +
:[[File:import_attributevalue_processingbutton.jpg|500px]]
 +
 
 +
;That's it!
 +
 
 +
Now the new Import Attribute Value window is ready to use along side with other Data Import Windows. And we can use it just like others.
 +
 
 +
;Enjoy!
 +
 
 +
:[[File:import_attributevalue.jpg|500px]]
 +
{{Note|
 +
*To package it to use in another system, use 2Pack to packout as Application and Module. It will pack everything except Java class that need to deploy separately.
 +
*It is recommended to use System Administrator to create Import Loader Format, and use Client's user to do the data import.
 +
}}
  
[[Category:Localisation]]
+
[[Category:Projects and Tools]]

Latest revision as of 02:33, 17 August 2011

Overview

In ADempeire, the recommended way to import data is through Data Import Process. 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.

You can read about how the Data Import works from Data Import.

The only problem is that, the Data Import windows comes with standard version of ADempiere do not cover all kinds of data. What if we want to import other kind of data? Or may be to modify the existing one?

This page will give you steps by steps guide on how to create a new Data Import Window of your own.

--Kittiu 09:33, 17 August 2011 (UTC), ecosoft

Example Scenario

To be an easy example, let's say we have a data list of Product's Attribute Values to import into the system. Note also that each Attribute Values will be linked to the existing Attribute Name already exist in the system.

As ADempiere do not provide Import Attribute Value data import window out of the box, we will want to creating a new Import Attribute Value window to do the task.

For demonstration purposes, we will be creating an Import Attribute Value window that pretty much matching with the Attribute Value tab we want to import data into.

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

Note: In real life case, we might design a complex import window that validate data and import into different tables in one go.

Creating New Data Import Window

Create User Interface

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) Create new Table
  • 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) Create required columns for Data Import table
  • 5 Mandatory columns: I_ErrorMsg, I_IsImported,Processing, Processed, ID column of target table - in this case, M_AttributeValue_ID -- (1)
    • Set all columns in this table not mandatory, except 2 columns --> Table's ID column and I_IsImported
  • 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.
I attrvalue columns.jpg
3) Create Import Table in Database
  • On any record in Column Tab, Click on Synchronize Column button.
  • On the first time, as table is not yet crated, it will create new table and all the columns.
  • Note that if the table created, this button will only alter the current column.
  • Drop of IsImported column in the database table, since it only allow 'Y' and 'N'. (later, we will use 'E' as well)
ALTER TABLE adempiere.i_attributevalue
  DROP CONSTRAINT i_attributevalue_i_isimported_check;
4) Create Interface and Model Classes for new table
  • In your Eclipse project, run GenerateModel class to create Interface and Model for this table. These models will be useful when we refer to data in this table in Business Logic.
  • In our case, we are creating I_I_AttributeValue.java and X_I_AttributeValue.java. Note that our Entity Type is 'RJC', so the arguments are,
C:\<target folder>\
org.compiere.model
'RJC'
'I_AttributeValue'
  • Deploy this 2 files to org.compiere.model package.
5) Create new Window
  • Login as System, go to window "Window, Tab & Field"
  • Create new Window = "Import Attribute Value"
Import attributevalue window.jpg
  • Create new Tab = "Attribute Value". Note that, for ease of use, Data Import window will always have only 1 tab.
  • Refer this tab to table "I_AttributeValue", created previously.
Import attributevalue tab.jpg
  • At the bottom of this tab, click on "Create Fields" button to create fields from columns in table I_AttributeValue
  • In Field Sequence tab, arrange the field order for layout. The recommended layout is,
    • Top part: Is Imported checkbox, reference ID from target table, Error Messages
    • Bottom part: Process Button, Is Processed CheckBox
    • Lookup Name fields same line as its ID fields. For example, in this case, Attribute Name (AttributeName) is next to Attribute (M_Attribute_ID).
Import attributevalue fields.jpg
6) Register new window to Menu
  • Login as System, got to window "Menu"
  • Create new Menu Item = "Import Attribute Value"
Import attributevalue menu.jpg

Now if you login again as admin user, you should be able to see the new window. You can test creating new records and save it.

Next step, we will look at how to create ImportAttributeValue Process, so that clicking at Process Now button will validate and import data into target table.

Create Business Logic

All Process Class for Data Import windows are in package org.compiere.process, all classes start with Import<XXX>. Now we want to create our own ImportAttributeValue.java Process.

1) Create JAVA process

You can see example from package org.compiere.process, or use the example provided here File:ImportAttributeValue.java. Although some Import Process as you can find from the package are relatively complex, they share the same pattern with our simple example. Here is what it does,

  1. protected void prepare()
    • Get the input parameter, in most cases are Client and/or Organization, Delete Old Imported?, Is Validate Only?
  2. protected String doIt()
    • If user select Delete Old Imported, then delete old imported records from previous time.
    • Reset importing records ready to start validation (set IsImported = 'N')
    • Data Validation and Data Lookup
      • Most of the logic goes here to ensure data integrity before the real import take place.
      • Data Validation can be as complex as we wanted, i.e., check whether the record exist, check duplication, etc. For invalid record, add Error Message (and set IsImported = 'E')
      • Data Lookup can be as automated as we want, i.e., getting ID of Attribute from Name, getting the default value, etc.
    • For valid data (IsImported = 'E'), do the real import to target table(s)
      • Then set Is Imported = true, Processed = true
    • And finally, return the number of successful / unsuccessful import
2) Create new Process in ADempiere
  • Login as System, got to window "Report & Process"
  • Create new Process = "Import_AttributeValue"
  • Set Classname = "org.compiere.process.ImportAttributeValue"
Import attributevalue process.jpg
  • In Parameter tab, set parameters based on what are required. In this case, Client , Delete Old Imported? and Is Validate Only?
  • Note that, we can use "Copy from Report and Process" button in the first tab, in case, the same already exists.
Import attributevalue parameter.jpg
3) Register this new process to "Process Now" button.
  • Login as System, go to window "Table and Column"
  • Open the newly crated Table "I_AttributeValue"
  • On column "Processing", set Process = "Import_AttributeValue"
Import attributevalue processingbutton.jpg
That's it!

Now the new Import Attribute Value window is ready to use along side with other Data Import Windows. And we can use it just like others.

Enjoy!
Import attributevalue.jpg
Note.gif Note:
  • To package it to use in another system, use 2Pack to packout as Application and Module. It will pack everything except Java class that need to deploy separately.
  • It is recommended to use System Administrator to create Import Loader Format, and use Client's user to do the data import.