In this post, “Playing Nice in the Sandbox”, our own local New England tech guru, John Dooley, raises the question – How Can you use on premise Microsoft Dynamics ERP software with Office 365?

Office 365 offers a range of services from to email via Exchange, to Lync to SharePoint Online in the Cloud. What is Office 365?

It all started when John saw a simple demo of expense management in Excel 2010 with SharePoint Online (Office 365).  But asked, how do you pay the employee for the expense report? With Dynamics ERP software (for example Dynamics GP). This is the tool to use to manage AP invoices and pay employee expenses. But how can you enable that expense report “in the cloud” with an on premise Dynamics GP system? And how can we get from the online version of SharePoint in Office 365 to Dynamics GP?

He solves this dilemma with two simple techniques to “bridge” between SharePoint Online and his Dynamics ERP on premise. According to his post:

  1. The first technique is to use Excel both as a tool to complete the expense report in the cloud and as well as a conduit to my intranet.  Once I open an Excel workbook from SharePoint Online on my local machine, I now have access to resources behind the firewall, thus providing an easy and secure way to access to local resources like a web service that can link to my on premise Dynamics ERP without all the complexity of the sandbox Silverlight callouts.  Basically thru bringing the document and data on premise thru Excel.
  2. The second technique is to leverage SharePoint Online workflow and email integration.  By triggering a SharePoint Online workflow via a task approval event in SharePoint Online I can have workflow send email to an on premise process or folder in a SharePoint on premise server that enables me to action the expense report at a data level where information like Vendor Id, Document Number, Invoice Amount can move from the spreadsheet to/thru a process to a web service that handles the create/post to Dynamics ERP.  This technique uses the DIP (Office Document Information Panel) to move values from Excel into SharePoint Online.

Watch John’s demo of the 2 scenarios to Connect Office 365 SharePoint Online and Dynamics ERP.