Dynamics 365 Business Central: How to delete Duplicate Lines (records) – Customization

Hi, Readers.
We have previously discussed how to find duplicates via AL (Filter for duplicate values). In the post, I described three ways to filter out duplicate records.

Recently I was asked if there was an easy way to delete duplicate records. For example, items with the same description. There is also a way to delete it using the methods I mentioned above, such as adding a loop to the result and then deleting them. But in this post I would like to introduce a new way, which is used in the standard.

table 8622 “Config. Line” -> procedure DeleteDuplicateLines()

To explain briefly, add a loop on the records that need to be checked, then define a normal record and a temporary record for this table, filter the temporary table according to the conditions you want, if found, delete it, if not found, insert it to the temporary record.

Let’s see a simple example. Delete items with the same Item Description and keep only one.

Great.

Test video:

Very simple, give it a try!!!😁

Source code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)

pageextension 50119 ItemListExt extends "Item List"
{
    actions
    {
        addafter(CopyItem)
        {
            action(DeleteDuplicateItems)
            {
                ApplicationArea = All;
                Caption = 'Delete Duplicate Items';
                Image = Delete;
                Promoted = true;
                PromotedCategory = Process;

                trigger OnAction()
                var
                    Item: Record Item;
                    TempItem: Record Item temporary;
                    NoOfDuplicateItems: Integer;
                    NoDuplicateItemsMsg: Label 'There are no duplicate items.';
                    NoOfDuplicateItemsDeletedMsg: Label '%1 item(s) were deleted.';
                begin
                    if Rec.FindSet() then
                        repeat
                            TempItem.Reset();
                            TempItem.SetRange(Description, Rec.Description); //Filter duplicates
                            if not TempItem.IsEmpty then begin
                                Item.Get(Rec."No.");
                                Item.Delete(true);
                                NoOfDuplicateItems := NoOfDuplicateItems + 1;
                            end else begin
                                TempItem.Init();
                                TempItem := Rec;
                                TempItem.Insert(); //Insert the record to the temporary table, the first inserted record will be retained
                            end;
                        until Rec.Next = 0;
                    if NoOfDuplicateItems = 0 then
                        Message(NoDuplicateItemsMsg)
                    else
                        Message(NoOfDuplicateItemsDeletedMsg, NoOfDuplicateItems);
                end;
            }
        }
    }
}

PS: Please use Delete(true) when deleting the record, because there are many checks in the standard OnDelete Trigger, there is a risk in skipping them. (RunTrigger: Specifies whether to run the AL code in the OnDelete Trigger)

END

Hope this will help.

Thanks for reading.

ZHU