Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 593

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

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

    You should not write select statements, instead use a Query object and add ranges to it, based on the dialog fields.

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

    let's expect I rebuild my code and use dialog fields and select statements. How can I create select statement with conditional where clauses because the where clause needs only to be there if there are values entered in the dielog fields

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

    yes in the query. values are done automatically when entered in dialog.

    Adding my own field I have to set values if added and unlimited value when nothing is added....so much more code

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

    You already have written code for each range. So I'm not sure what you mean.

    Anyway, these are just suggestions, you can utilize them if you want to.

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

    yes I know but I wanna avoid to write all the range options by code

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 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.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 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.

  • CRSW Profile Picture
    CRSW 593 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

  • nmaenpaa Profile Picture
    nmaenpaa 101,156 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 593 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)

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans