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 :
Finance | Project Operations, Human Resources, ...
Suggested Answer

How to find duplicate records across cross-company via sql query?

(0) ShareShare
ReportReport
Posted on by 162

I am trying to find the duplicate records across cross company, and it gives me an error on count keyword. Please help to identify it.

 while select crosscompany : conCompanies
            AccountNum from vendTable group by vendTable.AccountNum
            Having count(vendTable.AccountNum) > 1 order by asc

I have the same question (0)
  • GirishS Profile Picture
    27,833 Moderator on at

    Hi Harshal,

    Have you tried the same with X++ code?

    Thanks,

    Girish S.

  • Harshal Jain Profile Picture
    162 on at

    Hi Girish,

    I tried this on X++ only. There its giving me the error on count - invalid token

  • GirishS Profile Picture
    27,833 Moderator on at

    Can you use only count(AccountNum) instead of count(vendTable.AccountNum).

    Thanks,

    Girish S.

  • Harshal Jain Profile Picture
    162 on at

    Its giving me the same error Girish

  • GirishS Profile Picture
    27,833 Moderator on at

    Can you tell me what is the meaning of that line "Having count(vendTable.AccountNum) > 1"

    If it throws error that means you are not allowed to use count function in that place.

    I guess that without that line you will get the desired output.

    Thanks,

    Girish S.

  • Harshal Jain Profile Picture
    162 on at

    Hi Girish,

    I am looking for all the vendor accounts which have same value accross different legal entities.

  • Suggested answer
    GirishS Profile Picture
    27,833 Moderator on at
  • Suggested answer
    Mohit Rampal Profile Picture
    12,565 Moderator on at

    Hi, You can't use count in where condition. There is an alternative which I think we have discussed before, please check this article.

    allaboutax.blogspot.com/.../duplicate-records-in-dynamics-365.html

     while select crosscompany AccountNum, count(RecId) from vendTable
          order by vendTable.AccountNum asc
            group by vendTable.AccountNum 
         
        {
            if (vendTable.RecId) > 1)
            {
                //Duplicate record
            }
        }

  • Bharani Preetham Peraka Profile Picture
    3,634 Moderator on at

    For that case you can simply give a SQL query in SSMS which saves time. Any issue there?

  • Harshal Jain Profile Picture
    162 on at

    Hey Mohit,

    This worked. Thanks again

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!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 517 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 440

#3
Adis Profile Picture

Adis 266 Super User 2026 Season 1

Last 30 days Overall leaderboard

Product updates

Dynamics 365 release plans