AX – D365FO – Add dropdown filters that show results into a form grid but disconnected from the form datasource.

This is the result we want to obtain, 2 dropdown filters that show results into a form grid but disconnected from the form datasource.

When the year is selected and month no then the form will show data from 01/01/year and 31/12/year.

When the year and month are both selected then the form will show data from first day of the month of the uar and the last day of the selected year

Go to Form, Design, Grid container.

Add new Combobox control

Set Enum property to “MonthOfYear” enum

Now add a new IntEdit Control to add the extended data type (The EDT is custom and linke to a a custom table containing a list of years. to see how to create the relation see article : https://d365ffo.com/2021/07/13/ax-d365fo-add-table-relation-to-an-extended-data-type-edt

Raname the 2 controls

This is the result we have obtained

Put this code inside both modified methods

public boolean modified()

{

   boolean ret;

   ret = super();

   // A new filter value has been selected; refresh the form using that filter value.

   XXX.executeQuery();

   return ret;

}

Put this code inside executeQuery of datasource

public void executeQuery()

{

   // Get the filter value from the filter control.

 

   date firstYearOfMonth;

   str monthStr;

   str yearStr;

   MonthsOfYear monthEnum;

   int monthEnumToInt;

   TransDate tmpDate;

   TransDate dateInMonth;

   TransDate firstDateOfMonth;

   TransDate endDateOfMonth;

   QueryBuildDataSource ds;

   monthStr = MonthsList.valueStr();

   yearStr = YearsList.valueStr();

   ds = this.query().dataSourceTable(tablenum(XXX));

   ds.clearRange(fieldNum(XXX,calcdate));

 

   if (yearStr != "")

   {

 

       if (monthStr != "")

       {

           monthEnum = str2enum(monthEnum,monthStr);

           monthEnumToInt = enum2int(monthEnum);

 

           tmpDate = str2Date("15/" + int2str(monthEnumToInt) + "/" + yearStr, 123);

 

 

           dateInMonth=tmpDate;

 

           firstDateOfMonth = DateStartMth(dateInMonth);

           dateInMonth=tmpDate;

           endDateOfMonth= endmth(dateInMonth);

       }

       else

       {

           firstDateOfMonth = str2Date("01/01/" + yearStr, 123);

           endDateOfMonth =  str2Date("31/12/" + yearStr, 123);

       }

 

 

       ds.addRange(fieldNum(XXX, calcdate)).value(queryRange(firstDateOfMonth, endDateOfMonth));

   }

   /*else

   {

       ds.clearRange(fieldNum(XXX,calcdate));

   }*/

 

 

 

   super();

}

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 )

Connecting to %s