Latest revision as of 14:24, 23 November 2015

Return to Tutorials

Tutorial to create a new Table and 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. For simple functionality, the new table and window can be created without any changes to the source code. This avoids introducing bugs and ensures low cost maintenance of the system. A later section here will teach you how to extend beyond the normal AD capability to enhance the functionality as you require.

Creating the New Table

To create the table, we add a record to the {{#if: Table and Column|Table and Column|TableandColumn }} Window. The table is synchronized with the database by a process that can be run from any column in the Column tab.

1. Login as the System Administrator in ADempiere
2. Open Table and Column window
3. Create a New record
4. In the "DB Table Name" field type the name of the table that will appear in the database, for example XX_Material. (See Table Prefix for naming conventions.)
5. Fill in other relevant information about the table and save the record. For ease, set the data access level to all.

Creating Table Columns

(Since 3.8.0 update 2) When you save the table record, the necessary default columns are added to the table. Switch to the Column tab to see them.

(Since 3.1.5) Columns can be copied from another Table using the button "Copy Columns From Other Table" in Table window. To do so, click the button "Copy Columns From Table", choose a suitable table and run the process.

Note: A good table to copy from is M_FreightCategory - it has the 12 most common columns used in ADempiere tables.

If the table already exists in the the database, you can import the columns by clicking on the button Create Columns from DB. This process will create an entry in the AD_Column table for each column in the database table.

Navigate to columns tab and make the necessary modifications to the existing columns and add any other new columns you require. Check the automatically created Columns, especially the ID references (e.g. Number instead of Integer), for correctness.

Adding Columns Manually

Since common column definitions are often repeated in multiple tables, ADempiere uses an "Element" as the definition of the Column. An Element is mandatory for each new column. You can reuse Elements from the ADempiere Dictionary or create your own.

Once you have Elements defined for all the columns you'd like to add, you can add these columns as new records in the Table and Column window, Column Tab. Simply create a new record and enter an Element. The main fields in the record will be generated by the Element information. You can then update the other fields in the Column definition as required.

When you are finished, save the record and click the button, "Synchronize Columns" to add the new column to the database.

Synchronize the Table and Columns

This is an important step. Unless the columns were created from the DB, when a new table and/or column is created, it exists in the model but not in the database. Synchronization of the model with the database is required. This can be done from any Column record in the Table and Column window. Simply click the "Synchronize columns" button to create the table and columns in the database.

Other Tips

• Synchronize Terminology: This is a process that can be run which will overwrite changes you have made to window tab/field names with changes in the element if the records are centrally maintained and based on dictionary elements.
• 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.)
• See Virtual_Columns to add columns not included in the underlying table.
• In a later phase, you may have to optimize the database by adding the additional foreign keys or indexes.

Create an ADempiere Window based on the Table

• 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.

Note: The Window type should be "Maintain". If the type is "Transaction", you'll need a "processed" field in the table.

Add the Window to the Menu

Once the window is created, add it to a menu to it can be used.

• Open the System Admin{{#if: General Rules |  » General Rules }}{{#if: System Rules |  » System Rules }}{{#if: |  »  }}{{#if: |  »  }} {{#if: |{{{2}}}|Menu }} Window.
• Add a record and point the entry to the newly created window.
• Save the record and drag and drop it in the tree in a suitable location.

Give Users Access to the Window

At this point, no users will have access to the newly created window or menu item. Access can be granted manually to each Role or a process can grant access to multiple Roles.

To grant access to all Roles, run the process "Role Access Update" in the System Admin{{#if: General Rules | →General Rules }}{{#if: Security | →Security }}{{#if: | → }}.

Test the New Window

Log in as GardenAdmin and test the new window. You should be able to add records, generate a report, export data and use all the basic functionality, again, with no software changes.

Advanced Topics

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.

• Go go "Reference" item in the Application Dictionary Menu

• 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.

See An Example of Reference Table with Dynamic Validation

Record Identifiers or Keys

Note: This section gets into the nuts and bolts of tables and the underlying models. As a new developer, make your life easy by using single key tables. You need to understand how these keys work. For basic functionality of entering, editing and reporting, you do not need to generate models or deal with the software at all. Its only if you need to extend the functionality that you may have to develop software.

• You need to be aware of the standard way ADempiere identifies (or persists) records as unique. 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.
  • The name of the key column needs to be tablename_ID > When adding tables from Adempiere using the "Sync columns" function, this may not be obvious. (Since 3.8.0_2, this column is added when the table entry is first saved.)
  • Not including an ID is possible. Inserting records may work great, but updates will give an error: org.postgresql.util.PSQLException: ERROR: column "tablename_id" does not exist; State=42703; ErrorCode=0.
• 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".

Multi-Key Tables

There may be ways to make exceptions (called "multi-key" in the source), but make your life as a newbie easier, and use a single key field.

• "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 don'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.

• An alternative for generating the X_*/M_* classes is shown here.

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. MMaterial. If the prefix of the new table has 2 or less letters it is removed. To avoid conflicts with other model classes it is recommended to use a prefix of 3 or more letters. XYZ_Material would result in MXYZMaterial.

See this article for more in depth information on Model classes lookups.

See GenerateModel to get further information.

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

1. Output source path
2. output package
3. table type, e.g. 'U' for User Defined
4. (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).
• if the first prefix of the table has 2 or less letters it is stripped from the class name. 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). To avoid this problem it is recommended to use a prefix of 3 or more letters.

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;
}

See Also

• How to Create Screen/Window in ADempiere
• Reference Table with Dynamic Validation and other ADempiere Rapid Development hints.
• ModelValidator for doing more customisation with your Model
• Deep end stuff at wikiversity by Carlos Ruiz

Creating a New Table in the Database Directly

Note: The table can be automatically created by the application. This step is not required and is presented for information only.

• 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   	NUMERIC(10)     NOT NULL,
 AD_ORG_ID      	NUMERIC(10)     NOT NULL,
 ISACTIVE       	CHAR(1)         DEFAULT 'Y' NOT NULL,
 CREATED        	DATE            DEFAULT NOW() NOT NULL,
 CREATEDBY      	NUMERIC(10)     NOT NULL,
 UPDATED        	DATE            DEFAULT NOW() NOT NULL,
 UPDATEDBY      	NUMERIC(10)     NOT NULL,
 XX_MATERIAL_ID     	NUMERIC(10,0) 	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 );