First X++ query based view

(0) Share

Report

Posted on by Richard Wehmeyer

1,004

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:

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Mea_ 60,284 on at

Like (0)

Report

RE: First X++ query based view

As far as I can see you have issue with AOT query, what are you trying to say to us with a job screenshot ?

Could you please elaborate your question ? What is an error your are receiving ?

Was this reply helpful? Yes No
Richard Wehmeyer 1,004 on at

Like (0)

Report

RE: First X++ query based view

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.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

RE: First X++ query based view

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 :)

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

RE: First X++ query based view

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

Was this reply helpful? Yes No
Richard Wehmeyer 1,004 on at

Like (0)

Report

RE: First X++ query based view

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.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

RE: First X++ query based view

It's really not so hard as It looks like in the beginning :)

Did you read articles from my previous post ? If yes what is not clear for you, what questions do you have ?

Was this reply helpful? Yes No
Richard Wehmeyer 1,004 on at

Like (0)

Report
RE: First X++ query based view

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.

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report
RE: First X++ query based view

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.

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.

Was this reply helpful? Yes No
Richard Wehmeyer 1,004 on at

Like (0)

Report

RE: First X++ query based view

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.

Was this reply helpful? Yes No
Richard Wehmeyer 1,004 on at

Like (0)

Report

RE: First X++ query based view

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"

Was this reply helpful? Yes No

Community site session details

First X++ query based view

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

First X++ query based view

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates