Preferences: ScratchPad Calculated Field Definition

Variable name

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

Heading

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.

Length

Enter the maximum length for character type variables only.

Calculation

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

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

Updates the selected list line.

Add

Adds a new entry to the list line.

abs(N)

abs(-123.45) = 123.45

absolute value of the number N

asc(S,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(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’) = 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(N)

int(26.9876) = 26

integer 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’)=3

length of a string

low(S)

low(‘TRAIN’) = ‘train’

sets text strings to lower case

max(N1,N2,..)

max(8,2,12) = 12

the 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) = 2

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

mod(N1,N2)

mod(6,4) = 2

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

not(E)

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

the 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’) = 3

the 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.0

rounds the number N1 to N2 decimal places

tim(N,S)

tim(950) = 15:50

time formatting similar to dat() for dates

upp(S)

upp(‘Train’) = ‘TRAIN’

sets text strings to upper case

ˆ

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

Pc

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

nX

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

U

causes the data to be converted to upper case

L

causes the data to be converted to lower case

C

causes the data to be capitalised

Nnn

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

D

causes the data to be treated as a date

T

causes the data to be treated as a time

B

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

E

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″.

• Settings Maintenance Subwindow