Applies to: NITRO Workflows and Custom Actions (Online)
For complete list of functions, please refer to this article.
Introduction
This function is used to get the string output from Date/Number/Currency column in specified format.
Note: Target Column should be Plain Text column.
Syntax:
With Expression Builder Ex:
To use ‘Format Value 2’ function without expression builder use below syntax:
$formatvalue2(Currency/DateTime/Number,[Placeholder],FormatSpecifier,CultureInfo)
Parameters for the function:
Currency/DateTime/Number:
Specify type of input as Currency/DateTime/Number to format the input
Placeholder:
Specify the column placeholder, fixed Value, or variable for the input
Format Specifier:
Specify the output format as described below:
| Type of source Column | Format Specifier with Expression Builder | Format Specifier without Expression Builder |
| Currency | {0:c} | @{0:c} |
| Number | {0:n} | @{0:c} |
| DateTime | {0:dd/MM/yy} | @{0:dd/MM/yy} |
Note:
1. DateTime value will be taken as per time zone configured for the site (regional settings).
2. Specify number for decimal places in format specifier. Default value is 2 decimal places.
For ex:
- {0:c1}: here ‘1’ represent currency with one decimal place.
For currency format specifier refer below link:
Currency Format In C# (techieclues.com)
- {0:n0} : here ‘0’ represent Number with zero decimal place.
For number format specifier refer below link
Standard numeric format strings | Microsoft Learn
- For date format specifier refer below link:
https://www.techieclues.com/blogs/string-format-for-datetime-in-c-sharp
Culture Info:
This parameter is supported in NITRO Workflows only. If this parameter is blank then default value is “en-US” which represents country “United State”. Culture Info formats the Currency/Number/DateTime value as per the country specified in culture info.
Note: ‘Culture Info’ is only for format and does not change the date time value as per the time zone of the country.
To know Culture Info Code, follow below link:
Please refer below table ‘formatvalue2’ function usage examples:
| Target Column | Param1: Currency/DateTime/Number | Param2: [Placeholder] | Param3: [Format Specifier] | Param4: [CultureInfo] | Output |
| Plain Text Column/Variable Ex: [CurrencyUSText] | Currency | Placeholder/Fixed Value/Variable Ex: [SourceCurrency] = 20000 | {0:c} | $20000.00 | |
| Plain Text Column/Variable Ex: [NumberUSText] | Number | Placeholder/Fixed Value/Variable Ex: [SourceNumber]=500 | {0:n} | 500.00 | |
| Plain Text Column/Variable Ex: DateUSText | DateTime | Placeholder/Fixed Value/Variable Ex: [SourceDate]= 6/23/2023 6AM | {0:dd/MM/yy} | 23/06/23 | |
| Plain Text Column/Variable Ex: [CurrencyArgentina] | Currency | Placeholder/Fixed Value/Variable Ex: [SourceCurrency] = 20000 | {0:c0} | es-AR(Argentina) | $ 20.000 |
| Plain Text Column/Variable Ex: [CurrencyGermany] | Currency | Placeholder/Fixed Value/Variable Ex: [SourceCurrency] = 20000 | {0:c0} | de-DE(Germany) | 20.000 € |
| Plain Text Column/Variable Ex: [CurrencyBelgium] | Currency | Placeholder/Fixed Value/Variable Ex: [SourceCurrency] = 20000 | {0:c0} | fr-BE(Belgium) | 20.000 € |
| Plain Text Column/Variable Ex: [NumberArgentina] | Number | Placeholder/Fixed Value/Variable Ex: [SourceNumber] = 500 | {0:n3} | es-AR | 500,000 |
| Plain Text Column/Variable Ex: [NumberGermany] | Number | Placeholder/Fixed Value/Variable Ex: [SourceNumber] = 500 | {0:n3} | de-DE | 500,000 |
| Plain Text Column/Variable Ex: [NumberBelgium] | Number | Placeholder/Fixed Value/Variable Ex: [SourceNumber] = 500 | {0:n3} | fr-BE | 500,000 |
| Plain Text Column/Variable Ex: [DateTimeArgentina] | DateTime | Placeholder/Fixed Value/Variable Ex: [SourceDate] = 6/23/2023 6AM | {0:dddd, dd MMMM yyyy hh:mm tt} | es-AR | viernes, 23 junio 2023 06:00 a.m |
| Plain Text Column/Variable Ex: [DateTimeGermany] | DateTime | Placeholder/Fixed Value/Variable Ex: [SourceDate] = 6/23/2023 6AM | {0:dddd, dd MMMM yyyy hh:mm tt} | de-DE | Freitag, 23 Juni 2023 06:00 |
| Plain Text Column/Variable Ex: [DateTimeBelgium] | DateTime | Placeholder/Fixed Value/Variable Ex: [SourceDate] = 6/23/2023 6AM | {0:dddd, dd MMMM yyyy hh:mm tt} | fr-BE | vendredi, 23 juin 2023 06:00 |
Sample use case for FormatValue2 function: Calculate total amount of all associated items when new main item is created and generate a document with item values.
- List structure:
- “UH – Requests” is the main list.
- “UH PFA” is the associated list. It has a lookup column for “UH – Requests” list.
- “UH – Requests” has a NITRO associated items column ‘Associated UHPFA’ to show related “UH PFA” items.
- “Amount” is a currency column in “UH PFA” list.
- “Quantity” is a number column in “UH PFA” list.
Steps:
- Workflow is configured on the “UH – Requests” list.
- Define variables to get the total amount and total quantity specified in ‘Amount’ and ‘Quantity’ column in “UH PFA” list items.
- Run Query list action to get all related “UH PFA” items. And in variable mapping, calculate total amount and total quantity.
- Configure a generate document action to generate document and attach it to the item.
Workflow:
Define Variables:
Action 1: Get related PFAs
Configure ‘Query Builder’ condition as shown below:
- UHRequest = lookupid:%%[ID|ID]%%
This will get all related PFAs.
Configure Variable mapping as shown below:
- varGrandTotal = Get related PFAs##$sum([Amount|Amount])
This will get the sum of Amount of all related PFAs.
- varTotalQuantity = Get related PFAs##$sum([Quantity|Quantity])
This will get the sum of quantity of all related PFAs.
Action 2: Generate Pdf document for request
Generate a document that has total amount and total quantity of PFAs linked to this request item.
Column mapping for GrandTotal:
Column mapping for Total Quantity:
Column mapping for Created:
Note: ‘es-AR’ is Culture Info Code of ‘Argentina’. So, Currency/DateTime/Number will be formatted according to this Culture Info Code.
Below Item is created in ‘UH – Requests’ list
Generated Pdf document for request is shown below:
