Datasource Query Manipulation

When adding datasources to a form, you can join datasources to each other, setting join types. Often there is a need to perform more complex queries. When it comes to performance with complex query manipulation it is advisable to use a view, and use the view as your datasource. However, when you just need to filter, sort, or have dynamic query requirements, changing the query used by your form is often necessary.

see also: Expressions in query ranges

Putting It In The Right Place

As usual, it is up for discussion where the best place is to put your code. In these examples we will show you the most often used structure as per standard Axapta. When accepting filters or other parameters from outside your form, retrieve the values in the "Init()" method of your form. Manipulating the query itself we will be doing in the "Init()" method of the relevant datasource.

Example

In this example we will create a form with a search field and button at the top, to filter on item types.

Creating The Form

Create a new form, and add the InventTable as a datasource. Set the design property "Columns" to 1 (standard is 2) and create 2 groups on it. In the first group we will put our combobox field, set its property "EnumType" to "ItemType". On the second group add a grid and put some inventtable fields on it (make sure ItemId and ItemType are on the grid).

Adding A Range To The Datasource Query

In the "Classdeclaration" of the form we will keep the range on the itemtype to be able manipulate its value any time we want. To do so, add a new line in the "Classdeclaration":

<xpp>

     QueryBuildRange    mItemTypeRange;

</xpp>

Next we overwrite the "Init()" method of the InventTable datasource. First, we need to retrieve our query datasource, then we can put a range on it. We have only 1 datasource so it is the first one. Several methods exist to get the correct datasource (on datasourcename, on tablename - be careful since you may have the same table twice on the form with a different datasource name). That range we store in our newly created form member "mItemTypeRange".

<xpp>

     public void init()
     {
         QueryBuildDataSource    qbds;
         ;
         super();
         qbds = this.query().dataSourceNo(1);
         mItemTypeRange = qbds.addRange(fieldNum(InventTable,ItemType));
     }

</xpp>

Now we have a range on our datasource, but there is no value. Opening the form at this point will result in seeing all items.

Manipulating The Range

On the combobox we added (with enumtype "ItemType") we will overwrite the method "Modified". First we'll retrieve the value of the selected item type. There are several ways to do this (using "this.valueStr()" or "this.selection()") but we choose to use "this.getEditText()" here. This will allow you to blank out the value to clear your range. In other circumstances you would prefer to use valueStr(). We just put the getEditText() in our range's value, no need to convert since it is already a string. And to finish it off, we execute the query so our new range value will take effect.

<xpp>

   public boolean modified()
   {
       ;
       mItemTypeRange.value(this.getEditText());
       InventTable_DS.executeQuery();
       return true;
   }

</xpp>

Other Query Manipulations

Besides addRange() on our query build datasource, there are several other interesting methods you should look into:

    orderMode         : accepts enum of type OrderMode, useful to "group by"
    addSortField      : to sort on a certain field, multiple sorts possible,
                        when using "group by" this sets the "group by" field.
    addSelectionField : add fields to the select statement; to be used with
                        ordermode "group by" to get averages/max/min/... values
                        of fields

Example

One simple form that displays the SalesTable and provides a filter for the customer name. Typing in *Blink* will filter the sales table for orders from the "Blink and Blend Gmbh" (Demo Data).

The exmple is implemented as stated above. The sales table is linked with the CustTable in the init method where the range is created. A form with a StringEditField for the customer name to filter. The modified method sets the range and calls execute query.

Here is the XPO

File:Form SalesFilterForm.xpo

Summary

This was an easy example to show you how to manipulate queries of datasources on forms. However, there are several ways to filter and select records without changing the query like we do here. For more examples, see the example in Lookup Form.