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

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.

File Path

File > preferences > User Settings > Calculated button

This file path takes you to the Calculated Field Definition window

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.

Maximum length

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.

Cancel button

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.

Update button

Updates the selected list line.

Add button

Adds a new entry to the list line.

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 quantity 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(N)abs(-123.45) = 123.45absolute value of the number N
asc(S,N)asc(‘Train’,2) = 114the ASCII value of the Nth character in the string S (-1 if N=0 or > length of S)
cap(S)cap(‘train’) = ‘Train’capitalises text strings
con(S1,S2,..)con(‘T’,’R’) = ‘TR’concatenates a series of strings
dat(D,F)dat(#D,’YMD’) = 010826date 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(N)int(26.9876) = 26integer part of the number N
jst(S,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,…)
len(S)len(‘abc’)=3length of a string
low(S)low(‘TRAIN’) = ‘train’sets text strings to lower case
max(N1,N2,..)max(8,2,12) = 12the maximum value from a list of either all numbers or all strings
mid(S,N1,N2)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(N1,N2,..)min(8,2,12) = 2the minimum value from a list of either all numbers or all strings
mod(N1,N2)mod(6,4) = 2the modulus; the remainder of an integer division of N1 divided by N2
not(E)not(31<48) = not(TRUE) = 0the compliment (opposite) of a Boolean expression E
pick(N,P1,P2)pick(1,’T’,’R’,’A’,’I’)=’R’select the N+1 item from a list, the first of the list is returned when N is 0
pos(S1,S2)pos(‘A’,’TRAIN’) = 3the position of the first character of the string S2 found within S1. If no match is found it returns 0
rnd(N1,N2)rnd(29.89,1) = 30.0rounds the number N1 to N2 decimal places
tim(N,S)tim(950) = 15:50time formatting similar to dat() for dates
upp(S)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
Pccauses the part of the field not filled with data to be filled with the character c, E.G. -6N2P* will give **2.99
nXcauses the data to be truncated to a fixed number of characters or, if shorter, to be packed with spaces
Ucauses the data to be converted to upper case
Lcauses the data to be converted to lower case
Ccauses the data to be capitalised
Nnncauses 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
Dcauses the data to be treated as a date
Tcauses the data to be treated as a time
Bcauses the data to be treated as a Boolean Yes/No
Eapplies 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 AlsoSettings Maintenance Subwindow

Compiled in Program Version 6.00.  Help date last modified on 9 April 2024

Class wscratchcalcfield (Scratchpad Calculated Field Definition) last modified 25 May 2022 FP: File > preferences > User Settings > Calculated button

Was this article helpful?

Get started.

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