Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Monday, July 28, 2014

GP 2013 R2 – Copy paste GL journals from Excel “with Analytical Accounting Details”

With the release of GP 2013 R2, a nice feature was introduced which covers a widely required need, it is the ability to copy journal entries from Excel into Dynamics GP, General Ledger Transaction Entry. In this post, I want to shed a light on the “Analytical Accounting” part of this feature.

In the following testing scenario, two transaction dimensions are defined which are location and Project. The dimension codes of each transaction dimension is illustrated in the graph below:

Dimension Codes

 

Now the next step is to define AA alias, which is a required field for the “Copy Paste” feature. An alias is created to group the transaction dimension codes combinations to be used when entering the AA details on the GL journal entry window.

In our testing scenario, the possible combinations required could be:

  • Location (Local) with Project (A)
  • Location (Local) with Project (B)
  • Location (Export) with Project (A)
  • Location (Export) with Project (B)

To create an Alias, Go to Cards > Financial > Analytical Accounting > Alias

Alias Definition

Alias Group

Now we will proceed with the required fields of the data set on the Excel Sheet. These fields are :
  • Distribution Reference
  • Account Number
  • Debit
  • Credit
  • AA Alias
Excel - Data Set
After the data set on the Excel Sheet is ready to be entered into Dynamics GP, highlight it (excluding the columns) and click on Copy option. Then Go to GL transaction Entry window, consider the following points before pasting
  • You should fill the header details like (JE number, JE reference, JE Date ….etc)
  • Put the mouse cursor on the first line of the grid, in the Account number field
  • Then, click Paste icon

GL Transaction Entry

When posting the Journal Entry, the General Ledger and AA posting reports will print on the screen, these reports are provided below:

     GL Posting Report

      AA Posting Report

 

 

Best Regards,

Mahmoud M. AlSaadi

10 comments:

  1. Hi,
    We have GP 2015 R2 installed. We can use the Copy and Paste from Excel to GL but only with the functional currency. When another currency is selected, the Paste button is disabled.
    Is this a known issue? Is there a way to make it work? Thanks.

    ReplyDelete
    Replies
    1. There is a workaround solution for the multicurrency, I will try to post another comprehensive article on how to do multi-currency copy/paste.

      Delete
  2. Hi Mahmoud, your post have been v helpful. I wonder do you have the format if you have two or more AA dimensions(AA Alias)?

    ReplyDelete
    Replies
    1. Hello Jess
      It is actually the same format, only one column for the alias. On the other hand, the alias details are defined on Dynamics GP.

      Let me know if you have any further inquiries
      Best Regards,

      Delete
  3. Thank you so much Mahmoud! Will try that once get back to work! Look forward to your new post for multicurrency, it will be super helpful!!
    Many thanks! :-)

    ReplyDelete
  4. Hi Mahmood, I used exactly the same format, but none of the AA details got pasted; do you know what can be the reason?

    ReplyDelete
    Replies
    1. Hello Jess
      Have you created an alias before ?

      Best Regards,

      Delete
    2. Hi Mahmood, yes, we have two, i can manually input it but just not copy and paste.

      Delete
    3. Hello again Jess
      Let me test and get back to you

      Best Regards,

      Delete
  5. Same issue as Jess. Did you ever get back to her?

    ReplyDelete