Dynamics 365 Finance & Operations data management with Microsoft Office Excel

Introduction

In this blog post I will have a break from posting about CDS 2.0 share some thoughts about data management using Microsoft Office tools for Microsoft Dynamics 365 for Finance and Operations. Don't fret though, more CDS 2.0 posts will flow your way in the coming days.

Preamble

When meeting many customers and prospects who want to digitally transform their business using a Microsoft Dynamics 365, one of they key common concern is about data migration (from legacy systems) and data integration (with 3rd party systems such as banks and suppliers). As a matter of fact, Microsoft Dynamics 365 is a flexible, open, yet secure platform which provides multiple tools which can be leveraged both by IT Pros (without any development skills) and by developers. Dynamics 365 for Finance and Operations provides the oData service which is based on Data Entities and the REST metadata service which allows you to externally query data about the data (i.e. data model). You can also expose logic in classes as JSON or SOAP web-services. It is worth noting that all these tools respect the security authentication and authorization principles and happen at the business layer (not the data layer). This means that data is exported and imported intelligently with the capability to execute (or optionally by-pass!) business logic on both the data-entity and data dictionary layer. 

Of course, one can also find additional ISV solutions on Microsoft AppSource which add more value when it comes to data integration. In this blog post I will mainly show screenshots revolving around Purchase Order import/export which is a very common scenario in Procurement to Pay processes.

Microsoft Office Excel Integration

Microsoft Office Excel integration is based on data entities. Out of the box, Dynamics 365 for Finance and Operations provides many entities which cover various functional areas and business processes. The integration architecture also relies on the "Excel Add-in" being installed to your Microsoft Excel App. Details on how to install this can be found here. 

The beauty of the Excel integration is that you can generate intelligent import/export templates either from scratch or based on a template you generate from within the ERP interface. This, of course, is over and above the possibility to use the standard templates. When using a standard template you would need to manually transform any Excel data exported from other systems into the template format generated by Dynamics 365 FinOps while when using a custom template this is not required as you can design the Excel Template based on your specific design requirements or maybe a format specified by your vendor. The only constraint is that when you have a header/lines scenario, any header fields must be mapped to a cell while the lines must be mapped to columns containing multiple (1:N) rows of data.

The standard OOB templates can be access from the Microsoft Office logo found at the top right of every form, often referred to as the "Open in Office" menu.

This gives you the possibility to either "Open in Excel" with advanced designer and publishing features or simple "Export to Excel" which is a one-way export from FinOps to Excel.

The Excel Workbook Designer

In order to build your own custom templates without the need to write any code, you can browser to Organization Administration > Setup > Excel workbook designer or simply search for the name in the search bar. You will be presented with a form showing a list of data entities which can be used in such an integration: both OOB and custom.

To design an excel template you can use the selection boxes on the right-hand side of the form. When you are satisfied with the field selection and ordering, you can click the "Create workbook" button which presents you with a slider to be able to download the generated template. When you open the file it will automatically launch the Excel add-in side bar (if already installed) which gives you a myriad of advanced features such as the ability to make changes to the design and to publish data back to Dynamics 365 FinOps using the flexible data entity framework which works at the business layer and gives you the ability to use or not use staging tables and execute business logic upon import. 

You also have the possibility to create an Excel Template from scratch. In that case you would use the "Create blank workbook" button where you can start with an empty Excel file template.

This allows you to create templates from scratch and give them the design and appearance you want (including any images) such as the excel file below:

Now, to be honest, the designer is a bit quirky to use sometimes. For example you must remove a cell-mapping before you can move it to a new position and it is not formula-based, so anything you want to do must be done from the designer. The designer provides you the capability to join multiple data entities in order to achieve the header/lines scenario shown in the screenshot above. This can be done by using the "Add table" button within the designer which then provides a lookup to the data entities available.

Once you get the data from FinOps you can add records or do updates to existing records. You can use the Publish button to send the data back into FinOps.  The framework will recognise a create of a new record from an update (commonly referred to as an Upsert) so data integrity is enforced. As already discussed, any data integrity or business logic validations within FinOps are enforced and returned to you if there is a failure. If you are on an appropriate development environment you can also debug the insert/update logic by attaching Visual Studio to the usual IIS process (as you would do if you are inserting records from a form) to troubleshoot and find out the reason for a particular data publishing failure.  

Document Templates

Once you have finished designing your custom Excel template you have to make it available in the "Open in Office" menu. This can be achieve by using the Document Templates form which can be accessed from Organization Administration > Setup > Office Integration. 

You can now click on the new button where you will be presented with a slider which allows you to browse for your template which consequently gets uploaded. There you will also get some options concerning company, country/region and language specificity. After you click ok and get the new record ensure that the field "List in open in office menu" field is ticked to make it visible on the relevant forms which have this table as their data source. You would probably also want to tick the "Apply current record filter" and "Apply current company filter" for transactional data. My custom template in the screenshot below ends with "...Import-Export-Demo".

This is all you need! If you now browse to the relevant form with the relevant data source you would automatically get your custom template in the drop down beneath the "Open in office" > "Open in Excel" menu. My custom template in the screenshot below ends with "...Import-Export-Demo". Please note that the "Open in Excel" sub-menu could also be extended programmatically using Visual Studio as described here. 

That's it for this blog post! I hope I have inspired you with how you can achieve flexible and powerful data integration to the standard FinOps data model without writing a single line of code. With all the integration possibilities provided by the whole Dynamics 365 platform coupled with the exciting add-one can find on Microsoft Appsource, I firmly believe we arrived in an era where we do not tell our customers how they achieve their integration goals but they are now in the driver's seat to design and pick the best architecture which suits their business requirements!

If you have any questions or comments about any of my posts or anything related to Microsoft Dynamics 365, do not hesitate to get in touch with me on mbonello@bluefort.com.mt