Occasionally you might face a situation where you need to inform an external party – such as a vendor or customer – on something out of Dynamics AX. Thereby you might notice that no standard Dynamics AX report is available that might help you with your problem. In such situations you basically have two options: (a) try to find the data you need in different Dynamics AX forms, export them to Excel, gussy up your data and copy them into an Email that you send to your customer/vendor, or (b) ask somebody from IT to create a new Dynamics AX report for you. If (a) takes up much of your time and if (b) is not possible due to time and budget constraints, you can use document templates to create your own reports.
If you search the internet on how to setup document templates in Dynamics AX you will quickly notice that there are already several blog posts and websites available that give you an idea on how they can be implemented in Dynamics AX. Yet, I found that information rather abstract and difficult to implement in practice. For that reason, I will use an example that I experienced many times in customer projects and show you the different steps that are required to setup and use document templates.
The example that I am going to use in the following is based on a situation where a vendor calls and asks you to send some information on what invoices have been paid with the last payment that you made. To finalize this request you can reprint a payment advice statement and sent it to your vendor. (Please note that this requires that the payment advice document has previously been saved in Dynamics AX as reprinting payment advice statements is not supported in the standard application). Another option would be to send your vendor a screenshot of the payment history form that is available from the vendor list page. If none of those options suits you, take a look at the steps below that show you how to setup your own ‘payment report’.
Please note that document templates are not made for mass communication but rather for individual communication with a limited number of stakeholders. This is mainly because document templates do not ship with a multi-select functionality in Dynamics AX. As a result, you have to select the different parties you want to communicate with individually. Another remark that I would like to make here is that document templates are not limited to the example that I illustrate below. They can rather be used for numerous other purposes such as informing customers/vendors on their account transactions, requesting information from customers / vendors and other stakeholders, etc.
Now let’s have a look at the steps required for setting up a document template to inform your vendors about the payments you made.
Step 1: Setup document management parameters
The first step is to ensure that you have setup Microsoft Word Templates as a file type in the document management parameters form that can be found in the organization administration module.
Step 2: Setup queries
To include Dynamics AX data in your documents you need queries. For the purpose of this example, I copied the default query ‘Vend’ twice to ensure that I do not destroy the original query. Thereafter, I simply renamed the first copy into ‘Vend_VendTrans’ and extended the second copy by the VendSettlement table as illustrated in the following screenshot.
(Please note that the queries have to be in line with the tables to which you plan to link your documents to. For that reason all queries that I use start with the ‘VendTable’).
Step 3: Setup document data sources
After setting up your queries in the AOT you have to make them available in the document data sources form that can be found in the organization administration module. When doing that, please ensure that you use the data source type ‘Query reference’.
Step 4: Setup your MS Word Template
After setting up your document data sources you can link them to your MS Word template by selecting ‘Add data’ from the Dynamics AX menu.
To setup your template, simply add the data you want to have included in your document by dragging and dropping the fields from the Microsoft Dynamics AX pane on the left.
Please note the following when setting up your template:
In the upper part of the template, I selected all fields from the Vend_VendTrans query and setup a filter to ensure that only payment data are included in this section. To avoid that Dynamics AX shows all payment data for the selected vendor, I created a second document version where I added a second filter that limits the payment data to those that have been executed within the last seven days.All of the data that are included in the lower part of my template are from the Vend_VendTrans_VendSettlement query. Also here I used two filters to ensure that only invoice data for the last seven days are illustrated in the lower part of the document.
After finishing the setup of my document I saved it as a MS Word Template.
Step 5: Save template in template library
The next step is to save your template in a SharePoint template library.
Step 6: Setup document types
After you saved your document in the template library you can link it back to Dynamics AX via the ‘document types’ form that is available in the organization administration module. When doing that please make sure that you point to the correct Sharepoint document library site and that you activate your templates.
Step 7: Use the template
After having done all of this setup you can use your document template in the vendors list page in the accounts payable module.
Once you select the document template that has been setup, MS Word automatically opens and illustrates your data from your query. In my example all payments made to the selected vendor can be identified in the table that can be found in the upper part of my document and the corresponding invoices are illustrated in the lower part. The link between the payments and the invoices is made via the voucher and offset voucher fields.
Please note that I created two document versions for this example; one that shows me all vendor payments made (illustrated on the left side of the following screenshot) and one that shows me all vendor payments made within the last seven days (illustrated on the right side of the following screenshot).After generating the document report you can directly Email it to your vendor via the default MS Office Email functionalities.
Also note that Dynamics AX automatically saves a copy of the documents that you generated as an attachment to the record for which you created the document for, which allows you to track the information that has been sent out to your stakeholders.
In summary, using document templates provides you a good instrument when it comes to communicating with external stakeholders. Even though the document template solution illustrated here is probably not the best and most elaborate solution, it can be helpful especially in situations where you do not have the time and resources available for making adjustments to Dynamics AX.
Eugen Glasow said:
Isn’t it the same as the common “Payment remittance” letter?
In the new Electronic Reporting (http://erconsult.eu/blog/electronic-reporting-er-cookbook/) you may attach an Excel template and fill it along with the payment file.
Ludwig Reinhard said:
Yes, that’s about the payment remittance letter and the issue that the original cannot easily reprinted if for example a vendor calls and asks what has been paid.
Thanks for sharing the link and information!