Hi, Readers.
I was asked an interesting question on LinkedIn yesterday, is there a way to find out duplicates via AL? So in this post I would like to briefly talk about this topic.
First of all, as test data, I use the copy feature on the Item list page to create multiple items with the same Description, and we need to filter for these lines.
Because the test results are the same, so I put the test video first in front of this post.
I will mainly introduce two methods, let’s start.
1. Using Record.Count() Method to check if any of the the filtered records have more than 1 entry, those would be the duplicate values.
We have used this method briefly in How to check whether selected records have the same value in a field? This time it is similar to it, but it needs to collect all the filter conditions, i.e. duplicate Descriptions.
Source Code: Github
pageextension 50144 ItemListExt extends "Item List"
{
actions
{
addfirst(processing)
{
action(FindDuplicates)
{
Caption = 'Find Duplicates';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = FilterLines;
trigger OnAction()
var
Item01: Record Item;
Item02: Record Item;
DuplicatesFilter: Text[2048];
ItemDescription: Text[100];
begin
DuplicatesFilter := '';
ItemDescription := '';
Item01.Reset();
Item01.SetCurrentKey(Description);
Item01.Ascending(true);
if Item01.FindSet() then
repeat
if ItemDescription <> Item01.Description then begin
Item02.Reset();
Item02.SetRange(Description, Item01.Description);
if Item02.Count > 1 then begin
ItemDescription := Item01.Description;
if DuplicatesFilter = '' then
DuplicatesFilter := Item01.Description
else
DuplicatesFilter := DuplicatesFilter + '|' + Item01.Description;
end;
end;
until Item01.Next() = 0;
//Message(DuplicatesFilter);
Rec.SetFilter(Description, DuplicatesFilter);
CurrPage.Update();
end;
}
}
}
}I believe you all know the basic method, now let me improve it into a slightly more general method.
local procedure FindDuplicates: Using RecordRef Data Type and FieldRef Data Type, you only need Table ID and Field ID to get the filtered text.
Action Code:
Source Code: Github
pageextension 50144 ItemListExt extends "Item List"
{
actions
{
addfirst(processing)
{
action(FindDuplicates)
{
Caption = 'Find Duplicates';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = FilterLines;
trigger OnAction()
var
DuplicatesFilter: Text[2048];
begin
DuplicatesFilter := FindDuplicates(Database::Item, 3);
//Message(DuplicatesFilter);
Rec.SetFilter(Description, DuplicatesFilter);
CurrPage.Update();
end;
}
}
}
local procedure FindDuplicates(TableID: Integer; FieldID: Integer) DuplicatesFilter: Text[2048]
var
RecRef: RecordRef;
FldRef: FieldRef;
RecRef2: RecordRef;
FldRef2: FieldRef;
FilterText: Text[100];
begin
Clear(RecRef);
Clear(FldRef);
DuplicatesFilter := '';
FilterText := '';
RecRef.Open(TableID);
FldRef := RecRef.Field(FieldID);
RecRef.SetView(StrSubstNo('SORTING(%1)', FldRef.Caption));
RecRef.Ascending(true);
if RecRef.FindSet() then
repeat
if FilterText <> Format(FldRef.Value) then begin
RecRef2.Open(TableID);
FldRef2 := RecRef2.Field(FieldID);
FldRef2.SetFilter(FldRef.Value);
if RecRef2.FindSet() then
if RecRef2.Count > 1 then begin
FilterText := Format(FldRef2.Value);
if DuplicatesFilter = '' then
DuplicatesFilter := Format(FldRef2.Value)
else
DuplicatesFilter := DuplicatesFilter + '|' + Format(FldRef2.Value);
end;
RecRef2.Close();
end;
until RecRef.Next() = 0;
end;
}2. Using Query object to return duplicates
In fact, the practice is similar to that in the SQL statement (GROUP BY -> COUNT).
Query:
Open Query directly in the BC:
Then read the content of this Query in the code and collect the Descriptions.
Source Code: Github
pageextension 50144 ItemListExt extends "Item List"
{
actions
{
addfirst(processing)
{
action(FindDuplicates)
{
Caption = 'Find Duplicates';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = FilterLines;
trigger OnAction()
var
DuplicatesFilter: Text[2048];
ItemDuplicates: Query ItemDuplicates;
begin
DuplicatesFilter := '';
ItemDuplicates.SetFilter(No_, '>1');
if ItemDuplicates.Open() then
while ItemDuplicates.Read() do begin
if DuplicatesFilter = '' then
DuplicatesFilter := ItemDuplicates.Description
else
DuplicatesFilter := DuplicatesFilter + '|' + ItemDuplicates.Description;
end;
ItemDuplicates.Close();
Rec.SetFilter(Description, DuplicatesFilter);
CurrPage.Update();
end;
}
}
}
}
query 50112 ItemDuplicates
{
QueryType = Normal;
OrderBy = ascending(Description);
elements
{
dataitem(Item; Item)
{
column(Description; Description)
{
}
column(No_)
{
Method = Count;
}
}
}
}PS: If you want to find unique values, you need to modify the collection method of the filtered text. It should be “<>ATHENS Desk&<>PARIS Guest Chair, black”.
The above test code just extracts and saves the filter conditions. You can also use a temporary table to insert duplicate values and display them on the page or do other processing.
And please note that the source code is for reference only, you can improve it according to your own needs.
Update 2024.02.19: Dynamics 365 Business Central: How to delete Duplicate Lines (records) – Customization
END
Hope this will help.
Thanks for reading.
ZHU
コメント