1,000
I am new to the AOT side of AX and am trying to build this query into an eventual test report. I am unable to select DataSource on the view field property "PurchIDnotWork."
This is my progress:
Yes! The article does look like what I am trying to do. A case of not knowing what to look for I guess. This puts me on track now, thank you.
"technet.microsoft.com/.../gg724119.aspx"
Thank you.
Wow, that article is a mouthful. Its 11:15 for me so Ill take a look at this and work on it tomorrow and see what I can do with it. Thanks again so much.
Hi Richard Wehmeyer ,
You actually doing good for Excel guy :)
I removed comments where your were right and add where you were not :) And moved code around a bit.
Now you need to read this technet.microsoft.com/.../gg724119.aspx
As you can see from article query was created in AOT, so you don't need part about query and adding data sources, all should be created in AOT.
Part that iterates through QueryRun should be written in processReport method, you will see it in example in this article.
Share
Report
All responses (
Answers (
CustTable custTable;
Query query = new Query();
QueryRun qr; //we want to create query and run it after.
QueryBuildDataSource qbds = qr.query().addDataSource(tableNum(CustTable));
QueryBuildRange qbrAccN = qbds.addRange(fieldNum(CustTable,AccountNum));
QueryBuildRange qbrCountry = qbds.addRange(fieldNum(CustTable,Country));
QueryBuildFieldList qbfl = qbds.fields(); //don't need this
qbrAccN.value('4000..4050'); //You can use SysQuery::range(from, to) instead like qbrAccN.value(SysQuery::range(4000,4050));
qbrAccN.status(RangeStatus::Locked); //says that user can't change range in UI
qbfl.addField(fieldNum(CustTable,CreditMax),SelectionField::Sum); //You don't need this, sum could be done in SSRS using totals and sum function in design.
qbfl.addField(fieldnum(CustTable,RecId),SelectionField::Count); //same
qbds.addSortField(fieldnum(CustTable,Country)); //don't need
qbds.addSortField(fieldNum(CustTable,Currency)); //don't need
qbds.orderMode(OrderMode::GroupBy); //don't need
QueryRun qr = new queryRun(query); //this creates object that could run a query
if (qr.prompt()) //will prompt form for user to set ranges, don't need in reports, reports framework will do it for you
{
while (qr.next()) //loops through records if they are joined by 1:1 will return all records in 1 call, otherwise will return 1 records per call.
{
custTable = qr.get(tableNum(CustTable)); //get current selected record
print strfmt("%1 %2 %3 (%4 records)",custTable.Country,custTable.Currency,
num2str(custTable.CreditMax,10,2,0,0),custTable.RecId); //Infolog, you don't need it.
Ok, two of them are related to views which I'm not trying to get too hung up on (and the way they portray simple single table views I think I have a handle on that just fine).
The Query class looks more like what I am after but its hard to read the code.
CustTable custTable;
Query query = new Query();
QueryRun qr = new queryRun(query); //this says to run the query QueryBuildDataSource qbds = qr.query().addDataSource(tableNum(CustTable)); //info for the query to find custtable QueryBuildRange qbrAccN = qbds.addRange(fieldNum(CustTable,AccountNum)); //used to create custom range selector QueryBuildRange qbrCountry = qbds.addRange(fieldNum(CustTable,Country)); //used to create custom range selector QueryBuildFieldList qbfl = qbds.fields(); //Not sure why this is here ; qbrAccN.value('4000..4050'); //for my table this would probably be used for dates qbrAccN.status(RangeStatus::Locked); //line status would be nice here qbrCountry.value('CA..NO'); //probably N/A for me qbfl.addField(fieldNum(CustTable,CreditMax),SelectionField::Sum); //Sum for group footer qbfl.addField(fieldnum(CustTable,RecId),SelectionField::Count);
qbds.addSortField(fieldnum(CustTable,Country)); qbds.addSortField(fieldNum(CustTable,Currency)); qbds.orderMode(OrderMode::GroupBy); if (qr.prompt()) //what is the purpose of this? { while (qr.next()) //does it have to loop through individual records? { custTable = qr.get(tableNum(CustTable)); print strfmt("%1 %2 %3 (%4 records)",custTable.Country,custTable.Currency, num2str(custTable.CreditMax,10,2,0,0),custTable.RecId); //This will output to infolog?
Ok, so I think I might be able to hack this into what I want (except for the last lines) but then where do I put this block of text? In a class declaration? Then where do I put the class. Like I said: first exposure to X++, or anything beyond VBA for Access/Excel.
Please forgive my ignorance and thank you so much for the time you are taking to help. It is really appreciated.
I am trying to build a data provider to be eventually used in an SSRS report, the view is so that I can view my data before creating my report. Right now this report will contain all PO lines grouped on PO + orderer + requestor headers so that the report can be filtered on these data as needed. (i.e. total $ in po requested by John Doe in March)
We were shown how to do this but my note taking is not as fast as our instructor and I was getting a little lost in bouncing around code without understanding what was happening.
I can create a report based on a single table without code pretty easily and I think I can do a join table(yet to fully make that work though), however, we have been advised that the best practice is to do joins with code by making a dataprovider. As you can tell I don't yet know exactly what I am doing yet.
Also you cannot select any data source in you view because you query does not have data sources.
If you are going to build view that will contain purchTable and purch line you don't need a query at all.
You can add these two tables into data source node of view and join them using relations.
Please read these:
ajstudi0.blogspot.co.nz/.../microsoft-dynamics-ax-views.html
stoneridgesoftware.com/more-on-using-views-in-microsoft-dynamics-ax-2012
Can you please describe what is your real goal ?
In AX you have 2 ways to select data from tables:
1) select statement
2) Query
Query could be created in AOT and in run time. To get data from query you need to use queryRun. www.axaptapedia.com/Query_class
Methods on query does not make any sense at all in your context. When you open a query AX uses query form to prompt a dialog where you can specify ranges, it won't magically execute random code that you put into methods node :)
Sorry if I'm vague, I am jumping into this feet first so I am pretty ignorant to how to do this.
I created the class method (right), running this code as a job the infolog produces the results of the query I want.
I took the class and placed it as a method in a new query "FES_RW_PurchLineXcodeQ". On opening the query I expect the table browser to display the same result as when I performed it as a job to infolog. I instead get:
----------------------------------------
Stack trace: Calling wait or detach before calling run.
Stack trace
(C)\Classes\FormRun\wait
(C)\Classes\QueryRun\prompt
(C)\Classes\QueryRun\run
----------------------------------------
I then try to attatch the query to a new View metadata but when I try to define a field I cannot select from the datasource properties.
I know I'm probably doing this wrong but MSDN requires more knowledge than I currently have to navigate.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,349
Super User 2024 Season 2
#2
Martin Dráb
228,212
Most Valuable Professional
#3
nmaenpaa
101,148