delete record issue

[Joan-Mok]

Dear all, I am using your package currently on BC to Azure Synapse. When I track my records in the table SalesLine-37 in the DataLake, deleted records from BC did not be removed in the DataLake. Could you tell me which script I should look at and solve this problem?

Many thanks Joan

[Bertverbeek4PS]

When a record is deleted it will be stored in the "ADLSE Deleted Record" table. If you do an export it will be stored in the .csv file in the delta folder.

Then in the dataflow it will be deleted:

[Joan-Mok]

Hello, Thanks for the reply. I am confused about this sentence: If you do an export it will be stored in the .csv file in the delta folder.

• Do you mean those deleted record would be store in the delta folder -- SalesLine-37 ?

• Or it will be stored in the ADLSE Deleted Record in the delta folder?

but my issue is: when someone modified and delete records in the BC from today, I cannot see this changes in my DataLake after the daily exporting(nightlyload).

Many thanks Joan

[taylormoran]

I would be careful with exporting that table. Not sure if it would still be an issue in recent versions but a long time ago I tried exporting the ADLSE tables to take a look at what they had in them. If I remember correctly trying to clear deleted records or resetting tables became an issue because anything that would delete records from the Deleted Record table caused kind of a loop and never seemed to complete and had database lock issues. Luckily it was a sandbox environment for testing..

[Bertverbeek4PS]

@Joan-Mok I make it more clear :) When you delete a record inside BC you will get a record in table "ADLSE Deleted Record". When you do an export to Data Lake it will also export that table (out of the box). The records will be in the same file as all other records. You can recognize it with empty "systemCreatedAt"

So you don't need to add the table to your list of tables.

@taylormoran indeed got also that request. I think I will prefent it to add table "ADLSE Deleted Record" to the list. the ADLSE table I got also a working solution for it. Need to do a pull request

[Joan-Mok]

@taylormoran thanks for letting me know, big tip. Do you have the same issue like me that delete records in BC still remain in the datalake?

[Bertverbeek4PS]

@Joan-Mok you can check if the deleted records are exported in the .csv files (You can recognize it with empty "systemCreatedAt )

[Joan-Mok]

@Bertverbeek4PS thanks for the explanation. I will look it up and get back to you. Many Many thanks

[taylormoran]

@Joan-Mok we modified the Consolidation_OneEntity pipeline to capture those deletes in a separate table (folder in the data folder) using what @Bertverbeek4PS described so the "Vendor" table had a "Vendor_Delete" table too for example. We capture these deletes for multiple reasons but the consolidation process should be deleting records appropriately in the data lake though.

[Joan-Mok]

@Bertverbeek4PS I checked my data in the data lake, in SalesLine, the deleted records with empty 'systemCreatedAt' only contains in the DocumentType -- Quote. We also have the deleted records for Orders, but I cannot find order record with empty systemCreatedAt. We have deleted order records in the BC. Could you tell me why?

[Joan-Mok]

@taylormoran for my issue, only part of the deleted records are deleted in the data lake, in the SalesLine, only quote. Deleted record on orders still remain in the datalake with normal systemcreatedat.

[Bertverbeek4PS]

@Joan-Mok you must only filter for records with empty systemCreatedAt and look at the system-ID. All other values you don't have to look at.

With those two fields he will remove the existing records in your datalake.

So in your case Quote is the first option of that value. So he will use that value. But you only have to look at the systemId. All other values are ignored.

[Joan-Mok]

@Bertverbeek4PS

• good to know, what happen if quotes are deleted, but orders are still there. Will they use the same systemId?

• also, when we have modification on a quote or a order and we delete it at the same day, I cannot find the modification on it, why?

• also, when orders are completed, most of them are in the sales invoice line, but few of them are remained in the sales line.

• Could you tell me why?

Best wishes, Joan

[Bertverbeek4PS]

Hi @Joan-Mok Quote and Orders have different systemID. So in that case it will delete a quote or an order.

If you do a modification and a delete and then do an export it will just delete it. This is because you did only one export and the deletes will be first and after that it will do a sort on modified at date. So the records isn't there anymore. But in the end the data is the same as in BC.

Now sure if I understand your last question. But is there are still lines in the sales lines proberly they are also there in BC and maybe not finished in the process.

[Joan-Mok]

@Bertverbeek4PS Thanks for the reply.

1. referring to this:

   'If you do a modification and a delete and then do an export it will just delete it. This is because you did only one export and the deletes will be first and after that it will do a sort on modified at date. So the records isn't there anymore. But in the end the data is the same as in BC.' I have the issue that, after deleting and exporting, I still the order in the SalesLine in the DataLake with the information before deletion. How could I check this in the source code?

2. I checked it also, when some sales lines are cancelled, they were removed from the BC, but it was still remained in the SalesLine in the DataLake. Could I also track this in the source code?

Many thanks Joan

[Bertverbeek4PS]

What is exactly the scenario in BC? Maybe there is a bug. So if you provide me the scenario I can debug etc.

[Joan-Mok]

@Bertverbeek4PS the scenario is as following:

1. one colleague modified an order with several sales lines many times in different days (so we have done several export). The last time, after he modified it, he delete the whole order. After the deletion, we did an exporting from BC. This order still remains in the datalake, but without the last modification info and deletion info.

2. another colleague changed the value in the salesline to zero, which means it was cancelled and then, they delete it. After exporting, in the datalake, we only have the information in the SalesLine before the deletion.

3. I am still confused by

you must only filter for records with empty systemCreatedAt and look at the system-ID. All other values you don't have to look at.

With those two fields he will remove the existing records in your datalake.

So in your case Quote is the first option of that value. So he will use that value. But you only have to look at the systemId. All other values are ignored.

If a quote becomes an order, will they shared the same systemId? Because for my case, if I filter by empty SystemCreatedAt, I only saw the DocumentType 'Quote', but the truth is that we deleted order as well. Then, when an order is deleted, it will have systemId with empty SystemCreatedAt, but the DocumentType is 'Quote'.

Many thanks, Joan

[Bertverbeek4PS]

Hi @Joan-Mok I have tested your scenario's inside Data Lake. Created a sales order with two lines and they end up inside the delta folder: Then made a change from quantity 2 to 1 for one line. This wil be exported inside the delta folder: Now I change the quantity to 2 again and delete the line and then export it: There you can see the systemID and systemModifiedBy is an empty GUID. The rest of the columns doesn't matter. SystemId is unique per table. But we have to export is in the same way for the structure.

Als you see that the modification isn't there. Because he will only export the last change.

For the quote to order process: Create a quote it will go this way in the delta folder:

When you do the function Make order on then quote and then export again in the delta folder you get:

On that action I need to figure out. The deletion of quote must also be there. I didn't expected that.

[Joan-Mok]

Hi @Bertverbeek4PS , thanks for the help and explanation, and it is really helpful. I am fine with everything now.

Best wishes, Joan

[Isarien]

Hello, On my side, I have a similar issue on the purchaseLine & orderLine table. There are 2 cases :

1. for the same systemId I have information about a deletion, and some time later, an information about data update. When I check in BC, the line is always present in the sale order.

2. for some systemId I never get the information about deletion. The line is not anymore present in BC, but deletion information is not sent in the delta files.

For the 2 cases, there is no line in ADLSDeletedRecord table for this system Id.

here are is an example for case 1 :

We are trying to reproduce the issue, but it seems random

[Bertverbeek4PS]

@Isarien hopefully in the coming weeks I have time to investigate this. Very strange bahouvior

[Bertverbeek4PS]

I have digged into this issue. But currently with BC24 and newest release of bc2adls I get everything in my "ADLSE Deleted Record" table. Even the "Make Order" function. Looks like the Global trigger OnAfterOnDatabaseDelete sometimes has an hick up?

[Bertverbeek4PS]

@Isarien @Joan-Mok I have discovered the issue and created a fix for it in PR https://github.com/Bertverbeek4PS/bc2adls/pull/124

[Isarien]

Thank you @Bertverbeek4PS I hope we will try it soon.