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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Select records with mo...
Microsoft Dynamics AX (Archived)

Select records with more than five lines

(0) ShareShare
ReportReport
Posted on by Community Member

Hi Team,

I am looking to use AggregateFunction::count in query to filter records that have more than 5 line in salesline table

Could you please suggest, how can I achieve with query and sql select statement x++

Thanks in advanced 

Regards,

Sujit

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 239,602 Most Valuable Professional on at

    Hi Sujit, I moved your question from the thread about COUNT DISTINCT, because it didn't seem to belong there.

    When you say "records that have more than 5 line in salesline table", do you mean sales orders (SalesTable) with more than five lines? Then you need "HAVING" to compare an aggregation with another value, such as the number of lines with the number 5. In normal SQL, you would write something like having count(*) > 5.

    In AX, you can't write it directly in select statements, but it's supported in queries. Please look at Walkthrough: Creating an AOT Query that has Group By and Having Nodes. Also note that it's not supported in older versions (unfortunately I don't know which version you're using).

  • Suggested answer
    Rudi Hansen Profile Picture
    Rudi Hansen 4,079 on at

    This is the closest i could get to an SELECT statement in AX, the right approach would properly be to use a query, like Martin mentioned.

    static void Job20(Args _args)
{
    SalesLine   salesLine;
    
    while select count(RecId) from salesLine
        group by salesLine.SalesId
    {
        if(salesLine.RecId > 5)
        {
            info(strFmt("SalesTable %1, has %2 lines",salesLine.SalesId,salesLine.RecId));
        }
    }
}
  • Martin Dráb Profile Picture
    Martin Dráb 239,602 Most Valuable Professional on at

    The disadvantage of this approach is that it fetches even records not meeting the condition. If I have ten thousand orders and only one hundred of them have more than five lines, the query still returns and processes ten thousand records. Using having would filter data in database and it would return only those one hundred matching records.

    The query can still be constructed and run in X++, therefore this is not a limitation either.

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,079 on at

    [quote user=""Martin"][/quote]

    The disadvantage of this approach is that it fetches even records not meeting the condition. If I have ten thousand orders and only one hundred of them have more than five lines, the query still returns and processes ten thousand records. Using having would filter data in database and it would return only those one hundred matching records.

    The query can still be constructed and run in X++, therefore this is not a limitation either.

    Yes sorry i meant the closest i could get with an x++ select statement.

  • Community Member Profile Picture
    Community Member on at

    Thanks Martin for prompt response . I really am very impressed this is my fist post.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    I got answer with help of your guidance below query worked for me    

    ProjTable prjTable;

       ProjEmplTrans projTrans;

       Query   qr;

       QueryRun    queryRun;

       QueryBuildDataSource    qbdbProjTable, qbdbProjEmpTrans;

       QueryBuildRange         qbRange;

       QueryBuildLink          qbLink;

      QueryHavingFilter havingFilter;

    qr= new Query();

       qbdbProjTable = qr.addDataSource(tableNum(ProjTable));

       qbdbProjEmpTrans = qbdbProjTable.addDataSource(tableNum(ProjEmplTrans));

       qbdbProjEmpTrans.relations(True);

       //Add group

       qbdbProjEmpTrans.addGroupByField(fieldNum(ProjEmplTrans,ProjId));

       //add agregation on projid

       qbdbProjEmpTrans.addSelectionField(fieldNum(ProjEmplTrans, recid),SelectionField::Count);

       //Add the Having filter into query

       qr.addHavingFilter(qbdbProjEmpTrans,fieldStr(ProjEmplTrans,recid),AggregateFunction::Count).value('>=5');

       queryRun = new QueryRun(qr);

       while (queryRun.next())

       {

       //prjTable = queryRun.getNo(1);

       projTrans = queryRun.getNo(2);

       info(strFmt("%1 %2", projTrans.ProjId,projTrans.RecId));

       }

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 239,602 Most Valuable Professional on at

    It looks good. :-) Now you can mark the thread as resolved, as explained on the wiki under Suggested and Verified Answers. Note that you can mark more than one reply, e.g. your reply with actual code and replies that helped you to get there.

    Let me just add you a few more suggestions.

    If you switch to the rich formatting view, you can use the </> button to insert code with formatting. This is how it looks like (I also removed some unused variables that only complicated your code:

    ProjEmplTrans        projTrans;
Query                qr;
QueryRun             queryRun;
QueryBuildDataSource qbdbProjTable, qbdbProjEmpTrans;

qr = new Query();
qbdbProjTable    = qr.addDataSource(tableNum(ProjTable));
qbdbProjEmpTrans = qbdbProjTable.addDataSource(tableNum(ProjEmplTrans));
qbdbProjEmpTrans.relations(true);

//Add group
qbdbProjEmpTrans.addGroupByField(fieldNum(ProjEmplTrans, ProjId));

//Add aggregation on projid
qbdbProjEmpTrans.addSelectionField(fieldNum(ProjEmplTrans, RecId), SelectionField::Count);

//Add the Having filter into query
qr.addHavingFilter(qbdbProjEmpTrans, fieldStr(ProjEmplTrans, RecId), AggregateFunction::Count).value('>=5');

queryRun = new QueryRun(qr);

while (queryRun.next())
{
    projTrans = queryRun.getNo(2);

    info(strFmt("%1 %2", projTrans.ProjId, projTrans.RecId));
}


    It depends on situation, but I usually use QueryRun.get() instead of getNo(), because the index doesn't look very readable to me. Therefore instead queryRun.get(2), I would write queryRun.get(tableNum(ProjEmplTrans)) or queryRun.get(projTrans.TableId).

    Also, I wouldn't use qr for Query objects, because it's sometimes used for QueryRun and it may be confusing. query or q will would be better, IMHO.

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,079 on at

    I had to rewrite the code to run on SalesTable ans SalesLine, since i have no records in the other tables on my current environment.

    But I am not getting the expected result, is seems like the HavingFilter is not working correct.

    With this code.

    static void Job20(Args _args)
{
    SalesLine               salesLine;
    Query                   query;
    QueryRun                queryRun;
    QueryBuildDataSource    qbdbSalesTable, qbdbSalesLine;

    query = new Query();
    qbdbSalesTable    = query.addDataSource(tableNum(SalesTable));
    qbdbSalesLine = qbdbSalesTable.addDataSource(tableNum(SalesLine));
    qbdbSalesLine.relations(true);

    //Add group
    qbdbSalesLine.addGroupByField(fieldNum(SalesLine, SalesId));

    //Add aggregation on projid
    qbdbSalesLine.addSelectionField(fieldNum(SalesLine, RecId), SelectionField::Count);

    //Add the Having filter into query
    query.addHavingFilter(qbdbSalesLine, fieldStr(SalesLine, RecId), AggregateFunction::Count).value('>=5');

    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        salesLine = queryRun.get(tableNum(salesLine));
            
        info(strFmt("(%1) (%2)", salesLine.ProjId, salesLine.RecId));
    }   
}


    I get this as a result:

    Info Message (09:34:13) () (4)
    Info Message (09:34:13) () (1)
    Info Message (09:34:13) () (1)
    Info Message (09:34:13) () (5)
    Info Message (09:34:13) () (4)

    So it looks like its ignoring the addHavingFilter, or did i do something else wrong?

  • Martin Dráb Profile Picture
    Martin Dráb 239,602 Most Valuable Professional on at

    I simplified your code to this:

    Query                   query = new Query();
QueryBuildDataSource    qbds = query.addDataSource(tableNum(SalesLine));
QueryRun                queryRun;
SalesLine               salesLine;

qbds.addGroupByField(fieldNum(SalesLine, SalesId));
qbds.addSelectionField(fieldNum(SalesLine, RecId), SelectionField::Count);
query.addHavingFilter(qbds, fieldStr(SalesLine, RecId), AggregateFunction::Count).value('>=5');
    
queryRun = new QueryRun(query);

while (queryRun.next())
{
    salesLine = queryRun.get(tableNum(SalesLine));            
    info(strFmt("(%1) (%2)", salesLine.SalesId, salesLine.RecId));
}

    and I successfully reproduced your problem.

    And I've managed to find the cause too. The range value doesn't like the operator ('>='); it works fine with simple "greater than" ('>'). Therefore you'll get the expected behavior if you replace value('>=5') with value('>4').

  • Suggested answer
    Rohin Profile Picture
    Rohin 4,624 on at

    code shared by Martin and sujit is working for me , I reproduced it for salesTables And salesLine.

    i saw you are fetching projId from salesLine table, can you try to fetch salesid Instead ?

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 24

#2
Michel ROY Profile Picture

Michel ROY 14

#3
imran ul haq Profile Picture

imran ul haq 8

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans