When a value from a column or a field is formatted in a ForNAV report, the normal behavior is that it mirrors the internal Microsoft Dynamics NAV/Business Central formatting 1:1.
However, in some cases, you must specify extra information about the formatting to get the desired result:
Microsoft .net formatting
The ForNAV textbox control has a Format String property where you can specify a .net format string that is also used in most Windows software (Microsoft Excel, and so on):
For .net formatting to work, it is important that you know the data type of the result of the control data source expression. Otherwise, the format string will not work.
For example: 1.0 + 2.0 has the data type number and 1.0 + ‘USD’ has the data type string.
This link provides an overview of the format string: http://www.cheat-sheets.org/saved-copy/msnet-formatting-strings.pdf
Here’s another link for custom formatting strings: https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings
Dynamic formatting
You can use the OnPrint property on textbox controls to dynamically change the format string:
In this example, the value of NoOfDecimalDigits will control the number of decimals the number is formatted with.
JavaScript formatting
In more advanced formatting scenarios or when using a combination of numbers and strings in a data source expression, it is necessary to use the JavaScript function toLocaleString to get the right formatting: https://www.w3schools.com/jsref/jsref_tolocalestring_number.asp.
For example:
ProdOrderRoutingLine.RunTime
+ ‘
‘ + ProdOrderRoutingLine.RunTimeUnitofMeasCode
will give the result “1.2 kg” if
ProdOrderRoutingLine.RunTime
has the value 1.2 and ProdOrderRoutingLine.RunTimeUnitofMeasCode
has the value “kg”.
However, if you want a local decimal separator, you must use the toLocaleString() function with the locale as a parameter.
For example:
ProdOrderRoutingLine.RunTime.toLocaleString(‘de-DE’) + ‘
‘ + ProdOrderRoutingLine.RunTimeUnitofMeasCode
will give the result “1,20 kg”.
Or, if you want to have a fixed set of decimals, you must add options as a second parameter, for example:
ProdOrderRoutingLine.RunTime.toLocaleString(‘de-DE’ , { minimumFractionDigits: 2,maximumFractionDigits: 2}) + ‘
‘ + ProdOrderRoutingLine.RunTimeUnitofMeasCode
will give the result “1,20 kg”.
If you want to add currency symbols, you can use more advanced options, for example:
Line.Amount.toLocaleString(‘de-DE’ , { style: ‘currency’, currency: Line.CurrencyCode ,minimumFractionDigits: 2,maximumFractionDigits: 2}) will give the result as “€1.20” if the value of
Line.Amount is 1.2 and the value of
Line.CurrencyCode
is ‘EUR’.
AutoFormatExpr and AutoFormatType
From NAV 2015 and later, using AutoFormatExpr and AutoFormatType on report columns has not produced the right results. This is because the functionality in Microsoft Dynamics NAV and Microsoft Dynamics 365 Business Central calling AutoFormatTranslate has been partly broken. To get around this, you can instead use the function ReportForNav.AutoFormat(AutoFormatExpr, AutoFormatType). You can read more information in our KB article: https://www.fornav.com/knowledge-base/autoformatexpr-and-autoformattype/.
