BIDA0047 – BC 2023 Excel Dictionary Demo

0
55

Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.


(Note. We have swapped my photo for the cartoon character. I hope you like it!)

Hello and welcome to our latest blog post.

I am really pleased you have come back to see our latest video!

I am Sofi, your BIDA AI Assistant.

I will be reading this blog post for you today, on behalf of Mihai.

I am really pleased to be able to bring you some news that I think will really interest you.

This video is intended for companies who have Business Central 2023 installed on premise.

This video is a follow up to the prior video demonstration of the Navision 2018 Excel Dictionary database.

The obvious question that arises from prior demonstrations is this.

“If we have access to so many tables and fields in Business Central, how do we make it easier to find those fields because we can’t possibly remember them all.”

And that is where today’s video comes in.

If you are wondering how, you might be able to more easily search all the fields available in Business Central, then you should watch today’s video.

So, as ever, on with the demo!

Download Dictionary

Demonstration

Here we are in a simple Excel spreadsheet.

This spreadsheet has two sides to it, the left and the right.

You can download this spreadsheet using the button on the blog post.

On the left-hand side is a table of categories.

These at the same categories you saw in the prior demonstration.

They are drillable so you can immediately understand that I can click on a drill, and it will open up with the tables.

So, let’s drill on Financial Management.

Please note that the very first time you click, Excel will load the power query model, so it will take a second or two to respond.

Now you can see all the tables that are listed in the Financial Management Category.

We will scroll down to the GL Account table.

As you would expect, when we click on the GL Account table, we will get all the fields in the GL Account.

As an Excel user the rest would be obvious to you.

Since we would love you to download the workbook and play around with it yourself, we won’t go into too much detail of this simple drill down.

All the categories, all the tables, all the columns are there.

If we ever get good help text and descriptions?

Then we will include them in this workbook.

Now we will scroll back up to the top of the workbook.

We will close the Financial Management Category.

Please note at the bottom of the categories list we have two new categories.

They are.

One.

Ninety Eight, system tables.

And two.

Ninety nine, not in category.

In the prior Navision dictionary we excluded system tables.

We now think that it’s useful to have them in the dictionary.

For the Not in Category Tables, we have not yet gone through and moved these tables into categories.

If you think you have a good suggestion for what categories what tables should go in?

Please feel free to use the comments below or contact us and let us know your proposals.

Over time we expect to be able to move all the tables into a suitable category.

We know this is a matter of opinion and some people will have differing opinions.

Now we will show you something we think is really pretty cool.

Excel drills and searches are implemented in this workbook.

So, next to Table there is a drill.

I will click on this drill.

Now we can see there is a search option for all the tables in the dictionary.

I will enter G L and it will search for all the tables with G L in the name.

I will click off all “select all search results” to have no tables selected.

Then I will scroll down and select the GL Account table again.

When we click on the GL Account and click OK, we will be returned to the report.

The GL Account table is now selected but it may not be visible in the table.

You might have to click on Financial Management again to have GL Account visible.

Once you do that then you can click on GL Account to make the fields in the table visible.

Please note we have also numbered the columns in the order in which they appear in the tables.

Just like any other Excel report you might create; the dictionary is fully searchable in this table.

Now, we will clear those selections.

Now I would like to draw your attention to the right-hand side of the report.

You can see there is a bubble saying, “search a table”.

This provides a second way of searching for a table that you might like more.

You can see GL Account is selected.

In this search bar you can delete and type in your search term if you wish.

We recommend you download the spreadsheet and give it a try because it’s quite a new idea.

We have not seen this idea before.

What I will do is go to the search text and enter just GL.

I will again select GL Account.

Now it will show me what category GL Account is in, and it will show me all the fields in GL Account.

In this way you have a second way to search for tables using this filter and drop down.

We thought that in this first release of this workbook we would give you both options.

Please feel free to let us know which one you prefer in the comments section at the bottom of this blog post.

Of course, the value of this spreadsheet is this.

As you are creating queries on the Business Central database using SQL, you can refer to this spreadsheet.

This will help you to easily find the tables and fields that you are looking for.

Then, when you search the spreadsheet and know exactly what fields in what tables you want, you can more easily locate them in SQL Server Dictionary.

Also, the actual SQL names are the same as in this dictionary, only with underscores where the spaces are.

In our own development work, we have this spreadsheet on our left screen as we write SQL.

You might want to try that out for yourself.

If you would like to try this out for yourself on your PC?

Then please contact Mihai to book a time on the evaluation machine.

You can also download this spreadsheet from the button on the blog post.

You can try for yourself how easy it is to query data directly from Business Central using SQL while also having this dictionary available to you.

Further, if you would like one of these spreadsheets for your company to help you query your Business Central system?

We will prepare this spreadsheet for qualified Business Central installed companies.

So please feel free to contact me to see if you meet our qualification criteria.

This report takes a little while to create.

So, we will only create a limited number of these reports as “freebies”.

In Summary

So, to summarize what I have shown you today?

In our prior video we showed you that it is now possible to perform direct query to all data in your Business Central system using the Meta5 query tool and SQL.

However, because there are so many tables and so many columns it’s really handy to have a searchable dictionary right there on your left screen.

We created this report to be a searchable dictionary for any customized Business Central system.

It provides two ways to query what tables and columns you have available.

On the left-hand side, it allows you to drill down or to use the excel search and drill functions.

On the right-hand side, it allows you to search for tables containing the search string.

We are finding this very useful in our own development work.

We think you will find it useful too, even if you don’t have Meta5.

With that?

I would like to say thank you very much for watching our video today.

I really appreciate your time and attention.

We hope you found this blog post interesting and informative.

We would love to hear your comments, so please feel free to use the comments section below.

I wish you a great day.

Thank you.

Sofi.

Your BIDA AI Assistant.

Download Dictionary

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.