Adding a Format String will convert the value for a Field into a Read-Only text value that is in the format that the user will expect to read the value. It does this by adding a _Formatted backing Field that contains the formatted string value of the Field. You can then use the original Field for use in expressions and to display in a record for data input and you can use the formatted backing Field for Read-Only display in Records and Reports.
Formatting Number Fields
Common reasons for wanting to add a Format String to a numeric Field include displaying a value as money, limiting the display to a set number of decimal places and showing a comma separated number.
Standard Examples
Some common examples for the number 384537.21356 are:
{0:N2} - 384,537.21 - Represents Number format comma separated to the number of decimal places given
{0:F1} – 384537.2 - Represents Fixed format, where 1 represents the number of fixed points
{0:E3} – 3.845E+005 - Represents Scientific format to the number of decimal places defined here as 3
{0:G2} – Represents General format which will take the more compact of Fixed Point or Scientific string to the decimal places defined
{0:C} - $384,537.21 - Represents Currency format in US$ comma separated to 2d.p.
{0:P1} – 384,537.2 % - Represents Percent format comma separated to the number of decimal places defined (default is nearest integer)
Custom Examples
You can also add fixed characters to the Format string for a more custom value:
£{0:N2}p - £384,537.21p - It will display the formatted string for the number {0:N2} and also the other text characters in the order they appear.
Negative Numbers
{0:#,##0;(#,##0);0} - using blocks (separated by semi colons) indicate what to do when the number is positive;negative;zero - so in this example the number -2000 would be shown formatted as (2,000)
Note: Existing data will need record values updated before formatted values will appear.
Formatting Date and DateTime Fields
The input expected for a date Field will depend on both the browser and the language of the browser being used. For dates that take their values via expressions you can specify the format that you would like them to appear in and also use this format for Read-Only display in Reports.
Standard Examples
Some common examples for the date 23rd April 2017 are:
{0:d} - 4/23/2017 - Displays the date as a short date. Note it is month then day using this format string
{0:D} - Sunday, April 23, 2017 - Displays the date as a long date
{0:M} - April 23 - Returns just the month and day for the date
{0:Y} - April 2017 - Returns the month and year for the date
Custom Examples
There are a number of different display options for each of the date components:
Year - y, yy, yyy, yyyy or yyyyy depending on the minimum number of digits you want returned. If the year is lower than the number of digits then it will prefix the value with 0 until in the format required
Month - M returns the month, MM returns the month prefixed with 0 if less than 0, MMM returns the abreviated name for the month of the year, MMMM returns the full name for the month of the year
Day - d returns the day, dd returns the day prefixed with a 0 if less than 10, ddd returns the abbreviated name of the day of the week, dddd returns the full name of the day of the week
By putting these components together and entering the separators that you would like as text characters you can assemble a custom date format that fits for your users
{0:dd-MM-yyy} - 23-04-2017 - The day, month and year formats have been selected separated by '-'
Date is:{0:d MMM, yyyyy} - Date is: 26 Apr, 02017 - It can also add static text around the custom date format
Custom display of hours, minutes and seconds are also supported.
Hour - h returns 12hr clock to min 1 digit, hh returns 12hr clock to 2 digits, H returns 24hr clock to min 1 digit, HH returns 24hr clock to 2 digits
Minute - m returns minutes to min 1 digit, mm returns minutes to 2 digits
Seconds - s returns seconds to min 1 digit, ss returns minutes to 2 digits
Note: Existing data will need record values updated before formatted values will appear.
Patching Existing Record Data
Adding a format string adds a backing Field that contains the string value for the format given. This value will be populated when the Field changes value. This means that it will be fine for newly created records but if there are existing records then you will need to patch the data in order for the formatted backing Field to populate and be able to use this value in Records and Reports.
To set the formatted values for existing Fields you will need to update the value for the Field. If the Field is one that the user directly enters data into then this can be done via export and import of the data. First export the data to .csv. Remove all Fields from the import file except the one that you have added the format string to and then re-import the data. This will populate the formatted string backing value.
If the Field that you have added the format string to takes its value via expression then you will need to trigger a change to the value. An example would be if you had a monetary format string on a Field for total value that was calculated as [Hours] * [CostPerHour]. In order to set the backing field we need this expression to run. To do that we would use export and import to set all of the values for [CostPerHour] to a unique value such as £0.01, then set the values back to what they originally were. This change in value will trigger the expression to run which will set the formatted backing value for the Field.
Make sure to click the 'Save' button when making any changes in order for them to be added to the next app version. Once you have made all the changes you need to an application you are then ready to publish it to workspace.
Comments
0 comments
Article is closed for comments.