How to hide line captions based on expressions

Knowledge Base

Note: This requires as a minimum version 7.0.0.0 of the ForNAV Designer and extensions

A recurring issue in report design is how to hide column headers when there is no value in any of the records in that column. For example, in the following order, we have not yet added a unit price (highlighted in yellow for clarity). Because this column is empty, we want to hide it.

The problem here is that the headers are printed before all the line information has been processed. There is no way of knowing that the unit prices are all 0. To fix this, you need to add a JavaScript record to your report. This Sales Line record can be read before the line headers are printed and used to show or hide the Unit Price caption.

To do this, in the report properties, open the Records property. Add a new record that matches your current sales lines and set the link to match:

This record gets read when the header record gets read from the database. Therefore, it is available when we print the line headers. To check if the Unit Price has any value, you can use a nice trick. Because the Unit Price is a decimal, we can calculate the total sum of it. If that is not zero, we can print the header.

To set this up, you need to set the Show Output property of the Unit Price caption to this value:

SalesLine.FieldExtensions.UnitPrice.Sum != 0

 

After you have done this and you print the order, you can see that the Unit Price caption is hidden.

Note: If your total is 0 because you have, for example, one line with 100 and another line with -100, the caption will not be shown.

Before you edit your reports, there are two important caveats to this solution:

  1. You cannot set this up in the report templates. All sales documents use different tables. Therefore, setting this up in the Sales Template document will result in a runtime error.
  2. If you want to check a value that is not a number, you need to iterate through the records with JavaScript. The following video explains this: Get multiple records from related Business Central tables without writing code Link to recording.