We’ve been getting the question "How to show charges on the printed invoices" quite often, and in this article we will explain how this can be done using Electronic Reporting.
Assume that we need to summarize all invoice line and header charges grouped by type (Charges code), and print them in a new table on the invoice document like in the image below. We also want to support both the proforma and posted invoice scenarios.
We will achieve this with no-code approach, by changing the ER model mapping configuration provided by Microsoft for the Sales invoice report.
Derive and edit the ER model mapping configuration
Go to Electronic reporting > Reporting configurations to open the ER configurations.
If you haven’t already done so, import the ER configurations for Sales Invoice. We want to derive the model mapping from the original model mapping configuration provided by Microsoft, because we can modify only the derived configurations.
Click the Invoice model mapping and select Create configuration:
In the Create configuration dialog provide a Name for the derived configuration (I have used Invoice – Grouped Charges in the example below) and click Create configuration.
Select the newly created SalesInvoice model mapping and click the Designer button. The Model mapping designer form with this model mapping configuration opens:
Add the missing data sources
As stated in the introduction, we want to handle two possible scenarios: printing the posted invoice (i.e. printing from Invoice Journal) and printing the Proforma invoice. Let’s see which data sources should be added for these scenarios:
- Printing a Proforma invoice: we need to identify the linked Sales Order, then the lines on that order, and finally, the charges (markup transactions) linked to each line. We will also need the charges linked directly to the order header.
- Printing a posted invoice from Invoice Journal: as we know, charges are copied to the journal and transactions during the posting process, but it can happen (based on the setup) that they are deleted from the source (in this case Sales Order). That’s why we need to support this case in a different way. For that, we will first need to identify the journal and invoice transactions linked to it, then the charges (markup transactions) linked to that journal and those invoice transactions.
Our objective is to add two new data sources of Calculated field type for the above described lists of markup transactions. Then we will need a third Calculated field, which will return the markup transactions from the first or second Calculated field, depending on the existence of InvoiceId, which is known in the second case. And finally, we will group all returned charges by the charges code and summarize their values.
Add the data sources for Proforma invoice
We will first add a Table Records data source type and configure it to point to the SalesTable table. We will need this data source in order to identify the Sales Order linked to our invoice. Since we will need just a few new data sources, we will add them to the root of data sources tree. But if you want, you can first add one data source of an Empty container type to group all your customizations under it.
Click on Table records and then on the Add root button:
In the dialog form that now opens, name this data source simply as SalesTable and select the same table by filtering tables at the dropdown menu next to it, then confirm the form. You can, of course, choose a different name for it, but you must take this change into account later when you create all other formulas for the calculated fields that will refer to it.
Next, let’s create some new calculated fields. To do this, click on the Calculated field under Data source types and then click on the Add root button. After you enter the name, click the Edit formula button to open the Formula designer.
The first calculated field will give us the SalesTable based on the SalesId on the report header. We will name it $SalesTable and use the following formula for it:
FILTER(SalesTable, SalesTable.SalesId='$SalesInvoiceHeaderFooterTmp'.SalesId)
Let’s now create a calculated field that will return all markup transactions for proforma scenario. We will call it $MarkupTransactions_ForProForma. Repeat the initial steps for creating a root calculated field, name it, then copy or build the following formula in Formula designer:
LISTJOIN(
ALLITEMSQUERY('$SalesTable'.'<Relations'.MarkupTrans),
ALLITEMSQUERY('$SalesTable'.'<Relations'.SalesLine.'<Relations'.MarkupTrans))
This calculated field creates a list of markup charges from the header (first ALLITEMSQUERY) and lines (second ALLITMSQUERY) of a proforma invoice and joins them into one list (LISTJOIN function).
The same result could also be achieved by using the FILTER function, but I decided to use the less used ALLITEMSQUERY and LISTJOIN functions.
Note that this solution supports the simplest scenario, when Sales Invoice is created based on only one Sales Order and all Sales Order lines are included in the invoice. Docentric has implemented additional keys in the related tables, so that also other scenarios can be supported. If you need more details, feel free to contact me.
Add the data sources for Journal scenario
We should now create a calculated field that will return the markup transactions for a Journal scenario. We will name it $MarkupTransactions_ForJournal. Similarly, the formula now searches for any markup charges on the header and lines of an already posted invoice and then joins them:
LISTJOIN(
ALLITEMSQUERY('$SalesInvoiceHeaderFooterTmp'.'>Relations'.JournalRecId.
'<Relations'.MarkupTrans),
ALLITEMSQUERY('$SalesInvoiceHeaderFooterTmp'.'>Relations'.JournalRecId.
'<Relations'.'CustInvoiceTrans.CustInvoice'.'<Relations'.MarkupTrans))
Merge the data sources for both Proforma and Journal scenario
The last calculated field that we will add, $MarkupTransactions, switches between the last two calculated fields based on a simple condition. Namely, InvoiceId value is an empty string in the case of a proforma invoice:
IF('$SalesInvoiceTmp'.InvoiceId="",
'$MarkupTransactions_ForProForma',
'$MarkupTransactions_ForJournal')
We will next create a data source of type Group by: $MarkupTransactions_Grouped. It should group the charges both by Charges code and Sales tax group. Select the Group by data source type, then click the Add Root button and enter the data source name. Next, click the Edit group by button:
After creating the field, we are now able to edit Group by parameters, where we should first select what to group. Search for and select the last calculated field we created - $MarkupTransactions – and select Add field to > What to group:
Then expand this node, search for and select both Sales tax group(TaxGroup) and Charges code(Markup Code) as Grouped fields:
The last missing piece in our ER puzzle is summarizing the Charges value. That’s why we will set it as an Aggregation field and select the Sum method:
Save and exit the form, then confirm to save the Group by data source.
Here is an overview of all created data sources, and we should now proceed towards the last step – the binding and testing of our configuration.
Bind the data source with data model
Finally, for any changes to the ER configuration to effectively appear on our invoices, we need to fix the bindings of the existing Charges transaction for lines node in the Data model. Expand the node which you will find located under Invoice base root node and unbind it from whatever it was previously bound to. Do the same with all three subelements that are currently still bound (you will recognize them because they are marked as bold).
We should next first rebind the $MarkupTransactions_Grouped calculated field with the Charges transaction for lines. Click them so that they are both selected, then click the Bind button above.
Next, repeat the same steps for the other three subnodes that are marked with arrows on the screenshot, and once you are done, save the model mapping and exit the Designer.
Test the configuration
We can now test our new configuration. Note that to test the configuration that is still in Draft status, remember to enable both the Run draft and Default for model mapping flags.
For testing the results of our newly created configuration I first used a Docentric template, which is an exact replica of ER-CBD Excel format for this report.
Let's first look at a proforma invoice with 3 lines, where each line contains two charges: Freight and Handling charges of $10.00 each, and the Installation charge of $100.00 has been added to the invoice header:
Now let’s post and print the same document:
If you don’t use Docentric, you will still get the same result with ER-CBD Excel format:
Conclusion
While there are many other ways to add fees to your invoices – you can read an article on a similar yet more detailed approach here – this is a very simple solution for quickly adding line and header charges to the sales invoice via ER model mapping without having to make any additional changes to either Docentric template or the built-in Excel CBD format.
Resources
On the link below, please find the resulting Docentric template and used ER configurations.
Download resources >>
The summation breaks if you have charges that are not fixed but eg percantage.
Is there any easy solution to this problem?
Hi Hans, we’re looking into this and will let you know.