In this blog post, I’ll show you how to change lookup data in Dynamics 365 for Finance and Operations into internal enumerations set up by users, accessible through the standard UI for each legal entity. Specifically we’ll look at how to change tax codes from the TaxTable into external codes, like ‘VAT’, ‘Green taxes’, and others. The first part of this post will be more technical, and you’ll need to know about Electronic Reporting (ER), but the second part is functional and is quite similar to how you configure standard ER tax reports.
Development
In this example, we’re dealing with an invoice report (.txt) featuring a header and tax lines. Our goal is to convert the tax code from the ‘TaxTrans’ / ‘TaxTable‘, selected on the right in the model, to a fixed external code required by the tax authority.
We’ll begin by creating a format enumeration using the ‘Format Enumerations‘ button at the top. We’ll give a name to this enumeration, like ‘ExternalTax‘ and we’ll add the possible values in the fasttab ‘Format Enumeration values‘.
Next, we’ll add a container called ‘Taxes‘ using the ‘+add root‘ option, to organize our data. Within this container, we’ll include the table records from the ‘TaxTable’ and the format enumeration we created earlier. Be aware of a bug in ER: if you don’t select the ‘show details‘ button at the top left, some options might not appear when you try to ‘add‘ or ‘+add root‘.
Finally we will ‘+add‘ the Enumeration lookup:
In the ‘Format Enumeration‘ section, select the enumeration we created, then click on ‘Edit lookup‘. In the following form select ‘Columns‘ and next add the tax code from ‘Taxes.TaxTable‘ by selecting ‘TaxCode‘. Remember not to check any boxes, simply select ‘TaxCode‘ and click the arrow (→), then ‘OK‘. Note that this part can be glitchy; checking any box by mistake will require you to go back and start this step over.
Now, a new column named ‘Sales Tax Codes‘ should appear on the right. If you see it, you’ve followed the steps correctly. You can now save your changes and go back.
In the model, within the tax transactions (Model.Invoice.Taxes), we can add a new field named ‘ExternalCodes‘. This field will store the format enumeration for each tax transaction.
In this field, add the lookup we made and pass it a parameter with the internal tax code. It will return the format enum (ExternalTaxes) during execution.
Finally, link this new field in the format (File.Linebreak.TaxTrans.ExternalCode). Since it’s an enum, convert it to text, for example, using the CASE formula.
Now that the development is finished, we can complete the format. The only step left is to set up the lookup for the required legal entities.
Functional
We need to select the format in the form Reporting Configurations (you can find it in the ER Workspace by clicking the top left box). Once the format is selected, navigate through the top menu to Configurations > Application specific parameters > Setup.
Here, choose the lookup and in the conditions fasttab, add all the relations. This means selecting our internal tax code in the right column and the corresponding format enumeration for that tax code in the left column.
For each tax code, the system begins at the top row. If the tax code matches the one on the right, it returns the Lookup result. If no matching code is found during execution, an error will occur. To prevent this, we can add two rows at the end: ‘Blank‘ and ‘Not Blank‘. If the tax code is empty or doesn’t match any configured codes, the system will return ‘Other Taxes‘ as the enum. It’s crucial that ‘Not Blank‘ is the last row to prevent all tax codes from being incorrectly converted to ‘Other Taxes‘.
After finishing the configuration, ensure that the status of the latest completed version is marked as ‘Completed‘; in this example, it would be version 2.2. You can export and import this configuration using the buttons at the top.
Now, when we run the report, the first column of the tax transactions will display the external code:
Leave a Reply