Copy & Paste Journal Entry from Excel

This is my fifth post in the Did You Know series, tying into Pam Misialek's series on Inside Dynamics GP. Today's topic is the Copy-Paste from Excel feature for Journal Entries in Dynamics GP.

Summary

URL: https://community.dynamics.com/blogs/post/?postid=e51c226e-7e81-4187-8dac-08562e9d60cf

Version: available since Dynamics GP 2013 R2

History

If I'm not mistaken, this feature first came to light before it was available in GP core, when then-MVP Mohammad Daoud posted a blog about creating a copy-paste add-in. At the time, his company had a free and paid version of this and still does.

I hadn't heard of or seen that type of functionality before then, so please comment below if I am wrong about who came up with this first. Shortly afterwards, GP 2013 R2, it was introduced as a new feature in Dynamics GP.

Somewhere in there, before or after I'm not sure, there are at least two other ISVs with copy/paste products with varying functionalities that may be worth checking out: Titanium GP and Infinia Integrator (link removed, no longer valid).

Full disclosure: I have not tried or used any of the 3 ISV products above, and this blog post is purely covering the out-of-the-box Dynamics GP functionality.

How To

Here's how to use this feature.

• Open Excel. Make a quick template with the four fields in the correct order (five if you use AA):
  • Line Item Description
  • Account Number
  • Debit
  • Credit
  • Alias (if using AA, otherwise there is no 5th field)

Note: use whatever headings you want. You don't copy/paste the headings from Excel, they are there merely for your benefit.

A quick tip: if you forget the order of the fields for the template, just open the Journal Entry window and hit F1 to open Help. Click on the link in the help for this feature and it shows you right there what the format is.

Note: the Help is wrong in its explanation of the Description field, at least the way I'm reading it. This field is actually for the "Description" field, which is the line item description on a journal entry. The help document describes this as the "description of the account", which should say "description of the entry".

• Open the Transaction Entry window in Dynamics GP. (Transactions > Financial > General Entry)
• In Excel, copy the data to be pasted using CTRL-C or whatever your preferred copy method is.
  • Ensure you are copying all 4 (or 5) columns of data even if the Line Item Description is blank.
  • Do not copy the headings or blank rows beneath the data
  • Copy only one journal entry at a time.
• In the Transaction Entry window, click on the Paste button in the toolbar.
• Finish the details on the journal entry header - batch number if your company requires it, data, standard or reversing, and reference. (you can do this before pasting or after, it doesn't matter)
• Save or Post

Notice the line item descriptions in mine match what I copied from Excel, proving the help file is a little misleading in its definition. In this example, I opened the window and clicked Paste when my cursor was still on the Journal Entry Number field, and it worked just fine.

Note: I read a handful of blogs while researching this one, to find other ISV solutions and some history. There are a few articles that indicate you must do things in a certain order or place your cursor in a specific spot for this to work. I tested this for blog purposes on GP 2013 R2, and have found none of that to be true.

1. The data will paste no matter where your cursor is!
2. You don't have to fill out the journal entry header info before you paste, it makes no difference. (You *do* need to fill it out of course, but you can do that after pasting if you wish).

Troubleshooting

Here are a few common things that could go wrong.

You get a Not Privileged error when pasting

This means two things:

• There's an error. 😄
• The user doesn't have access to the report.

If you upgraded to GP 2013 from an earlier version, chances are the new report isn't in any of the security tasks. On a new install, this report is included by default in task TRX_FIN_001* so that is a good place to include this report if it isn't already there. It's under Microsoft Dynamics GP > Reports > Financial in the task setup drop-down lists.

Once you have security access to the report, try again and go to point number 2, which is your next problem! 😄

2. You get the Validation report as soon as you click on paste

If there is an issue with the journal entry, you will be prompted to view the "GL Transaction Paste Validation" report. I don't have AA installed to test what validation occurs with that; however, without AA, it's only validating that you are using GL accounts that exist.

In this first example, once I open the report up on-screen, and the error is in straightforward language (although, there is a typo, oops!). This is an example of a legitimate "account does not exist" error.

In this second example, you can tell from the multiple errors that something is off. I forgot to COPY all 4 columns of data and it looked at my first column (Account Number) as Description and then tried to use Debit as Account Number. Every failure is a dollar amount pasted in the Account Number field. This report would look quite similar if you got the order of the columns that you copied from, as it is expecting the data in the exact order specified above.

Otherwise, that's all there is to it. If you want to paste multiple entries, include Intercompany data etc., then look to the various ISV solutions that may have more functionality to suit your needs. If you use a third-party product that has an alternate Transaction Entry window, the paste may also not work, depending on what the alternate window layout/changes are.

This functionality is pretty basic, but it's free, it's quick and it works like a charm with no pre-configuration required!