Contents

Overview

The format column is generally useful for specifying a format for numeric, date/time and list data, and affects how the field data is displayed in the QEST Platform. The format strings are generally very similar to the types that you might define in Microsoft Excel.

Numeric

QESTLab and QESTField support the following types of formatting/rounding for numeric fields:

[0-9]+([.][0-9]+)?r

[0-9]+([.][0-9]+)?rb

[0-9]+sf

[0-9]+sfrb

^#Banker's rounding - https://en.wikipedia.org/wiki/IEEE_754#Rounding_rules

^%Note that for all rounding and significant figure formats, the numeric component must be greater than zero.  Rounding to the nearest zero (or to zero significant figures) doesn't make sense.

Specifying multiple numeric formats

Custom fields also support a format string that specifies a set of formats to use for different ranges of values.

The range format is essentially a list of range/format pairs, separated with a semi-colon (;)

R:<range1>:<format1>;<range2>:<format2>;<range3>:<format3>...

The <range> is specified using an f or F to designate a 'from' value, and a t or T to designate a 'to' value.

• To specify an inclusive range, use upper-case letters, F and/or T.
• To specify an exclusive range, use lower-case letters, f and/or t.
• You can omit the from and/or to portion of a range to include all values smaller/larger than specified.  

e.g.:

The range t5 means all numbers less than 5

The range F20 means all numbers greater than or equal to 20

The range f20T100 means all numbers greater than 20, and less than or equal to 100

An empty range means all numbers (you can use as the final item in the list, to specify a default format)

 
The <format> is specified using one of the numeric formats in the table above.

A complete example:

R:T5:0.0;f5T10:0.2r;:0

Explanation:

• T5:0.0 - for any value less than or equal to 5, use the format "0.0" (round to 1 decimal place).  This includes all negative numbers.
• f5t10:0.2r - for any value greater than 5, and less than or equal to 10, use the format "0.2r" (round to nearest 0.2)
• :0 - for any (other) value, use the format "0" (round to nearest whole number)

Invalid Format Strings

For numeric fields, in QESTField, any format string that is not recognized as a numeric format will not be used.

Date/Time

Named and custom format strings can be used with date/time custom fields.  These format strings will be used in QESTLab and QESTField to affect the display of date and time data.

• QESTLab requires the format string to be a valid VB6 format string, and uses the format string to display and interpret entered values
• QESTField only uses the format string to determine whether to display a time input or a date input

For detailed documentation on custom date/time formats for QESTLab, see: https://msdn.microsoft.com/en-us/library/aa263414(VS.60).aspx

Date Inputs

Named date formats:

• Short Date
• Medium Date
• Long Date
• General Date

Examples of custom date formats:

• yyyy-mm-dd
• mm/dd/yyyy
• dd/mm/yyyy

Time Inputs

Named time formats:

• Short Time
• Medium Time
• Long Time

Examples of custom time formats:

• hh:nn
• hh:nn:ss
• h:nn AM/PM
• h:nn:ss AM/PM
• hhnn
• hhnnss

Invalid Format Strings

For date and time fields, in QESTField, any format string that is not recognized as a time format, a date input will be used for the custom field.

Since QESTField doesn't use the date/time format for any other purpose, an invalid format will not cause issues in QESTField.

The full range of custom format strings that will detected as time formats is described by the following regular expression:

^[Hh]h?:?[NMnm][nm]:?[Ss]?s?[ ]?(?:[aA][mM]?[/]?[pP][mM]?)?$

Lists

The format setting can also be used with string fields to create custom dropdown lists.

Pre-defined lists

A standard fixed list dropdown can be created by adding the format string:

LIST:Item1;Item2;Item3;

Where Item1, Item2, and Item3 refer to the strings which populate each item in the dropdown. Any number of items can be added to the list.

Example:

List:Fine Sand;Coarse Sand;Gravel;

QESTLab Lists

A QESTLab list dropdown can be created by specifying the format string:

LISTRESTRICT:<ListField>;<ListId>

Where <ListId> is the QESTLab list identifier, and <ListField> is field/column in the QESTLab list that will be used for the dropdown.

Example:

LISTRESTRICT:ClientName;20001

This will display a dropdown containing items from the Client list.

Link To

When using QESTLab lists, a pair of custom fields that map to the same list can be linked together.  This is typically used for lists that contain a Code/Name pair.

When a pair of fields are linked together, selecting a value for one of them will automatically update the second field with the corresponding value from the same list entry.

Note that this functionality only supports a pair of linked fields.  If there are three or more fields that use the same QESTLab list, only two of them can be linked.

Invalid Format Strings

For all lists

• The syntax of the format string is verified

For QESTLab lists

• The list identifier is checked to ensure that it is in the range 20001-29999, however it not checked to ensure that it corresponds to a valid list
• The list field is not verified (i.e. to ensure that it exists).  An invalid field name will result in a runtime error
• The 'Link To' field is verified, to ensure that it corresponds to a custom field in the same custom field set that uses the same QESTLab list

If the verification fails the format will not be used.

Products described on these pages, including but not limited to QESTLab®, QESTNet, QESTField, QEST Web App, Construction Hive, and associated products are Trademarks (™) of Spectra QEST Australia Pty Ltd and/or related companies.

The content of this page is confidential. Do not share, duplicate or distribute without permission.

© 2021 Spectra QEST® Australia Pty Ltd and/or related companies.  Terms of Use and Privacy Statement