Dynamics 365 Business Central AS 101 Lesson 19: Row numbering
Row numbering in account schedules can make you a little crazy until you understand what you need to do to get it right the first time. In the example below, I show the design view for five different ways to number the rows for the same account schedule.
Some of these work, and some of them don’t, and you might be surprised which one you should choose. Let’s look at the results and talk about why we have what we have.
Five approaches to row numbering (row setup view)
Five approaches to row numbering (screen view)
The first and second examples give us the same incorrect result. The third example gives a different incorrect result, and the last two give the same correct result. What is happening?
In the first and second example, the formulas are 1+2+3 and 1..4. In these cases, NAV is picking up any row that has the value of 1, 2 and 3 and adding them all together. The total, highlighted in green, is the sum of all the rows highlighted in yellow.
Results of first and second example (total highlighted in green)
In the third example, the formula asks for the result of any row labeled 1. NAV shows the same behaviour by picking up any row that has a value of 1 and adding them all together. The total, highlighted in green, is the sum of all the rows highlighted in yellow.
Results of third example (total highlighted in green)
The fourth example, using only the letter A in the formula, will actually have the same problem as the section above it if there are any other sections in the account schedule where the rows are labeled A. However, this technique, called row grouping, will assist you in making your account schedule formulas more efficient. There is a character limit on the number of letters or numbers you can put in an account schedule total, and if you exceed it, you’ll get this error:
The fifth section, using an alpha numeric code, is considered a best practice because it allows you the largest amount of variety when designing a larger sized account schedule and helps you to avoid the types of problems seen above by giving NAV something a little more complex to work with than single numbers or letters.
You can come up with pretty complex account schedules using alpha numeric codes without having to keep track of which numbers may or may not repeat, and you can keep your formulas short and simple, which makes them easier to type and less error prone. Many companies employ this technique in their balance sheet designs, and use alpha numeric codes to subgroup things like current assets and long-term assets, and then group them at a higher level again for total assets. Below is another simple example to show you what a small section of the balance sheet might look like using this technique, this time for fixed assets.
Example five is considered best practice — using an alpha numeric code
This posting is part of the NAV Account Schedules 101 series. Find the entire list of lessons here.
Don’t forget to visit the Account Schedule Examples page if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics 365 Business Central.