Advanced Methods for Working with Your Data in Dynamics 365 Business Central

In a recent article about utilizing data in Dynamics 365 Business Central we covered a few topics such as editing master records manually, using the data connector to update records in Excel and some general tips and tricks for sorting through data. If you feel you’re already a master of the basics then continue reading but if you want a quick refresher you can find our first article on the topic here.

In this article, I will be focusing on more advanced methods for working with your data in Business Central. While the manual method is accurate and the Edit in Excel method can be quick, Configuration Packages are the most robust way to update, add and export records from Business Central. After this article you’ll be the unofficial Business Central pro in your office.!

Configuration packages (a.k.a RapidStart packages) are an extremely customizable tool where a user can identify tables in the system, export data from those tables or even upload new data. These packages can be saved and uploaded to new Business Central environment or companies. 

Consultants use these packages to perform a large amount of configuration so in the Dynamics 365 world these packages are the proverbial “how the sausage is made” tool of choice. Not only can this tool handle large amounts of master data records but we can also use it to load in batches of transactional data. 

You can view a list of current configuration packages and create new ones from the Configuration Packages page: 

If I was to select one of those Configuration Packages, for example the one labeled VENDOR you can see that there’s quite a bit going on.

Product Version, Language ID and Processing Order aren’t required fields and aren’t really used in the large majority of situations we’ll encounter but I do recommend enabling the toggle to “Exclude Config. Tables” as we won’t want to be working with any data that isn’t required.

You can see in the screenshot I’ve identified a Vendor table and the Vendor Bank Account table with the IDs of 23 and 288 respectively. When creating a Configuration Package identifying these Table IDs is generally one of the first steps.

Every single table in Business Central is identified with a Table ID in the background and conveniently this is visually represented to us on the pages within the system.

If we ever need to identify a table ID, the easiest way to go about determining the ID is to open that page up and inspect the page. To do so either use the shortcut keys of ALT + CTRL + F1 or you can always click on the help icon in the top right, select help and support and then select “Inspect pages and data”

This will open up the Page Inspection windowpane which itself contains a host of information. You’ll notice there is both a Page and Table cell that have different IDs, we want to focus on the Table cell (ID 23). The Page ID (27) is used when setting up web services but that’s a separate topic altogether.

You’ll also notice that there are three tabs in the Page Inspection window; Table Fields, Extensions and Page Filters.

Under Table Fields you can view a list of every column that is contained within the selected Table. There are often dozens of columns and we can search within this window as well. This is useful when we want to personalize a page and add a column that is visually shown to users. Given that the columns exist in this windowpane it’s then rather easy to add it to the page but sometimes this does require a customization (not every column listed here can be added to the page out of the box).

Under the Extensions tab we can see a list of extensions that are enabled within this page. If for example, we at Catapult have added custom fields to a page you would see the app extension displayed here. Typically, you’ll see just the base Microsoft extensions here including the version.

And the last tab is the Page Filters which contains the filters that have been enabled on this page.

Every page in Business Central can be inspected this way and when it comes to troubleshooting, configuration and testing this is always a good place to start.

Now that you know a bit about configuration packages and how to identify the ID of a page the next step is utilizing them. With this information we can make routine data entry a breeze.

Let’s continue with the example presented above, using that VENDOR package, say we wanted to upload a batch of new vendors. When we know that Table ID the mundane task of adding a batch of vendors will now be a bit less mundane.

Before we export the package to excel, we first want to look at the Tables section of the Configuration Package and get an understanding of what each column means:

• Table ID: We discussed this above, this is a unique identifier for the Table
  • If a message pops up when entering the Table ID that there are related tables, you can just select No
• Table Name: A description of the Table Name
• Parent Table ID: If there is a parent table it will be identified here
• Data Template: If there are data templates for Vendors and we want to apply it to uploaded records we can specify the template here.
• Dimensions as Columns: If we would like to have our dimensions shown as a column, we can toggle this on.
• Delete Table Records Before Proceeding: A toggle where we can opt to delete the entire table before adding the new records.
• Columns: The Configuration Package will also show you the number of package records being uploaded, fields available (all the columns), fields included (we can modify what columns we want to include), what fields are validated, any errors in the upload and the records inside the database.

Overall, you can see there are 80 fields available which is quite a bit and not every field is needed. If we click on the “80” in the No. of Fields Available column and drill down into that field, we will then get a list of all the columns in the Vendor table.

This shows us all the columns included in our package that will be included in the export and also all the fields that will be validated (that is they are checked before upload to confirm they match a set of options or meet requirements such as preventing duplicate No.s)

If we want to change what columns are included, we need to first select “Edit List” and then change what fields are included by selecting or unselecting the box with the check mark. You can also select Clear Included to clear every field and then add fields as needed.

Once we’ve identified our columns, we want to export we can then select Package > Export to Excel and that file can be opened.

It will contain a list of records from the system. We can either add new records on top of the current records or overwrite the records in Excel, it all depends on whether “Delete Table Records Before Proceeding” is selected or not.

In this example I’ll be loading new vendors:

All I have to do is add them to excel, fill in the required columns, save the file and then upload the package back to the Configuration Package. Note, don’t remove any columns, only add new rows to the table.

Once the file has been uploaded the No. of Package Records field will update with the number of new records:

The last step is to select Functions > Apply Data:

And just like that I’ve uploaded 3 vendors:

There are numerous use cases for this, above was uploading vendors but you could use this to upload a batch of purchase/sales invoices, customers, items, assets, G/L Accounts, Journal Entries and more.

We can even utilize filters within a configuration package by selecting Tables > Filters

We could in theory then create a journal entry upload for specific batches so if you have an accounting team with many employees and each works in their own batch, they could each have their package for uploading.

Additionally, we can also use it to extract data. Maybe you want a detailed list of all GL entries. All you have to do is identify the GL Entry table (Table 17) and then extract the package.

While there is a lot more to learn with configuration packages this article will provide a great foundation to build off of. Knowing how and when to use the data entry and manipulation methods outlined in these 2 articles will make you the resident expert for all things data in Business Central.