Text Formatting


A very common use of expressions is to format and manipulate text strings.

This article summarizes information relevant to this topic.

For more information on the functions referenced below, please see our Functions Guide. For general information on expressions, please see our Expressions Guide.

Table of Contents

String Literals

String literals are surrounded by single quotes, such as the ‘ m’ in this expression:

text(fixed([LocationDetails.FinalDepth],2,false))+' m'

Joining/Concatenating Text

+ Operator

As seen in the example above, the + operator can be used to join text strings together.

In that example, the Final Depth is combined together with a ‘ m’ units label.

text() Function

In order to join strings with the + operator, values must be of text data types. Values can be converted to text data types using the text() function.

In the example above, this portion of the expression would return a numeric value:

fixed([LocationDetails.FinalDepth],2,false)

By wrapping it in the text() function like below, the value is handled as a text value:

text(fixed([LocationDetails.FinalDepth],2,false))

Note – When using the + operator to join text, it is best practice to wrap values in the text() function even if you expect that value to always be a text string. In some scenarios, values that you would expect to be treated as text are not. For example, values returned by Aggregate expressions in Templates may need to be converted using text() even if they appear to be a text string.

Concatenate and Joining Functions

In addition to the + operator, there are functions that can be used to concatenate or join text strings together, such as:

These functions are very useful to streamline expressions that require a lot of text joining. For examples, see the Model Calculation expressions for building component Material and Discontinuity Descriptions, such as in our US, North America Metric, and Australia configuration packs.

Aggregate Functions (in Templates and Model Calculations) can also be used to join values together, such as:

Formatting Text and Controlling Number of Decimal Places

Using fixed()

As seen in the example from above, the fixed() function can be used to format numerical values.

text(fixed([LocationDetails.FinalDepth],2,false))+' m '

The second argument of the fixed() function is the number of decimal places, in this case 2.

The third argument is a true/false value to indicate if thousand separators should be used for the formatted value, in this case set to false so the separators are not used.

As described above, fixed() needs to be wrapped in text() in order to join the value with other text strings.

There is a known issue where if fixed() is used within certain nested Template expressions, and the value to be formatted is blank, that the entire expression may undesirably return nothing. If you encounter this, please use text() instead of fixed() to format numerical values.

Using text()

Alternatively, the text() function can be used to format numeric values to a certain number of decimal places, as well as to other formats.

text() accepts an optional second argument for the format you would like a value to use.

For example, this expression would return nearly identical* results as the expression above as far as formatting the value to 2 decimal places:

text([LocationDetails.FinalDepth],'F2') +' m'

This expression would also return nearly identical results, just using a different formatting code:

text([LocationDetails.FinalDepth],'0.00') +' m'

Another difference between fixed() and text() is that the fixed() number of decimal places must be hard-coded into the expression. However, for text(), the formatting codes can be supplied from a nested expression. For example, please see this article demonstrating how Options can be used to control the number of decimal places.

Please see the section below for additional information on the formatting codes that can be used.

(*We say ‘nearly identical' results because fixed() uses 'banker’s rounding' for midpoint values, while text() rounds up for midpoint values.)

Formatting Codes

Various ‘formatting codes’ are available that can be used to change the appearance of numeric values, as well as dates and times, within text strings.

These formatting codes are most commonly used within the text() function, but can also be used within other functions, such as the aggregate functions join() and joinDistinct().

The examples below demonstrate some of the commonly used formatting codes.

OpenGround formatting codes are derived from .NET standard and custom numeric formats strings, as well as standard date and time format strings. However, note that not all format strings within the Microsoft documentation are supported in OpenGround.

Leading/Trailing 0 Placeholder – ‘0.00’

‘0’ can be used in formatting codes to include leading or trailing 0’s. For example:

text(1.2,'00.00')

Digit Placeholder – ‘#.##’

‘#’ can be used as a digit-placeholder symbol. If the value has a digit in the position where the # code is used, it will be included in the final text string. Otherwise, it will not be included. For example:

text(1.2,'#.##')

Digit-placeholders can also be used to include thousand separators in the formatted value. For example:

text(1000000,'#,###,###.00')

Number of Decimal Places – ‘F#’

‘F#’ can be used to show a numeric value to # number of decimal places, such as ‘F1’ or ‘F2’ for 1 or 2 decimal places. For example:

text(1.2345,'F1')

Significant Figures – ‘G#’

‘G#’ can be used to show a numeric value to # number of significant digits, such as ‘G1’ or ‘G2’ for 1 or 2 significant digits. For example:

text(1.2345,'G1')

Scientific Notation – ‘E’ or ‘e’

‘E#’ can be used to show a numeric value to # number of decimal places in scientific notation. For example:

text(0.012345,'E1')

You can see the result includes some leading 0’s in the exponent by default.

Alternatively, the ‘E’ formatting code can be combined with the # and 0 placeholder codes to further control the scientific notation format, such as to remove leading 0’s in the exponent. For example:

text(0.012345,'#.#E0')

Note that ‘e’ can be used, and a ‘+’ can be used to include a plus sign in the exponent. For example:

text(123.45,'#.#e+0')

Dates and Times

Most .NET date and time formatting codes are supported within OpenGround. For example:

text([LocationDetails.DateStart],'dd/MM/yyyy')

Or as another example:

text([DepthRelatedExploratoryInformation.DateTimeStart],'MMMM dd, yyyy hh:mm:ss.ff tt')

Note that TimeSpan (duration) fields within OpenGround can not currently be formatted using the standard formatting codes. Please see the section below for more information.

Formatting TimeSpan/Duration Headers

As noted above, headers with a ‘TimeSpan’ Data Type (durations) can’t currently be formatted using formatting codes. For example, you could not use the code ‘hh:mm’ to format a duration to hours:minutes formats.

TimeSpan fields are stored in milliseconds in the database. For example (assuming hh:mm units are set in the model):

When you retrieve the value from the database, such as within the text() function, it is returned in milliseconds.

An expression is needed to convert TimeSpans from milliseconds to some other format.

This expression can be used to report a TimeSpan value in hh:mm format:

text(Floor([TimeRelatedRemarksLocation.Duration]/3600000),'00')+
text(':')+
text([TimeRelatedRemarksLocation.Duration]/60000-Floor([TimeRelatedRemarksLocation.Duration]/3600000)*60,'00')

This expression can be used to report a TimeSpan value in mm:ss format:

text(Floor([TimeRelatedRemarksLocation.Duration]/60000),'00')+
text(':')+
text([TimeRelatedRemarksLocation.Duration]/1000-Floor([TimeRelatedRemarksLocation.Duration]/60000)*60,'00')

Note that you can also just reference the Header directly (instead of using an expression), which will print the value in the units used in the database. You would only need to use these expressions if you wanted to show the duration in different units than used in the database, or if you needed to use the duration within an expression for some other reason.

Text Styling (Font Color/Italics/Bold) Using textStyle() Function

The textStyle() function can be used to format the font color, weight (boldness), style (normal/italics) within an expression. Please see our Functions Guide for more information and examples.

textStyle() is only supported within Template expressions.

For example, this expression formats the text string to bold, italics, and red font.

textStyle('My Text','700','Italic','FF0000')

New Line Character

To create a new line, use the string:

'\n'

For example:

'ABC'+'\n'+'DEF'

‘\n’ can also be used within functions, such as concatenatewith(). For example:

concatenatewith('\n','ABC','DEF')

New lines can be used in Template expressions and Model Calculation expressions for LongText headers. They can’t be used for ShortText headers, which don’t support multiple lines values.

Text Formatting Example

This expression demonstrates a number of the concepts introduced above:

concatenatewith(
  '\n',
  textStyle('N: ','700','Italic','FF0000') + text(fixed([LocationDetails.Northing], 2, false)) + ' m',
  textStyle('E: ','700','Italic','FF0000') + text(fixed([LocationDetails.Easting], 2, false)) + ' m',
  textStyle('Depth: ','700','Italic','FF0000') + text([LocationDetails.FinalDepth], '0.00') + ' m'
)