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) / Comparing data of two ...
Microsoft Dynamics AX (Archived)

Comparing data of two same tables but different records using query?

(0) ShareShare
ReportReport
Posted on by Marykate 139

Hi,

I have 1 table (InventTable). I want to compare the itemId of its 2 records using query. 

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), tableStr(InventTableA));
dsInventTable = dsInventTable.addDataSource(tableNum(InventTable), tableStr(InventTableB));
dsInventTable.relations(true);
dsInventTable.joinMode(JoinMode::ExistsJoin);

dsInventTable.addRange(fieldNum(InventTable, ItemId)).value(strFmt('(%1 == %2)',

strFmt("%1.%2", qbds.name(), fieldStr(InventTable, ItemId)),

strFmt("%2.%2", qbds.name(), fieldStr(InventTable, ItemId))));

 

But this does not work, how should I write the code for InventTableB?

*This post is locked for comments

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

    Can you please tell us what you're trying to achieve? We should first define the behavior and only then start writing code.

    If you have two specific records to compare, then you can simply fetch them one-by-one and compare their values in the application. Note that your code doesn't lacks conditions to get these specific records.

    If you're trying to evaluate all records in the table (e.g. to find duplicates), tell us what exactly you want. Your current code can't do it.

  • Marykate Profile Picture
    Marykate 139 on at

    Hi Martin, I am trying to find if there is duplicate records in the table.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 239,381 Most Valuable Professional on at

    Then you should use a completely different solution. Realize that you can get the number of records for each item by grouping data by ItemId and using COUNT() aggregation method. Then you can use HAVING to filter by the result (returning only rows where the record count for a given item is higher than one).

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 304,508 Super User 2026 Season 1 on at

    Hi Marykate,

    Your example shows a message on the ItemID field. Are you trying to look at duplicate item ID's in the InventTable? The index does not allow for duplicate ItemID's per dataarea. So, if you are only interested in this column, you would never find a duplicate unless someone changed the standard indexes on this table.

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 March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 39

#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