“What if I could replicate a table to a system in an entirely different database with a different language in an entirely different OS evenly?”
Wondered what I could do with webhooks which wasn’t just a standard use case. This post isn’t a how-to for creating custom API pages in AL or how webhooks work, other people have done this and they are way better at explaining it than me.
The flow I wanted:
- Add generic Business Central AL extension, which exposes the installed APIs and the Field table. This is the main, it will not expose any data tables itself, but will be used to handle the communication.
- Add extension with custom API pages for the tables needing replication. (In this example it will contain the table also, but normally not.)
- Server (called CopyCat) will call the main extension (ALleyCat) and get a list of all API pages within a specific APIGroup (BadSector) and their table structure.
- CopyCat will connect to a secondary database and create tables.
- CopyCat will copy all records.
- CopyCat will subscribe via webhooks for further changes.
- Each webhook response is just a record ID and the change type, ex. Updated or Deleted. So CopyCat will either request new/updated record or delete if needed.
Will keep a list of new or modified records in an array and only request a predefined records pr. sec, so the main service-tier won’t be overloaded. - Periodic requests for table changes in BC and if new fields are detected, they are added to the table.
Goals:
- Use something different than MSSQL, maybe MYSQL, or even MongoDB.
- Use NodeJS.
- Have it all running on a Raspberry Pi.
What I wanted to end up with was a “lazy” copy of a or multiple tables. This could be used for BI or other processes where changes aren’t needed in the instant they are made.
Pro:
- OS independent
- Programming language independent (Your job, I will use NodeJS)
- DB independent
- Will not overflow the copy database with writes, as they are delayed by default in the service tier. So multiple changes to the same record are bundled up. (One write on multiple field updates, instead of one for each field)
Cons:
- Data is not perfectly up-to-date.
- Not dependent on Azure (So no shares of the blog post by MVPs) 😉
- Requires a custom API Page for each table
I started by researching a bit on how to use the APIs and webhooks. The standard documentation wasn’t that good, but by mixing it with some of Dmitry Katson‘s great posts, I got what I need to get started.
Quick note, I could properly have used the light version of BC (Cloud) but used the full version (on-prem) to be sure I had full control over my environment.
To get a table replicated, I needed to expose the tables data and supply some form of field type info. But I didn’t want the last part to be a manual step, so I thought; maybe use the Fields table.
But sadly, the field SQLDataType is bugged when shown on a Page of type API, as it showed all fields as being Varchar as seen below.
So I added my own field to the API Page and exported the formats I remembered them to be.
page 50500 FieldApi
{
PageType = API;
Caption = 'field';
APIPublisher = 'BadSector';
APIGroup = 'ALleyCat';
APIVersion = 'beta';
EntityName = 'field';
EntitySetName = 'fields';
SourceTable = Field;
DelayedInsert = true;
Editable = false;
layout
{
area(Content)
{
repeater(General)
{
field(TableNo; TableNo)
{
ApplicationArea = All;
}
field(No; "No.")
{
ApplicationArea = All;
}
field(TableName; TableName)
{
ApplicationArea = All;
}
field(FieldName; FieldName)
{
ApplicationArea = All;
}
field(Type; Type)
{
ApplicationArea = All;
}
field(Len; Len)
{
ApplicationArea = All;
}
field(Class; Class)
{
ApplicationArea = All;
}
field(Enabled; Enabled)
{
ApplicationArea = All;
}
field(TypeName; "Type Name")
{
ApplicationArea = All;
}
field(FieldCaption; "Field Caption")
{
ApplicationArea = All;
}
field(RelationTableNo; RelationTableNo)
{
ApplicationArea = All;
}
field(RelationFieldNo; RelationFieldNo)
{
ApplicationArea = All;
}
field(SQLDataType; SQLDataType)
{
ApplicationArea = All;
}
field(OptionString; OptionString)
{
ApplicationArea = All;
}
field(ObsoleteState; ObsoleteState)
{
ApplicationArea = All;
}
field(ObsoleteReason; ObsoleteReason)
{
ApplicationArea = All;
}
field(DataClassification; DataClassification)
{
ApplicationArea = All;
}
field(CustomSQLDataType; CustomSQLDataType)
{
ApplicationArea = All;
}
}
}
}
var
CustomSQLDataType: Text[20];
trigger OnAfterGetRecord()
begin
CASE Type of
Type::Boolean:
begin
CustomSQLDataType := 'tinyint';
end;
Type::Code:
begin
CustomSQLDataType := 'nvarchar';
end;
Type::Date:
begin
CustomSQLDataType := 'datetime';
end;
Type::DateTime:
begin
CustomSQLDataType := 'datetime';
end;
Type::Decimal:
begin
CustomSQLDataType := 'decimal';
end;
Type::Duration:
begin
CustomSQLDataType := 'int';
end;
Type::Integer:
begin
CustomSQLDataType := 'int';
end;
Type::Option:
begin
CustomSQLDataType := 'int';
end;
Type::Text:
begin
CustomSQLDataType := 'nvarchar';
end;
Type::Time:
begin
CustomSQLDataType := 'datetime';
end;
else
CustomSQLDataType := 'Unsupported';
end;
end;
}
Now I just needed to find out which fields a given API page relates to. This took a while to figure out, but I ended up making another API page with “Page Metadata” as the source table. This made it possible to find API pages with a specific APIGroup and, at the same time, give me the source table, so I knew which fields to expect.
page 50502 "Page Metadata List"
{
PageType = API;
APIPublisher = 'BadSector';
APIGroup = 'ALleyCat';
APIVersion = 'beta';
EntityName = 'page';
EntitySetName = 'pages';
DelayedInsert = true;
Editable = false;
SourceTable = "Page Metadata";
Caption = 'Page Metadata List';
ApplicationArea = All;
UsageCategory = Lists;
layout
{
area(content)
{
repeater(General)
{
field(ID; ID)
{
ApplicationArea = All;
}
field(Name; Name)
{
ApplicationArea = All;
}
field(Caption; Caption)
{
ApplicationArea = All;
}
field(Editable; Editable)
{
ApplicationArea = All;
}
field(PageType; PageType)
{
ApplicationArea = All;
}
field(CardPageID; CardPageID)
{
ApplicationArea = All;
}
field(DataCaptionExpr; "DataCaptionExpr.")
{
ApplicationArea = All;
}
field(RefreshOnActivate; RefreshOnActivate)
{
ApplicationArea = All;
}
field(APIPublisher; APIPublisher)
{
ApplicationArea = All;
}
field(APIGroup; APIGroup)
{
ApplicationArea = All;
}
field(APIVersion; APIVersion)
{
ApplicationArea = All;
}
field(EntitySetName; EntitySetName)
{
ApplicationArea = All;
}
field(EntityName; EntityName)
{
ApplicationArea = All;
}
field(SourceTable; SourceTable)
{
ApplicationArea = All;
}
field(SourceTableView; SourceTableView)
{
ApplicationArea = All;
}
field(InsertAllowed; InsertAllowed)
{
ApplicationArea = All;
}
field(ModifyAllowed; ModifyAllowed)
{
ApplicationArea = All;
}
field(DeleteAllowed; DeleteAllowed)
{
ApplicationArea = All;
}
field(DelayedInsert; DelayedInsert)
{
ApplicationArea = All;
}
field(ShowFilter; ShowFilter)
{
ApplicationArea = All;
}
field(MultipleNewLines; MultipleNewLines)
{
ApplicationArea = All;
}
field(SaveValues; SaveValues)
{
ApplicationArea = All;
}
field(AutoSplitKey; AutoSplitKey)
{
ApplicationArea = All;
}
field(DataCaptionFields; DataCaptionFields)
{
ApplicationArea = All;
}
field(SourceTableTemporary; SourceTableTemporary)
{
ApplicationArea = All;
}
field(LinksAllowed; LinksAllowed)
{
ApplicationArea = All;
}
field(PopulateAllFields; PopulateAllFields)
{
ApplicationArea = All;
}
}
}
}
}
When calling the API with the following query, I got the respond below:
https://blog:7048/NAV/api/BadSector/Alleycat/beta/companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/pages?$filter=APIGroup eq ‘CopyCat’
{
"@odata.context": "https://blog:7048/NAV/api/BadSector/Alleycat/beta/$metadata#companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/pages",
"value": [
{
"@odata.etag": "W/\"JzQ0OzZtU1pKNzY0Nms4ODhvNzRJR2FZMitOTGhoUjZuRXRESkpXVG1JalFFcEE9MTswMDsn\"",
"ID": 50510,
"Name": "TableWithDataToDuplicatedApi",
"Caption": "TableWithDataToDuplicatedApi",
"Editable": false,
"PageType": "API",
"CardPageID": 0,
"DataCaptionExpr": "",
"RefreshOnActivate": false,
"APIPublisher": "BadSector",
"APIGroup": "CopyCat",
"APIVersion": "beta",
"EntitySetName": "records",
"EntityName": "record",
"SourceTable": 50510,
"SourceTableView": "",
"InsertAllowed": true,
"ModifyAllowed": true,
"DeleteAllowed": true,
"DelayedInsert": true,
"ShowFilter": false,
"MultipleNewLines": false,
"SaveValues": false,
"AutoSplitKey": false,
"DataCaptionFields": "",
"SourceTableTemporary": false,
"LinksAllowed": true,
"PopulateAllFields": true
}
]
}
So now I have a dynamic list of table to replicate:
https://blog:7048/NAV/api/BadSector/Alleycat/beta/companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/pages?$filter=APIGroup eq ‘CopyCat’
and I can get a list of all the fields for each table exposed by replacing the Table No below:
https://blog:7048/NAV/api/BadSector/Alleycat/beta/companies(c29fb1d5-b8ff-4bcf-a075-e8512fa17802)/fields?$filter=TableNo eq 50510
PART 2 (Link here when live)
If you were hoping to see more AL you will be disappointed, part 2 will entirely be NodeJS and will focus on how to make the CopyCat server call Business Central APIs and subscribing to webhooks.
Part 3 will be having it run on a Raspberry Pi plus a bit of performance testing on some real server-grade hardware. It will also include a link to GitHub with the working prototype (all AL and NodeJS code) .
So stay tuned!
