Enumerate lines in Configurable Business Documents

The delivery note, invoice etc. Report Data Providers in Dynamics 365 for Finance do not expose line numbers for no obvious reason. For instance, the SalesInvoiceTmp table sent from D365 to the report renderer – whatever it is – misses the line number. The situation becomes dire when it comes to Electronic Reporting / Configurable Business Documents.

The ENUMERATE(Lines) function applied to the lines makes a totally new object {Number, Value} and every binding “@” becomes “@.Value“. This is a massive change in the report, very hard to maintain and upgrade (rebase).

In XML or CSV outbound formats there is a Counter element, it auto-counts itself. In Excel and Word outbound formats there are just Ranges, Cells. I thought we worked with lists as isolated objects and there were no internal variables in Electronic Reporting which were aware of the execution history.

But today I finally understood what the DATA COLLECTION does. It is a listener, you pass a value to it, it returns it back unchanged but keeps a global variable to count and summarise the values.

For example, to enumerate delivery note (en-us: packing slip) lines, first create a root Data collection class of the integer type in the Mapping to the right, Collect all values = Yes, let’s call it a LineNumberCollection.

Then put the following formula into the Excel Cell with the line number:
LineNumberCollection.Collect(1)+LineNumberCollection.Sum(false)-1

The first LineNumberCollection.Collect() call takes 1, records 1 internally, and returns the 1 back into the cell. The second call LineNumberCollection.Sum(false) adds the running total of 1’es recorded so far. The last operand subtracts 1.

Here is the result: Bingo!

Electronic reporting blog series

D365 Electronic Reporting: JOIN records in pairs

D365 Electronic Reporting: JOIN records in pairs

Recently, I came across a really challenging requirement in Electronic Reporting / Configurable Business Documents in Dynamics: in an additional section in the Delivery note, print a flat table where every pair of consecutive records becomes one line:

No	Not like this	But like this	No
1	Left lace	Left lace – Right lace	1
2	Right lace	Left shoe – Right shoe	2
3	Left shoe
4	Right shoe

This required a set of sophisticated techniques I am thrilled to share. The idea is to split into odd and even records and build something like

SELECT *, ROW_NUMBER as Rn1 FROM Records JOIN *, ROW_NUMBER as Rn2 from Records ON Rn2= Rn1+1

Enumerate: make a so called Calculated field at the ER configuration’s root level with a record list and a row Number inside:
ENUMERATE(ALLITEMS(MyRecords))
Add another Calculated field at the row level to check if the current record has an odd or an even row number: LEN(REPLACE("13579",RIGHT(NUMBERFORMAT(@.Number,"#"),1),"",false))<5 I’ll leave the formula uncommented, but I wonder if somebody manages to write a shorter function. I can also imagine using TRANSLATE() or the VALUEIN(SPLIT(“1,3,5,7,9″…) functions. The problem is the missing MOD operator in Electronic Reporting: https://experience.dynamics.com/ideas/idea/?ideaid=09590ad4-70a4-e711-80c0-00155d7cd0b4. They promised, but failed to deliver. Anyway, now we’ve got this:

Next, let’s add another root Calculated field, filter the list by $Odd = true and make a list with only the odd records:
WHERE('$AllRecordsEnumerated', '$AllRecordsEnumerated'.'$Odd')
Finally, attach an ‘even’ record to every ‘odd’ one by placing another Calculated field with a lookup inside of the $OddRecord: FIRSTORNULL(WHERE('$AllRecordsEnumerated', '$AllRecordsEnumerated'.Number=@.Number+1)). Here is the result, a JOIN to itself:

Bingo!

Electronic reporting blog series

Canvas sections in D365 Configurable Business Documents

Canvas sections in D365 Configurable Business Documents

Embedding static sections of a fixed width and length into configurable Business documents in Excel seems to be hard to impossible, but it is not. An example can be an American pre-printed Cheque or the notorious French Bill of exchange at the end of the invoice. The section may not span 2 pages, i.e. it should be kept together to be torn off or scanned by the bank OCR system. This cannot be achieved by merging cells but with floating graphical elements only. Follow the below advice how to make it:

Better use Excel. The mapping of an Excel file to an Electronic Reporting format configuration is based on the named ranges (name manager), a mapping of a named Text box in a drawing is also possible. It is not necessary to attach an XML schema to the Excel file, the design of an Excel report is a simple WYSIWYG exercise.
You cannot use the Configurable business documents UI, because the changes needed are intricate and can hardly be made in the online Office 365. You have to locate the exact format in Electronic reporting (here: Sales invoice (Excel) (FR)), open and save the attachment with the template on your computer, amend it, upload it as an attachment and reassign to the root node in the format Designer.
Figure out the exact width of the paper by printing the document. Check at which column the page boundary begins (here: M-N).
Capture a raster image of the section you have to embed and paste it as a template into your document to position the vector elements over it nicely. Send it to the background and resize carefully to match the full page width (here in the background, cyan stained).
The drawing elements get anchored by Excel to the adjacent cell, and to maintain the relative positions they should be floating inside of a large merged cell of the size of the whole section (here: $B$121:$M$121). The outer boundaries of the elements shall not cross the boundaries of this mother sell. Make the row a named range which will be holding all the boxes and images (here: BoE for the bill of exchange).
The Electronic Reporting may only work properly with Text boxes and Images. Everything must be rendered with text boxes.
To address and populate the elements in the Electronic Reporting format definition, they must be named ranges* in Excel (here for the red line: BoE_Line).
* Not really a range but a named/renamed element of a drawing. The ER engine is going to multiply them – in theory – and a BoE_Line element becomes BoE_Line1 etc. in the final output.
The Electronic Reporting cannot currently place circles, lines or any other geometric form properly. The little triangles in this example have been drawn with Unicode symbols like “∇”. If you need to draw a line, it must be an empty Text box with a height of around 0,03 cm and an opaque line around of the given thickness divided by 2. The inner boundaries of the text must be all zero, otherwise the ER will corrupt the width, the position and the line type and make it disappear:
Every element (including the “lines”!) must be explicitly listed (listed, not necessarily mapped!) in the format definition as a cell or range, otherwise the graphical object remains linked to the absolute number of the adjacent cell (e.g. B121) and starts floating all around, appearing in the middle of the invoice when you print it.
The objects must be in the proper order, or the ER Designer is going to warn you that “The range BoE_Line must be preceded by the range XXX“. The sorting order is a bit special. If the cells are sorted by {ROW, COLUMN}, the drawing objects are apparently sorted by the cartesian coordinates {X;Y} of the upper left corner, from left to right, then from the up to the bottom (which is a theoretical aspect, since it is hard to place the elements at exactly the same X coordinate by drag and drop only). See the above enumeration 1-7, the upper line starting the leftmost.
Test and print your report with a proforma document often. If you don’t know how to test an ER configuration in a Draft status, here is the sacred answer: Electronic Reporting (ER) Cookbook 2: new tips from the kitchen.
The raster image shall be removed in the end, of course, it is too cheap a solution for the static texts and lines.

Conclusion

To avoid all this mess, you may try to convince the customer that negotiable instruments from the XVIII-XIX centuries may become obsolete in the XXI (you know: the wire payments, SEPA, blockchains and Co…) Good luck with that.