Datasource Query Manipulation
From Axaptapedia
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.
Contents |
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":
QueryBuildRange mItemTypeRange;
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".
public void init()
{
QueryBuildDataSource qbds;
;
super();
qbds = this.query().dataSourceNo(1);
mItemTypeRange = qbds.addRange(fieldNum(InventTable,ItemType));
}
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.
public boolean modified()
{
;
mItemTypeRange.value(this.getEditText());
InventTable_DS.executeQuery();
return true;
}
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
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.
