D365 Electronic Reporting: JOIN records in pairs

Test for odd number
Test for odd number

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

  1. Enumerate: make a so called Calculated field at the ER configuration’s root level with a record list and a row Number inside:
  2. 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:
  3. 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')
  4. 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: