Converts a value into its numeric equivalent.
Syntax
$tonumber(expression,decimals)
Parameters
expression | Any value, or an expression that yields any value. |
decimals | A number specifying the number of decimal places required in the converted value. Decimals can be negative. |
Return Value
Number equivalent of expression.
Comments
Use $tonumber to obtain the numeric equivalent of expression. If decimals is negative, the implicit number of decimal places in expression is used.
Example
$tonumber(1.273,2)
Evaluates to 1.27.
$tonumber(16/6,3)
Evaluates to 2.667.
$tonumber("2345",2)
Evaluates to 2345.00.
$tonumber("Smith",5)
Evaluates to 0.00000 (and produces an error).
$tonumber("1.2"*1.20,-1)
Evaluates to 1.44.
See Also
$money
$round
$toalpha
$tocharacter
About Functional Expressions
Decimals and Rounding
Decimals in Functional Expressions
Number Literals
Returns the minimum value of a set of values.
Syntax
$min(expression)
Parameters
Return Value
The value of the selected instance of expression.
Comments
Use $min to find the smallest value among members of a set. Instances of expression that are $Null are ignored.
Expression is often a WHERE expression that includes only selected values in the operation. If the WHERE expression (expr1 WHERE expr2) is true, the expr1 value is included in the operation; otherwise, expression is considered $Null and is ignored.
Example
compute Employees where DeptName = "Sales"
evaluate (let MaxSal = $max(Salary))
(let MinSal = $min(Salary))
(let MaxSalF = $max(Salary where Sex = "F"))
(let MinSalF = $min(Salary where Sex = "F"))
At the end of this operation, MaxSal contains the highest salary paid to any employee in Sales; MinSal contains the lowest salary paid to any employee in Sales; MaxSalF contains the highest salary paid to any female employee in Sales; and, MinSalF contains the lowest salary paid to any female employee in Sales.
See Also
$average
$count
$max
$total
About Functional Expressions
WHERE
Returns the maximum value of a set of values.
Syntax
$max(expression)
Parameters
Return Value
The value of the selected instance of expression.
Comments
Use $max to find the largest value among members of a set. Instances of expression that are $Null are ignored.
Expression is often a WHERE expression that includes only selected values in the operation. If the WHERE expression (expr1 WHERE expr2) is true, the expr1 value is included in the operation; otherwise, expression is considered $Null and is ignored.
Example
compute Employees where DeptName = "Sales"
evaluate (let MaxSal = $max(Salary))
(let MinSal = $min(Salary))
(let MaxSalF = $max(Salary where Sex = "F"))
(let MinSalF = $min(Salary where Sex = "F"))
At the end of this operation, MaxSal contains the highest salary paid to any employee in Sales; MinSal contains the lowest salary paid to any employee in Sales; MaxSalF contains the highest salary paid to any female employee in Sales; and, MinSalF contains the lowest salary paid to any female employee in Sales.
See Also
$average
$count
$min
$total
About Functional Expressions
WHERE
Returns the average value of a set of values, excluding $Null values.
Syntax
$average(expression)
Parameters
Return Value
Number, with the number of decimal places implied by expression.
Comments
The function calculates the average of a set of values, excluding $Null values.
Expression is often a WHERE expression that includes only selected values in the averaging operation. If the WHERE expression (expr1 WHERE expr2) is true, the expr1 value is included in the averaging operation; otherwise, expression is considered $Null and is not included in the averaging operation.
Example
compute Employees where DeptName="Sales"
evaluate (let AvgAge = $average($year($date)-$year(BirthDate)))
(let AvgSal = $average(Salary where Sex = "F"))
Finds the average age of all employees, and the average salary of all female employees, in the sales department.
report footing
$average(Salary)
($total(Salary*Salary) / $count(Salary) - $average(Salary) * $average(Salary))
Reports average salary and salary variance.
See Also
$count
$max
$min
$total
How to Use The Report Generator
WHERE
Builds a single character string out of separate strings, inserting a separator string.
Syntax
$squeeze(separator «,string»)
Parameters
separator | A character string, or an expression that evaluates to a character string. Separator is placed between each instance of string. |
string | A character string, or an expression that evaluates to a character string. Leading and trailing blanks are trimmed from string. Each string must be separated from the next by a comma. |
Return Value
Character string.
Comments
$squeeze combines specified strings into a single string, with separator placed between each string. In the function call, each string must be separated from the next by a comma. Leading and trailing blanks are trimmed from each string.
Example
$squeeze (",",LastName,FirstName,Salutation)
Evaluates to “Smith,John,Mr.” when FirstName is “John “, LastName is “Smith “, and Salutation is “Mr. “.
list all Ents format $squeeze ("",EntName,EntType,AvgSize,DirName)
Compresses the LIST output as much as possible.
Related Information
$concat
Calculates a date value by adding weeks to (or subtracting weeks from) a specified date value.
Syntax
$addweeks(date,number)
Parameters
date | a date, or an expression that evaluates to a DATE data type |
number | a number, or an expression that evaluates to a number |
Return Value
Number, representing a DATE value.
Comments
Use $addweeks to perform arithmetic with date values. The $addweeks function calculates a date value by adding a number representing weeks to a date value. If number is negative, the effect is to subtract the weeks from the date.
The + (add) and – (subtract) operators can be used to achieve the same results.
If date or the result of the $addweeks expression is an invalid date (e.g., 19930231), it is adjusted to produce a valid date (e.g., 19930228).
Example
If $Date has the value 19981225, then
$addweeks($date,1)
Evaluates to 19990101.
$addweeks($date,-2)
Evaluates to 1998.501.
$addweeks(19980201,4)
Evaluates to 19980301.
See Also
$adddays
$addmonths
$addyears
$todate
$weekday
+ (Add/Positive)
– (Subtract/Negative)
About Functional Expressions
Arithmetic with Dates
Generates the “sounds-like” value of a string.
Syntax
$soundex(source)
Parameters
source | a character string, or an expression that evaluates to a character string |
Return Value
Character string (alpha).
Comments
$soundex yields a “sounds-like” value for source. The value is created using an heuristic algorithm and is based on English pronunciation.
When $soundex is applied to two separate strings and they generate the same “sounds-like” value, then the two original strings can be assumed to sound the same. $soundex can be used to compare and find strings that sound the same but are spelled differently.
The result of $soundex is of data type ALPHA, therefore comparisons involving soundex values are case-insensitive.
Example
find all Customers where $soundex(LastName) = $soundex("Burton")
The above command returns customer records for names like Burton, Berton, Bertyn, Bertin, BURTON, BERTIN, and so on.
find all Customers where sLastName = $soundex("Burton")
The preceding command takes advantage of indexed searching by setting up sLastName as an indexed virtual field that contains the soundex values of LastName in the Customers EntitySet.
let SoundsLike = $soundex("Smith")
find all Customers where sLastName = SoundsLike?
The preceding code fragment uses prefix searching (? wildcard) with soundex values.
See Also
? (Wildcard)
About Character Literals
About Functional Expressions
Copies files from the client side to the server side.
Syntax
$copytoserver(source file name, destination file name, file type)
where
source file name | an expression that evaluates to a character string containing a valid file address |
destination file name | an expression that evaluates to a character string containing a valid file address |
file type | an expression that evaluates to a character string that starts either with “B” (from “Binary”) or “T” (from “Text”) |
Return Value
The value return by this function is the file address of the destination if it is executed correctly; otherwise, it will return a $Null value.
Comments
This function is designed to work under ZIMTC and copies file from the client side to the server side using the file type specified. Files of the binary type are sent as they are, without their contents being checked (like images, videos, etc). On the other hand, files of the text type are copied using text file conventions for Windows and Unix.
Both the source and the destination file names can be either relative or absolute file paths. The following remarks apply to these paths:
source file is absolute | the file is taken from the absolute path. Care should be taken because this absolute path refers to a location in the client’s machine; if there are many different users running, all users must have the same valid address |
destination file is absolute | the file is written onto the absolute path |
source file is relative | the file is read from a place that is relative to the current directory in the client session which is always where the Zimtc session has been started |
destination file is relative | normally, the current directory is the database directory; therefore, the file will be written on a place inside the current directory |
If the destination file name contains sub-directories that don’t exist, they are created.
The ZIM executable ignores this function as it makes no sense copying a file to itself.
Example
This example copies the text file from the database path to a fixed location in the server’s machine.
out $copytoserver("mytext.txt", "c:\mextext.txt", "T")
Related Topics
$copytoclient
Returns the position of the current member of a result set.
Syntax
$currentmember(setname)
Parameters
setname | a character string, or an expression that evaluates to a character string, being the name of a result set |
Return Value
Number, with no decimal places.
Comments
$currentmember returns the (numerical) position of the current member within the specified result set. To explicitly specify the current set, use “CurrentSet” (including the quotation marks), as CurrentSet is a reserved word and the function is expecting a character string.
To ensure against unexpected results, choose set names carefully. At run time, the software distinguishes sets only by their names, even if they are permanent set names created in different application directories.
If, for example, you produce a set called Set1 in directory PersonnelData, and later you produce another set called Set1 in directory TestData, the second version of Set1 replaces the first version. Function $currentmember works only on the most recent version of the set.
Example
let vMemberNum = $currentmember("Set1")
If the current member of Set1 is the fifth member, then variable vMemberNum is set to 5.
while
... process current member ...
if $currentmember("TempSet") = $lastmember("TempSet")
break
endif
next TempSet
endwhile
The WHILE loop processes TempSet one member at a time, breaking out when the last member of the set has been processed.
See Also
$lastmember
Converts an ordinal number into a character.
Syntax
$tochr(number)
Parameters
number | a number, or an expression that evaluates to a number |
Return Value
Each of the decimal numbers from 0 to 255 represents a character output by your workstation. $tochr converts a given number to the corresponding character.
Note: The ascii character 0 returns an ascii null value. Zim sees the null character as an End of Line character and stops outputting data. In addition, the ascii character 32 (a space) translates to a 0 since the $tochr function expects a number as the parameter. So, $tochr (” “) translates to $tochr(0) and results in the null value. No matter how encountered, a null value in a string truncates the string at the first null character encountered.
If number is negative or greater than 255, the result of the function is $Null.
The character produced by the $tochr function can be converted back to a decimal number by the $toord function.
Comments
Ordinal number refers to the decimal numbers 0 to 255 that you computer uses to represent the characters available on your system.
Example
$tochr(32)
Evaluates to a space (on ASCII machines).
$tochr($toord("Z"))
Evaluates to “Z”.
See Also
$left
$substring
$toalpha
$todate
$tonumber
About Character Literals
About Functional Expressions
Conversion Between Data Types