In this blogpost I’ll show you how to create a list of account numbers from the totalling syntax that you find in Dynamics NAV account schedules or chart of accounts for example:
1000..1090|1500|2000..2020
This string shall be transformed into a “real” list of account numbers in the query editor that can be used to select all accounts within those ranges.
-
Split by pipe (“|”)
- Text.Split(“1000..1090|1500|2000..2020”, “|”) splits the text into a list like so:
NAV account schedules: 1) Split by pipe
- Text.Split(“1000..1090|1500|2000..2020”, “|”) splits the text into a list like so:
-
Split by dots (“..”)
- In order to split each element of the list above by the 2 dots, I have to apply the split command to every item of the list. Therefore I use List.Transform like so:
- List.Transform(SplitByPipe, each Text.Split(_, “..”)
- This returns a nested list like below, with 2 list elements for those who contained a dotted range and just one element for the single ones like so:
NAV account schedules: 2) Nested split by dots using List.Transform
- In order to split each element of the list above by the 2 dots, I have to apply the split command to every item of the list. Therefore I use List.Transform like so:
-
Create lists
- In this step, the list of actual account number will be created. Again, I use List.Transform to transform the existing list by applying a function to each of its elements. The formulas required depend on the number of items in each list. If there is just one item in it (like the “1500” in the second row), nothing has to be done and the value can stay as it is (see yellow part in the pic below). But if the element contains 2 elements (like the “1000” and “1090” above), I create a list that starts from the first list element to the last. And as the elements in my list so far are in text-format, I have to convert them to number before when using them in the list creator ( {…} in green ):
NAV account schedules: 3) Create list of accounts
- In this step, the list of actual account number will be created. Again, I use List.Transform to transform the existing list by applying a function to each of its elements. The formulas required depend on the number of items in each list. If there is just one item in it (like the “1500” in the second row), nothing has to be done and the value can stay as it is (see yellow part in the pic below). But if the element contains 2 elements (like the “1000” and “1090” above), I create a list that starts from the first list element to the last. And as the elements in my list so far are in text-format, I have to convert them to number before when using them in the list creator ( {…} in green ):
-
Flatten nested list of lists
- Now that I have all numbers per list element, I just have to combine the 3 list into one. The function List.Combine does this like so:
NAV account schedules: 4) Flatten nested list of list
Further steps
From here, you can convert that list into a table and merge it with your chart of accounts as an inner merge. That will eliminate all the numbers that have been created above who might actually not have a match in the accounts table.
Function
For convenience, I’ve converted these steps to a function that take the original string as the only mandatory parameter. There are 2 additional optional parameters that take different characters for the pipe and dots if you want to apply this logic to a syntax that used different signs:
Enjoy & stay queryious 😉
