Announcements
No record found.
how to Select Count Distinict InvoiceDate ?
Note : the Recid i used it in Select Statement
and group by InvoiceDate
but wrong Result
any other Idea for this Issue.
*This post is locked for comments
It doesn't work that way in X++. If you use RecId in your select statement it will give you the count of first Group based on the invoiceDate value, not the count of unique date values. (Run it with while select, you'll get the idea).
You want to count distinct InvoiceDate values, right?
I don't think the distinct keyword is available in X++.
If I understand your problem correctly, you can achieve that by either running direct sql commands.
Or you can simply use your x++ statement with while select and use a variable as counter to count the iterations that would give you distinct count of Dates, it is not an ideal solution but it will give you what you need. Something like this:
Table myTable; int counter; While select RecId from myTable group by (myTable.Date) { counter++; } info(strfmt("%1",counter));
Hi Zain
i tried the idea of sql but did,t run corectlly
the other idea will work but this is the last idea i will go to do
is there any other idea to do that .
thanks,
Hi,
Is there any reason to execute and get the distinct invoice dates only through constructing SQL from Ax?
I made some small changes to the job given by Zain and it returned intended results for me.
Please find the following job where job is written on custInvoiceTable.
Samething is achievable through ax query classes too.
static void DistinctDate(Args _args){CustInvoiceTable custInvoiceTable;int counter;
While select count(RecId), Invoicedate from custInvoiceTablegroup by InvoiceDate{counter++;info(strfmt("%1",custInvoiceTable.InvoiceDate));}
info(strfmt("%1",counter));}
Chaitanya is right, you can also achieve it via X++ Query and QueryRun classes. Create your query through the Query class and then you can get the count of records(Iterations) from QueryRun object.
The typical workaround solution for distinct in AX is to create a View with a group by on your "distinct" field. Then if you want aggregation, you could simply run the count against the created view in a select statement, or add another query/view for the aggregation to consume that like in a FactBox.
Hi all,
i have the records but can't count the records
i tried to use countTotal,countLoop but it didn,t work
is that because i used join in query??
thanks.
Yes. Joined query will return all number of records. Not just the grouping count for you.
I don't know why exactly you need the count for. But if it is feasible for you then create a view like suggested by Vilmos or if not then simply use the While select and count the iterations.
Explaining or showing what you did might help to identify better what you did wrong.
Count works as an aggregate function on a field in a query/view.
Please share your code or AOT object design with detailed explanation.
qbd = query.addDataSource(tablenum(SalesTable)); qbd.addRange(fieldNum(SalesTable,WorkerSalesResponsible)).value("5637168582"); qbd2 = qbd.addDataSource(tableNum(CustInvoiceJour)); qbd2.addGroupByField(fieldNum(CustInvoiceJour,InvoiceDate)); qbd2.addSelectionField(fieldNum(CustInvoiceJour,InvoiceDate),SelectionField::Count); qbd2.relations(true); queryRun = new QueryRun(query); while(queryRun.next()) { custInv = queryRun.get(tableNum(CustInvoiceJour)); print custInv.InvoiceDate; }
this is my code
You cannot do the counting in a single query to get the number of groupped objects like this.
You need to use a view that does the grouping first, then you can count the number of records in that afterwards.
That is exactly what Chaitanya trying to demonstrate with the separate total variable for the counting - it will not work in one go like you are trying to do!
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.
Congratulations to our 2026 Super Stars!
We are thrilled to have these Champions in our Community!
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Joris dG 5
Andrew Jones a1x 2
Basit 1
