Formulas: formatting, string validation

Formatting dates, times and numbers

Formula runtime has been extended with Format() function which has number of variants.

Unlike ToText() family of functions, Format() respects user-specific locale settings and time zone and the result can not be parsed back to original value.

Format() function works on Date, TimeOfDay, Timestamp and Numeric types. First argument is the value to format. If column reference is provided, TeamDesk derives formatting options from the column properties.

If you provide an expression as the first argument (and, therefore, formatting options can not be determined), you can use two-arguments version of Format() function – second parameter is a text literal specifying the format; or three-arguments version to provide the format AND unit, if it is different from "$" or "%"

Format strings are specific for each type. You can find the list of allowed strings below.

Type	Format	Meaning
Date	"d"	Short Date
	"D"	Long Date
	"M"	Month Day
	"Y"	Year Month
TimeOfDay	"t"	Short Time
	"T"	Long Time
Timestamp	"g"	Short Date Short Time
	"G"	Short Date Long Time
	"f"	Long Date Short Time
	"F"	Long Date Long Time

Due to number of possible options numeric format specifier is a bit more complicated.

* The position of the unit can be specified by prefixing the string with "$" (left side) or "-$" (between sing and number), or by (optionally) suffixing the string with "$".

* The unit placeholder is either "$" for numbers and currencies and "%" for percent columns and formulas. Latter explicitly specifies the value should be multiplied by 100 prior to display.

* Use zero digit preceding decimal point (if any) to specify that zero values should be displayed. Use pound sign to suppress zero values and display blank string instead.

* Use "#," construct before the specifier described above to designate that thousand separators should be displayed.

* The precision is specified by the number of zeroes or pound sign (#) following decimal point. Use "0" to indicate trailing zeros should be displayed.

Here are some examples:

Format	Meaning
$#	Unit to the left, no digit grouping, hide zero values, no fractional part
#,#	Unit to the right, digit grouping, hide zero values, no fractional part
#,0	Unit to the right, digit grouping, display zero values, no fractional part
0	Unit to the right, no digit grouping, display zero value, no fractional part. This is the default format for Numeric columns.
0%	Unit to the right, no digit grouping, display zero values, no fractional part, multiple value by 100. This is the default format for Numeric – Percent columns.
$#,#.####	Unit to the left, digit grouping, hide zero values, 4 fractional digits, trim trailing zeroes
-$#,0.00	Unit between sign and number, digit grouping, display zero values, 2 fractional digits, display trailing zeros. This is the default format for Numeric – Currency columns.

Validating user input against character pattern

TeamDesk supports validation rules to check if user input is correct. Validation rules allow you to check whether the values entered are in certain range or correlate to each other. However, checking the text against the pattern, such as SSN number was non-trivial task.

To help dealing with patters we are introducing Match(text, pattern) function. First argument can be any type of text expression. Second argument is a text literal specifying the regular expression to check.

Regular expression language is very powerful and capable to handle variety of scenarios ranging from simple to quite complex. Many of samples for everyday use can be found in the internet. Here are just two examples:

"^[0-9]{3}[ -]?[0-9]{2}[ -]?[0-9]{4}$"

checks if the text looks like SSN number – has the group of three digits followed by the group of two then by group of four digits. Yet, this expression passes invalid SSN 000-00-0000. Below is more complicated example that rejects SSN containing all-zero groups and not-yet-allocated numbers (those that starts with 7-9)

"^(?!000)([0-6][0-9]{2}|7([0-6][0-9]|7[012]))[ -]?(?!00)[0-9]{2}[ -]?(?!0000)[0-9]{4}$"

Please note that Match() is a relatively time-consuming function and currently its usage is limited to validation rules only.

Comments

[Nathan Phillips]

NICE. I'm loving both of these new features. Very cool. Thanks.