Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics 365 | Integration, Dataverse...
Suggested answer

FetchXML Query- to retrieve and delete duplicates and to keep the newer record by date

(0) ShareShare
ReportReport
Posted on by 873

Hello everyone,

I have two custom tables that have 1:N relationships as the below example:

Screenshot-2022_2D00_03_2D00_24-230034.png

What I want to achieve is below:

I want to create a fetch XML query that will detect the duplicates by the above ID column provided on the line-items table and filter based on the old creation date.

That means based on the above example provided, the fetchxml query should only return the record that has been created on "3/24/2022 10:00 PM".

The aim of this query is to bulk delete all the duplicates based on the above condition so the ID will not be static because I have thousands of rows that are duplicates.

Could you please provide an example and a solution to achieve the above requirements?

Any help is highly appreciated.

Best regards,
EBMRay




  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: FetchXML Query- to retrieve and delete duplicates and to keep the newer record by date

    Hi EBMRay,

    Please try this:

    1. Create a roll up field on your parent table(In my sample is Line table):

    Screenshot-2022_2D00_03_2D00_29-094038.png  Screenshot-2022_2D00_03_2D00_29-102240.pngScreenshot-2022_2D00_03_2D00_29-102212.png

    2. Create a flow in Power Automate.

    Screenshot-2022_2D00_03_2D00_29-105446.png

    The following is the each step in flow.

     2.1 List all Line Rows whose value of rollup field is greater than 1.

    Screenshot-2022_2D00_03_2D00_29-105633.png

     2.2 Loop the above step's value and list all Line Details(Row count expression: sub(items('Apply_to_each')?['cr32a_detailsnum'],1)):

      Screenshot-2022_2D00_03_2D00_29-110002.png

     2.3 Loop Line Details in step2.2 and delete them.(Please backup your data first)

      Screenshot-2022_2D00_03_2D00_29-110132.png

    Result:

    Screenshot-2022_2D00_03_2D00_29-102436.png

  • EBMRay Profile Picture
    EBMRay 873 on at
    RE: FetchXML Query- to retrieve and delete duplicates and to keep the newer record by date

    Hi ,

    Thank you for your reply.

    No, I don't mind on any solution that you want and you see it it is the best way to achieve it please guide me through steps how I can implement it to achieve the above requirements.

    The main idea is after generating the data needed, I need to do a bulk delete for all of them.

    Your help is highly appreciated.

    Best regards,

    EBMRay

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: FetchXML Query- to retrieve and delete duplicates and to keep the newer record by date

    Hi EBMRay,

    I don't think FetchXML can achieve this. Do you mind using Power Automate or code to do this?

  • EBMRay Profile Picture
    EBMRay 873 on at
    RE: FetchXML Query- to retrieve and delete duplicates and to keep the newer record by date

    Hey  ,

    Thank you so much for providing an example.

    I have followed the same steps you mentioned and below is fetchXML that I am using where I have some issues:

    
    
    
    
    
    
    
    
    
    
    
    
    

    The above fetch XML will retrieve the N items related to one work order. However, in my query I want to add more conditions as below:

    1. To find only the WO's that are having records of more than 1 line item. (So mainly if a work order like the below screenshot it should not be included in the result because in my case it is the last one and the newer record by date).

    that is similar to this query in SQL:

    SELECT haceb_workorder, COUNT(haceb_workorder)
    FROM haceb_assetdetail
    GROUP BY haceb_workorder
    HAVING COUNT(haceb_workorder)>1

    2. Will it be possible to filter the data based on a year and my UTC as you mentioned? I think it will be between two date ranges to make it work.

    Example: I want only the line items that are created in the year 2020 and the newer records should not be included in the result.

    Please let me know if the above is feasible.

    I would highly appreciate it if you can provide an example.

    Best regards,
    EBMRay

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: FetchXML Query- to retrieve and delete duplicates and to keep the newer record by date

    Hi EBMRay,

    Please try this(Please compare UTC time):

    
    
    
    
    
    
    
    
    
    
    
    
    
    

    This is my sample data:

    Screenshot-2022_2D00_03_2D00_28-111455.png

    Result:

    Screenshot-2022_2D00_03_2D00_28-111032.png

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,419 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,492 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans