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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / differences between tw...
Microsoft Dynamics AX (Archived)

differences between two tables

(0) ShareShare
ReportReport
Posted on by Community Member

Hello!

I have (simple I think) task to compare two tables and return table with differences between those. Both tables are the same, but with other content.

I have found some solutions in SQL sites, but theres no (?) equivalent of "except" in x++. All the thing I need is a table with rows unique for two other tables.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 236,503 Most Valuable Professional on at
    RE: differences between two tables

    Look at Tutorial_ComparableContextProvider class (run it to see GUI). It shows how to compare two CustTable records.

  • Community Member Profile Picture
    Community Member on at
    RE: differences between two tables

    In both, Tutorial_CompareContextProvider and Tutorial_Comparable classes I can't find any "while", but nested "for's" which says me nothing.

    Is there to do it using while?

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    RE: differences between two tables

    on SQL forum I have found this code:

    select *

    from tableA

    EXCEPT

    select *

    from tableB

    go

    select *

    from tableB

    EXCEPT

    select *

    from tableA

    go

    But there is no EXCEPT in x++. There is possibility to do it manually like:

    boolean Found =false

    while select * from tableA

    {

    while select * from tableB where tableA.field1 == tableB.field1, tableA.field2 == tableB.field2...  tableA.fieldN == tableB.fieldN)

    {

    Found =true;

    break;

    }

    if(!Found)

    {

    tableC. field1 = tableA.field1;

    ...

    tableC.fieldN = tableA.fieldN;

    tableC.insert();

    tableC.Clear();

    }

    Found = false

    }

    }

    And the same from tableB to tableA. But seems to not being the best solution.

  • Martin Dráb Profile Picture
    Martin Dráb 236,503 Most Valuable Professional on at
    RE: differences between two tables

    The API allows you two compare two objects implementing a given interface; how you select the objects and which control structures you use for that it's completely up to you. For example, Tutorial_CompareContextProvider shows a dialog where user can choose customers to compare.

  • Community Member Profile Picture
    Community Member on at
    RE: differences between two tables

    The only logic I have found is inside comparableTextList in Tutorial_Comparable, and It's just not clear for me. I know what this class can do, I opened it.

    Anyway thanks for help, I'll wait two hours and anyway give you "yes".

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    RE: differences between two tables

    The tables must have a key which indentifies what is the same. Cant you just create a union query with two data sources joining the other table with a notexists join?

    union

    tableA

    notExists join tableB where (unique identifier are equal)

    tableB

    notExists join tableA where (unique identifier are equal)

    So you get everything in A thats not in B and everything in B thats not in A.

    Maybe I didn't understand your questin tought...

  • Community Member Profile Picture
    Community Member on at
    RE: differences between two tables

    I do not have unique identifier.

  • Martin Dráb Profile Picture
    Martin Dráb 236,503 Most Valuable Professional on at
    RE: differences between two tables

    I thought that "with differences between those" means actually differences in field values, but because neither EXCEPT nor your code would provide that, it's clearly not what you actually need.

    You can rewrite your code with not exists join, nevertheless note that it won't be able to handle unlimited strings (and binary data, of course).

    Regarding the code that "you know what the class it does, just the logic isn't clear to you", note that the comparison is actually done in SysCompare and related classes. The tutorial classes merely uses the API and implement a few very pieces of logic specific for this particular example.

  • Community Member Profile Picture
    Community Member on at
    RE: differences between two tables

    If you don't have an identifier, you have to use all fields.

  • Martin Dráb Profile Picture
    Martin Dráb 236,503 Most Valuable Professional on at
    RE: differences between two tables

    And isn't it a mistake that you don't have a unique identifier (regardless whether it would help you with this requirement or not)?

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Community Member Profile Picture

Community Member 2

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans