Count Records in Tables on Business Central SaaS

Count Records in Tables on Business Central SaaS

Scenario

It might be useful to simply count the records in the tables of a company in a simple way; on Business Central On-premises, we have access to SQL Server, on Business Central SaaS no. Not all tables are accessible and readable in BC SaaS (this applies to both Hidden System tables and some standard tables ex:AllObjWithCaption” table) and you cannot build extensions referred to these tables (that are blocked ByDesign); also you can open a table data on BC SaaS but you cannot count records.

Ex: you can open Table 15 businesscentral.dynamics.com/sandbox?table=15

.. also you can open the “Fields” Table, but you can’t see the numbers of records contained in the tables.

https://businesscentral.dynamics.com/sandbox?table=2000000041

“Count Table Records” extension

How-to count records in tables in simple ways?

To achieve this goal there are several ways; I propose also a little and simple extension created for this.

You can achieve this goal in many methods:

  • With Rapid Start Services (making a Package with all tables and after filter record > 0); Rapid Start can read all hidden system tables (ex: “ALLCaptions”, Objects etc.) but you can’t create an extension with these tables.
  • With Dynamic Web Services (reading from C#, Python etc and from a Temporary and Dynamics defined WS)
  • With a simple extension (to read tables, cont records and write records counts in a table)
  • …Other ways… ??

About the “Count Table Records” extension

This simple extension check if one or more table have data, the application read all tables from Business Central SaaS with data and write results of count in a table, can be useful for go-live scenarios to check setup configurations.

You can run this feature in each company and after you can copy\paste results to Excel.

Code

You can find the simple solution here:

https://github.com/rstefanetti/AL-Samples/tree/CountRecords

https://github.com/rstefanetti/AL-Samples/tree/CountRecords/src

#COUNTRECORDS #BCSAAS #TOOL

Leave a Reply

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

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