Date range provision in general lookup window in ADempiere

Problem Statement:

While using ERP application, ease on which user will be able to filter only required data in a window matters lot. To support this, ADempiere has in built look-up window, which allows user to filter based on particular need. This helps user to filter data with ease. But when users want search for transactional documents like sales order, purchase order and invoice, general trend is to search for records in a given date range. But deafult look up window does not support this and user has to go to advanced lookup. Using advanced lookup needs learning curve and time consuming. If deafult lookup window can provide range based lookup then that will be very good feature to have in ERP

Scope of Article:

As part of this article we will cover the changes that we need to make to create range based look-up and how it behaves

Use Case:

We have taken leave request window as an example, where user will filter leave requests based on date range of request from date

Technology :

  • Adempiere 360 or more

Details :

We need customization to get the required behavior. Steps mentioned below, will help you to understand the changes we need to make in system

  1. Add a new column in AD_Column table and recreate AD_Field_V view to include this column
     DROP VIEW AD_Field_V;
    SELECT t.ad_window_id, f.ad_tab_id, f.ad_field_id, tbl.ad_table_id, f.ad_column_id,, f.description,, f.isdisplayed, f.displaylogic, f.displaylength, f.seqno, f.sortno, f.issameline, f.isheading, f.isfieldonly, f.isreadonly, f.isencrypted AS isencryptedfield, f.obscuretype, c.columnname, c.columnsql, c.fieldlength, c.vformat, COALESCE(f.defaultvalue, c.defaultvalue) AS defaultvalue, c.iskey, c.isparent, COALESCE(f.ismandatory, c.ismandatory) AS ismandatory, c.isidentifier, c.istranslated, COALESCE(f.ad_reference_value_id, c.ad_reference_value_id) AS ad_reference_value_id, c.callout, COALESCE(f.ad_reference_id, c.ad_reference_id) AS ad_reference_id, COALESCE(f.ad_val_rule_id, c.ad_val_rule_id) AS ad_val_rule_id, c.ad_process_id, c.isalwaysupdateable, c.readonlylogic, c.mandatorylogic, c.isupdateable, c.isencrypted AS isencryptedcolumn, c.isselectioncolumn, c.isrange,tbl.tablename, c.valuemin, c.valuemax, AS fieldgroup, vr.code AS validationcode, f.included_tab_id, fg.fieldgrouptype, fg.iscollapsedbydefault, COALESCE(f.infofactoryclass, c.infofactoryclass) AS infofactoryclass, c.isautocomplete
    FROM ad_field f
    JOIN ad_tab t ON f.ad_tab_id = t.ad_tab_id
    LEFT JOIN ad_fieldgroup fg ON f.ad_fieldgroup_id = fg.ad_fieldgroup_id
    LEFT JOIN ad_column c ON f.ad_column_id = c.ad_column_id
    JOIN ad_table tbl ON c.ad_table_id = tbl.ad_table_id
    JOIN ad_reference r ON c.ad_reference_id = r.ad_reference_id
    LEFT JOIN ad_val_rule vr ON vr.ad_val_rule_id = COALESCE(f.ad_val_rule_id, c.ad_val_rule_id)
    WHERE f.isactive = 'Y'::bpchar AND c.isactive = 'Y'::bpchar;
  2. Regenearte Models for  AD_Column table
  3. In add new method to define whether seleection column is range based or not
      /**           Selection column in range based or not        */  
         public boolean isRange()  
           return m_vo.IsRange;  
  4. In initiate newly added column
         *     Clone Field.
         *    @param Ctx ctx
         *    @param windowNo window no
         *    @param tabNo tab no
         *    @param ad_Window_ID window id
         *    @param ad_Tab_ID tab id
         *    @param TabReadOnly r/o
         *    @return Field or null
        public GridFieldVO clone(Properties Ctx, int windowNo, int tabNo, 
            int ad_Window_ID, int ad_Tab_ID, 
            boolean TabReadOnly)
              clone.IsRange = IsRange;
  5. In make changes in addSelection() and cmd_ok_simple() methods
    private ArrayList<WEditor> m_sEditors2 = new ArrayList<WEditor>();
     * Add Selection Column to first Tab
     * @param mField field
     public void addSelectionColumn(GridField mField)
    if ( mField.isRange() ) {
    Hbox box = new Hbox();
    editor = WebEditorFactory.getEditor(mField, false);
     label = editor.getLabel();
     // The Editor
     WEditor editor2 = WebEditorFactory.getEditor( mField, false);
     // New Field value to be updated to editor
    // label = editor2.getLabel();
     m_sEditors2.add (editor2);
     Label separator = new Label(" - ");
    if (displayLength > 0) // set it back
     private void cmd_ok_Simple()
    if (field.isRange() ){
     WEditor editor2 = (WEditor)m_sEditors2.get(i);
     Object value2 = null;
     Object parsedValue = null;
     Object parsedValue2 = null;
     String infoDisplay_to = null;
     String infoDisplay = null;
     if (editor2 != null)
     value2 = editor2.getValue();
     //GridField field = null;
    if (value != null && value2 != null && value2.toString().length() > 0)
    String ColumnName = editor2.getColumnName();
     log.fine(ColumnName + "=" + value2);
    GridField field = getTargetMField(ColumnName);
     infoDisplay = value.toString();
     parsedValue = parseValue(field, value);
    parsedValue2 = parseValue(field, value2);
     infoDisplay_to = value2.toString();
     if (parsedValue2 == null)
     rangeValidation(wed,editor2, value, value2);
     m_query.addRangeRestriction(ColumnSQL, parsedValue, parsedValue2,ColumnSQL, infoDisplay, infoDisplay_to );
    // Case2 : If in given range filed First value as given and 2nd value is null
     //then get all the records after the First value
     }else if( value!= null && ! value.toString().isEmpty() && value2 == null ){
    String ColumnName = wed.getColumnName();
     rangeValidation(wed,editor2, value, value2);
    m_query.addRestriction(ColumnSQL, MQuery.GREATER_EQUAL, value, ColumnName, wed.getDisplay());
    // Case3 : If in given range filed First value is given as null and 2nd value is given 
     // then get all the records before the second value 
     else if( value== null && value2 != null && ! value2.toString().isEmpty() ){
    String ColumnName = editor2.getColumnName();
     GridField field = getTargetMField(ColumnName);
     ColumnSQL = field.getColumnSQL(false);
     rangeValidation(wed,editor2, value, value2);
     m_query.addRestriction(ColumnSQL, MQuery.LESS_EQUAL, value2, ColumnName, editor2.getDisplay());
  6. With this we are done with customization, now we need to configure this feature for specific columns i.e. From Date and To Date columns in leave request window. ‘Range’ Check box is selected for both the columns LeaveRequestFromDateLeaveRequestToDate
  7. Now login as user and leave request window and click on lookup,For From Date, now we have an option to enter Date rangeLeaveRequestDetails
  8. Leave Requests are filtered with Date Range enteredLeaveRequestFilterData


In this article, we covered steps to enable range based filtration of data through general lookup window of ADempiere. Hope you have enjoyed this article.

Walking Tree promotes Adempiere and we support the users as well as the developers to ensure that the business is able to take complete advantage of Adempiere’s wonderful capability. In case you are looking for a professional assistance then do visit our website to get in touch with us.


Tagged with: , , , , ,
Posted in ADempiere, EagleRP
5 comments on “Date range provision in general lookup window in ADempiere
  1. kamblesaurabh05 says:

    I followed the steps as given by you. But I could not found “rangeFirstEditor” and “rangeValidation” in code. Rest all is not giving any error.
    Please help me regarding this.


  2. Finov says:

    Please help me, give some more explanation about this method ‘rangeFirstEditor’ and ‘rangeValidation’, because I can’t find it. Thanks

  3. ravurisuman says:

    Thanks Adaxa for your comments.

  4. adaxa says:

    Hi Walking Tree
    A long overdue improvement. Thanks

  5. Giri says:

    Good implementation……….

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

We Have Moved Our Blog!

We have moved our blog to our company site. Check out for all latest blogs.

Sencha Select Partner Sencha Training Partner
Xamarin Authorized Partner
Recent Publication
%d bloggers like this: