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.

To realize that, I first added all fields that I need from the LedgerJournalTrans_Project table to the LedgerJournalTrans table.

Thereafter, I did the same with the fields that I need from the LedgerJournalTrans_Asset table.

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.

As in the previous blog post, my next step was dragging and dropping the new fields from the LedgerJournalTrans table to the AxdLedgerGeneralJournal query.

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.

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.

So far, everything looked fine. The upload recognized the “TM1” project that I created and uploaded all other information that I entered.

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.

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.

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

As a result of the import, you can find the following project where you have to do some adjustments to the blue highlighted methods.

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.

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.

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

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.

Step 6: Update document service
The next step is updating the LedgerGeneralJournalService.

When doing that ensure that the following yellow highlighted parameters are checked.

As a result, the AxLedgerJournalTrans class gets extended by the fixed asset and project parameter fields. Example:

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.

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.

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.

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.

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.

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 …

… and select the General Ledger data source that has been setup in step 3 before.

Next, setup the journal header by dragging and dropping the required fields from the Ledger journal table.

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.

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.

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:

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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

Against the background of this setup I posted a sales order invoice. The outcome of this posting is illustrated in the next screenshot.

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.

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

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.

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)
The 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.
Initially, 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:
After recording the free text invoice and re-running the periodic update process for 31 December 2015 as illustrated in the next screen-print, …… 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.

The last test that I did in this respect was running the periodic update job with a different aging period definition as illustrated next.
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.
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:
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.

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.

Outcome test run 2:
As expected, the open balance is now shown in the 30 days column of the collection list page.

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.Outcome test run 3:
Now, the open balance is shown in the “not due” column.

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

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

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.

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.