Business Central Online, quick comparison and detection of differences between table data structures using Powershell

Business Central Online, quick comparison and detection of differences between table data structures using Powershell

Previously it was possible to export data structures to files (available in runtime1), the functionality was blocked from runtime 2 onwards.

Interesting function that in my opinion should be reinserted and made usable at least for On-premise.

Immagine che contiene tavolo

Descrizione generata automaticamente

“ExportPbjectsFIlename” ( Only for On-prem 1.0)

“Exports application objects to a file.”

System.ExportObjects(FileName: Text, var ObjectRecord: Record [, Format: Integer])

Testing

trigger OnOpenPage();Funzione interessante che secondo me dovrebbe essere reinserita e resa utilizzabile almeno per On -premise    var

Cust: Record customer;

Obj: Record Object;

begin

Obj.SetRange(Type, Obj.Type::Table);

Obj.SetRange(ID, 18);

System.ExportObjects(‘C:\Run\my\exportObjects.al’, Obj);

end;

** System.ExportObjects is not permitted **

System.ExportObjects(Text, var Record [, Integer]) Method – Business Central | Microsoft Docs

How-to quickly check a table structure?

How to quickly check a table structure between two systems (example one on NAV \ BC Onpremise and one on BC Online?)

Possibilities:

Go On-Prem

– Export the db from the Cloud and restore on On-prem or on docker (SaaS to Docker script by Freddy)

– Use the standard powershell scripts to run the checks and generate the differences (being all the db On-prem it is easy to do)

– Use custom scripts to analyze

– Use Sql Script & Functions to generate the differences (many scripts ready to use)

– Use Source code control tools

Cloud & OnPrem

Or if we want to make a first check between the Cloud and On-prem data structures

– Install page list of objects on the Cloud

– Export the object list to txt on the Cloud

– Do the same on On-prem

– Export the object list to txt on On-prem

– Run a powershell script to check the differences

On BC Cloud Sandbox

Install page “Table Fields List”

The page shows all the fields of both the base app and the extensions (as normally happens) with references to the extensions that publish them. It is not possible to see the complete definition of the object as some properties are directly saved in the metadata (example the definitions of flowfields fields)

  • Filter for your table\tables

Table Fields List

Export the table structure

  • Run XMLPort “XMLPort – export table structure”

Example: export tbl 18 Structure

The result is this:

On On-Prem

On On-Prem NAV or BC create a Dataport\Xmlport to do the same thing

Export from NAV2009 – Dataport!!

The result is this:

Powershell – check table structure

Now through powershell it is possible to launch a check between the two exported structures and detect the differences. it is already a first check without going through other tools.

TEST ON TBL 287 “Customer Bank Account”

POWERSHELL ISE

Launch Powershell Script

Launch this simple powershell script

#FILE  SOURCE & TARGET

$File1 =  “C:\temp\COMPARE\tbl287_BC202_FullExtensions.txt”  #NEW

$File2 = “C:\temp\COMPARE\tbl287_NAV2009.txt”               #OLD

$Location = “C:\temp\COMPARE\Differencestbl287.txt”          #DIFFERENCES

#OBJECT COMPARE

Compare-Object -referenceObject $(Get-Content $File1) -differenceObject $(Get-Content $File2) | ft -auto | out-file $Location -Verbose ascii

RESULTS

Immagine che contiene testo

Descrizione generata automaticamente

DIFFERENCES WITH INDICATORS

  • And 🡸 (Source-Target)

Take a look to tbl 2 definition

OPEN IN EXCEL (CSV FILE)

Filter objects with excel to analyze

Source code here

https://github.com/rstefanetti/AL-Code-Samples-Education/tree/AL—CheckableSctructure

Stay tuned!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.