web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / problems with SysQuery
Microsoft Dynamics AX (Archived)

problems with SysQuery

(0) ShareShare
ReportReport
Posted on by CRSW 625

Hi I created a class in which I use a SysQuery. The init looks like this:

void initQuery()
{
    Query                   query = new Query();
    QueryBuildDataSource    qbdsOrders, qbdsOrderLines, qbdsInventTable, qbdsCustomers;
    ;

    //Aufträge
    qbdsOrders      = query.addDataSource(tablenum(SalesTable));
    qbdsOrderLines  = qbdsOrders.addDataSource(tablenum(SalesLine));
    qbdsOrderLines.joinMode(JoinMode::InnerJoin);
    qbdsOrderLines.relations(true);
    qbdsInventTable = qbdsOrders.addDataSource(tablenum(InventTable));
    qbdsInventTable.relations(false);
    qbdsInventTable.addLink(fieldnum(InventTable, ItemId), fieldnum(SalesLine, ItemId));
 
    SysQuery::findOrCreateRange(qbdsOrders, fieldnum(SalesTable, CreatedDate));
    SysQuery::findOrCreateRange(qbdsOrders, fieldnum(SalesTable, SalesStatus));
    SysQuery::findOrCreateRange(qbdsOrders, fieldnum(SalesTable, CustAccount));
    SysQuery::findOrCreateRange(qbdsOrders, fieldnum(SalesTable, SalesOriginId));

    SysQuery::findOrCreateRange(qbdsOrderLines, fieldnum(SalesLine, SalesStatus));

    //Kunden
    qbdsCustomers       = query.addDataSource(tablenum(CustTable));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, AccountNum));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, CreatedDate));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, LineDisc));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, Blocked));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, CountryRegionId));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, EndDisc));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, MOCTotalVolume));

    queryrun = new SysQueryRun(query);
}

now I call 2 methods in the run-method of my class to work with the data.

method one wants to process the data from the custtable part of the query and the second method I use for the SalesTable,SalesLine and InventTable data.

CustTable returns with no values (even if they are in the table in my ax.

For SalesTable there is something found but for salesline I get a runtime error.

Here my code to get SaleTable, SalesLine and INventtable:

 while(this.queryrun().next())
    {
        salesTable  = this.queryrun().get(tableNum(SalesTable));
        salesLine   = this.queryrun().get(tableNum(SalesLine));
        inventTable = this.queryrun().get(tableNum(InventTable));
       

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: problems with SysQuery

    What would you expect your query to return?

    How is the CustTable data source supposed to be related to the other data sources?

    It seems you are only sharing part of your code - we can't see if your "this.queryRun()" has anything to do with the queryRun that you set up in the other part of your code.

    I suggest you get more familiar with the query objects by creating an x++ job, adding data sources one by one and running your query. This way you learn to understand it.

  • CRSW Profile Picture
    CRSW 625 on at
    RE: problems with SysQuery

    there is no relation between custtable and the other datasources (as seen in the initQuery).

    The call for custTable is similar to the one posted for salesTable

    while(this.queryrun().next())

       {

           custTable  = this.queryrun().get(tableNum(CustTable));

  • nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: problems with SysQuery

    Why did you add the tables to the same query if they are not related? What are you trying to achieve?

  • CRSW Profile Picture
    CRSW 625 on at
    RE: problems with SysQuery

    because this is in a class extended by runbase_form and I want to have those tables and ranges to select on the dialog and I do not know how to handle 2 queries and 2 queryruns in the dialog

  • nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: problems with SysQuery

    Could you share your business requirement? It would help us help you.

    Also sharing all your code would surely help.

  • CRSW Profile Picture
    CRSW 625 on at
    RE: problems with SysQuery

    I would give the user the option to set ranges on the query and then later on loop through the results and create a csv.

    How can I post my complete class here. I think the important parts (generating and calling the queries are shown)

  • nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: problems with SysQuery

    I still would like to hear the business requirement. You are only describing your technical solution but it limits us from providing best help.

    What is the purpose of the CustTable range? If it's not related to the sales orders that you are iterating?

    How does your run method look like? How about all methods that are called from the run method?

  • CRSW Profile Picture
    CRSW 625 on at
    RE: problems with SysQuery

    I want to create 2 files. one for customers (exported to csv) and one for salesTable,salesline and inventtable to export some data

    my run method is very simple....it calls 2 methods. 1 to export the custtable data and one for the salestable plus linked tables.

    and how I try to get the data in those methods is already posted

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: problems with SysQuery

    I don't think it's a very good design to have one functionality export to totally different things. And you definetely need two separate queries.

    But let's assume your query would work. What is the expected outcome when you iterate your query?

    We can assume that you have 8 customers in your database: A, B, C, D, E, F, G, H and I.

    And two sales orders, SO1 and SO2.

    Now, let's start iterating your query (by calling queryRun.next()).

    1. How many iterations do you expect to get? 8 or 2?

    2. What CustTable would you expect to get on first iteration?

    3. What SalesTable would you expect to get on first iteration?

    4. What SalesTable would you expect to get on iterations 3-8?

    Like I already suggested, please write an x++ job where you can concentrate only on understanding the Query class, query data sources etc. Once your job produces an expected result, you can apply your learnings to your actual classs.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: problems with SysQuery

    And remember that instead of the query window, you can just add unbounded dialogFields in your RunBase form, and use the values from these fields freely in your x++ code to select data and export it to CSV.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
AlissonGodoy Profile Picture

AlissonGodoy 2

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans