By AJ Ansari, NAV Product Manager
A key shortcoming with Microsoft’s current data migration tools for importing data from QuickBooks, Dynamics GP or Excel into Dynamics 365 for Financials is that they all focus on migration of Master records, and not on import of any transactional data.
Most customers at least want an opening balance for each G/L Account, and sometimes Net Change by G/L Account by month for the last 12 months (for comparative reporting), and to bring in opening Receivable balance for each Customer, Payable balance for each Vendor, and Inventory valuation and quantities.
These can be done by entering or importing data into the General Journal, Sales Journal, Purchase Journal, and Item Journal. BUT, with no RapidStart for Data Migration yet, and the fact that none of these Journals have an ‘Edit in Excel’ option out-of-the-box, getting transactional data in can be quite a hassle.
What I find curious is that there is an Edit in Excel button on the ribbon on the Batch selection screen for each journal, but not on the actual journals themselves (where it is needed).
So you either need to type all that data in, or you have to get creative. What we’ve done is created and published a Web Service for each of those Journal pages, and did a little reverse-engineering to be able to Publish data from Excel into Dynamics 365 for Financials like you can from an “Edit in Excel” list.
The pages you need to publish from Web Services are 39, 40, 253, and 254 (General Journal, Item Journal, Sales Journal, and Purchase Journal respectively). To help you figure out what default values in each journal are, key in one line in each journal. This way, when you “edit in excel” you will have a cheat sheet of sorts.
Make sure you’ve used the Edit in Excel option with Dynamics 365 for Financials at least once. Open Excel, go to Insert > My Add-Ins > Microsoft Dynamics Office Add-In (from under Office Add-ins), and click OK. Click on “Add Server information”, and in the server information, populate this URL (edited for your tenant id):
Click OK. You may be prompted to reload the add-in; click Yes.
Next, click on the gear icon (as circled in screenshot below), and scroll down to find a field called ‘Company’. Use the Drop-down associated with this field to select the desired company, and click OK.
Next, click Design, then click on ADD TABLE, select one of the Web Services you created and published in Dynamics 365, select the fields you want, and click Done. You will need to hit Refresh to get the new template loaded up in Excel. Save this spreadsheet, and repeat this process with a new spreadsheet for each additional journal.
You now have (4) four Excel spreadsheets into which you can populate historical data and import into Dynamics 365 for Financials. Go to each journal, review the imported data and post.
As a best practice, I suggest you create a batch called INITIAL in each of the (4) four journals and import your data into these batches. It will allow you to easily identify these opening entries when you’re looking at the General Ledger or the subledgers in the future.