This is Part 1 of my “Data for Dummies” series that will introduce you to all things data in Dynamics 365 Business Central. One thing for certain is that ERPs are just chock full of data. There’s data about customers and vendors, data that your auditors want, data that stakeholders in your organization want analyzed. Everyone seems to want a piece of that data and to varying degrees.
With all this data comes a variety of requirements from all levels of users. Some may want to just view data; others may be tasked with updating and maintaining data and increasingly more common these days is users who want to perform analyses. This article will be primarily focused on the “maintenance” side of data, that is entering new records, updating records, and extracting information. Primarily looking at customer, vendor, item, asset, and general ledger account records but also dipping into more detailed “how the sausage is made” information such as inspecting pages, interpreting table IDs and some general tips.
Starting With the basics
So, just what are the fundamentals? It starts with Master Data records, which if we used an analogy of say a house would be the foundation. Transactional records such as Invoices, Orders, Receipts and more would be all the fixing and furnishings.
Master Data is any “Subject” or “Object” that a process in Business Central depends on. If you create a Sales Invoice, you’ll need Customers. If you want to track depreciation you need Assets and so on.
All this information is stored in a table but conveniently is visually represented in both a list page and a card page.
List page – This shows multiple records, below you can see my three vendors:
Card page – This provides a detailed look about a specific record such as my first Vendor:
Most commonly I see clients utilize the Plain Jane, John Doe method of handling their business data. This is simply put just doing it manually record by record. You would go into an Item Card and manually change the data as needed – in the case below changing the Vendor Name:
Not always the quickest but it’s rather easy, accurate and the least intimidating method. Once the change is made the system automatically saves it (you should see a “Saved” message in the top right-hand corner of the window) and any future documents for that vendor will utilize the new name.
Let’s Get Efficient
What happened when we made that change? If you noticed above, I mentioned that lists and cards are just visual tools. In the back end there are thousands of interconnected tables. When we change a value such as a Name in a card, we are updating just one row in that table. If we want to update more than one row at a time, we have two options, the first is using Excel. I always recommend this method to clients as a good next step. For myself it’s akin to sipping on hot cocoa next to a warm fire on a cold wintery night. Excel is used by every accountant I know and it’s a bit more comfortable working inside Excel versus trying to interpret the seemingly endless Business Central tables.
If you’re an Excel fan Business Central has just the tool. When we’re within a list page and we select Page we have typically have two options – Open in Excel or Edit in Excel – some pages in Business Central will only show the Open option but in the case of master records we will have the option to Edit.
If we select Edit in Excel and open the downloaded file, we then get that same vendor list but with a lot more columns. This file will actually contain every single column in that table so what you’ll see here may include tons of columns you’ve never used, may never use and probably haven’t seen before. Another note is that this is a static view of what’s in Business Central. If you make any changes, they won’t have an impact until you opt to send those changes to Business Central.
You’ll also have a convenient new add-in that you can utilize in excel called the Microsoft Dynamics Data Connector. This is an excel add-in built by Microsoft that creates a semi-dynamic connection to Dynamics (almost a tongue twister).
If you at first don’t see the Data Connector on the right side of your screen you can open the Insert tab in Excel and then select My Add-ins and click on the Dynamics add-in from there to open up the Data Connector window.
The data connector is a powerful tool. Any changes I make to fields in the Excel document can then be published and sent back to Business Central. You can make changes to any fields that aren’t marked as read-only. If a field is read-only the data connector will actually let you know the field, you’re working inside is read-only, denoting it by changing the text color to red and adding a little caution symbol with the pen.
When you’re done making changes you just have to select the publish button, additionally you can add new records, refresh the table (this gets rid of your changes and updated the table based on the most recent data in business central) and you’ll notice there’s also a Filter and Design button. Filter and Design functions are a bit more advanced and won’t be covered in this article, they let you add multiple tables and specify certain filters as well.
If you do publish your changes, you’ll see a few messages at the bottom of the connector pop up and at the end you’ll hopefully receive confirmation they were successfully published.
If there are any errors, you can click on the flag to the right of that message, and you’ll see a list of error messages. Honestly, I find them a bit sparse with details, but it will direct you to the row in question that has an error. Usually, I see clients try to make changes to read only cells and clearing those cells generally fixes the errors so just double check the data you’re uploading or changing, and you’ll be set.
Other General Tips
Lastly for this part I just wanted to add a few notes on some tips that I’ve helped clients with in the past. If you setup a filter on a page (any page) and then use the Page > Open in Excel or Edit in Excel it will respect the filters you have setup on your page. Furthermore, many clients ask for specific reports but sometimes the best tool is to dig into your ledger entries, use some clever filters and “Open” that page in Excel.
For example, want a list of Overdue Customer Invoices? Well, if you open the Customer Ledger Entries you can create a filter for Open documents, Invoices and maybe some specific Customers and then save that filter for future use. When you go to Page > Open in Excel it will not only respect the Filter but will also respect the column layout (whereas the Edit in Excel doesn’t).
Another good tip I utilize myself quite a bit is using special characters in search criteria. Business Central is able to handle special symbols and basic math expressions. Commonly used symbols are:
| – This means either/or. I can specify for example Vendor 1 OR Vendor 2 like so:
* – With this character I can search for an unspecified number of unknown characters. If for example I wanted all Vendors that started with Number V1 but didn’t know what characters came after V1 I could use the * character like so:
I can even use special characters in tandem together:
Other special characters include & for AND statements, greater than, less than, equal to (<, >, <=. >=) and searching for one specific character with ? like below:
It’s a good practice to play around with these special characters and get used to how they function as it can help immensely when you have larger lists of data or you’re digging through your general ledger.
If you’ve made it this far, you’re now one step closer to becoming a Data pro. I always find it best to start with the basics before digging into the intricacies of a big ERP system. With the next part of this guide, I will dig deeper into the more nuanced and detailed methods of working with data.
