OVERVIEW
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.
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.
Maximum 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 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):
$ctask.tCustom1.$MyMethod(pRow.PTMPTNO)
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:
pRow.PTMCMC+pRow.PTMCLC+pRow.PTMCOC+pRow.PTMCOVC+pRow.PTMCROC
Common Functions
| Syntax | Example | Description |
| 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 |
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
| Syntax | Result |
| ˆ | 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″. |
| See Also | Settings 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
