Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

How write a dynamic select statement in X++

Posted on by Microsoft Employee

Hi All,

I have a requirement to write my select statement based on one table. The table is as below.

Customer Customer Name Where clause Table Field
13441 TMU SalesId SalesTable CustOrderNumber
11996 TRU SalesId SalesTable EDI_ShipToLocation
123456 ADT LineNum SalesLine LineNum

So if the customer is "13441" I need to use the table as "SalesTable and Field as "CustOrderNumber and in where clause need to use"SalesId".

So my select statement will change based on customer account every time. 

Is there anyway I can achieve it and can build my select statement ?

Regards

  • Suggested answer
    Andrew Xu Profile Picture
    Andrew Xu 3,875 on at
    RE: How write a dynamic select statement in X++

    Mukti,

    Here's 1 solution should work.

    1. Build a T-SQL statement based on your table.

    2. Execute that statement with class Statement. 

    https://msdn.microsoft.com/en-us/library/statement.executequery.aspx

    3. Iterate the result set to get your data.

  • Dauvis Profile Picture
    Dauvis 170 on at
    RE: How write a dynamic select statement in X++

    If I am understanding that requirement correctly, you should be able to create a query using QueryBuildDataSource with just the table you need and your where clause field would specify the range you add to it (studying Chaitanya's example will teach you what you need to know). To get the field from the record that the QueryRun returns, you will have figure out what the field Id is. Once you have the field Id, you can get its value like this.

    int fieldId = ... // your field Id

    Common tableRecord = ... // the record you get from the QueryRun

    anytype value; // your value

    value = tableRecord.(fieldId);

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How write a dynamic select statement in X++ AX 2012

    Hi Sukrut, 

    I am sorry if I was not able to explain it properly.

    Ok, so the requirement is, in above table (which is basically a configuration table) we have all the customer Id, name, table name id field name.

    I will get the customer Id from external source and will match from this table, if customer Id exists, I need to create a select statement where I need to use the above table and fetch the data from the "SalesTable"; in this case for customer 13441. But my It team can change the value of Table "SalesTable" to "xyzTable"  so basically I need to use run time / dynamic select statement.

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: How write a dynamic select statement in X++ AX 2012

    Hi,

    Could you explain what does field mean in your table, is it the fieldlist you are referring to. You can check code below and let me know if anything is missing.

    SalesTable salesTable;
        SalesLine salesLine;
        Query query;
        QueryRun queryRun;
        Querybuilddatasource qbds, qbdsLine;
        QuerybuildRange qbr;
        AccountNum c1;
        
        c1 = "13441";
        
        Query = new Query();
        qbds = query.addDatasource(tablenum(SalesTable));
        qbdsLine = qbds.addDataSource(tablenum(SalesLine));
        qbdsLine.relations(true);
    
        if (c1 == "13441")
        {
            qbr = SysQuery::findOrCreateRange(qbds, (fieldnum(SalesTable, SalesId)));
            qbr.value(queryvalue("XYZ1"));
        }
        else if (c1 == "11996")
        {
            qbr = SysQuery::findOrCreateRange(qbds, (fieldnum(SalesTable, SalesId)));
            qbr.value(queryvalue("XYZ2"));
        }
        else if (c1 == "123456")
        {
            qbr = SysQuery::findOrCreateRange(qbdsLine, (fieldnum(SalesLine, LineNum)));
            qbr.value(queryvalue("XYZ3"));
        }
        
        QueryRun = new QueryRun(query);
    
        while (queryRun.next())
        {
            salesTable = queryRun.get(tablenum(SalesTable));
            salesLine  = queryRun.get(tablenum(SalesLine));
            
            info(strfmt("SalesId: %1", SalesTable.SalesId));
            info(strfmt("LotId: %1", salesLine.InventTransId));
        }


Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,986 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans