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
    ALTER TABLE AD_Column ADD COLUMN IsRange CHARACTER(1) DEFAULT 'N'::bpchar NOT NULL;
     DROP VIEW AD_Field_V;
     CREATE OR REPLACE VIEW AD_Field_V AS
    SELECT t.ad_window_id, f.ad_tab_id, f.ad_field_id, tbl.ad_table_id, f.ad_column_id, f.name, f.description, f.help, 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, fg.name 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 GridField.java 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 GridFieldVO.java 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 FindWindow.java 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();
     box.appendChild(editor.getComponent());
    
     rangeFirstEditor.add(mField.getColumnName());
     // The Editor
     WEditor editor2 = WebEditorFactory.getEditor( mField, false);
     // New Field value to be updated to editor
     editor2.setMandatory(false);
     editor2.setReadWrite(true);
     editor2.dynamicDisplay();
     //
    // label = editor2.getLabel();
     m_sEditors2.add (editor2);
     Label separator = new Label(" - ");
     box.appendChild(separator);
     box.appendChild(editor2.getComponent());
    if (displayLength > 0) // set it back
     mField.setDisplayLength(displayLength);
     //
     panel.appendChild(LayoutUtils.makeRightAlign(label));
     panel.appendChild(box);
    
     }
     ...............
    }
    
     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)
     continue;
     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

Summary:

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.

References:

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.

    Thanks

  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
    adaxa

  5. Giri says:

    Good implementation……….

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

We Have Moved Our Blog!

We have moved our blog to our company site. Check out https://walkingtree.tech/index.php/blog for all latest blogs.

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