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