12 Sep Dynamics 365 Business Central and Excel
Microsoft has conducted studies and estimated that 40% of the loss of time in the workplace is motivated by the constant exchange between applications. It is for this reason that the strong bets of Business Central are and will be based on the interaction between the ERP and the rest of the products of the Microsoft suite.
When we say that Business Central is a product of the Microsoft Suite, we mean that it is one more application that is found in the Office 365 login.
In this article we will see a couple of examples of interaction between Dynamics 365 Business Central and Excel.
Business Central makes analysis and work with financial data simpler with integration with Excel, allowing you to easily extract data from any list, so you can quickly analyze the data. In addition, we can do this analysis with the Excel tools that we are already used to.
Case 1: Create a report of customer movements
We will create a report of customer movements, which shows us sales per customer.
In all the pages of lists we see that the option of “Open in Excel” and “Edit in Excel” appears. We click on “Edit in Excel”, download the list of customer movements and when we open it in Excel we will see that the Microsoft Office add-in is loaded.
This creates a connection between our data and the controls of our ERP. By opening Excel, we can format and update the data. We will also have the possibility to republish this data directly in the ERP.
Once we have the list loaded in Excel, we can use the tools that Excel provides us in a standard way to make graphs and reporting.
In this example we want to see sales per customer, therefore, we will create a pivot table and drag the fields to create the graphics we need.
As we see the following image, we place the date as a row, the customer number in the columns and the sum in the values. The customer movements page contains all the client documents and in this case we just want to see the invoices. For this purpose we will put a filter in the dynamic table of “document type”.
We can also give it the format we want, for example, that the euro symbol appears. In this easy way we have been able to create a report just a few minutes with the data of our ERP.
We can even create a graph directly with this data. As time passes, this data can be updated from the same Excel, without the need to export the document again. Simply clicking on the “Update” button refreshes the source data.
Case 2: Edit and publish data directly in Excel
Let’s now look at another example in which we want to directly edit and publish data in Excel. We access the customer list directly from our Role Center.
Imagine the example that one of our sellers has had a promotion and therefore will not exercise the role of seller. How can we change all the customers we have related to this seller?
If we have few customer records, assigning them to another vendor is obviously going to be a simple task. But when we have tens and hundreds of customer records, we will surely want to automate this action. The first thing we will do is export the customer list with all the columns.
We will follow the same process as in the previous example: we open the document and enable editing so that the Excel Add-in is loaded. Then we look for the column that contains the seller’s code. In this example, it corresponds to the Letter O column, in which we see “JR”, “PR” and empty cells as sellers.
Suppose that “JR” is who represents the seller with the promotion and we replace it with a seller that is going to be called “OM”.
Another noteworthy aspect is that the publication process also includes data validation. That is, if the data we are trying to publish is not correct, an error message like the one shown in the following image will appear.
After publishing the error appears that the seller “OM” does not belong to our database. This means that you are not registered as a seller in our Business Central database. We correct the error and replace it with the seller “MS” that is created in our database… We publish and thus the errors will disappear.
When Excel indicates that the publication has been completed, we will return to Business Central and we can see that the changes have actually been made. To do this we will have to refresh the browser to visualize the changes.
To continue with this example, we see that the customer list does not have the vendor column shown. Quickly the same user can create this basic customization. We look for the column of the seller code and drag where you want to view it. Once we accept, the changes are applied for that user.
To finish the example, we see that we actually have the seller “PR” that was the one we had left unmodified, and all the rest have been assigned to the seller MS. Therefore, we have managed to publish directly from Excel. And with an error control, it will be essential not to make mistakes while we are working directly from Excel.
También te puedo interesar: