• Home
  • About
  • Contact
  • German

Dynamics 365FO/AX Finance & Controlling

Dynamics 365FO/AX Finance & Controlling

Monthly Archives: August 2015

Excel add-in enhancements (2)

22 Saturday Aug 2015

Posted by Ludwig Reinhard in General Ledger

≈ Comments Off on Excel add-in enhancements (2)

Tags

Dynamics AX, Excel add-in, General Ledger journal

Within this blog post I will show you how the standard Excel add-in for uploading General Ledger transactions can be used for uploading project and fixed asset related transactions. Different from the uploads that I showed you in the previous blog post, the upload of project and fixed asset related transactions requires that the the LedgerJournalTrans table and the related LedgerJournalTrans_Asset / LedgerJournalTrans_Project tables are filled with the data prepared in the Excel template.
EN_36_0010
To realize that, I first added all fields that I need from the LedgerJournalTrans_Project table to the LedgerJournalTrans table.
EN_36_0015
Thereafter, I did the same with the fields that I need from the LedgerJournalTrans_Asset table.
EN_36_0020
My next step was to adjust the field properties of the fields that I dragged & dropped into the LedgerJournalTrans table. Note that I also added a new label later on to identify the newly added fields easily later on in the Excel template.
EN_36_0025
As in the previous blog post, my next step was dragging and dropping the new fields from the LedgerJournalTrans table to the AxdLedgerGeneralJournal query.
EN_36_0030
After generating a full CIL, refreshing the AIF services and de-activating and re-activating the LedgerService in the System administration module, the newly added fields showed up in the field section of my Excel template. Please see the following screenshot for an example.
EN_36_0035
As usual and illustrated in the previous blog post, I setup my template with the new fields and added a project transaction that I uploaded to Dynamics AX.
EN_36_0040 EN_36_0045
So far, everything looked fine. The upload recognized the “TM1” project that I created and uploaded all other information that I entered.
EN_36_0050
Yet, once I had a look in the project tab of my journal I noticed that none of the project related fields have been filled with the data that I prepared in my Excel template.
EN_36_0060
After having a look at the table structures and the relationships of the LedgerJournalTrans tables, I noticed that the upload cannot work with the standard Excel upload functionalities.

That is because Dynamics AX creates the RecId value that links the LedgerJournalTrans table with the LedgerJournalTrans_Asset/ LedgerJournalTrans _Project tables at the time I upload my values into the General Ledger journal.

Once this RecId value is created/known a link to the LedgerJournalTrans_Asset/ LedgerJournalTrans _Project tables can be established. Yet, since there is only one upload transaction without an additional “update” that would be required to fill also the related tables.
EN_36_0065

Solution
After agonizing over this issue for a while, I finally asked my friend Ewa Watkins for some help and we developed a project that adds an event handler to the insert method of the LedgerJournalTrans table. This event handler executes an update on the uploaded values which ensures that the related LedgerJournalTrans_Asset/ LedgerJournalTrans_Project tables are filled with the values that I entered in the Excel template.

Within the following sections I will provide you some additional details on the adjustment that we realized so that you can get an idea on how to deal with similar issues in your environment. Please note that the sample code provided in the following was only used for demonstration purposes and might require some adjustment before using it in a live environment. If you want to test the next steps I would thus suggest you to do this in a development / test environment.

 

Step 1: Import the demo xpo-file attached
Please note that I had to change the file type to “.docx” due to file contrainst on this site. In order to use the demo code in Dynamics AX, you have to download the file and change the file type from “.docx” to “.xpo”.  SharedProject_EWA_Ludwig
EN_36_0070 EN_36_0075
As a result of the import, you can find the following project where you have to do some adjustments to the blue highlighted methods.
EN_36_0080


Step 2: Mapping of the fields
Map the newly added fields included in the LedgerJournalTrans table with the ones in the LedgerJournalTrans_Asset and LedgerJournalTrans_Project table as illustrated in the next screenshots.
EN_36_0085 EN_36_0090

Step 3: Specify the additional fields that need to be imported
In the createExtendedJournals method, enter the fixed asset and project related fields that have been added to the ledgerJournalTrans table as illustrated in the following screenshot.
EN_36_0095

Step 4: Drag & drop the event handler method to the insert method of the ledgerJournalTrans table
Example:
EN_36_0100

Step 5: Change property of the event handler method
After the event handler has been added to the ledgerJournalTrans table, change the CalledWhen property from „PRE“ to „POST” as shown in the next screen-print.
EN_36_0105

Step 6: Update document service
The next step is updating the LedgerGeneralJournalService.
EN_36_0110
When doing that ensure that the following yellow highlighted parameters are checked.
EN_36_0115
As a result, the AxLedgerJournalTrans class gets extended by the fixed asset and project parameter fields. Example:
EN_36_0120

Step 7: Setup new Excel template and upload your data
After generating a full CIL, refreshing the AIF services and deactivating and re-activating the LedgerService in the System administration module, create a new Excel template and add the fixed asset and project related transactions that you want to upload to AX.

In the example illustrated in the next screenshot, I added one line with a fixed asset transaction and another line with a project transaction.
EN_36_0125
After uploading the data to Dynamics AX you will now find that the fixed asset and project related fields in the General Ledger journal have been populated with the data prepared in the Excel template. Please see the following screenshots for an example.
EN_36_0130 EN_36_0135 EN_36_0140

 

Summary
By implementing the system modifications illustrated in this blog post basically any kind of transaction can be uploaded to Dynamics AX General Ledger journals.

Yet, please be aware that the pitfalls mentioned in the previous post still apply.

A final remark that I want to make here is that the sample code provided covers only situations where the fixed asset and project related data are entered on the “offset account side” of the account entry. If you want to use the sample code in a live environment you might thus have to make some additional enhancements to cover also other ways of preparing vouchers, such as split postings, entering fixed asset / project related data on the “account side” of a transaction and alike.

Despite those shortcomings, I hope that you found the information provided in this and the previous blog post helpful.

Finally, I would like to express special thanks to my friend Ewa Watkins without whom this post would not have been possible.

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.

Reuse number sequence parameters

08 Saturday Aug 2015

Posted by Ludwig Reinhard in Miscellaneous

≈ 1 Comment

Tags

Dynamics AX, Number sequences, Parameters

If you have ever wondered about the “reuse numbers” parameters in the AP, AR and Project module and whether to check those parameters or not, have a look at the following descriptions.

For the sake of order, I will start off with a setup that uses separate number sequences (“Sale_69” and “Sale_72”) for the customer invoice and customer invoice voucher. Please note that the reuse numbers parameter is not activated for those number sequences.
EN_28_00100

To differentiate both number sequences, I setup the “Sale_69” number sequence with the prefix “CIV”, …EN_28_00200

… and the number sequence „Sale_72“ with the prefix „INV“.EN_28_00150

Against the background of this setup I posted a sales order invoice. The outcome of this posting is illustrated in the next screenshot.
EN_28_00250
As you can identify from this screenshot, the invoice number is taken from the “Sale_69” number sequence and the ledger voucher from the “Sale_72” number sequence.

Next, I activated the “reuse numbers” parameter for the customer invoice voucher and posted another sales order invoice.EN_28_00300

The outcome of this second transaction can be identified in the next screenshot.
EN_28_00350

As you can see from the previous screenshot, both, the invoice number and the ledger voucher number are now taken from the “Sale_69” number sequence. A major advantage of this outcome is that a single unique link between the invoice document and the ledger voucher is established. This unique link can be very beneficial especially in situations where you have to search for an invoice document based on the ledger voucher or vice versa.

Note: The other reuse numbers parameters in the AR, AP or Project module do have similar effects on the linkage of documents and ledger vouchers. For that reason, reference can be made to the previous explanations. 

A note on the customer collections list page

01 Saturday Aug 2015

Posted by Ludwig Reinhard in Accounts Receivable

≈ 3 Comments

Tags

Collections, Customer aging snapshop, Dynamics AX

Recently, a colleague asked me how Dynamics AX calculates the data shown in the collections list page (see the next screen-print) and how those data can be reconciled with the available Dynamics AX customer reports. As I could not give him an immediate answer, I did several tests that I included further below because I believe that the one or the other might have come across similar questions on the data shown in the collections list page.
EN_26_0000


Section 1: Calculation of the collections list page data
In order to check out how the “Amount due balance” is calculated and what kind of transactions are included in this balance, I run the periodic update process “Customer aging snapshot” with the criteria “Transaction date” for the periods ending 31 December 2012, 31 December 2013 and 31 December 2014 (see the following two screenshots)
EN_26_0001 EN_26_0002The result of this first exercise was that I always got the same total balance if I calculated the sum of the amounts shown in the “Amount due balance” column. Please see the next screenshot for this comparison.
EN_26_0003Initially, I thought that there must be something wrong with the way how I processed the data. Yet, after repeating the tests in several other applications, having a look at the CustAgingSnapshot class and the following TechNet site, I noticed that the collections list page always shows the total open customer balance amounts irrespective of the date that was selected in the “Aging as of” field of the periodic processing window.

What is more, the collections list page does also include transactions that have been recorded in future periods. To verify this, I posted a free text invoice for 1000 USD with the following date values:
EN_26_0004After recording the free text invoice and re-running the periodic update process for 31 December 2015 as illustrated in the next screen-print, …EN_26_0005… I noticed that the free text invoice that I posted with a transaction date “01 January 2016” is included in the collections window even though I run the periodic update process for end of December 2015. Please see the following screen-print.
EN_26_0006
The last test that I did in this respect was running the periodic update job with a different aging period definition as illustrated next.EN_26_0007
Yet, also in this case, future transactions were included in the collections list page and the sum of the “Amount due balance” column did not change.EN_26_0007a
After verifying how the figures shown in the “Amount due balance” column are calculated, I tested what effect the “criteria” and “aging as of date” fields have on the other columns included in the collections list page. In order to realize that, I run the periodic update process several times with different selections made in the “criteria” and “aging as of” fields.

Test run 1:
Initially, I run the periodic job with the following setup:EN_26_0008
Outcome test run 1:
As expected and identified before, the free text invoice that I posted 1 January 2016 with a due date of 02 February 2016 and a document date 03 March 2016, is included in the collections list page classified in the “not due” column.EN_26_0009

Test run 2:
Thereafter, I run the periodic job with the transaction date criteria as of 31 January 2016, as the free text invoice that I posted would be 30 days overdue on this day.
EN_26_0010
Outcome test run 2:
As expected, the open balance is now shown in the 30 days column of the collection list page.EN_26_0011

Test run 3:
My next test was running the periodic process again with an aging as of date 31 January 2016 but with the “due date” criteria.EN_26_0012Outcome test run 3:
Now, the open balance is shown in the “not due” column.
EN_26_0013

Test run 4:
Next, I run the periodic job for 4 March 2016 based again on the “due date” criteria.EN_26_0014Outcome test run 4:
As one might already expect, the open balance is now shown in the 30 days column.EN_26_0015

Test run 5:
Within the last test-run, I repeated the previous one with an aging as of date 4 April 2016.EN_26_0016Outcome test run 5:
Also here, as one could expect, the open balance is now shown in the 60 days column.EN_26_0017

The result of those test-runs is that the criteria selected (transaction date, due date or document date) and the selected “aging as of” date do only have an influence on the aging column in which a certain amount is illustrated. The selection made in those fields do, however, not influence the amounts shown in the “Amount due balance” column which always includes all currently open customer transactions.


Section 2: Reconciliation of the collections list page data
The fact that the collections list page shows all currently open customer transactions and does include transactions that have been posted in future periods makes it impossible to reconcile the amounts shown in the “Amount due balance” column with standard customer reports – such as the customer open transaction report or the customer balance report – as of a specific date in the past.

For that reason, the only way to reconcile the amounts shown in the collections list page is to run those reports as of a (future) date after which no other transactions have been recorded. For an example, please see the following screenshot.
EN_26_0018

In summary, one has to remember that the collections list page is not able to show open customer transactions as of a certain date in the past if other customer transactions have been recorded after this date.

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
  • Project
  • Uncategorized

Tags

Accruals Advanced bank reconciliation Allocations Approval Bank reconciliation Controlling Cost accounting module Cost center accounting Cost of Sales Method customer Dynamics AX Dynamics AX 2012 Earned Value Analysis Electronic reporting Email Fixed asset statement General Ledger journal indirect costs intercompany Inventory Inventory reconciliation Inventory valuation Inventory value report invoice invoice recording Management Accounting Management Reporter MS Flow MT940 Nature of Expense Method parallel Posting setup PowerPlatform Prepayments Profit & Loss Statement Project Project controlling Project module Purchase Order search settlement SharePoint standard costs Tax upload valuation 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

  • 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.

Cancel

 
Loading Comments...
Comment
    ×