Do you ever find yourself wishing your journal entry form had the ability to export and import using excel, but you don’t have the data entity for it?

You don’t want to go through all the work to get that new data entity added, just to be able to get through this one journal entry, even if it is a few hundred lines.

One new release that Microsoft Dynamics 365 introduced in October 2020 titled “New Grid Control” allows for some new easy data entry options form.

While users have always had the ability to export data from grids in Dynamics Finance and Operations apps to Microsoft Excel by using the Export to Excel mechanism, the task of importing data back into the system isn’t always so easy or readily available. This system limitation has forced many of us to manually type in entries, sometimes more than a hundred lines.

Within the newer “Grid Capabilities” feature we can finally copy and paste rows of information from excel directly into the Finance and Operations interface.

If you have a table you are trying to populate, for example, many lines to be entered on a transfer journal, you can create this in an excel document, copy, and click into the first cell needed in your F&O Journal window- and paste the information back into the journal lines screen.

It may take a few minutes for the system to catch up with the number of lines (you will see a thinking wheel and italicized text as they load). Once the system has finished thinking through the lines pasted, you can save, validate, and post, just as if you had manually entered all the lines, one by one.

Steps to Copy and Paste into F&O:

1. Create the journal that needs to be and pasted into

2. Recommended: Create one line to make sure that all required fields are determined.

3. Export All Rows to Excel

4. Create as many journal lines as you need to copy and paste, within excel. Making sure to include something in all required fields.

5. Copy and paste all data rows that will need to be pasted into Dynamics F&O.

6. Starting in the first column cell (shown as date below) – highlight the date and paste in your copied data.

7. You will get a pop-up asking if you want to save your changes to the table upon pasting. You must press save to allow the pasting to happen.

8. The system will load each row of pasted data. Any rows being processed are shown by a loading “circle” to the left of that first column (shown as date above).

There are some natural limitations around this process:

• For the data to hold in the lines you are pasting, you must have something in all the required fields that screen requires. This can often be determined by going into the form and adding a line. Required fields are often shown by a red asterisk indicating that something must be in that cell for the data to “hold”

 

• If the number of columns in the copied table exceeds the number of columns that remain in the grid, starting from the paste location, the user is notified that the extra columns have been ignored.
• If the number of rows in the copied table exceeds the number of rows in the grid, starting from the paste location, the existing cells are overwritten by the pasted content, and any extra rows from the copied table are inserted as new rows at the bottom of the grid.

This is a nice addition from Microsoft that allows for the features of Microsoft Excel, without having to export and import back into Dynamics F&O.

Please refer to the video below for a quick walkthrough of the process.