ScratchPad Calculated Field Definition

Document path Reference Contents > Preferences > ScratchPad Calculated Field Definition

This window is used to add or modify a calculated column in a ScratchPad. To do this you click on the Calculation button in the Settings Maintenance Subwindow.

You should refer to Omnis Studio documentation for full details of available functions that can be used in calculations.

Some common functions are listed below.



Variable name

Enter a Name for the variable that will be used in the list.


The heading that will appear on the list.

Field type

Select from the dropdown list of field types. The subtype list below will be reset depending on your selection.

Field sub-type

Select from the dropdown list of field sub-types, if any. This list will be reset depending on your selection of field types.


Enter the maximum length for character type variables only.


The calculation. This must be a valid Omnis Studio calculation using fields only from the main file used by the ScratchPad.




Aborts the process currently in session. The keyboard equivalent is the Esc key for Windows computers and Command-.(full stop or period) for the Mac.


Updates the selected list line.


Adds a new entry to the list line.

Customisation CUSTOM CAPABILITY: You can write custom functions (that can do literally anything) in the oCustom1 object class. Such methods should never disturb the state of the ScratchPad main file in the current record buffer and could upset some program operations if it is extreme in its function. To enter a custom function use the calculation syntax:

$ctask.tCustom1.$MyMethod(Parameters if any)

Common Functions





abs(-123.45) = 123.45

absolute value of the number N


asc('Train',2) = 114

the ASCII value of the Nth character in the string S (-1 if N=0 or > length of S)


cap('train') = 'Train'

capitalises text strings


con('T','R') = 'TR'

concatenates a series of strings


dat(#D,'YMD') = 010826

date value of a string or number optionally formatted in a particular way. Only strings with a recognizable date format can be converted. If the formatting parameter is omitted the context of the function will determine whether a numeric or string date is returned


int(26.9876) = 26

integer part of the number N


jst('abc','^5') = ' abc '

justification of a string S in a format determined by N (uses the same formatting parameters as described in the table below). The jst() function also includes concatenation, jst(S1,N1,S2,N2,...)



length of a string



advanced lookup function not for the faint-hearted, see Omnis Studio documentation


low('TRAIN') = 'train'

sets text strings to lower case


max(8,2,12) = 12

the maximum value from a list of either all numbers or all strings


mid('Train',2,3) = 'rai'

returns a substring from S starting at position N1, N2 long. If N1 is greater than the length, an empty string is returned


min(8,2,12) = 2

the minimum value from a list of either all numbers or all strings


mod(6,4) = 2

the modulus; the remainder of an integer division of N1 divided by N2


not(31<48) = not(TRUE) = 0

the compliment (opposite) of a Boolean expression E



select the N+1 item from a list, the first of the list is returned when N is 0


pos('A','TRAIN') = 3

the position of the first character of the string S2 found within S1. If no match is found it returns 0


rnd(29.89,1) = 30.0

rounds the number N1 to N2 decimal places


tim(950) = 15:50

time formatting similar to dat() for dates


upp('Train') = 'TRAIN'

sets text strings to upper case

Parameters in functions can be absolute values like 'Train' or 29.89, as illustrated above, variables like upp(CUSCNAM), other functions like pos('A',upp(CUSCNAM)) or calculations involving any combination. Normal math precedence is applied (i.e. (), *, /, +, -, &, |or).

Justification Syntax




causes the data to be centre justified in the field

places a sign in front of the data


places a $ sign in front of the data


causes left justification, overriding the default


causes right justification, overriding the default


causes the part of the field not filled with data to be filled with the character c, E.G. -6N2P* will give **2.99


causes the data to be truncated to a fixed number of characters or, if shorter, to be packed with spaces


causes the data to be converted to upper case


causes the data to be converted to lower case


causes the data to be capitalised


causes the data to be treated as a fixed decimal number. If there is no nn parameter, then a suitable number of decimal places is applied


causes the data to be treated as a date


causes the data to be treated as a time


causes the data to be treated as a Boolean Yes/No


applies only to numbers and leaves the field empty when the value is zero


applies only to numbers and places a separating comma in thousands positions: E.G. N2, will yield 2,555,666.22


applies only to numbers and places negative values in brackets: E.G. -22.88 with N2( will display (22.88)


applies only to numbers and shows negative values with a '-' on the right: E.G. -22.88 with N2) will display 22.88-


applies only to numbers and shows positive values with a '+': E.G. 22.88 with N2+ will display +22.88


causes the following characters to be interpreted as a formatting string. This must be the last option since all characters following it become part of the formatting string. The meaning of the formatting string depends on the type of the data. This is particularly useful for date/time fields where the following characters can be used as in D:CY
Y = Year in the form 01
y = Year in the form 2001
C = Century in the form 20
M = Month in the form 06
m = Month in the form JUN
n = Month in the form June
D = Day in the form 12
d = Day in the form 12th
W = Day of week in the form 5
w = Day of week in the form Friday
H = Hour in the form 0..23
h = Hour in the form 1..12
N = Minutes in the form 00..59
S = Seconds in the form 00..60
s = Hundredths in the form .00...99
A = AM/PM in the form AM..PM

For example "D:w, d n CY" will format as "Saturday, 29th November 2001"

If the data is neither a date or a time, and the formatting string contains an X, the data value is inserted at the position of the X: For example, where the data is 0, "BC:The answer is X! will format as "The answer is No!"

If the formatting string does not contain an X, then the formatting string is concatenated to the left of the data value: For example, with data 25.89, "-7N2:" will format as " 25.89".

See also: -

Compiled in Program Version 3.10. Help data last modified 22 MAY 2008 08:28. Class wScratchCalcField last modified 14 FEB 2009 05:24:04.

Document path Reference Contents > Preferences > ScratchPad Calculated Field Definition