Expression Results and Formatting

For detailed guidance on expression please refer to this article: Conditional Expressions

The Encodian population engine provides additional features which allow the result of an expression to be formatted differently.

This article covers the following topics:

Expression Formatting Syntax

String Formatters

Date Formatters

Number Formatters

Currency Formatters

Other Formatters

Expression Formatting Syntax

Expression formatting follows this simple convention:

<<[token]:"format">>

Consider this JSON document:

{
"companyName": "Encodian",
"formedOn": "17/06/2008"
}

Consider this standard expression format:

<<[formedOn]>>

Which generates the following output:

mceclip0.png

The expression result can be formatted by adding the format configuration, see below:

Formed: <<[formedOn]:"yyyy.MM.dd">>

Which generates the following output:

mceclip1.png

String Formats

The following string formats are supported by the population engine:

lower: Converts a string to lower case, for example: "this is a string"

Example JSON:

{
  "product": "Encodian for Microsoft Power Automate"
}

Token Format Configuration

mceclip1.png

<<[product]:lower>>

Output:

mceclip0.png

upper: Converts a string to upper case, for example: "THIS IS A STRING"

Example JSON:

{
  "product": "Encodian for Microsoft Power Automate"
}

Token Format Configuration

mceclip2.png

<<[product]:upper>>

Output:

mceclip3.png

caps: Capitalizes the first letter of every word in a string, for example: "This Is A String"

Example JSON:

{
  "product": "encodian for microsoft power automate"
}

Token Format Configuration

mceclip4.png

<<[product]:caps>>

Output:

mceclip5.png

firstCap: Capitalizes the first letter of the first word in a string, for example: "This is a string"

Example JSON:

{
  "product": "encodian for microsoft power automate"
}

Token Format Configuration

mceclip6.png

<<[product]:firstCap>>

Output:

mceclip7.png

Date Formats

By default, the population engine tries to detect and parse DateTime values automatically using the following formats listed in order:

  • The ISO-8601 format (for values like “2015-03-02T13:56:04Z”)
  • The Microsoft® JSON date-time format (for values like “/Date(1224043200000)/")
  • All date-time formats supported for the current culture (Specific to processing region)
  • All date-time formats supported for the English USA culture
  • All date-time formats supported for the English New Zealand culture

The population engine supports the standard .NET date and time format strings. We've included a few examples below for reference:

Example #1

Example JSON:

{
"formedOn": "2008-08-10T13:49:51.141Z"
}

Token Format Configuration

mceclip15.png

<<[formedOn]:"dd MMM yy H:mm:ss">>

Output:

mceclip16.png

Example #2

Example JSON:

{
"formedOn": "2008-08-10T13:49:51.141Z"
}

Token Format Configuration

mceclip13.png

<<[formedOn]:"dd-MM-yyyy">>

Output:

mceclip14.png

Override Date Formatting

To override the default DateTime parsing configuration; please either set the 'Date Time Format' advanced property to:

  • A specific format, for example: DD/MM/YYYY
  • Specific formats using the '~' character, for example: MMMM dd, yyyy~DD/MM/YYYY
  • The value 'disable' to prevent JSON values from being parsed as a DateTime

Number Formats

To ensure precision or to make longer numbers easier to read number formatting can be applied. A few common examples are shown below:

Example JSON:

{
    "ThreeDP": 3.1415926,
  "Millions": 2001032.91,
    "MillionsWhole": 2001032.91
}

Example 1: Round to three decimal places

3DP1.png

<<[ThreeDP]: "#,##0.#00">>

Output:

3DP2.png

Example 2: Format a large number with commas

MillionsI.png

<<[Millions]: "#,##0.00">>

Output:

MillionsO.png

Example 3: Only show a whole number

MillionsWI.png

<<[MillionsWhole]: "#,##0">>

Output:

MillionsWO.png

Currency Formats

In a similar way to number formatting, currency formatting allows separators to be added to large numbers and decimal places to be limited. It also allows a currency symbol to be added. (this does not have to be the pound (£) symbol) 

Example JSON:

{
"Money" : 10005.12345,
"NegativeMoney": -5.00,
}

Example 1: Rounded to 2DP with the currency symbol and commas added (this works for positive and negative numbers)

Money1.png

<<[Money]: "£#,##0.00">>

Output:

MoneyO.png

Example 2: Show the minus symbol after the currency symbol (this will cater for positive and negative numbers; only showing the negative symbol if appropriate)

NMI.png

<<[NegativeMoney]: "£#,##0.00;£-#,##0.00">>

Output:

NMO.png

All the above examples assume that the decimal separator is a period and the thousand separator is a comma. We are aware that for certain European countries this is not the case and so the following example caters for cases where comma is the decimal separator and period is the thousand separator.

Example JSON

{
"Money" : 10005.12
}

This example will use Germany so firstly make sure the Culture Name parameter is correct; a complete list can be found here

Example 3: Add the separators as shown below do not switch the comma and period. The Culture name parameter will change these to what they should be. 

Text Box

<<[Money]: "€#,##0.00">>

Output:

Note: Setting the correct Culture Name is important to prevent unexpected results. For example in Belgium, both French and English are used. Setting the culture as "fr-BE" would result in

€10 005,12

Whereas a culture of "en-BE" would result in

€10.005,12

Other Formats

The population engine also supports the following formats for numbers, the following examples all utilise this example JSON data:

{
"example": 1
}

alphabetic: Formats a number as an upper-case letter (A, B, C, …)

Token Format Configuration

mceclip0.png

<<[example]:alphabetic>>

Output:

mceclip1.png

roman: Formats a number as an upper-case Roman numeral (I, II, III, …)

Token Format Configuration

mceclip2.png

<<[example]:roman>>

Output:

mceclip3.png

ordinal: Appends an ordinal suffix to a number (1st, 2nd, 3rd, …)

Token Format Configuration

mceclip4.png

<<[example]:ordinal>>

Output:

mceclip5.png

ordinalText: Converts a number to its ordinal text representation (First, Second, Third, …)

Token Format Configuration

mceclip6.png

<<[example]:ordinalText>>

Output:

mceclip7.png

cardinal: Converts a number to its text representation (One, Two, Three, …)

Token Format Configuration

mceclip8.png

<<[example]:cardinal>>

Output:

mceclip9.png

hex: Formats a number as hexadecimal (8, 9, A, B, C, D, E, F, 10, 11, …)

Token Format Configuration

mceclip10.png

<<[example]:hex>>

Output:

mceclip11.png

arabicDash: Encloses a number with dashes (- 1 -, - 2 -, - 3 -, etc.)

Token Format Configuration

mceclip12.png

<<[example]:arabicDash>>

Output:

mceclip13.png

 

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk