Range based lookup

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.



Sponsoring: Walking Tree Consultancy Services Pvt. Ltd.

Concept: Walking Tree Consultancy Services Pvt. Ltd.

Implementation: Walking Tree Consultancy Services Pvt. Ltd.

Testing: Walking Tree Consultancy Services Pvt. Ltd.

Documentation: Walking Tree Consultancy Services Pvt. Ltd.


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


The main purpose of this range based in general Look up ,user to filter data with easy. If we give the date/period/year/number etc range and search for records ,system shows records between that two dates/periods/years/numbers etc. 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. 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


2.Drop existing view and create the with adding IsRange 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.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;

3. Regenearte Models for AD_Column table

4. 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;  

5. 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;

6. 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();
       // 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());

7. 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 window. ‘Range’ Check box is selected for both the columns.


Design Considerations





Functional Requirements

Functional team

  • Volunteers for analyzing:
    • --John Agudelo 02:46, 25 June 2013 (UTC)- Functional Review
  • Result of analysis:

Result of review by --John Agudelo 02:46, 25 June 2013 (UTC)

  • Login with User(System/System). [1]
  • Login with System Administrator role.
  • Go to Table and Column Window.
  • Select purchase order window.
  • Go to Column Tab.
  • Select 'Date Ordered column
  • Select Selection Column
  • Select Range field = true.
  • Now Login with GardenAdmin role.
  • Go to Purchase Order Window.
  • Click on LookUp Toolbar.
  • Testing with 4 scenarios and the result are ok
  • Conclusion: Accepted

User roles & profiles

Business process definition

User stories

Functional requirements based on business processes

User Interface Mockups

Acceptance criteria

QA and test cases

Scenario 1 : Range field Implementation In Look up Window


1.Login with User(System/System).
2.Login with System Administrator role.
3.Go to Table and Column Window.
4.Select purchase order window.
5.Go to Column Tab.
6.Select 'Date Ordered column '.
7.Select Selection Column and Range filed as True .
8.Login with User(GardenAdmin/GardenAdmin).
9.Login with GardenAdmin role.
10.Go to Purchase Order Window.
11.Click on LookUp Toolbar. 	

Expected Result:

(after steps 4) :C_Order table ' should be select.
(after steps 6) :'Date Ordered column' should be select.
(after steps 7) :Selection Column and Range filed should be True.
(after steps 8) :Login should be successfully.
(after steps 9) :GardenAdmin role should be show.
(after steps 10):Purchase Order Window should be open.
(after steps 11):Look Up Window 'Date Ordered should be show date range.

Scenario 2 :System Should Show Records between two DateAndTime Records


1.Login with User(GardenAdmin/GardenAdmin).
2.Login with Garden Admin role.
3.Go to Purchase Order Window.
4.Click on Lookup Record toolbar.
5.Select 'Date Ordered' 
6.Click on Ok button.

Expected Result:

(after steps 3): Purchase Order Window should be display.
(after steps 4): LookUp Record Window should be display.
(after steps 5): 'Date Ordered'should be select .
(after steps 6): All record between mention date should be show. 

Scenario 3 :System Should Show the records after the First value while second value is null in Date Range, Enter From Value and give To value null of Date.


1.Login with User.
2.Login with Garden Admin role.
3.Go to Purchase Order Window.
4.Click on Lookup Record Toolbar.
5.Select Date Ordered.
6.Click on Ok button. 	

Expected Result:

(after step 1): Login should be successfully.
(after step 2):HR Manager role should be show.
(after step 3):Purchase Order window should be display.
(after step 4):Lookup Record window should be display.
(after step 5):Date Ordered should be select.
(after step 6):System should show the record from mention Date.

Scenario 4 :System Should Show the records before the Second value while first value is null in DateRange

Same like Above steps

Development infrastructure

Technical Requirements

  • Adempiere 360 or more

Technical team

  • Volunteers for analyzing:
  • Result of analysis:

Data Requirements

Non-Functional Requirements

Open Discussion Items

Closed Discussion Items