In Business Central we can delete any type of data. For instance, when deleting payment methods there is no prior checking if this method exists in any customer, vendors, etc. We always have to build some sort of permission solution for deleting master data.

In this post I would like to share a tool that prior to deleting any data, checks if it exists in any other related tables and have more control over the integrity of our data.

This solution is based on the ‘Field table’ so in order to understand it better, this example requires that we create the field page and have a visual perspective of what we are trying to achive.

Field Page

First step is to create the ‘Field’ page and add all fields:

When we open it we will see a page like the following:

This table contains all the fields in the database as if it were one big table that contains all the tables.

We can see the table number and field numbers as well as names, types, and the most important for this tool, the ‘RelationTableNo’, wich indicates the table relation property.

To explain the procedure we will use the payment method table as an example. We wish to check if a given payment method exists in a related table prior to deleting, if so, an error is displayed.

The idea is to loop through the fields in which ‘RelationTableNo’ is 289, that is, the payment method table and check if the payment method we wish to delete exists in these tables.

Coding

We will use a xlRecordRef variable and pRecordRef as entry parameter of the payment method.

  1. Filter ‘Field’ by type and RelationTableNo.
  2. Loop ‘Field’.
  3. Open each table and look for the value of the primary key of the pRecordRef parameter, that is, the data we wish to delete. For instance the value will be ‘GIRO’.
  4. Give and error if data is found.

A little more detail of the process is shown in the following image. The code can be a little tricky at the beginning as it uses the dot notation but the idea is quite simple:

procedure CheckDataIntegrity(pRecordRef: RecordRef)
    var
        rlField: Record Field;
        xlRecordRef: RecordRef;
    begin
        //Gets the record Field to filter the Type of Field table with the info. from the RecordRef parameter
        rlField.Get(pRecordRef.Number, pRecordRef.KeyIndex(1).FieldIndex(1).Number);
        rlField.SetRange(Type, rlField.Type);
        //Filters RelationTable No with the pRecordRef table Number
        rlField.SetRange(RelationTableNo, pRecordRef.Number);
        //Loop for searching in every table
        if rlField.FindSet(false) then begin
            repeat
                //Opens the table provided by rlField
                xlRecordRef.Open(rlField.TableNo);
                //Filters the field with the value of the primary key of pRecordRef
                xlRecordRef.Field(rlField."No.").SetRange(pRecordRef.KeyIndex(1).FieldIndex(1).Value);
                //If values are found we can delete it
                if not xlRecordRef.IsEmpty then begin
                    Error('The value %1 exists in the field %2 of %3 table. Deletion is not allowed.', pRecordRef.KeyIndex(1).FieldIndex(1).Value, rlField."Field Caption", xlRecordRef.Caption);
                end;
                //Closes the table
                xlRecordRef.Close();
            until rlField.Next() = 0;
        end;
    end;

Finally, for the table we wish to check, we use the ‘OnBeforeDelete’ trigger:

Video example:

As ‘GIRO’ exists in the Customer table, deletion is not allowed and an error is shown. But when we create one from scratch we can delete it as it´s only in the payment method table.

Hope you find it useful.

Advanced data integrity tool

Post navigation


Leave a Reply

Your email address will not be published. Required fields are marked *