Calculation Commands

Quick reference information for Calculation Commands

However, sometimes you may need to add a new field from a pick list in order find the name of what you want.

jst(string1,number1,string2,number2,string3,number3,...) Returns a string containing the specified string left or tight justified with sufficient spaces added to make a total length specified by number.

The jst() function also includes concatenation. If number is negative the resulting string is right justified, if the number is positive the string is left justified; number must be in the range of 1 to 999.

• jst('This is left justified',30) gives |This is left justified   |
• jst('This is right justified',-30) gives |  This is right justified|

• jst('This is centered',^30) gives | This is centered |

• jst('This is Right',-20) gives |*******This is Right|where * represents spaces

• jst(PS_TOTAL_PAID,'$' gives $12.00 if the ticket price paid was 12.00

• jst(PS_TOTAL_PAID,'-7P*' gives '**12.00' if the ticket price paid was 12.00

• jst('abcdef','4') gives 'abcdef'
• jst('abcdef','4X') gives 'abcd'

• jst('this IS upper Case','U' gives 'THIS IS UPPER CASE'

• jst('this IS lower Case','L' gives 'this is lower case'

• jst('this IS Capitalized','C' gives 'This Is Capitalized'

• jst(0.235,'N') gives '0.235'
• jst(0.235,'N2') gives '0.24'

• jst(0,'N2') gives '0.00'
• jst(0,'N2E') gives ' '

• jst(1234,'N2') gives '1234.00'
• jst(1234,'N2,') gives '1,234.00'

Returns the capitalized representation of a string, that is, the first letter of each and every word in the string is capitalized.

• cap('tHeATre MANAGER') returns 'Theatre Manager'

Returns a string concatenating or combining two or more string values.

Theatre Manager will build concatenations. Simply select more than one field from the available fields list when editing the field calculation. To format the concatenation simply treat the concatenation as one field calculation.

• con(C_FIRST_NAME,'/',C_ADDRESS1,'/',C_POSTAL_CODE).

First name, address, and postal code information together on the same line and places a slash between each item. Formatting is limited to the group of fields linked together.

• jst(con(MS_SECTION,', Row ',MS_ROW_NUMBER,', Seat ',MS_SEAT_NUMBER),'^38x').

Centers section, row, and seat number in a space 38 characters wide. A comma and row precede the row number and separate it from the actual number. A comma and seat precede the seat number and separate it from the actual number.

• jst(con(dtw(PB_PERFORM_DATE),', ',dtm(PB_PERFORM_DATE),dtd(PB_PERFORM_DATE),dtcy(PB_PERFORM_DATE),' Time: ', jst(PB_PERFORM_TIME,'T:h:N A')),'^50')

Prints 'Saturday, December 21 1998 Time: 8:00 PM'. The printing would be centered in a 50 character space.

• len('abcd') returns '4'
• len('abcd') + 15 returns '19'

• low('tHeATre MANAGER') returns 'theatre manager'
• low('1234') returns '1234'

• mid('Theatre Manager',9,3) returns 'Man'
• mid('Theatre Manager',9,24) returns 'Manager'

• pos(' ',J. Smith') returns 2 (the position of the space character)
• pos('Mouse','Mickey Mouse') returns 8
• pos('mouse','Mickey Mouse') returns 0 - note the case of the substring

• upp('tHeATre MANAGER') returns 'THEATRE MANAGER'
• upp('1234') returns '1234'
• upp(mid(dtw(PB_PERFORM_DATE),1,3)) returns 'MON'

CR's LF's, Tabs are converted to spaces and double spaces are then removed from within the string.

will take a long string like P_PLAY_TITLE and find entire words within the width of 40 characters and return only those.

eg, for a title like:

• The Curious Incident of the Dog In the Night Time
• 40 characters is: The Curious Incident of the Dog In the N
• The last space before the 40th character is the space before the 'N'
• segment #1 is: The Curious Incident of the Dog In the
• Segment #2 is: Night Time

Converts a datestring or number to a date value using a second optional argument, a dateformat string. The date formatting string can be composed of the following symbols:

• Y Year 98 (no century)
• y Year 1998 (with century)
• C Century 19
• m Month short form (JUN)
• M Month number (06)
• n Month long form (June)
• D Day of month (12)
• d Day of month (12th)
• W Day of week (5)
• w Day of week long form (Friday)
• V Day of week short form (FRI)
• E Day of year (between 1 and 366)
• G Week of Year (between 1 and 52)
• F Week of Month (between 1 and 6)

• dat('Dec 21 11','MDY') returns '122111'
• dat('Dec 21 11','D m Y') returns '21 DEC 11'
• dat('Dec 21 11','w, d n, y') returns 'Wednesday, 21st December, 1911'
• dat('Dec 21 98','V m D Y') returns 'WED DEC 21 98'

Increments a datestring by a number of months. Months containing different numbers of days are accounted for.

Jan 31 96 increased by one month gives Feb 29 96, but beware, Feb 29 96 decreased by one month (using a negative number) returns Jan 29 96, not Jan 31 96.

• dim(dat('Dec 21 98',3)) returns 'Mar 21 99'

Returns the year and century of a datestring as a string.

• dtcy(dat('Dec 21 98')) returns '1998'

• dtcy(dat('Dec 21 98')) returns '21st'
• dtcy(dat('Dec 21 98')) + 0 returns '21'
• dtcy(dat('Dec 21 98')) + 1 returns '22'

• dtm(dat('Dec 21 98')) returns 'December'

• dtw(dat('Dec 21 98')) returns 'Monday'
• upp(mid(dtw(PB_PERFORM_DATE),1,3)) returns 'MON'

• dtcy(dat('Dec 21 98')) returns '98'

Converts a timestring to a time value using a second optional argument, a timeformat string. The time formattinG string can be composed of the following symbols:

• H Hour (between 0 and 23)
• h Hour (between 1 and 12)
• N Minutes
• S Seconds
• s Hundredths
• A AM/PM

• tim('13:25:57','h:N A')) returns '1:15 PM'
• tim('13:25:57','h:N.S A')) returns '1:15.57 PM'

 Returns the maximum value from the list of values. The values should all be numbers when numeric comparison is used or all strings when string comparison is used.

• max(3,6,2,7) returns '7'
• max('dagger','dog','digger') returns 'dog'

• min(3,6,2,7) returns '2'
• min('dagger','dog','digger') returns 'dagger'

Selects an item from a list of string or numeric values. The number argument is rounded to an integer and used to select the item. value0 is returned if the result is 0, value1 if the result is 1, value2 if the result is 2, and so on. If the number is less than zero or greater than the number of values in the list, then an empty value is returned. The list of values can be a mixture of string and numeric values.

• pick(2,'A','B','C','D') returns 'C'
• pick(2>4,'A','B','C','D') returns 'A', as 2 is not greater than 4, thus it is False, where False equals 0.
• pick(2<4,'A','B','C','D') returns 'B', as 2 is less than 4, thus it is True, where True equals 1.
• pick(pos('A','DEF')>0,'Price Not Found','Price Found') returns 'Price Not Found'
• pick(pos('A','ABC'),'Price Not Found','Price A','Price B','Price C') returns 'Price A'

Returns the magnitude of a real number ignoring its positive or negative sign.

• abs(1002) returns '1002'
• abs('-203.45') returns '203.45'
• abs('12ABC') returns '0'

 Returns the integer part of a number; it does not round to the nearest integer.

• int(23.1056) returns '23'
• int('-2.66') returns '-2'
• abs(int('-2.66')) returns '2'

• mod(6,4) returns '2'
• mod(6,4) returns '2'
• mod(9,1.5) returns '0'

Rounds a number to a specified number of decimal places by dp .

• rnd(2.105693,2) returns '2.11'
• rnd(0.5,0) returns '1'