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.