How to get all fields name list of tables in Business Central


In a recent blog post, I was asked how to get the list of all field items in a table. So I will show you some tips. To answer the question first, you can specify the page number as an argument to the URL, so you just need to specify Page=9806.
All you have to do is remember this Page9806, but in reality, there are quite a few things that you can’t remember when you need it, so I will show you how to download the list to Excel along with tips for identifying the page number.

To get the list of table items, it is better to use the ‘Permission Set’ list page. Search for the ‘Permission Set’ function and display it in the list page, then click on the ‘Permission’ button. You can use an existing Permission Set, but I recommend you to create a test permission for investigation.

A ‘Permission Set’ is a collection of permissions. You can define which tables have what permissions, and so on. Thus, you can specify a table. We will use this. If you open a permissions card page for a new Permission Set, the default Object Type of first line is ‘Table Data’. button on the Object ID column.

Then, a list of tables will be displayed. (Technically, it’s not a list of tables, but since we get a list of tables, we won’t worry about the minor differences. I want to download this list, but I want to know the object information of this list itself. Click on the “?” icon in the upper right corner and click on ‘Help & Support’. (If you just want to download the list, you can do that from this page, but we’ll take a little detour.)

Then click ‘Inspect Pages and Data’.

Then the table that is the source of this list and the page number of the list will be displayed on the right side. This is a very useful feature that can be used on all pages, not just this one.

The list page can be called directly by specifying it in the URL. Add the page number to the URL in the format “https://businesscentral.dynamics.com[TenantID]/[EnvironmentID]?Page=xxxx”. Type the URL and hit enter key.

Then, instead of the pop-up style, the table list was displayed as below. You can download the list by selecting ‘Open in Excel’ from the ‘Page’ tab. If you select ‘Open in Excel’ from the ‘Page’ tab, you can download the list. (Actually, you can download the list in the pop-up style as well.

Now I got The list of tables , but the list of table “Field” items are not here. We can get table field items on permission set card page. Specify the Object Type as Table Data and the Object number as whatever you want. Then click on “…” in the Security Filter section on the far right.

This item is used to divide permissions by the value of a field in a table, so that you can display a list of fields. Click on “…” in the Field Number column.

Then a list of field items will be displayed. As before, use Page Inspection to get the Page number.

Similarly, add the page number to the URL and display the list page. Unlike the pop-up format screen above, all items in all tables are displayed.

The table numbers are not shown on this screen. If you download the file in this state, the table number will not be downloaded, so add the page number first. Click on the gear icon in the upper right corner and click on Personalize.

Add the hidden “Table No.” field to the list by dragging and dropping it.

The preparation is now complete. Select ‘Open in Excel’ from the Page tab to download the file to Excel.

Let’s take a look at the downloaded Excel. The table number, table name, field number, and field name have been downloaded. There are 35,884 items in total. (That’s surprisingly small.)

If you know the page numbers by heart or have developer skills, you don’t need this tip in the first place, but I introduced it because I think it’s a good tips to know that you can get the table list and item list with Permission Set and check the table number and page number with Page Inspection function.

Thanks for your reading.

返信を残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です