1. Home
  2. Knowledge Base
  3. Preferences
  4. Preferences: ScratchPad Calculated Field Definition

Preferences: ScratchPad Calculated Field Definition

ScratchPad Calculated Field Definition

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 tables used by the ScratchPad with columns referenced using the pRow. prefix.




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 be self-contained and return a single value for the column. They can be written with multiple parameters that derive from the main table using pRow. syntax. To enter a custom function use the calculation syntax (showing part number):


The above example is available and returns the total net sales quntity for the part.

Calculated columns must be Omnis Studio calculations. They are calculated after the ScratchPad list row has been loaded with the main table row. You therefore must use the pRow. prefix syntax when referencing a main table column. For example, this is the calculation for total part current cost:


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


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(pRow.CUSCNAM), other functions like pos(‘A’,upp(pRow.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 5.10. Help data last modified 7 Jun 2016 05:13:00.00. Class wScratchCalcField last modified 10 Oct 2017 11:48:43.

Was this article helpful?

Related Articles

Need Support?

Can't find the answer you're looking for?
Contact Support

Get started.

Try our state-of-the-art ERP Software today.