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.