Hello All,
I have two combo boxes having integer values…
Sample UI as follows
criteria box which has criteria’s such as >, <, =, <=… combobox#1 1to10
criteria box which has criteria’s such as >, <, =, <= … combobox #2 1to10
grid with a column which has values 1 to 10
Foe eg: I want to filter > 5 and < 9
grid columns should display 6,7,8
Please advice
I am able to filter thru one combo box using QueryFilter but please guide me about this advanced filtering
Thanks in advance
Regards
Shankar Iyer
It seems that nobody knows what you need. Maybe you should ask a question.
And aren’t standard Advanced filtering and query options sufficient?
Thanks for the reply Martin.
Advanced
I need to filter is two columns of the same table simultaneously using query build range and query build source.
One column is Id field and other is a date field…
Any idea folks how to achieve this?
Thanks all in advance
Shankar Iyer
Thanks for the reply Martin.
I need to filter is two columns of the same table simultaneously using query build range and query build source.
One column is Id field and other is a date field…
Any idea folks how to achieve this?
Thanks all in advance
Shankar Iyer
Thanks for the reply Martin
Hello All,
I tried out that link and tried these links too http://msdn.microsoft.com/en-us/library/aa893981.aspx
and http://junctionsolutions.com/blogjuice/examining-x-query-statements-for-microsoft-dynamics-ax-2012/
also tried making a class in which I kept three methods
Class Deceleration
class QueryFilter{
Query q = new Query(); // Create a new query.
QueryRun qr;
CustTable ct;
QueryBuildDataSource qbr1;
str strTemp;
}
Main method
Queryfun method
{
if (qr.changedNo(1))
{
ct = qr.getNo(1);
strTemp = strFmt("%1 , %2", ct.AccountNum, ct.CreditMax);
print strTemp;
info(strTemp);
}
}
pause;
}
I did not try it as a job as mentioned in the links but I tried as a class with 3 methods class deceleration, Main method and Queryfun method
I also tried on the execute query method of the table with super() keyword.
After trying these various methods it is still giving me an error "QueryBuildRange not initialised".
Any Idea how to solve this?
Please advice
Have a great weekend everyone and thanks in advance
Thanks
Shankar Iyer
I don’t think that your classDeclaration can compile, because initializing variables there is not allowed there. You can initialize them in constructor (new()).
I don’t understand why you created a class encapsulating the query, but it’s up to you. But don’t call it QueryFilter - such a class already exists in AX 2012 and you would get a name conflict there.
Do I understand correctly that now your requirement is to have query with OR condition using two different fields? (I don’t see such a requirement in your original question, therefore it’s not covered by my previous answer.). An alternative solution would be using a separate query for each condition combined to a single union query.
Hello All,
Martin, Thanks for your valuable time in seeing my message and replying it
I don’t think that your classDeclaration can compile, because initializing variables there is not allowed there. You can initialize them in constructor (new()).
— the above code is working in the class.
Do I understand correctly that now your requirement is to have query with OR condition using two different fields? (I don’t see such a requirement in your original question, therefore it’s not covered by my previous answer.).
Yes you have understood it properly. query with OR condition using two different fields and the resulting answers of the query should be displayed on the grid ( The query is working fine in the class but when try to replicate the same code on execute query of the table)
The code snippet is here :
Form has a grid which displays all year accounts with cost and this needs to be filtered for Year2014 and cost 0.00
Form > Datasources> CostTable>ExecuteQuery
public void executeQuery()
{
Query q; QueryRun qr; QueryBuildDataSource qbr1;(For reference purposes)
q = new Query(); // Create a new query.
qbr1 = q.addDataSource(tablenum(CostTable));
qbr1.name(“CostingTable”);
qbr1.addRange(fieldNum(CostTable,AcctName)).value(
strFmt(’((%1.%2 == “Year2014”) || (%1.%2 == “0.00”))’,
qbr1.name(),
fieldStr(CostTable,AcctName),
fieldStr(CostTable,Cost)));
qr = new QueryRun(q);
super();
}
An alternative solution would be using a separate query for each condition combined to a single union query.
Martin, it would be great if you can provide a sample snippet of what you trying to tell.
Thanks all in adavnce
Regards
Shankar Iyer
If I write code like this:
class Class1
{
Query q = new Query();
}
I get the following compilation error: “The class variable cannot be initialized here. Use the new method.”.
Also, are you sure that the requirement “Year2014 and cost 0.00” actually means “Year2014 OR cost 0.00” (as you’re trying to implement)?
If you want AND, just use two ranges. If you really want OR, you have to refer to correct fields (your code currently doesn’t use Cost field) and use right format of values. This is how it should look (using another table for demonstration):
Query q = new Query();
QueryBuildDataSource qbds = q.addDataSource(tablenum(CustTable));
qbds.addRange(fieldNum(CustTable, AccountNum)).value(
strFmt('((%1.%2 == %4) || (%1.%3 == %5))',
qbds.name(),
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, CreditMax),
queryValue("xyz"),
queryValue(0)));
Hello All,
Thanks for the reply Martin.
Everything works fine when I am trying it in a class but my question for you is I want to try the same in a form.
Suppose we have a form which has a grid(Datasource: CustTable)which displays all the Account max and CreditMax values.
Now I want to apply the above query in execute query method(Form > Datasources> CusttTable>ExecuteQuery) such that the grid should show only those values resulted from this query
Code Snippet as follows:
public void executeQuery()
{
Query q; QueryRun qr; QueryBuildDataSource qbr1;(For reference purposes)
q = new Query(); // Create a new query.
QueryBuildDataSource qbds = q.addDataSource(tablenum(CustTable));
qbds.addRange(fieldNum(CustTable, AccountNum)).value(
strFmt(’((%1.%2 == %4) || (%1.%3 == %5))’,
qbds.name(),
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, CreditMax), queryValue(“xyz”), queryValue(0)));
qr = new QueryRun(q);
super();
}
Please let me know for nay concerns/questions
Thanks in advance everyone
Regards
Shankar Iyer
The code does exactly what you told him to do: it creates new Query and QueryRun instances that aren’t used for anything and the garbage collector destroys them at the end of the method.
You should rather add a range to the existing query used by the form data source. You’ll get it by calling this.query() (in AX 2012, you can directly access the query data source through this.queryBuildDataSource()).
You will find many example in the standard AX application and also on Internet. Learn how to use these resources.
Hello All,
Thanks Martin for your reply.
Problem Statement: We have a datasource/table CostingTable with fields Semeter,Course and Cost. We want to filter the grid on the form as per the query which we have created in the executeQuery method of the data source on the form.
I referred http://dynamicsuser.net/forums/p/16943/78239.aspx and solved our query.We had a similar problem and used this response to resolve our issue, with a small modification. We defined the range in init and passed it the expression in executeQuery.
Initial Setup:
Please place a grid on the form (e.g FIMMSessionalInstructor) with data source property as FCMSemesterCostingTable**.**
Here is working code snippet::
Form(FIMMSessionalInstructor) > Methods > classDeclaration
public class FormRun extends ObjectRun
{
QueryBuildRange qbr;
Str criteria;
}
Form(FIMMSessionalInstructor) > Datasources>FCMSemesterCostingTable>init
public void init()
{
qbr = this.query().dataSourceName(‘FCMSemesterCostingTable’).addRange(fieldNum(FCMSemesterCostingTable,FCMInstructorName));
//qbr = this.query().dataSourceName(‘FCMSemesterCostingTable’).addRange(fieldNum(FCMSemesterCostingTable,FCMSemesterName));
// For semester Name filtering
}
Form(FIMMSessionalInstructor) > Datasources>FCMSemesterCostingTable>ExecuteQuery
public void executeQuery()
{
//criteria = strFmt(’((%1== “Summer 2014”)|| (%1 ==“Winter 2014”))’,
//fieldStr(FCMSemesterCostingTable,FCMSemesterName)); // Multiple Semester names
//criteria = strFmt(’((%1== “Alexander Bell”))’,
//fieldStr(FCMSemesterCostingTable,FCMInstructorName));// Single Instructor
criteria = strFmt(’((%1== “Alexander Bell”))|| (%1 ==“Eliah Clooney”))’,
fieldStr(FCMSemesterCostingTable,FCMInstructorName));// Multiple Instructor
qbr.value(criteria);
super();
}
Thanks
Regards
Shankar Iyer
Junior Application Developer || Microsoft Solutions Specialist for Dynamics AX 2012
www.fiscot.com
Fiscot Inc.
Hello All,
I have two combo boxes that takes No. of Hours under consideration
The Hours filtering works well by hard-coded values in the execute query method of the table
public void execteQuery()
{
criteria = strFmt(’((%1== 18))|| (%1 == 20))’, // hard-coded value
fieldStr(FIMMSummarizationTable,WeeklyHours));// Multiple hours
qbr.value(criteria);
// For filtering the grid according to the condition specified in hours
qbrComboHours1.value(element.design().controlName(“FIMMHours1ComboBox”).valueStr());
qbrComboHours2.value(element.design().controlName(“FIMMHours2ComboBox”).valueStr());
Super();
}
but when we try to use inputs from the user i.e. when the user selects the no of hours in both of the combo boxes, it is not working. Individual combo filtering is also working fine
public void init()
{ super();
qbrComboHours1 = FIMMSummarizationTable_ds.query().addQueryFilter(FIMMSummarizationTable_ds.queryBuildDataSource(),“FIMMWeeklyHours”)
// for filtering Hours1
qbrComboHours2 = FIMMSummarizationTable_ds.query().addQueryFilter(FIMMSummarizationTable_ds.queryBuildDataSource(),“FIMMWeeklyHours”);// for filtering Hours2
}
I think it is not working because execute query has already executed when form is implemented and after that when you try to enter anything the execute query method is unable to get these inputs done by the user
Please advice.
Thanks,
Shankar Iyer
What works is enum value ID such as 18 or 20. What doesn’t work is filtering by combo box string values (valueStr()). It seems that it will work as soon as you provide correct values.
You’ll also simplify your life A LOT if you learn to analyze the query string instead of building queries by trial and error. You can get the query string by calling FIMMSummarizationTable_ds.queryBuildDataSource().toString(). Then you’ll be able to compare the actual query string with the query string you intended to create.
Try as mentioned below.
Create two ranges on the datasource (ex. Table1)
Create two comboBoxes(unBoundFields) in the form
In the Modified method of comboboxes filter the records and and call exccuteQuery() method of darasource
See underneath code
public class FormRun extends ObjectRun // formlevel
{
QuerybuildRange qbr,qbr2;
str salestype;
}
public void init() //formlevel
{
super();
qbr = Table1_ds.query().dataSourceNo(1).addRange(fieldnum(Table1,Gendermalefemale));
qbr2 = Table1_ds.query().dataSourceNo(1).addRange(fieldnum(Table1,SalesType));
}
public boolean modified() //comobox1
{
boolean ret;
;
ret = super();
qbr.value(this.valueStr());
Table1_ds.executeQuery();
Table1_ds.research();
return ret;
}
public boolean modified() // comobox2
{
boolean ret;
;
ret = super();
qbr2.value(this.valueStr());
Table1_ds.executeQuery();
Table1_ds.research();
return ret;
}
please let me know is it fit your scenario…
Thanks Krishna and Marin for your respective reply.
Krishna,
Your code did work as I needed but again I have one more question.
I have one more combo box which has values >(greater than), less than(<) and many more…
In its modified method:
public boolean modified()
{
boolean ret;
ret = super();
if(ret)
{
if(FIMMHours1ConditionComboBox.selection() == FIMMFilters::Element1)
{
qbr2.value(strFmt(’(FIMMWeeklyHours > qbr.value)’)); /// here I want to access other combo boxes’s qbr value
FIMMSummarizationTable_ds.executeQuery();
FIMMSummarizationTable_ds.research();
}
}
return ret;
}
public void init()
{super();
qbr = FIMMSummarizationTable_ds.query().dataSourceName(‘FIMMSummarizationTable’).addRange(fieldNum(FIMMSummarizationTable,FIMMWeeklyHours));//for filtering according to hours1
qbr1 = FIMMSummarizationTable_ds.query().dataSourceName(‘FIMMSummarizationTable’).addRange(fieldNum(FIMMSummarizationTable,FIMMWeeklyHours));//for filtering according to hours2
qbr2 = FIMMSummarizationTable_ds.query().dataSourceName(‘FIMMSummarizationTable’).addRange(fieldNum(FIMMSummarizationTable,FIMMWeeklyHours));//for filtering according to condition hours1
}
All 3 combo boxes are accessing to access the same columns i.e. FIMMWeeklyHours
Any idea how to proceed
Please advice.
Thanks,
Shankar Iyer
You’re trying to find FIMWeeklyHours higher than text qbr.value makes very little sense:
qbr2.value(strFmt('(FIMMWeeklyHours > qbr.value)'));
You could refer to qbr.value(), but you would have to use it as a method and actually execute it, not just sending it’s name to SQL Server.
Like this:
qbr2.value(strFmt('> %1', qbr.value());
But it still doesn’t seem correct. What if qbr.value() is already something like ‘> 1’? You would be filtering by “> N’> 1’” or something like that, which is obviously not what you want.
Move your code from modified() to executeQuery() and simply refer to all combo boxes from there (so you don’t have to refer back to range values).
Thanks Martin for your valuable suggestion. It worked.
I wrote the code in execute query, but it fails when I try the following:
if(FIMMHours1ConditionComboBox.selection() == FIMMFilters::Element2 && FIMMHours2ConditionComboBox.selection() == FIMMFilters::Element3)
{ qbr2.value(strFmt(’ > %1 && < %1’, qbr.value(),qbr1.value())); }
// trying a condition where hours > combo1 and hours < combo2
Please correct me if I am wrong
Thanks
Shankar
your query is solved or not ?
also could you post your whole code
If you want to write condition hours > combo1, why do you implement hours > qbr.value() instead? It’s very difficult to understand what you’re doing. I wondered in my previous answer why you aren’t using combo box values directly, but you didn’t explain anything.
You also failed to check your query, otherwise you would have noticed that you don’t have %2 in your filter, therefore the other value is ignored. Please do some basic debugging on your side.
It’s also not clear to me why you complicate your life with extended query syntax. I would expect simple code like this:
qbr2.value(queryRange(combo1.selection(), combo2.selection()))