Filters in Record Deletion Tool

Published on 1 September 2023 at 07:00

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

There are no comments yet.