Business Central, deleting records from some tables for “Go-Live”

Assuming that it is necessary to delete data only from some tables for example for a Go-live (I remember that in Business Central Online – SaaS cannot access the database in any way, I do not like this thing but it is so).

How can we do?

Don’t Panic… Keep calm and GO LIVE! You have two methods to achieve this goal.

#1 – OPEN IN EXCEL: It does not allow deleting everything, only some tables, for example it is not possible to delete tables with transactional data (non-sense and very large time consuming). It is the only mode officially provided by Microsoft embedded with the pages.

#2 Go-LIVE Tool CUSTOM EXTENSION: Create and install a dedicated extension (for example, creating a delete report or a table selection mask and assigning the necessary permissions). In this case I modified one of the old GO-LIVE tools already existing (there are a couple of versions of it) adapting it to BC Online, in this case that of Olof Simren. Remember that it is to be used with discretion, only in the test \ setup environments … NOT on the production db of course.

Both solutions are based on the “DELETE” Standard method of BC

DELETE Method (Record)

Deletes a record in a Dynamics 365 Business Central table

[Ok :=] Record.DELETE([RunTrigger])

 

#1 – USE “OPEN IN EXCEL”

As said is the solution proposed by Microsoft, waiting for other tools \ kit etc.

Example:

• Delete the table “Customer”

You can delete data directly from Excel, deleting each records, Publish and it works, we have NO more customers in my database.

• “BLANK” ALL DATA e REIMPORT THE EXCEL FILE

And the Customer table is Empty!

 

#2 – MY “BCGOLIVETOOL EXTENSION”

As I said, I created this very simple extension that allows you to erase the data from the chosen Tables, based on one of the already existing Go-Live tools; it’s a very simple, improvable app, useful for quickly deleting data.

Objects in APP

• Table
• Page
• Codeunit
• XML Permissions file for master table

WHAT YOU CAN’T DO

For example, some methods or system tables that are locked ByDesign and cannot be used (for system security and integrity); I could not therefore use some methods or table ex: “table information”, DELETE Method is obviously allowed.

Is only permitted to publish the “Objects” and “Fields” Virtual tables AS Web Services, if you need to recover tabled and fields Names from these.

TYPES OR METHODS BLOCKED “CAN’T BE USED IN EXTENSIONS” ERROR

SOME CHECKS BEFORE INSTALL THE APP

“RUNTIME” VERSION 1.0, 2.0

• In this case i’m using Runtime 2.0 (October ’18 Release)

@Today you cannot upload a Runtime 2.0 Extension, Only 1.0

Recompile with Runtime 1.0 version

“Deployment failed. Errors: App ID : bf8dac69-7579-48bf-8df6-e21544280ad5 Message : { The runtime version of the extension package is currently set to ‘2.0’. The runtime version must be set to ‘1.0’ or earlier in the app.json file in order to install the extension package on this server. } – Job Id : d5885f00-0c37-49da-9466-48202f2adf18”

MICROSOFT SYSTEM

Use Platform and Application = 12.0.0.0 to Deploy extensions for Current BC Online release

If you try to use v. 13.0.0.0… instead of 12.0.0.0 …

Deployment failed. Errors: App ID : bf8dac69-7579-48bf-8df6-e21544280ad5 Message : { Extension compilation failed

error AL1024: A package with publisher ‘Microsoft’, name ‘System’, and a version compatible with ‘13.0.0.0’ could not be loaded. } – Job Id : a5932f7c-f880-49a4-aff6-25009278b7bc

 

You can download the APP BCGOLIVETOOL from:

AL Code Source

https://github.com/rstefanetti/AL-Samples/tree/AL-Go-Live-Tool

READY APP: https://github.com/rstefanetti/AL-Samples/blob/AL-Go-Live-Tool/Roberto%20Stefanetti_BCGT_2.1.0.0.app

TESTING ON PRODUCTION ENVIRONMENT! Load the APP on BC Online

Create a Test Company for test Data Delection (Copy Company from Cronus ad example)

UPLOAD THE APP

AND RUN “Delete Records Page”!

 

“HOW” TO USE THE INSTALLED APP

Search for “Record Deletion” in the SERCH MENU’

Insert the tables that you want do delete (Table ID is the table “No.”)

• Example: tbl 32, 17, 21, 25, etc. manually inserrted.
• You can import tables using RapidStart Services (a package wth all tables to delete), loaded from an Excel List for example.

Click on “Delete Records”

..AND WAIT SOME MINUTES.. ALL THE TABLES LISTED IN PAGE WILL BE DELETED.

 

Check Data from the Web Client,  you ca do the same with the Windows Client (if you are using BC On-premise)

https://businesscentral.dynamics.com/?company=TestDeleteCompany&table=32

http://localhost:8981/dynamicsnav130/?company=TestDeleteCompany&table=32

IF The System “Hangs Up” or NOT complete the deletions… don’t warry…. you can run more times!

Stay tuned!