As with each of the first 3 entries in this series, we’ve focused on the release of SmartConnect.com and the best features available to users. The goal of any feature that exists within SmartConnect is to improve the experience for the user by solving a problem or task that would otherwise prevent the solution from working. This is part 4 (part 1, 2, and 3 are already available) of the new blog series where we are looking at different sets of features that are key parts of SmartConnect.
This week we are focusing on the built-in Excel capabilities included with the add-in and the Excel templates. The Excel add-in allows users to run any integration they have setup and pass in the data within their workbook as a source. Additionally, they can retrieve the data for any integration they have configured and pull it into their workbook as well. The templates all give an updated user interface within Excel that pulls data in from whatever system they are connecting to.
The Excel Add-in is available for both on-premise SmartConnect and SmartConnect.com. A user can install the add-in and configure it to work with their SmartConnect instance. The main feature is running any pre-existing integration that has been setup within SmartConnect. The data source could be Excel originally, but it doesn’t have to be. The add-in could run an integration that was originally SQL to Dynamics 365 Business Central as long as the column names in the Excel workbook match the original data source. To assist with that, a Get Columns button has been added to create the columns you would need to run the map from Excel.
The setup for the add-in consists of connecting to either SmartConnect.com or on-premise SmartConnect, and then filling in the details to allow the workbook to run an integration on demand or to return the data source from an integration. The Get Data feature in the add-in lets the data source from any integration – SQL, CRM, flat file, Business Central, etc. – and then bring in into a worksheet. Additional filtering can be done on that data as well while it is brought in.
There are multiple Excel templates that have been built already on our site that allow users another way to enter data and run integrations without leaving Excel. These have a bit more coding built into them and each template is designed to work with one integration (where the add-in could be setup to run with any integration). The benefit is that you can have specific data pulled into Excel and then format the UI for the end-user. A specific example would be the Excel Sales Invoice template for Business Central: The setup involves connecting to the BC system so the customers, items, locations, pricing, etc. can all be pulled into Excel. Then the user has an interface to enter both header and line details for the sales invoice before clicking the Run button to push everything off to SmartConnect to process.
Utilizing the Excel add-in and the ever-growing collection of Excel templates helps simplify the integration process for end users. If you have team members that work primarily in Excel, the add-in and templates allow them to remain in Excel and perform almost all their integration work directly from there.