Introduction
It's been quite a while since Olof Simren shared his Record Deletion Tool. We've been using it at NavBiz for a long time, even on the latest version of Business Central.
Here's a brief description of the features on how we decided to upgrade it: by adding record deletion filters.
Table Record Deletion Filter
Source code:
table 38027211 "EVE Record Deletion Filter"
{
Caption = 'Record Deletion Filter';
DataClassification = CustomerContent;
fields
{
field(1; "Table ID"; Integer)
{
Caption = 'Table ID';
TableRelation = "EVE Record Deletion Table"."Table ID";
}
field(5; "Field ID"; Integer)
{
Caption = 'Field ID';
trigger OnValidate()
var
"Field": Record "Field";
TypeHelper: Codeunit "Type Helper";
begin
Field.Get("Table ID", "Field ID");
TypeHelper.TestFieldIsNotObsolete(Field);
CalcFields("Field Name", "Field Caption");
end;
}
field(6; "Field Name"; Text[30])
{
CalcFormula = lookup(Field.FieldName where(TableNo = field("Table ID"),
"No." = field("Field ID")));
Caption = 'Field Name';
Editable = false;
FieldClass = FlowField;
}
field(7; "Field Caption"; Text[250])
{
CalcFormula = lookup(Field."Field Caption" where(TableNo = field("Table ID"),
"No." = field("Field ID")));
Caption = 'Field Caption';
Editable = false;
FieldClass = FlowField;
}
field(8; "Field Filter"; Text[250])
{
Caption = 'Field Filter';
trigger OnValidate()
begin
ValidateFieldFilter();
end;
}
}
keys
{
key(Key1; "Table ID", "Field ID")
{
Clustered = true;
}
}
fieldgroups
{
}
procedure ValidateFieldFilter()
var
RecRef: RecordRef;
FieldRef: FieldRef;
begin
RecRef.Open("Table ID");
if "Field Filter" <> '' then begin
FieldRef := RecRef.Field("Field ID");
FieldRef.SetFilter("Field Filter");
end;
end;
}
Page Record Deletion Filters
page 38027464 "EVE Record Deletion Filters"
{
Caption = 'Table Filters';
PageType = List;
SourceTable = "EVE Record Deletion Filter";
layout
{
area(content)
{
repeater(Group)
{
field("Field ID"; Rec."Field ID")
{
ApplicationArea = Basic, Suite;
ToolTip = 'Specifies the ID of the field on which you want to filter records in the configuration table.';
trigger OnLookup(var Text: Text): Boolean
var
"Field": Record "Field";
ConfigPackageMgt: Codeunit "Config. Package Management";
FieldSelection: Codeunit "Field Selection";
begin
ConfigPackageMgt.SetFieldFilter(Field, Rec."Table ID", 0);
if FieldSelection.Open(Field) then begin
Rec.Validate("Field ID", Field."No.");
CurrPage.Update(true);
end;
end;
}
field("Field Name"; Rec."Field Name")
{
ApplicationArea = Basic, Suite;
ToolTip = 'Specifies the name of the field on which you want to filter records in the configuration table.';
}
field("Field Caption"; Rec."Field Caption")
{
ApplicationArea = Basic, Suite;
ToolTip = 'Specifies the field caption of the field on which you want to filter records in the configuration table.';
}
field("Field Filter"; Rec."Field Filter")
{
ApplicationArea = Basic, Suite;
ToolTip = 'Specifies the field filter value for a configuration package filter. By setting a value, you specify that only records with that value are included in the configuration package.';
}
}
}
}
actions
{
}
}
Notice that functionalty of record filtering is copied from Base Application from
table 8626 "Config. Package Filter"
and
page 8623 "Config. Package Filters"
Modified Record Deletion Table:
table 38027112 "EVE Record Deletion Table"
{
// version OSRD15.1
// **************************************************************************************************************************
// Created and Designed by Olof Simren 2014
// Downloaded from olofsimren.com
//
// For illustration only, without warranty, free to use as you want.
// **************************************************************************************************************************
Caption = 'Record Deletion Table';
fields
{
field(1; "Table ID"; Integer)
{
DataClassification = CustomerContent;
Caption = 'Table ID';
Editable = false;
}
field(2; "Table Name"; Text[250])
{
CalcFormula = lookup(AllObjWithCaption."Object Name" where("Object Type" = const(Table), "Object ID" = field("Table ID")));
Caption = 'Table Name';
Editable = false;
FieldClass = FlowField;
}
field(3; "No. of Records"; Integer)
{
// CalcFormula = lookup ("Table Information"."No. of Records" where ("Company Name" = field (Company),
// "Table No." = field ("Table ID")));
DataClassification = CustomerContent;
Caption = 'No. of Records';
Editable = false;
// FieldClass = FlowField;
}
field(4; "No. of Table Relation Errors"; Integer)
{
CalcFormula = count("EVE Rec. Del. Table Rel. Error" where("Table ID" = field("Table ID")));
Caption = 'No. of Table Relation Errors';
Editable = false;
FieldClass = FlowField;
}
field(5; "Delete Records"; Boolean)
{
DataClassification = CustomerContent;
Caption = 'Delete Records';
}
field(6; Company; Text[30])
{
DataClassification = CustomerContent;
Caption = 'Company';
}
}
keys
{
key(Key1; "Table ID")
{
}
}
fieldgroups
{
}
trigger OnInsert()
begin
Company := CopyStr(CompanyName(), 1, 30);
end;
procedure ShowFilters()
var
RecordDeletionFilter: Record "EVE Record Deletion Filter";
RecordDeletionFilters: Page "EVE Record Deletion Filters";
begin
RecordDeletionFilter.FilterGroup(2);
RecordDeletionFilter.SetRange("Table ID", "Table ID");
RecordDeletionFilter.FilterGroup(0);
RecordDeletionFilters.SetTableView(RecordDeletionFilter);
RecordDeletionFilters.RunModal();
Clear(RecordDeletionFilters);
end;
}
action(Filters)
{
Caption = 'Filters';
Image = "Filter";
ToolTip = 'View or set field filter values for a table.';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Category4;
PromotedIsBig = true;
trigger OnAction()
begin
Rec.ShowFilters();
end;
}
procedure DeleteRecords()
var
RecordDeletionTable: Record "EVE Record Deletion Table";
RecordDelTableRelError: Record "EVE Rec. Del. Table Rel. Error";
RecordDeletionFilter: Record "EVE Record Deletion Filter";
RecRef: RecordRef;
FieldRef: FieldRef;
Window: Dialog;
begin
if not Confirm(Text0001Qst, false) then exit;
Window.Open(Text0002Lbl);
if RecordDeletionTable.FindSet() then
repeat
if RecordDeletionTable."Delete Records" then begin
Window.Update(1, Format(RecordDeletionTable."Table ID"));
RecRef.Open(RecordDeletionTable."Table ID");
RecordDeletionFilter.SetRange("Table ID", RecordDeletionTable."Table ID");
if RecordDeletionFilter.Findset() then
repeat
if RecordDeletionFilter."Field Filter" <> '' then begin
FieldRef := RecRef.Field(RecordDeletionFilter."Field ID");
FieldRef.SetFilter(RecordDeletionFilter."Field Filter");
end;
until RecordDeletionFilter.Next() = 0;
RecRef.DeleteAll();
RecRef.Close();
RecordDelTableRelError.SetRange("Table ID", RecordDeletionTable."Table ID");
RecordDelTableRelError.DeleteAll();
end;
until RecordDeletionTable.Next() = 0;
Window.Close();
end;
Conclusion
During the company's go-live preparation phase, consultants often use Record Deletion, and sometimes they don't want to delete all the data from a selected table. This feature can be useful in some cases.
Add comment
Comments