Filter a date column using a string edit field

Dynamics 365 for Finance and Operations/AX Developer Forum
#1

Hello All,

I am working in 2012 R2.

I am having 5 columns in my grid and I have a string edit field and a button in my form. I have a date column in my grid. All i want is to filter the form respective to the date field when i enter a date value in the string edit field and click the button.

This is the code I used for filtering my columns.

public void executeQuery()
{

qbr.value(strFmt(’(%1 LIKE “%7”) || (%2 LIKE “%7”) || (%3 LIKE “%7”) || (%4 LIKE “%7”) || (%5 == %8) || (%6 LIKE “%7”)’,
fieldStr(LearningCSVTable,CustomerID),fieldStr(LearningCSVTable,CustomerName),fieldStr(LearningCSVTable,Item),fieldStr(LearningCSVTable,VendorName),fieldStr(LearningCSVTable,OrderDate),fieldStr(LearningCSVTable,Status),’’+StringEdit.valueStr()+’’,date2StrXpp(str2date(stringedit.valueStr(),123))));

super();
}

Thanks in Advance!..

#2

Sorry I have missed this.

I have added range in init method

public void init()
{
LearningCSVTable learningCSVTablefin;

super();

qbr = this.query().dataSourceName(‘LearningCSVTable’).addRange(fieldNum(LearningCSVTable,CustomerID));

}

Thanks

#3

Hi Wekey,
If you want to filter grid with respective date which is entered in stringedit field (for that you have to set ExtendedDataType property for date) then why you are adding range with the Customer Id. Instead of CustomerId you have to add range of OrderDate filed.

Please refer below code, It may help you.

  1. init method of LearningCSVTable datasource
    public void init()
    {
    LearningCSVTable learningCSVTablefin;
    ;
    super();

qbr = this.query().dataSourceName(‘LearningCSVTable’).addRange(fieldNum(LearningCSVTable,OrderDate));
}

  1. executeQuery method of LearningCSVTable datasource
    public void executeQuery()
    {
    qbr.value(queryvalue(strinfeditfieldname.text()));
    super();
    }

3)modified method of stringedit filed
public boolean modified()
{
boolean ret;
;

ret = super();

LearningCSVTable_ds.executeQuery();

return ret;
}

#4

Hello Amol!,

Thanks for your reply, But I want to filter values with all the field and not only with date field. That is why I have set range for customer ID and used LIKE keyword in my executequery() method.

Thanks.

#5

You need not to do these things just try the above code, remaining fields will also filtered.

#6

The remaining fields will be getting filtered according to the date field we are giving in the the string edit. But what i want is I need to give any values in the string edit field and filter according to it. If i give your code it is only getting filtered according to the date field. If i give any values from other fields in the stingedit it will not work.

#7

Wekey,

It isn’t possible to perform the task in the way you are trying.

One option is to write SQL statement in AX. Refer to https://community.dynamics.com/ax/b/klaasdeforche/archive/2010/06/21/executing-direct-sql-statements.

The kind of query you are writing in AX, you have to write it as an SQL query. Return unique field you have on that datasource from SQL by executing direct SQL statement in AX.

If customerId is the unique field in your table, return only that field from SQL. Now take all the CustomerIds that you got in response as a comma separated value (either in a string or container) & pass it into you query range on CustomerId field.

str customerIds;

//write the the code here to get customer ids from SQL through code with the required filters.

if(strlen(customerIds) > 0)

{

qbr = this.query().dataSourceName(‘LearningCSVTable’).addRange(fieldNum(LearningCSVTable,CustomerID));

qbr.value(customerIds);//if customerIds field is a container//qbr.value(con2str(customerIds));
}This solution will definitely work but it might impact the performance. It is recommended to search on a single field at a time. Let the user select which field he wants to search on. It will give faster response.

#8

Krupa,

Thank you so much for your reply. I will work on it and let u know the result.

Thanks Again.