CAPITAL

Converts the first word in the returned string to initial caps.

CAPITAL
(text)

CAPITAL(@COMP_NAME)

Displays the string with the first letter of the first word capitalized; for example: Atlas city.

CAPITALW

Converts the returned string to capitalized words.

CAPITALW
(text)

CAPITALW(@COMP_NAME)

Displays each word in the string as a capitalized name; for example: Atlas City.

FILL

Fills a string of a given length with the specified (“pad”) characters.

FILL(len,
[pad])

where pad is the optional character or characters for filling

FILL(8,*)

Returns a string of eight characters, using * to represent all empty characters in the string. For example, HOME would be displayed as HOME****

Similar or related functions: RPAD, LPAD, ZPAD

FORMAT

Enables the formatting of translatable output strings, by using variables.

FORMAT(text,
parameter list)

where text is the string (including placeholder values for the variable strings), and parameter list is the comma-separated list of parameters. You can specify an unlimited number of parameters.

=FORMAT("Mr. {1} {2}", @FIRSTNAME, @LASTNAME)

Displays @FIRSTNAME and @LASTNAME values in the placeholders at runtime, for example Mr. John Smith.

The values of the additional parameters (following @) are inserted into the placeholders during runtime.

The placeholder values reference the additional parameters according to their order of appearance. Therefore placeholder {2} in the text string refers to the @LASTNAME value. Placeholder values start with '1', not '0'.

If a placeholder value has no corresponding string parameter, an empty string is used. If a string parameter has no corresponding placeholder value, it is ignored.

MID

Returns a string starting from a specified number of characters from the beginning of the string and containing the next specified number of characters.

MID(text,
start,len)

MID(@SHORT_TEXT,5,3)

Displays a substring consisting of the fifth, sixth and seventh character of the string. For example, DOCUMENTATION would be displayed as MEN.

RIGHT
LEFT

Returns a substring of a specified number of characters, starting from the end of the string (RIGHT) or beginning of the string (LEFT).

RIGHT(text,
[len])

LEFT(text,
[len])

Where len is the optional number of characters

RIGHT(@SHORT_TEXT,4)

Displays the last four characters of the string. For example, a value of FINALCOST would be displayed as COST.

LEFT(@Country,3)

Displays the first three characters of the returned string. For example, DOCUMENTATION would be displayed as DOC.

TRANSLATE

Indicates the string parts that require translation.

TRANSLATE(text,
[tType])

where text is the string to be translated and tType (optional) is one of the classifications listed in Table F at the end of this section

=IF(@Status=="1", TRANSLATE("green"), TRANSLATE("red"))

Specifies that the values "green" and "red" should be included in the translation file. If these values are translated (using translation tools), the corresponding translated text appears at runtime, according to the value of the Status field.

=(TRANSLATE("Football", "XTIT")=="Football")

When used in the Hidden property of a control, hides the control for languages that translate the term 'football' as something other than 'football'.

Do not use TRANSLATE for static values (use it only for dynamic expressions) because interpretation of the TRANSLATE function may affect runtime performance.

TRIM

Removes the white space from both ends of the string.

LTRIM removes the white space only from the left.

TRIM(text)

TRIM(@YEAR_TO_END)

Removes the white space in the returned value. For example, the value “    ANNUAL TURNOVER    “ would be displayed as ANNUAL TURNOVER (without the spaces at the beginning and the end).

Related or similar functions: COMPACT, LTRIM, RTRIM

UPPER
LOWER

Converts the returned string to upper case or lower case, respectively.

UPPER(text)

LOWER(text)

UPPER(@COUNTRY)

Displays the string in all capital letters; for example: ATLAS.

LOWER(@COUNTRY_NAME)

Displays the string in all lower-case letters; for example: usa

CEIL

Rounds a number upward to the nearest integer or multiple of a defined factor.

CEIL
(n,factor)

where factor is the basis for rounding off the number

CEIL(@num,0.01)

Returns a number rounded off to the next highest multiple of 0.01. For example, 6.2468 would be rounded off to 6.25.

Related or similar function: FLOOR

MAX

Returns the largest value among a specified set of values.

MAX(n1,n2)

where n indicates a numbered list with unlimited arguments

MAX(EARNINGS_Q1,@EARNINGS_Q2)

Displays the larger of the values of the two cells.

Related or similar function: MIN

NSTR

Returns a formatted string representing the defined number.

NSTR(n,mask)

where mask (optional) is a formatted sequence defined according to Table E at the end of this section

NSTR(@num,’B’)

Displays or hides zero/blank values. For example, an empty space or a value of 0 would not be displayed.

NSTR(@SD_DOC,’Z’)

Displays leading zeros in the field value. For example, if there are 10 characters in the string, the number 1432 would be displayed as 0000001432.

NSTR(@num,’10.2’)

Rounds off a long decimal output to a two-place decimal value. For example, a value of 142.3213762 would be displayed as 142.32.

NSTR(@TOTAL_PRICE,’C’)

Adds a comma separator to values in the thousands. For example, a value of 682328 would be displayed as 682,328.

DADD
TADD

Increases/decreases a date/time value by the specified number of date units.

DADD(dt,n,
unit)

TADD (t,n,unit)

where unit (optional) is one of the enumerations listed in Table A at the end of this section

TADD(@CREATE_TIME,13,’H’)

Adds 13 hours to the returned value. For example: 10:17:22 would be converted to 23:17:22

Default format: HH:NN:SS

DGET
TGET

Returns the numeric value of the selected part of a date or time.

DGET(dt,
part)

TGET(t,part) where part is one of the enumerations listed in Table C at the end of this section

DGET(@CREATE_DATE,’M’)

Returns only the value of the specified time unit from within the date value. For example, 30.05.2002 would return 5, while 27.02.2004 would return 2.

DSTR
TSTR

Converts a date or time object format to a defined text string.

DSTR (dt,mask)

DSTR (t,mask)

where mask (optional) is one of the devised characters from the set of special characters listed in Table B at the end of this section

DSTR(@DATE_FIELD,’MON DD, YYYY’)
Converts the returned date value to a string date format. For example, 30.05.2002 would be converted to May 30, 2002

DSTR(DADD(@CREATE_DATE,13,’D’),’XML_DATE’)
Using the DADD function, this expression adds 13 days to the returned date, and then converts the date format to the XML standard format. For example, 30.05.2002 would be converted to: 2002-06-12

DSUB
TSUB

Returns the difference between two dates, in the specified date unit.

DSUB(dt1,dt2,unit)

TSUB (t1,t2,unit)

where unit (optional) is one of the enumerations listed in Table A at the end of this section

DSUB(NOW(),@CREATE_DATE,’D’)

Calculates the difference in the two dates, specified here, in days. For example, values of 16.09.2004 (today) and 05.8.2004 for these two fields would return 39.

DVAL
TVAL

Converts a date or time string to a standard date/time object format.

DVAL(str,
seq)

TVAL(str,
seq)

where seq is:

EUR (DD/MM/YYYY HH:NN:SS)

USA (MM/DD/YYYY HH:NN:SS)

XML
(YYYY-MM-DDTHH:NN:SS)

FREE (free format)

DVAL(@TIME_FIELD,USA)

Converts the date string returned to a standard format. For example,
May 30, 2002 would be converted to the USA standard date format: 05/30/2002.

NOW
TNOW

Returns the current date or time.

NOW()

NOW()

See the example for the DSUB function.

QGET

Extracts the numeric component of the specified quantity.

QGET(q)

where q is the quantity

QGET(20USD)

Extracts the numeric component from within the 20USD quantity value, in this case, 20.

QGETUNIT

Extracts the unit code of the specified quantity.

QGETUNIT(q)

where q is the quantity

QGETUNIT(20USD)

Extracts the unit code from within the 20USD quantity value, in this case, USD.

QSTR

Returns a formatted string of the specified quantity in the format “NUM UNIT", where NUM is the numeric component and UNIT is the unit code (note the space between NUM and UNIT).

QSTR(q,[mask])

where q is the quantity and mask is an optional formatted sequence defined according to Table E at the end of this section

QSTR(1250EUR)

Returns the 1250EUR quantity as a formatted string in the format "NUM UNIT", in this case, 1250 EUR.

QSTR(1250USD,'C.2')

Returns the 1250USD quantity as a formatted string in the format "NUM UNIT", adding a comma separator to values in the thousands, and rounding off the decimal output to a two-place decimal value, in this case, 1,250.00 USD.

QVAL

Parses a string containing a quantity value. If the string contains an invalid quantity value, the result is undefined.

QVAL(str)

Where str is a string containing a quantity value in the format “NUM UNIT"

QVAL('125.75 USD')

Parses the string 125.75 USD as a quantity value, in this case, 125.75USD.

QVAL('200 EUR')

Parses the string 200 EUR as a quantity value, in this case, 200EUR.

IF

Checks whether a specified condition is met. If it is, returns the first specified value; otherwise, returns the second specified value.

IF(test,
expr1,expr2)

IF(@NET_PRICE>1000,’Expensive’,’Cheap’)

If the expression in the field is TRUE (the value is greater than 1000), the text Expensive is displayed. If the expression is FALSE (the value is equal to or less than 1000), the text Cheap is displayed.

IF(@initials==’DE’,’Germany’,’Other’)

If the string DE is returned, the text Germany is displayed. Otherwise, the text Other is displayed.

IF(@BANK_CTRY==’US’,United States’, IF(BANK_CTRY==’GB’,’England’,IF(@BANK_

CTRY==’IL’,’Israel’,IF(@BANK_CTRY==’DE’,

’Germany’,’Other’))))

A nested IF expression defining display text for country names. If the value returned is not US, GB, IL, or DE, the text Other is displayed.

ISNULL

Returns TRUE if a reference is to an empty, undefined or null cell; otherwise, returns FALSE.

ISNULL

(value)

IF(ISNULL(@num),’NO FAX NUMBER’, @num)

If the value of the @num is empty, the value NO FAX NUMBER is displayed; otherwise, the returned value is displayed.

Mathematical operators

+

-

*

/

%

&

Addition

Subtraction

Multiplication

Division

Modulus arithmetic

Concatenatation (Connects two returned values in order to produce a single, continuous value, using the syntax(string)&(string))

Comparison operators

<

<=

>

>=

==

!=

<>

Less than

Less than or equal to

Greater than

Greater than or equal to

Equal to

Not equal to

Not equal to

Logical operators

AND

&&

││

OR

NOT

!

Logical AND

Logical AND

Logical OR

Logical OR

Logical NOT

Logical NOT

Z

Milliseconds

1_ms

S

Seconds

1,000_ms

N

Minutes

60,000_ms

H

Hours

3,600,000_ms

D

Days

86,400,000_ms

M

Months

2,592,000,000_ms

Q

Quarters

7,776,000,000_ms

Y

Years

31,536,000,000_ms

C

Centuries

3,153,600,.000,000_ms

Mask

Description

date-part

Listed in Table C at the end of this section

embedded-chars

Character codes other than those listed in Tables A and C

special-format

Listed in Table D at the end of this section

D

Days as 1-31

DD

Days as 01-31

DAY

Days as Sun-Sat

M

Months as 1-12

MM

Months as 01-12

MON

Months as Jan-Dec

Y

Years as 0-99

YY

Years as 00-99

YYYY

Years a 1900-9999

MS

Milliseconds as 000-999

SS

Seconds as 00-59

NN

Minutes as 00-59

H

Hours as 0-23

HH

Hours as 00-23

H12

Hours as 0-12

HH12

Hours as 00-12

AM|PM

Meridian indicator

DATE

DD/MM/YYYY

31/01/2001

SHORT_DATE

D/M/Y

31/1/1

LONG_DATE

Long date format

Wed Jan 31 2001

XML_DATE

YYYY-MM-DD

2001-01-31

LOCALE_DATE

Locale date format

Jan 31, 2001

TIME

HH:NN:SS

02:39:40

SHORT_TIME

H:NN

2:39

LONG_TIME

Long time format

02:39:40 UTC+0200

XML_TIME

THH:NN:SS

02:39:40

LOCAL_TIME

Local time format

2:39:40

DATETIME

DD/MM/YYYY HH:NN:SS

31/01/2001 02:39:40

SHORT_DATETIME

D/M/Y H:NN

31/1/1 2:39

LONG_DATETIME

Long date/time format

Wed Jan 31 2001 02:39:40 UTC+0200

XML_DATETIME

YYYY-MM-DDTHH:NN:SS

2001-01-31T02:39:40

LOCALE_DATETIME

Locale date/time format

2:39:40, Jan 31, 2001

+

Displays + for positive values and - for negative values.

-

Displays a space for positive values and - for negative values.

Z

Displays insignificant digits as zeros.

P

Displays insignificant digits as spaces.

B

Displays a space when the value is zero, regardless of other codes.

C

Inserts a thousands separator into the number.

N

Number of digits to display to the left of the decimal point. If omitted, only the significant digits are displayed.

.m

Number of digits to display to the right of the decimal point. If omitted, only the significant digits are displayed.

@r

Displays numbers using radix r (2 - 36).

%

Displays numbers as a percentage of 100.

$

Inserts the locale-specific currency code

[CUR]

Inserts the given currency code. The currency code must be enclosed in square brackets (such as [USD] or [EUR]).

NORMAL

Displays the number in standard format.