Delete records in Dynamics when records are deleted in external system

(1) Share

Report

Posted on by Johnny

6,478

Hello,

We are using external system where we have a table with combination of Vendor, Item and Price. Periodically these records are transfered to D365 with DMF reccuring jobs.

Our problem is that, if in the external system records will be deleted, we need to delete same records in D365 too.

What would be an approach to achieve it? The problem is that when records are deleted in external system they are completely gone.. no history table or something similar.

I thought to add a logic in current recurring jobs where we would compare what data came with what is already in D365 and if there are some records that aren't in D365, it would mean that they were deleted in external system. But I don't really like this approach.

Any suggestions?

Thanks

I have the same question (0)

All responses (8)

Answers (0)

Layan Jwei 8,118 Super User 2025 Season 2 on at

Like (0)

Report

Hi Johnny,

One approach could be that when deleting a record in the external system, then you can call odata to delete that record in FO. But first we need an answer to some questions, as odata might not be a good approach

1. How frequent records get deleted in the external system?

2. Should the delete happen in real time? I mean once it's deleted in the external system then it should directly be deleted in FO?

3. How many records can be deleted per day (volume)?

If it's real time, and the calls won't be frequent (i mean not alot of calls per day) and the number of records to delete per day is small, then odata might be an option

If not we'll think of another solution after your reply.

Thanks,

Layan Jweihan

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

Hello Layan,

here are answers:

1. It can happen daily, or at least few times per week.

2. It is not necessary that the corresponding records are deleted in real time. Can happen for example during the night.

3. It depends, on avarage can be around 1000 records.

Thank you

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (1)

Report

I think you should add a piece of logic to the external system. When a record is deleted, it should either store or send information about the event and an ID of the deleted record. It may use database change tracking, a custom table, send a message to an Azure queue or something like that.

I too dislike the idea of comparing all records in both systems.

Was this reply helpful? Yes No
Anthony Blake 2,963 Super User 2025 Season 2 on at

Like (0)

Report

Hi Johnny,

Just to throw another thought into the mix, would you want to delete Vendors and Items which potentially have transactions against them in D365? You may want to consider instead placing them on stop/hold?

Anthony

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (1)

Report

Hi Anthony,

no, we do not delete Vendors or Items. The table in external system is kinda same as Trade agreements in D365.

So based on what has been deleted in external system, we want to delete it from trade agreement, or make it inactive.

Was this reply helpful? Yes No
Anthony Blake 2,963 Super User 2025 Season 2 on at

Like (0)

Report

Hi Johnny,

Clear, thanks. Definitely another vote from me for logging what has been deleted externally, as the trade agreement table can get quite big depending on how many changes and combinations you have in the long term. Then you need to journal in some end dates for the deleted prices/combinations.

Was this reply helpful? Yes No
Raj Borad 1,428 on at

Like (0)

Report

Hi,

You can create a Tmp table and stores the value from external system to the temp table.

Here is the steps:

- Fetch data from the external system and store in a staging (temp) table in D365 FO.

- Compare the main table with the record in tmp table and identify the records which are not in tmp table.

- Delete the main table where matching conditions is false.

TmpTable tmpTable;
MainTable mainTable;

// Loop to find records not in the staging table
while select * from mainTable
{
select firstonly recId from tmpTable
where tmpTable.Vendor == mainTable.Vendor
&& tmpTable.Item == mainTable.Item
&& tmpTable.Price == mainTable.Price;

// If no match found in staging table, delete from main table
if (!tmpTable.RecId)
{
mainTable.delete();
}
}

Thank you,

Raj D Borad

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

Yes, such a comparison of all data was discussed below, but as mentioned, it's very inefficient. You'd have to transfer all records every time and then run the comparison.

Your code makes the performance problem even worse because you make a separate query for every record in MainTable. Using a join (or even delete_from, if possible) would be much more efficient.

Was this reply helpful? Yes No