Dynamics 365 general forum

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

(0) Share

Report

Posted on by EBMRay

867

Hello everyone,

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

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

Categories:

Customization / Solutions / SDK and API

All responses (5)

Answers (0)

Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

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):



2. Create a flow in Power Automate.

The following is the each step in flow.

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

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



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



Result:
EBMRay 867 on at

Like (0)

Report

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 Microsoft Employee on at

Like (1)

Report

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 867 on at

Like (0)

Report
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 Microsoft Employee on at

Like (0)

Report
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:

Result: