• Home
  • About
  • Contact
  • German

Dynamics 365FO/AX Finance & Controlling

Dynamics 365FO/AX Finance & Controlling

Tag Archives: General Ledger journals

Electronic Reporting: Import of GL Excel Journals (Part 3)

10 Sunday Feb 2019

Posted by Ludwig Reinhard in General Ledger

≈ 6 Comments

Tags

Electronic reporting, Excel Import, General Ledger journals

This third and last post on GL journal imports with the help of Electronic Reporting focuses on the import of so-called split postings where one debit account and multiple credit accounts (or vice versa) are imported in MSDyn365FO GL journals.

Excel add-in enhancements (1)

14 Friday Aug 2015

Posted by Ludwig Reinhard in General Ledger

≈ 4 Comments

Tags

Dynamics AX, Excel add-in, General Ledger journals

Using the Dynamics AX Excel add-in for uploading General Ledger journal transactions into Dynamics AX is already well documented on TechNet, various blog sites and alike (see e.g. the following Website).

Yet, when using this functionality in a live environment you will quickly encounter several issues that considerably limit the usability of this feature. As an example, customer transactions, fixed asset transactions, project related transactions, sales tax information and alike cannot be uploaded without making some enhancements to the standard Excel add-in feature.

Within this and the following blog post I will introduce how those enhancements can be implemented to make the Excel add-in feature more applicable in your daily work. Yet, before doing that, let’s start by having a look on how to set up and use the Dynamics AX Excel add-in for uploading transactions into General Ledger journals.

 

Setup standard Excel add-in for uploading General Ledger transactions
Step 1: Deploy the LedgerServices service group in the AOT
The first step in setting up the standard Excel add-in is to deploy the LedgerService in the AOT.
EN_35_0010


Step 2: Activate the inbound port
Thereafter, you have to ensure that the LedgerService inbound port is activated. This can be verified in the System administration module under “inbound ports”. Please see the next screenshots.
EN_35_0015 EN_35_0020

Step 3: Setup a document data source
The third step is setting up a document data source in the Organization administration module as illustrated in the following screen-prints.
EN_35_0025 EN_35_0030

Step 4: Setup Excel template for upload
After the previous steps have been finalized, you can set up the Excel template that is required for uploading the data. To realize this simply select “Add Data” in the Dynamics AX tab in Excel …
EN_35_0035
… and select the General Ledger data source that has been setup in step 3 before.
EN_35_0040
Next, setup the journal header by dragging and dropping the required fields from the Ledger journal table.
EN_35_0045
Once you setup the journal header section, the journal line template fields can be setup similarly by dragging and dropping the required fields into the Excel sheet.
EN_35_0050

Note: Rather than setting up your own template, you can make use of the Microsoft template that ships with Dynamics AX. In a Microsoft “Contoso” demo environment you can find this template in the General Ledger Template folder. If you plan to use the Microsoft template in a live environment, please ask your system administrator for details where this file can be found.
EN_35_0055
Please note that the major difference of the Microsoft template and the one I created is that the Microsoft template uses separate worksheets for the journal header, the lines and the notes. Example:
EN_35_0060

 

Shortcomings of the standard Excel add-in for uploading General Ledger Transactions
Once you have finished setting up your template, add the header and line data and publish (upload) your data into Dynamics AX. In my example the upload failed because the standard General Ledger Excel add-in does not support all transaction types; in my case, the customer transaction recorded in the third line of my Excel template.
EN_35_0065
The following screenshot shows you the error message generated. What you can identify from this message is that the standard Excel upload does only support ledger, bank and vendor transactions.
EN_35_0070

 

Enhancing the standard Excel add-in for uploading General Ledger Transactions
To overcome the problem illustrated before, I made a change to the LedgerJournalTransType class to ensure that all account types that I need are accepted. Please note that those changes have to be made in the validateAccountType method and the validateOffsetAccountType method of the LedgerJournalTransType class as exemplified in the next screenshot.
EN_35_0075

A second major shortcoming of the standard Excel add-in is that important fields required for generating ledger postings are not available in the field section of the Excel template. As an example, the standard Excel add-in does not provide users the possibility to select and upload sales tax information, invoice information, due date information and alike.

Luckily, there is also a solution for this issue available. The solution consists of dragging and dropping the required fields from the LedgerJournalTrans table into the AxdLedgerGeneralJournal query. Please see the following screenshot for an example.
EN_35_0080
Once you have added all additional account types and fields required for preparing and uploading your transactions, you have to (a) generate a full CIL, (b) refresh the AIF services in the AIF services form that can be accessed from the AOT and (c) de-activate and re-activate the LedgerService in the System administration module.

After you have gone through all those steps and open Excel again, you can identify and select the fields that you have added to the AxdLedgerGeneralJournal query. Please see the next screenshot.
EN_35_0090

After entering all necessary lines in the Excel template that you want to have uploaded and posted in Dynamics AX, you will find that all transactions have been uploaded including the values that are entered in the newly added fields (in my example, the sales tax and invoice information highlighted in yellow color in the next screens). EN_35_0095
EN_35_0100

 

Additional tips and tricks
At the end of this post I want to provide you some additional tips that might help you when using the Excel add-in for uploading General Ledger transactions:

Tip 1:
Use a filter similar to the one illustrated in the next screen-print to ensure that Excel does not update and show you all previously posted General ledger transactions. That is because such an update might require some time.
EN_35_0105


Tip 2:
Accountants often prepare the data they want to upload to Dynamics AX in a separate file that allows them repeating uploads quickly in future periods. When doing that, copy and paste the transactions into the second line of the Excel template as illustrated in the next screenshot.
EN_35_0110
The underlying reason is that the Excel add-in does often not upload the first line that is pasted from a different source into the Excel upload template.


Tip 3:
Once you try posting the transactions that have been uploaded to Dynamics AX you might see a message that says that your vouchers do not balance. This often happens if the rounding of your data in Excel differs from the rounding that Dynamics AX applies.
EN_35_0115
Rather than deleting the General Ledger journal and trying to fix this issue in the Excel template, try using the post and transfer option that transfers the sticky transactions into a new General Ledger journal. When Dynamics AX is doing this posting transfer, the Dynamics AX rounding rules are applied and usually you can post the transferred transaction in the newly created journal without any other issue.
EN_35_0120

 

Summary
To sum up, the illustrated Excel add-in enhancements allow you preparing and uploading almost all kind of transactions from Excel to Dynamics AX. You can thus use the Excel add-in e.g. for uploading intercompany transactions, vendor invoice transactions and transactions required for data migrations from previous systems. When doing that, please be aware of the following pitfalls:

  • General Ledger journals do not have an invoice number check. As a result, users might post the same invoice numbers several times without noticing it.
  • Despite the fact that you can post customer revenue transactions in General Ledger journals, those journals do not provide you the possibility to print tax compliant customer invoice documents without making further system adjustments.
  • Changes in your account structures require setting up new Excel templates. Making adjustments to existing templates usually does not work.
  • Do not try to upload mass data. If you plan to upload more than 1000 transaction lines on a regular basis, think about using other upload tools such as the data migration framework.
  • A final pitfall of the illustrated Excel add-in that I want to mention here is that it does not allow you uploading project and fixed asset related transactions even if you made the previously illustrated adjustments to the LedgerJournalTransType class. Within the next blog post I will show you how this pitfall can be fixed.

Communities4Future

Dynamics UserGroup Deutschland

Project Accounting Book – Part 2

Project accounting book

Categories

  • Accounts Payable
  • Accounts Receivable
  • Bank Management
  • Book reviews
  • Budgeting
  • Cost accounting
  • Fixed Assets
  • General Ledger
  • Inventory
  • Management Reporter
  • Miscellaneous
  • Podcast
  • Project
  • Sustainability
  • Uncategorized

Tags

Advanced bank reconciliation Allocations Bank reconciliation Budgeting Controlling Cost accounting Cost accounting module Cost center accounting customer D365 D365FO Dynamics AX Dynamics AX 2012 Electronic reporting Email Environment Fixed asset statement General Ledger journal Global Warming indirect costs intercompany Inventory Inventory reconciliation invoice invoice recording IOT Management Accounting Management Reporter Modern Finance MS Flow MT940 PowerApps PowerAutomate PowerPlatform Project Project module Purchase Order Resource scheduling Sensor settlement SharePoint Sustainability Sustainability Accounting Tax time recording timesheet Vendor invoice recording Vendor payments WBS workflow

Important Websites

  • Dynamics AX/365FO Links

Legal

  • Disclaimer

Subcribe

  • RSS - Posts
  • RSS - Comments

Enter your email address to follow this blog and receive notifications of new posts by email.

Archives

  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015

Blog at WordPress.com.

  • Follow Following
    • Dynamics 365FO/AX Finance & Controlling
    • Join 569 other followers
    • Already have a WordPress.com account? Log in now.
    • Dynamics 365FO/AX Finance & Controlling
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...