NewWindow
Contents
Tutorial to create a new Table + Window
- This tutorial teaches you how to create a new Window in the ADempiere application. It works with the Application Dictionary (AD) which resolves the MVC (Model-View-Controller) needs of the table. This allow minimal touching of sourcecode in creating anything new in the system to avoid introducing bugs and ensure low cost maintenance of the system. A later section here will teach you how to extend beyond the normal AD capability.
Creating a new table (DB)
- Firstly we need to create our new table in the database.
- The table must contain the ADempiere default columns (italic)
- This is necessary if you want the Application Dictionary to recognise your table for all its related AD functionality such as AD_Window and AD_PrintFormat.
- Following is an example SQL script to create a table
CREATE TABLE XX_Material ( AD_CLIENT_ID NUMBER(10) NOT NULL, AD_ORG_ID NUMBER(10) NOT NULL, ISACTIVE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL, CREATED DATE DEFAULT SYSDATE NOT NULL, CREATEDBY NUMBER(10) NOT NULL, UPDATED DATE DEFAULT SYSDATE NOT NULL, UPDATEDBY NUMBER(10) NOT NULL, XX_MATERIAL_ID INTEGER NOT NULL, MATNR SMALLINT NOT NULL, COLORNR SMALLINT NOT NULL, NAME VARCHAR(100) NOT NULL, NAMESHORT VARCHAR(100) NOT NULL );
ALTER TABLE XX_Material ADD PRIMARY KEY (XX_MATERIAL_ID );
Activating the new table in the AD
Now we have to make that new table in our database appear in the AD.
- Login as SystemAdmin in ADempiere
- Open Table and Column window
- Create a New record
- Type into DB Table Name, the name of the newly created DB-table i.e XX_Material.
- Click on the button Create Columns from DB.
- This creates for each db-tablecolumn a corresponding AD Column.
- Check the automatic created Columns, especially the ID references (e.g. Number instead of Integer)
- This creates for each db-tablecolumn a corresponding AD Column.
Notes
- You need to be aware of the standard way ADempiere persists records. Every record is expected to have a an ID column whose name ends in _ID, type numeric(10,0).
- This column should be marked as "key column" in the Table Column record.
- ADempiere uses this like an object id. This is what it stores to track which records have been modified by the user, etc.
- If this is a detail file (child table to a parent table such as OrderLine to Order), then there will be an ID field of the master record, which should be marked as "parent link column".
Children Tables
- There may be ways to make exceptions (called "multi-key" in the source), but make your life as a newbie easier, and just follow this pattern.
- "Multi-key" tables in Adempiere are intended for children tables, which are tables that depends on several parent tables (n-n relations in ER-model).
- Multi-key tables don't need a unique "key column" as explained before. They need to reference keys (_ID's) from parent, and every parent key must be marked as "parent link column".
- Please take notice that Adempiere creates the primary/unique constraint for key column, but you need to create manually the multi-key primary constraint (as well as all foreign keys).
Issues with Multi-Key Tables
- Due to the fact that multi-key tables doesn't have a key column, all functionality based on key column won't work, i.e. chat, attachments, record lock, reference to record table in requests, etc.
Understanding the Persistence Layer
This was adapted from a posting by Karsten Thiemann.
- The persistence layer of ADempiere consists (mainly) of the Persistence Object (PO.java) which is an abstract class and defines methods like load(), save(), delete(), get_ID(), ...
- Every Persistent Object (otherwise a table in the database) has to extend PO.java in order to get all the persistence functionality (in some cases you don't need this but it never hurts..)
- To ease the extending, and because of the fact that most needed methods are just set and get methods (the X_* classes are simply POJOs), ADempiere offers a class to generate X_* classes that extend the PO.java for all tables (objects) defined in the application dictionary (AD).
- This is done with org.adempiere.util.GenerateModel.
- Previously org.compiere.util.GenerateModel (before ADempiere 3.3.0)
- Now we generate not only the setter/getter X_* class but also an interface I_* for it to implement.
That is all what you need to have a full functional (in terms of persistency) Persistent Object.
Generate Model
Now you need to create your Persistence Object for your new table in order to access it for CRUD (Create, Read, Update, Delete) and other modelling functions.
- Run the class org.adempiere.util.GenerateModel class. It should create a class X_XX_Material.java. Since this is a generated class, you cannot change this class.
- You can extend it and put some logic in your extension (see example). The name of the extension-class usually starts with an M prefix i.e. MXX_Material.
Running Generate Model
- In the source, there are Eclipse .launch files. But these were created with
Eclipse in the many small projects mode. If you checked out one big project, then the launchers won't show up in your list. [Make them visible], then edit to change the project name and source paths passed.
- When running from the command line, the arguments are
- Output source path
- output package
- table type, e.g. 'U' for User Defined
- (optional) SQL pattern to select table
- Also check that you have the jars in lib/ to the classpath for GenerateModel. Eclipse seems to do this automatically, but from the command line you need a wrapper script.
Extending the model
- Extending PO classes is usefull when you need to do some kind of validation for a screen or tab. For example of the needs for Localization-Brazil project is that there's a Government Federal Inscription number that has to be validated when the user exits the screen. This inscription number is needed in the organization screen and in the business partner screen.
- To handle it on organization screen, a table called LBR_Org_Info was created, with the responsibility of extending the AD_Org_Info table.
- The name of the model (PO class) created by GenerateModel.java is X_LBR_Org_Info. The name for the class that extends that model class has to be stripped of all the underlines, so it looks like MLBROrgInfo, if you do it in a different way, it won't work.
- For an idea of how ADempiere handles the M* classes, take a look at MTable.java class, method getClass(String).
- This means that the table names after their prefixes have to be unique because C_BPartner and XY_BPartner would both result in MBPartner PO classes (so XY_SpecialBPartner would be the way to go).
Example 1: the extended model skeleton
package adempiere.model;
import org.compiere.model.X_XX_Material;
public class MMaterial extends X_XX_Material {
/**
* Create & Load existing Persistent Object
* @param ctx context
* @param XX_Material_ID The unique ID of the object
* @param trxName transaction name
*/
public MMaterial(Properties ctx, int XX_Material_ID, String trxName) {
super(ctx, XX_Material_ID, trxName);
// other initializations here
}
/**
* Create & Load existing Persistent Object.
* @param ctx context
* @param rs optional - load from current result set position (no navigation, not closed)
* if null, a new record is created.
* @param trxName transaction name
*/
public MMaterial(Properties ctx, ResultSet rs, String trxName) {
super(ctx, rs, trxName);
// other initializations here
}
/**
* [OPTIONAL] Called when the object load is complete
* @param success success
*/
protected void loadComplete (boolean success)
{
}
/**
* [OPTIONAL] Called before Save for Pre-Save Operation.
* @param newRecord new record
* @return true if record can be saved
*/
protected boolean beforeSave(boolean newRecord)
{
return true;
}
/**
* [OPTIONAL] Called after Save for Post-Save Operation
* @param newRecord new record
* @param success true if save operation was success
* @return if save was a success
*/
protected boolean afterSave (boolean newRecord, boolean success)
{
return success;
}
/**
* [OPTIONAL] Executed before Delete operation.
* @return true if record can be deleted
*/
protected boolean beforeDelete ()
{
return true;
}
/**
* [OPTIONAL] Executed after Delete operation.
* @param success true if record deleted
* @return true if delete is a success
*/
protected boolean afterDelete (boolean success)
{
return success;
}
/**
* [OPTIONAL] A nice string representation for your object
* @return String representation
*/
public String toString()
{
return super.toString();
}
}
Example 2: a method to get a MMaterial-Object with the given materialno and colorno.
public static MMaterial get(Properties ctx, int materialno, int colorno) {
MMaterial retValue = null;
String sql = "SELECT * FROM XX_Material WHERE MATNR=? AND COLORNR=?";
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement (sql, null);
pstmt.setInt(1, materialno);
pstmt.setInt(2, colorno);
final ResultSet rs = pstmt.executeQuery ();
if (rs.next ()){
retValue = new MMaterial(ctx, rs, null);
}
rs.close ();
pstmt.close ();
pstmt = null;
} catch (SQLException e){
s_log.log(Level.SEVERE, sql, e);
}
try {
if (pstmt != null){
pstmt.close ();
}
pstmt = null;
} catch (SQLException e) {
pstmt = null;
}
return retValue;
}
Example 3: Adding caching support to optimize your previous get method.
/** Cache: MaterialNo_ColorNo -> MMaterial, with initialCapacity=10 and expireMinutes=default value */
private static CCache<String, MMaterial> s_cache = new CCache<String, MMaterial>("XX_Material", 10);
/**
* Gets the MMaterial for materialno and colorno.
* The objects are cached.
* @param ctx context
* @param materialno
* @param colorno
* @return MMaterial or null if not found
*/
public static MMaterial get(Properties ctx, int materialno, int colorno) {
// check cache
String key = "" + materialno + "_" + colorno;
MMaterial retValue = s_cache.get(key);
if (retValue != null)
return retValue;
String sql = "SELECT * FROM XX_Material WHERE MATNR=? AND COLORNR=?";
PreparedStatement pstmt = null;
try {
// Create the prepared statement
// Note: when you are querying objects that will be cached it is recommended
// to query "outside transaction" (trxName=null),
// That's why this kind of static getters does not have trxName parameter
pstmt = DB.prepareStatement (sql, null);
// Fill the parameters
pstmt.setInt(1, materialno);
pstmt.setInt(2, colorno);
// Execute the query
final ResultSet rs = pstmt.executeQuery ();
// Get the first result if exist
if (rs.next()){
retValue = new MMaterial(ctx, rs, null);
}
// To protect the integrity of your cache check if you get more than one result for your query.
// This situation should be avoided !
// Another quick way to avoid this is using database unique constraints.
if (rs.next()) {
// TODO: do something here: log a warning, throw an exception, set retValue = null etc
}
// Don't forget to close the ResultSet and the PreparedStatement
rs.close ();
pstmt.close ();
pstmt = null;
} catch (SQLException e) {
// handle the SQLException
s_log.log(Level.SEVERE, sql, e);
}
// Don't forget to close the PreparedStatement (again!)
// This situation happens when an exception was thrown in the previous try/catch block
try {
if (pstmt != null){
pstmt.close ();
}
pstmt = null;
} catch (SQLException e) {
pstmt = null;
}
// Add the result (if any) to cache
if (retValue != null)
s_cache.put(key, retValue);
// Finally we are returning the resulting MMaterial object
return retValue;
}
Create an ADempiere window
Create a new entry in the window Window Tab & Field with the name „XX_Material“.
In the tab Tab create a new entry with the name „Material“ and select „XX_Material_XX_Material“ as it's table.
Use the button Create Fields to create fields for all columns of the table.
In the tab Field Sequence you can choose the sequence of the fields in the new window and in the tab Field you can select some view-related attributes. Set the read-only flag for „XX_Material_ID“ (the id should be unchangeble).
To change the display properties (read-only, displayed, mandatory) of some fields see Display properties of columns and fields for more details.
The Window type should be "Maintain". If the type is "Transaction", you'll need a "processed" field in the table.
Create a Reference and Zoom Target
If you want to have a drop-down-box with values of the new table in other windows (like the drop-downs for bpartner-locations...) you need to create a Reference for the new table.
Create a new entry in the window Reference with the name „XX_Material“ and select „Table Validation“ as Validation Type.
In the tab Table Validation select „XX_Material_XX_Material“ as table, „XX_Material_ID as Key Column and „Name“ as Display Column. The last one selects the column, that is used for the drop-down values.
Remark: Close all windows (Reference and Window Tab & field) before you proceed. ADempiere does not update the selectable references if you don't...
Now you can create a field with the name „Material“ in a ADempiere window of your choice and select „XX_Material_ID_Material“ as it's Column. After this, you will see a drop-down-box with all values of the name-column in that window.
To let the user zoom to your new window from the dropdown (rightclick and select zoom) you have to go back to the window 'Table and Column' and select your new table. From the 'window' drop down field select the new created window „XX_Material“. You may want to have a different window for purchase transactions (see table C_Order). That's it - if the zoom doesn't work you have to clear the cache or restart the client.
You are done!
New Quick Functionality
Now, you don't need to know SQL, or have a SQL tool to create a new table.
In 3.1.5 was added a functionality to "Copy Columns From Other Table" in Table window.
Then you can replace the first two steps by:
- Create new table (Adempiere)
- Choose a similar table and run the process "Copy Columns From Table" with the chosen table
- Navigate to columns tab and make the necessary modifications
- Push "Synchronize columns" button and the table will be created in database
It's good that in a later phase you optimize the database adding the corresponding foreign keys.
TIP: A good table to copy from is M_FreightCategory - it has the 12 commonest columns used in Adempiere tables.
Tips
- The creating Element is Mandatory: You can use Elements from owned or ADempiere Dictionary.
- The Menu Name is derived from the Window Name.
- The Field info is derived from Element.
- Synchronize Terminology.
- What about Explicit and Implicit Parents?
- If the Child Table has one Parent: Link automatically; no action is required.
- If the Child Table has two or no Parents: Explicit Link is required (Tab: Link Column)
- If you are not going to need beforeSave and afterSave methods generating model is not necessary.
- Some things don't work without the generated model X_ class, like change log
- Use prefixes of 3 or more letters for customizations (e.g. LBR_) - this is a recommended practice to avoid collisions of classes - Adempiere cut the prefix in the model class when < 3 letters - when >= 3 letters the prefix is preserved (e.g. X_LBR_Org_Info)
- When naming your columns pay attention to case. The id columns should end in uppercase _ID.
- Do not use isapproved in custom tables unless you are implementing documents (Order, Invoice etc.)
- Virtual column
- sf.net message
- Not necessary to include WHERE clause as part of Virtual column SQL statement.
SELECT IsActive, XX_Material_ID, MatNr, ColorNr, Name , (COLORNR + MATNR) AS T_Integer , AD_Client_ID, AD_Org_ID, Created, CreatedBy, Updated, UpdatedBy FROM XX_Material WHERE XX_Material.AD_Client_ID IN (0,1000000) AND XX_Material.AD_Org_ID IN(0,1000000) AND XX_Material.XX_Material_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 1000007 AND AD_User_ID <> 100 AND IsActive = 'Y' )
It's a SELECT [all fields comma seperated] from TABLENAME WHERE [Client/Org][PrivateAccess]. If we have a virtual column the SQL statement (in this case (COLORNR + MATNR)) is just included in the comma seperated field list.
See Also
- For newbies, here is a simplified way to Creating New Window.
- ModelValidator for doing more customisation with your Model
- Deep end stuff at wikiversity by Carlos Ruiz
