Filtering a DataSet from Code behind

Enterprise portal allows filtering the list pages using the Filter control available on the EP Grid. The Filter control provides a UI to select the field, criteria and the values you want to filter the grid. It is also possible to add multiple conditions.

But there may be scenarios where you want to use your own controls to filter the grid or the underlying DataSet. The EP framework provides the following classes to do this –

- FilterObject

- ConditionType

Let’s look at how we can do the filtering from code behind.

1. Add a Filter to list only the Customer whose name that starts with “Light”

2. Using Between operator – Between operator is a special case. There are two ways to use the Between operator -

a. Using – value..value

b. Using – valueCollection class

3. How do I use more than one condition -

4. Filter on a date – which format should a date be in? Is there a single format which can always be used?

When using the Filter control available in EP Grid, the date will be in user regional settings. But when using from the code behind, the IIS server’s regional settings will be applicable. So if the regional settings on the IIS are ‘MM/DD/YYYY’ the date value must be specified in ‘MM/DD/YYYY’ format.

Comments

• Anonymous
  November 17, 2008
  PingBack from http://www.tmao.info/filtering-a-dataset-from-code-behind/

• Anonymous
  November 17, 2008
  Enterprise portal allows filtering the list pages using the Filter control available on the EP Grid.

• Anonymous
  August 06, 2009
  This is really great stuff, but can't get it to work on an AxLookup.  This code doesn't end up doing anything, as in the filter does not show up in the lookup popup window.  I've debugged and everything appears to be fine but it just won't show the filter in the lookup. Alan void a_Lookup(object sender, AxLookupEventArgs e) {    // The underlying control in this case is the lookup control    AxLookup lookup = e.LookupControl;    // Get the edit value of the cell    String lookupString = tDesc.Text;    DataSetView dsv = lookup.LookupDataSet.DataSetViews[0];    Microsoft.Dynamics.Framework.Data.Ax.filterObject flt = new filterObject();    flt.name = "DVCAXLookupBoundFieldCustomFilter";    Microsoft.Dynamics.Framework.Data.Ax.conditionType myConditionType1 = new conditionType();    myConditionType1.@operator = Microsoft.Dynamics.Framework.Data.Ax.operatorType.startswith;    myConditionType1.status = Microsoft.Dynamics.Framework.Data.Ax.conditionStatus.open;    myConditionType1.value = lookupString;    myConditionType1.attribute = DataFieldLookUpDesc;    flt.conditionCollection.Add(myConditionType1);    dsv.UserFilter.Add(flt); }

• Anonymous
  August 13, 2010
  I'm trying to achieve the same result that AlanFlanders is after. (See above code sample regarding AxLookup). I'd like to filter the lookup contents using the OnLookup event handler. I've applied the same code to my lookup handler, and yet the data is not filtered. Any help on this would be greatly appreciated. Thanks.

• Anonymous
  October 20, 2010
  Hi, When I add a filter this way (userfilter, status open) it will filter the data so the effect is ok. However when I expand the filter control it doesn't show the filter?? This is a problem because the user has to be able to change the filter value. I first added the filter to the page load event (in a !isPostBack block) and after that on the page init event. Help anyone?! the code:            DataSetView dsv = this.QRSQuickOrderList_ds.GetDataSet().DataSetViews[0];            dsv.UserFilter.ClearOpenFilter();            filterObject flt = new filterObject();            flt.name = "myFilter";            conditionType con = new conditionType();            con.@operator = operatorType.like;            con.status = conditionStatus.open;            con.value = "test";            con.attribute = "ItemName";            flt.conditionCollection.Add(con);                        dsv.UserFilter.Add(flt);

• Anonymous
  December 07, 2010
  It looks like the solution that Alan and Micano were looking for can be obtained based on code from Santosh Kumar Paruvella's Blog:  "Usage of (OR) condition in Ax Query’s of Ax Client and EP" paruvellas.wordpress.com/.../3 Hopes that helps others in need of a similar solution.

• Anonymous
  February 22, 2012
  I have the same problem, added filter work, but i cannot see him. Dil you solve this problem? Thanks

• Anonymous
  February 05, 2014
  This works very well except when I try to use a Dimension as the attribute. I can't seem to find the correct syntax

---

DataSetView Dsv = this.DSlmb_InventJournalTrans_InventDim.GetDataSet().DataSetViews[0]; Dsv.UserFilter.ClearOpenFilter(); filterObject Flt = new filterObject(); Flt.name = "CustomFilter"; conditionType MyCondition1 = new conditionType(); MyCondition1.@operator = operatorType.eq; MyCondition1.status = conditionStatus.open; MyCondition1.value = TextPDS.Text; MyCondition1.attribute = "Dimension[3]"; Flt.conditionCollection.Add(MyCondition1); Dsv.UserFilter.Add(Flt);

The code above always gives me this error message: "Datasetviewfield 'Dimension[3] is not found in DataSetView InventJournalTrans" I tried Dimension[3], Dimension(3), Dimension_3: Always the same error message Any help would be greatly appreciated