DataWindow Reference
DataWindow Expression Functions

Description

Calculates the absolute value of a number.

Syntax

Abs ( n )

Return value

The datatype of n. Returns the absolute value of n.

Examples

This expression counts all the product numbers where the absolute value of the product number is distinct:

Count(product_number for All DISTINCT Abs (product_number))

Only data with an absolute value greater than 5 passes this validation rule:

Abs(value_set) > 5

See also

Count

Abs in the section called “Abs” in PowerScript Reference

Description

Calculates the arc cosine of an angle.

Syntax

ACos ( n )

Return value

Double. Returns the arc cosine of n if it succeeds.

Examples

This expression returns 0:

ACos(1)

This expression returns 3.141593 (rounded to six places):

ACos(-1)

This expression returns 1.000000 (rounded to six places):

ACos(.540302)

See also

Cos

ASin

ATan

ACos in PowerScript Reference

Description

Converts the first character of a string to its Unicode code point. A Unicode code point is the numerical integer value given to a Unicode character.

Syntax

Asc ( string )

Return value

Unsigned integer. Returns the code point value of the first character in string.

Usage

Use Asc to test the case of a character or manipulate text and letters.

To find out the case of a character, you can check whether its code point value is within the appropriate range.

Examples

This expression for a computed field returns the string in code_id if the code point value of the first character in code_id is A (65):

If (Asc(code_id) = 65, code_id, "Not a valid code")

This expression for a computed field checks the case of the first character of lname and if it is lowercase, makes it uppercase:

IF (Asc(lname) > 64 AND Asc(lname) < 91, lname, WordCap(lname))

See also

Char

WordCap

Asc in the section called “Asc” in PowerScript Reference

Description

Converts the first character of a string to its ASCII integer value.

Syntax

AscA ( string )

Return value

Integer. Returns the ASCII value of the first character in string.

Usage

Use AscA to test the case of a character or manipulate text and letters.

To find out the case of a character, you can check whether its ASCII value is within the appropriate range.

Examples

This expression for a computed field returns the string in code_id if the ASCII value of the first character in code_id is A (65):

If (AscA(code_id) = 65, code_id, "Not a valid code")

This expression for a computed field checks the case of the first character of lname and if it is lowercase, makes it uppercase:

IF (AscA(lname) > 64 AND AscA(lname) < 91, lname, WordCap(lname))

See also

CharA

WordCap

AscA in PowerScript Reference

Description

Calculates the arc sine of an angle.

Syntax

ASin ( n )

Return value

Double. Returns the arc sine of n if it succeeds.

Examples

This expression returns .999998 (rounded to six places):

ASin(.84147)

This expression returns .520311 (rounded to six places):

ASin(LogTen (Pi (1)))

This expression returns 0:

ASin(0)

See also

Sin

ACos

ATan

Pi

ASin in the section called “ASin” in PowerScript Reference

Description

Calculates the arc tangent of an angle.

Syntax

ATan ( n )

Return value

Double. Returns the arc tangent of n if it succeeds.

Examples

This expression returns 0:

ATan(0)

This expression returns 1.000 (rounded to three places):

ATan(1.55741)

This expression returns 1.267267 (rounded to six places):

ATan(Pi(1))

See also

Tan

ASin

ACos

ATan in the section called “ATan” in PowerScript Reference

Description

Calculates the average of the values of the column.

Syntax

Avg ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )

Return value

The numeric datatype of the column. Returns the average of the values of the rows in range.

Usage

If you specify range, Avg returns the average value of column in range. If you specify DISTINCT, Avg returns the average value of the distinct values in column, or if you specify expresn, the average of column for each distinct value of expresn.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:

In calculating the average, null values are ignored.

Examples

This expression returns the average of the values in the column named salary:

Avg(salary)

This expression returns the average of the values in group 1 in the column named salary:

Avg(salary for group 1)

This expression returns the average of the values in column 5 on the current page:

Avg(#5 for page)

This computed field returns Above Average if the average salary for the page is greater than the average salary:

If(Avg(salary for page) > Avg(salary), "Above Average", " ")

This expression for a graph value sets the data to the average value of the sale_price column:

Avg(sale_price)

This expression for a graph value sets the data value to the average value of the sale_price column for the entire graph:

Avg(sale_price for graph)

Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the average of the order amount for the distinct order numbers:

Avg(order_amt for all DISTINCT order_nbr)

See also

Median

Mode

Description

Displays the specified image file.

Syntax

Bitmap ( string )

Return value

The special datatype bitmap, which cannot be used in any other function.

Usage

Use Bitmap to dynamically display an image file in a computed field. When string is a column containing image files, a different image can display for each row.

Examples

These examples are all expressions for a computed field.

This expression dynamically displays the image file contained in the column named employees:

Bitmap(employees)

If the employees column is column 3, this next expression gives the same result as the expression above:

Bitmap(#3)

This expression displays the tools.bmp file:

Bitmap("TOOLS.BMP")

If the image file has no file extension, the method will check whether the file is a valid image file (a BMP, GIF, JPEG, RLE, or WMF file). For example, this expression displays the person file if it is a valid image file.

Bitmap("E:\images\person")

This expression tests the value in the column named password and then uses the value to determine which image to display:

Bitmap(If(password = "y", "yes.bmp", "no.bmp"))

See also

Example 3: creating a row indicator

Description

Tests the values of a column or expression and returns values based on the results of the test.

Syntax

Case ( column WHEN value1 THEN result1 { WHEN value2 THEN result2 
   { ... } } { ELSE resultelse } ) 

Return value

The datatype of resultn. Returns the result you specify in resultn.

Usage

If more than one WHEN clause matches column, Case returns the result of the first matching one.

Examples

This expression for the Background.Color property of a Salary column returns values that represent red when an employee's salary is greater than $70,000, green when an employee's salary is greater than $50,000, and blue otherwise:

Case(salary WHEN IS >70000 THEN RGB(255,0,0) WHEN IS
>50000 THEN RGB(0,255,0) ELSE RGB(0,0,255))

This expression for the Background.Color property of an employee Id column returns red for Id 101, gray for Id 102, and black for all other Id numbers:

Case(emp_id WHEN 101 THEN 255 WHEN 102 THEN RGB(100,100,100) ELSE 0)

This expression for the Format property of the Marital_status column returns Single, Married, and Unknown based on the data value of the Marital_status column for an employee:

Case(marital_status WHEN 'S'THEN 'Single' WHEN 'M' THEN
'Married' ELSE 'Unknown')

See also

Example 3: creating a row indicator

If

Description

Retrieves the smallest whole number that is greater than or equal to a specified limit.

Syntax

Ceiling ( n )

Return value

The datatype of n. Returns the smallest whole number that is greater than or equal to n.

Examples

These expressions both return -4:

Ceiling(-4.2)

Ceiling(-4.8)

This expression for a computed field returns ERROR if the value in discount_amt is greater than the smallest whole number that is greater than or equal to discount_factor times price. Otherwise, it returns discount_amt:

If(discount_amt <= Ceiling(discount_factor * price),
String(discount_amt), "ERROR")

To pass this validation rule, the value in discount_amt must be less than or equal to the smallest whole number that is greater than or equal to discount_factor times price:

discount_amt <= Ceiling(discount_factor * price)

See also

Int

Round

Truncate

Ceiling in the section called “Ceiling” in PowerScript Reference

Description

Converts an integer to a Unicode character.

Syntax

Char ( n )

Return value

String. Returns the character whose code point value is n.

Examples

This expression returns the escape character:

Char(27)

See also

Asc

Char in the section called “Char” in PowerScript Reference

Description

Converts an integer to an ASCII character.

Syntax

CharA ( n )

Return value

String. Returns the character whose ASCII value is n.

Examples

This expression returns the escape character:

CharA(27)

See also

AscA

CharA in the section called “CharA” in PowerScript Reference

Description

Calculates the cosine of an angle.

Syntax

Cos ( n )

Return value

Double. Returns the cosine of n.

Examples

This expression returns 1:

Cos(0)

This expression returns .540302:

Cos(1)

This expression returns -1:

Cos(Pi(1))

See also

Pi

Sin

Tan

Cos in the section called “Cos” in PowerScript Reference

Description

Calculates the total number of rows in the specified column.

Syntax

Count ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )

Usage

If you specify range, Count determines the number of rows in column in range. If you specify DISTINCT, Count returns the number of the distinct rows displayed in column, or if you specify expresn, the number of rows displayed in column where the value of expresn is distinct.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.

Settings for Rows include the following:

Null values in the column are ignored and are not included in the count.

Examples

This expression returns the number of rows in the column named emp_id that are not null:

Count(emp_id)

This expression returns the number of rows in the column named emp_id of group 1 that are not null:

Count(emp_id for group 1)

This expression returns the number of dept_ids that are distinct:

Count(dept_id for all DISTINCT)

This expression returns the number of regions with distinct products:

Count(region_id for all DISTINCT Lower(product_id))

This expression returns the number of rows in column 3 on the page that are not null:

Count(#3 for page)

See also

Example 1: counting null values in a column

Description

Calculates the average of the values returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabAvg can also calculate averages of the expression's values for groups of column values.

Syntax

CrosstabAvg ( n {, column, groupvalue } )

Return value

Double. Returns the average of the crosstab values returned by expression n for all the column values or, optionally, for a subset of column values. To return a decimal datatype, use CrosstabAvgDec.

Usage

This function is meaningful only for the average of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band.

Null values are ignored and are not included in the average.

How functions in a crosstab are used

When a crosstab is generated from your definition, the appropriate computed fields are automatically created using the Crosstab functions. To understand the functions, consider a crosstab with two columns (year and quarter), a row (product), and the values expression Avg(amount for crosstab).

The Crosstab Definition dialog box looks like this.

When you define the crosstab described above, the painter automatically creates the appropriate computed fields. A computed field named avg_amount returns the average of the quarterly figures for each year. Its expression is:

CrosstabAvg(1, 2, "@year")

A second computed field named grand_avg_amount computes the average of all the amounts in the row. Its expression is:

CrosstabAvg(1)

Other computed fields in the summary band use the Avg function to display the average of the values in the amount column, the yearly averages, and the final average.

The crosstab in the Design view looks like this.

Each row in the crosstab (after adjusting the column widths) has cells for the amounts in the quarters, a repeating cell for the yearly average, and a grand average. The crosstab also displays averages of the amounts for all the financial codes in the quarters in the summary band at the bottom.

What the function arguments mean

When the crosstab definition has more than one column, you can specify column qualifiers for any of the Crosstab functions, so that the crosstab displays calculations for groups of column values. As illustrated previously, when year and quarter are the columns in the crosstab, the expression for the computed field is:

CrosstabAvg(1, 2, "@year")

The value 2 refers to the quarter column (the second column in the Crosstab Definition dialog) and "@year" specifies grouping values from the year column (meaning the function will average values for the quarters within each year). The value 1 refers to the crosstab-values expression that will be averaged. In the resulting crosstab, the computed field repeats in each row after the cells for the quarters within each year.

Tips for defining crosstabs

When you define a crosstab with more than one column, the order of the columns in the Columns box of the Crosstab Definition dialog box governs the way the columns are grouped. To end up with the most effective expressions, make the column that contains the grouping values (for example, year or department) the first column in the Columns box and the column that contains the values to be grouped (for example, quarter or employee) second.

To display calculations for groups of rows, define groups as you would for other DataWindow presentation styles and define computed fields in the group header or footer using noncrosstab aggregation functions, such as Avg, Sum, or Max.

Examples

The first two examples use the crosstab expressions shown below:

Count(emp_id for crosstab),Sum(salary for crosstab)

This expression for a computed field in the crosstab returns the average of the employee counts (the first expression):

CrosstabAvg(1)

This expression for a computed field in the crosstab returns the average of the salary totals (the second expression):

CrosstabAvg(2)

Consider a crosstab that has two columns (region and city) and the values expression Avg(sales for crosstab). This expression for a computed field in the detail band computes the average sales over all the cities in a region:

CrosstabAvg(1, 2, "@region")

This expression for another computed field in the same crosstab computes the grand average over all the cities:

CrosstabAvg(1)

See also

CrosstabAvgDec

CrosstabCount

CrosstabMax

CrosstabMin

CrosstabSum

Description

Calculates the average of the values returned by an expression in the values list of the crosstab and returns a result with the decimal datatype. When the crosstab definition has more than one column, CrosstabAvgDec can also calculate averages of the expression's values for groups of column values.

Syntax

CrosstabAvgDec ( n {, column, groupvalue } )

Return value

Decimal. Returns the average of the crosstab values returned by expression n for all the column values or, optionally, for a subset of column values.

Usage

Use this function instead of CrosstabAvg when you want to return a decimal datatype instead of a double datatype. For more information, see CrosstabAvg.

See also

CrosstabMaxDec

CrosstabMinDec

CrosstabSumDec

Decimal support in DataWindow expressions

Description

Counts the number of values returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabCount can also count the number of the expression's values for groups of column values.

Syntax

CrosstabCount ( n {, column, groupvalue } )

Return value

Long. Returns the number of values returned by expression n for all the column values or, optionally, for a subset of column values.

Usage

This function is meaningful only for the count of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band.

Null values are ignored and are not included in the count.

For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg.

Examples

These examples all use the crosstab-values expressions shown below:

Count(emp_id for crosstab), Sum(salary for crosstab)

This expression for a computed field in the crosstab returns the count of the employee counts (the first expression):

CrosstabCount(1)

This expression for a computed field in the crosstab returns the count of the salary totals (the second expression):

CrosstabCount(2)

The next two examples use a crosstab with two columns (year and quarter), a row (product), and the values expression Avg(sales for crosstab).

This expression for a computed field returns the count of the sales for each year:

CrosstabCount(1, 2, "@year")

This expression for a computed field returns the count of all the sales in the row:

CrosstabCount(1)

For an example illustrating how the painter automatically defines a crosstab by creating computed fields using the Crosstab functions, see CrosstabAvg.

See also

CrosstabAvg

CrosstabMax

CrosstabMin

CrosstabSum

Description

Calculates the maximum value returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabMax can also calculate the maximum of the expression's values for groups of column values.

Syntax

CrosstabMax ( n {, column, groupvalue } )

Return value

Double. Returns the maximum value returned by expression n for all the column values or, optionally, for a subset of column values. To return a decimal datatype, use CrosstabMaxDec.

Usage

This function is meaningful only for the maximum of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band.

Null values are ignored and are not included in the comparison.

For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg.

Examples

These examples all use the crosstab-values expressions shown below:

Count(emp_id for crosstab), Sum(salary for crosstab)

This expression for a computed field in the crosstab returns the maximum of the employee counts (the first expression):

CrosstabMax(1)

This expression for a computed field in the crosstab returns the maximum of the salary totals (the second expression):

CrosstabMax(2)

The next two examples use a crosstab with two columns (year and quarter), a row (product), and a values expression Avg(sales for crosstab).

This expression for a computed field returns the largest of the quarterly average sales for each year:

CrosstabMax(1, 2, "@year")

This expression for a computed field returns the maximum of all the average sales in the row:

CrosstabMax(1)

For an example illustrating how the painter automatically defines a crosstab by creating computed fields using the Crosstab functions, see CrosstabAvg.

See also

CrosstabAvg

CrosstabCount

CrosstabMaxDec

CrosstabMin

CrosstabSum

Description

Calculates the maximum value returned by an expression in the values list of the crosstab and returns a result with the decimal datatype. When the crosstab definition has more than one column, CrosstabMaxDec can also calculate the maximum of the expression's values for groups of column values.

Syntax

CrosstabMaxDec ( n {, column, groupvalue } )

Return value

Decimal. Returns the maximum value returned by expression n for all the column values or, optionally, for a subset of column values.

Usage

Use this function instead of CrosstabMax when you want to return a decimal datatype instead of a double datatype. For more information, see CrosstabMax.

See also

CrosstabAvgDec

CrosstabMinDec

CrosstabSumDec

Decimal support in DataWindow expressions

Description

Calculates the minimum value returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabMin can also calculate the minimum of the expression's values for groups of column values.

Syntax

CrosstabMin ( n {, column, groupvalue } )

Return value

Double. Returns the minimum value returned by expression n for all the column values or, optionally, for a subset of column values. To return a decimal datatype, use CrosstabMinDec.

Usage

This function is meaningful only for the minimum of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band.

Null values are ignored and are not included in the comparison.

For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg.

Examples

These examples all use the crosstab-values expressions shown below:

Count(emp_id for crosstab), Sum(salary for crosstab)

This expression for a computed field in the crosstab returns the minimum of the employee counts (the first expression):

CrosstabMin(1)

This expression for a computed field in the crosstab returns the minimum of the salary totals (the second expression):

CrosstabMin(2)

The next two examples use a crosstab with two columns (year and quarter), a row (product), and the values expression Avg(sales for crosstab).

This expression for a computed field returns the smallest of the quarterly average sales for each year:

CrosstabMin(1, 2, "@year")

This expression for a computed field returns the minimum of all the average sales in the row:

CrosstabMin(1)

For an example illustrating how the painter automatically defines a crosstab by creating computed fields using the crosstab functions, see CrosstabAvg.

See also

CrosstabAvg

CrosstabCount

CrosstabMax

CrosstabMinDec

CrosstabSum

Description

Calculates the minimum value returned by an expression in the values list of the crosstab and returns a result with the decimal datatype. When the crosstab definition has more than one column, CrosstabMinDec can also calculate the minimum of the expression's values for groups of column values.

Syntax

CrosstabMinDec ( n {, column, groupvalue } )

Return value

Decimal. Returns the minimum value returned by expression n for all the column values or, optionally, for a subset of column values.

Usage

Use this function instead of CrosstabMin when you want to return a decimal datatype instead of a double datatype. For more information, see CrosstabMin.

See also

CrosstabAvgDec

CrosstabMaxDec

CrosstabSumDec

Decimal support in DataWindow expressions

Description

Calculates the sum of the values returned by an expression in the values list of the crosstab. When the crosstab definition has more than one column, CrosstabSum can also calculate the sum of the expression's values for groups of column values.

Syntax

CrosstabSum ( n {, column, groupvalue } )

Return value

Double. Returns the total of the values returned by expression n for all the column values or, optionally, for a subset of column values. To return a decimal datatype, use CrosstabSumDec.

Usage

This function is meaningful only for the sum of the values of the expression in a row in the crosstab. This means you can use it only in the detail band, not in a header, trailer, or summary band.

Null values are ignored and are not included in the sum.

For more information about restricting the calculation to groups of values when the crosstab definition has more than one column, see Usage for CrosstabAvg.

Examples

These examples all use the crosstab-values expressions shown below:

Count(emp_id for crosstab),Sum(salary for crosstab)

This expression for a computed field in the crosstab returns the sum of the employee counts (the first expression):

CrosstabSum(1)

This expression for a computed field in the crosstab returns the sum of the salary totals (the second expression):

CrosstabSum(2)

The next two examples use a crosstab with two columns (year and quarter), a row (product), and the values expression Avg(sales for crosstab).

This expression for a computed field returns the sum of the quarterly average sales for each year:

CrosstabSum(1, 2, "@year")

This expression for a computed field returns the sum of all the average sales in the row:

CrosstabSum(1)

For an example illustrating how the painter automatically defines a crosstab by creating computed fields using the Crosstab functions, see CrosstabSum.

See also

CrosstabAvg

CrosstabCount

CrosstabMax

CrosstabMin

CrosstabSumDec

Description

Calculates the sum of the values returned by an expression in the values list of the crosstab and returns a result with the decimal datatype. When the crosstab definition has more than one column, CrosstabSumDec can also calculate the sum of the expression's values for groups of column values.

Syntax

CrosstabSumDec ( n {, column, groupvalue } )

Return value

Decimal. Returns the total of the values returned by expression n for all the column values or, optionally, for a subset of column values.

Usage

Use this function instead of CrosstabSum when you want to return a decimal datatype instead of a double datatype. For more information, see CrosstabSum.

See also

CrosstabAvgDec

CrosstabMaxDec

CrosstabMinDec

Decimal support in DataWindow expressions

Description

Calculates the total value of the rows up to and including the current row in the specified column as a percentage of the total value of the column (a running percentage).

Syntax

CumulativePercent ( column { FOR range } )

Return value

Long. Returns the cumulative percentage value.

Usage

If you specify range, CumulativePercent restarts the accumulation at the start of the range.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.

Settings for Rows include the following:

In calculating the percentage, null values are ignored.

Examples

This expression returns the running percentage for the values that are not null in the column named salary:

CumulativePercent(salary)

This expression returns the running percentage for the column named salary for the values in group 1 that are not null:

CumulativePercent(salary for group 1)

This expression entered in the Value box on the Data property page for a graph returns the running percentage for the salary column for the values in the graph that are not null:

CumulativePercent(salary for graph)

This expression in a crosstab computed field returns the running percentage for the salary column for the values in the crosstab that are not null:

CumulativePercent(salary for crosstab)

See also

Percent

CumulativeSum

Description

Calculates the total value of the rows up to and including the current row in the specified column (a running total).

Syntax

CumulativeSum ( column { FOR range } )

Return value

The appropriate numeric datatype. Returns the cumulative total value of the rows.

Usage

If you specify range, CumulativeSum restarts the accumulation at the start of the range.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:

In calculating the sum, null values are ignored.

Examples

This expression returns the running total for the values that are not null in the column named salary:

CumulativeSum(salary)

This expression returns the running total for the values that are not null in the column named salary in group 1:

CumulativeSum(salary for group 1)

This expression entered in the Value box on the Data property page for a graph returns the running total for the salary column for the values in the graph that are not null:

CumulativeSum(salary for graph)

This expression in a crosstab computed field returns the running total for the salary column for the values in the crosstab that are not null:

CumulativeSum(salary for crosstab)

See also

CumulativePercent

Description

Reports the number of the current row (the row with focus).

Syntax

CurrentRow ( )

Return value

Long. Returns the number of the row if it succeeds and 0 if no row is current.

Examples

This expression in a computed field returns the number of the current row:

CurrentRow()

This expression for a computed control displays an arrow bitmap as an indicator for the row with focus and displays no bitmap for rows not having focus. As the user moves from row to row, an arrow marks where the user is:

Bitmap(If(CurrentRow() = GetRow(),"arrow.bmp",""))

Alternatively, this expression for the Visible property of an arrow picture control makes the arrow bitmap visible for the row with focus and invisible for rows not having focus. As the user moves from row to row, an arrow marks where the user is:

If(CurrentRow() = GetRow(), 1, 0)

See also

Example 3: creating a row indicator

GetRow

Description

Converts a string whose value is a valid date to a value of datatype date.

Syntax

Date ( string )

Return value

Date. Returns the date in string as a date. If string does not contain a valid date, Date returns null.

Usage

The value of the string must be a valid date.

Valid dates

Valid dates can include any combination of day (1-31), month (1-12 or the name or abbreviation of a month), and year (two or four digits). Leading zeros are optional for month and day. If the month is a name or an abbreviation, it can come before or after the day; if it is a number, it must be in the month location specified in the Windows control panel. A 4-digit number is assumed to be a year.

If the year is two digits, the assumption of century follows this rule: for years between 00 and 49, the first two digits are assumed to be 20; for years between 50 and 99, the first two digits are assumed to be 19. If your data includes dates before 1950, such as birth dates, always specify a four-digit year to ensure the correct interpretation.

The function handles years from 1000 to 3000 inclusive.

An expression has a more limited set of datatypes than the functions that can be part of the expression. Although the Date function returns a date value, the whole expression is promoted to a DateTime value. Therefore, if your expression consists of a single Date function, it will appear that Date returns the wrong datatype. To display the date without the time, choose an appropriate display format. (See Using DataWindow expression functions.)

Examples

These expressions all return the date datatype for July 4, 2004 when the default location of the month in Regional Settings is center:

Date("2004/07/04")
Date("2004 July 4")
Date("July 4, 2004")

See also

IsDate

Date in the section called “Date” in PowerScript Reference

Description

Combines a date and a time value into a DateTime value.

Syntax

DateTime ( date {, time } )

Return value

DateTime. Returns a DateTime value based on the values in date and optionally time. If time is omitted, DateTime uses 00:00:00.000000 (midnight).

Usage

To display microseconds in a time, the display format for the field must include microseconds.

For information on valid dates, see Date.

Examples

This expression returns the values in the order_date and order_time columns as a DateTime value that can be used to update the database:

DateTime(Order_Date, Order_Time)

Using this expression for a computed field displays 11/11/01 11:11:00:

DateTime(11/11/01, 11:11)

See also

Date

Time

DateTime in the section called “DateTime” in PowerScript Reference

Description

Obtains the day of the month in a date (or datetime) value.

Syntax

Day ( date | datetime )

Return value

Integer. Returns an integer (1-31) representing the day of the month in date.

Examples

This expression returns 31:

Day(2020-01-31)

This expression returns the day of the month in the start_date column:

Day(start_date)

The following expressions return the day of the month for today:

Day(today())

Day(datetime(today(),now()))

See also

Date

IsDate

Month

Year

Day in the section called “Day” in PowerScript Reference

Description

Gets the day of the week in a date (or datetime) value and returns the weekday's name.

Syntax

DayName ( date | datetime )

Return value

String. Returns a string whose value is the name of the weekday (Sunday, Monday, and so on) for date.

Usage

DayName returns a name in the language of the deployment files available on the machine where the application is run. If you have installed localized deployment files in the development environment or on a user's machine, then on that machine the name returned by DayName will be in the language of the localized files.

For information about localized deployment files, see the section called “Internationalizing an Application” in Application Techniques.

Examples

This expression for a computed field returns Okay if the day in date_signed is not Sunday:

If(DayName(date_signed) <> "Sunday", "Okay", "Invalid Date")

To pass this validation rule, the day of today must not be Sunday:

DayName(datetime(today(),now())) <> "Sunday"

Or

DayName(today()) <> "Sunday" //today returns datetime if it is a DataWindow function

See also

Date

Day

DayNumber

IsDate

DayName in the section called “DayName” in PowerScript Reference

Description

Gets the day of the week of a date value and returns the number of the weekday.

Syntax

DayNumber ( date )

Return value

Integer. Returns an integer (1-7) representing the day of the week of date. Sunday is day 1, Monday is day 2, and so on.

Examples

This expression for a computed field returns Wrong Day if the date in start_date is not a Sunday or a Monday:

If(DayNumber(start_date) > 2, "Okay", "Wrong Day")

This expression for a computed field returns Wrong Day if the date in end_date is not a Saturday or a Sunday:

If(DayNumber(end_date) > 1 and DayNumber(end_date) 
< 7, "Okay", "Wrong Day")

This validation rule for the column end_date ensures that the day is not a Saturday or Sunday:

DayNumber(end_date) >1 and DayNumber(end_date) < 7

See also

Date

Day

DayName

IsDate

DayNumber in the section called “DayNumber” in PowerScript Reference

Description

Gets the number of days one date occurs after another.

Syntax

DaysAfter ( date1, date2 )

Return value

Long. Returns a long containing the number of days date2 occurs after date1. If date2 occurs before date1, DaysAfter returns a negative number.

Examples

This expression returns 4:

DaysAfter(2005-12-20, 2005-12-24)

This expression returns -4:

DaysAfter(2005-12-24, 2005-12-20)

This expression returns 0:

DaysAfter(2005-12-24, 2005-12-24)

This expression returns 5:

DaysAfter(2004-12-29, 2005-01-03)

See also

Date

SecondsAfter

DaysAfter in the section called “DaysAfter” in PowerScript Reference

Description

Converts the value of a string to a decimal.

Syntax

Dec ( string )

Return value

Decimal. Returns the contents of string as a decimal if it succeeds and 0 if string is not a number.

Usage

The decimal datatype supports up to 28 digits.

You can also append the letter D in upper or lowercase to identify a number as a decimal constant in DataWindow expressions. For example, 2.0d and 123.456789012345678901D are treated as decimals.

Examples

This expression returns the string 24.3 as a decimal datatype:

Dec("24.3")

This expression for a computed field returns "Not a valid score" if the string in the score column does not contain a number. The expression checks whether the Dec function returns 0, which means it failed to convert the value:

If ( Dec(score) <> 0, score, "Not a valid score")

This expression returns 0:

Dec("3ABC")    // 3ABC is not a number

This validation rule checks that the value in the column the user entered is greater than 1999.99:

Dec(GetText()) > 1999.99

This validation rule for the column named score insures that score contains a string:

Dec(score) <> 0

See also

Dec in the section called “Dec” in PowerScript Reference

Decimal support in DataWindow expressions

Description

Reports the values of properties of a DataWindow object and controls within the object. Each column and graphic control in the DataWindow object has a set of properties, which are listed in Controls in a DataWindow and their properties. You specify one or more properties as a string and Describe returns the values of the properties.

Syntax

Describe ( propertylist )

Return value

String. Returns a string that includes a value for each property or Evaluate function. A new line character (~n) separates the value of each item in propertylist.

If propertylist contains an invalid item, Describe returns an exclamation point (!) for that item and ignores the rest of propertylist. Describe returns a question mark (?) if there is no value for a property.

Usage

Specifying the values for propertylist can be complex. For information and examples, see the Describe method for the DataWindow control.

Examples

This expression for a computed field in the header band of a DataWindow object displays the DataWindow object's SELECT statement:

Describe("DataWindow.Table.Select")

See also

Describe

Description

Raises e to the specified power.

Syntax

Exp ( n )

Return value

Double. Returns e raised to the power n.

Examples

This expression returns 7.38905609893065:

Exp(2)

See also

Log

LogTen

Exp in the section called “Exp” in PowerScript Reference

Description

Gets the factorial of a number.

Syntax

Fact ( n )

Return value

Double. Returns the factorial of n.

Examples

This expression returns 24:

Fact(4)

Both these expressions return 1:

Fact(1)
Fact(0)

See also

Fact in the section called “Fact” in PowerScript Reference

Description

Builds a string of the specified length by repeating the specified characters until the result string is long enough.

Syntax

Fill ( chars, n )

Return value

String. Returns a string n characters long filled with repetitions of the characters in the argument chars. If the argument chars has more than n characters, the first n characters of chars are used to fill the return string. If the argument chars has fewer than n characters, the characters in chars are repeated until the return string has n characters.

Usage

Fill is used to create a line or other special effect. For example, asterisks repeated in a printed report can fill an amount line, or hyphens can simulate a total line in a screen display.

Examples

This expression returns a string containing 35 asterisks:

Fill("*", 35)

This expression returns the string -+-+-+-:

Fill("-+", 7)

This expression returns 10 tildes (~):

Fill("~~", 10)

See also

FillA

Space

Fill in the section called “Fill” in PowerScript Reference

Description

Builds a string of the specified length in bytes by repeating the specified characters until the result string is long enough.

Syntax

FillA ( chars, n )

Return value

String. Returns a string n bytes long filled with repetitions of the characters in the argument chars. If the argument chars has more than n bytes, the first n bytes of chars are used to fill the return string. If the argument chars has fewer than n bytes, the characters in chars are repeated until the return string has n bytes.

Usage

FillA replaces the functionality that Fill had in DBCS environments in PowerBuilder 9. In SBCS environments, Fill and FillA return the same results.

See also

Fill

FillA in the section called “FillA” in PowerScript Reference

Description

Reports the value in the first row in the specified column.

Syntax

First ( column { FOR range  { DISTINCT { expresn  {, expres2 {, ... } } } } } )

Return value

The datatype of the column. Returns the value in the first row of column. If you specify range, First returns the value of the first row in column in range.

Usage

If you specify range, First determines the value of the first row in column in range. If you specify DISTINCT, First returns the first distinct value in column, or if you specify expresn, the first distinct value in column where the value of expresn is distinct.

For most presentation styles, values for range are:

For Crosstabs, specify CROSSTAB for range to indicate the first of all rows in column in the crosstab.

For Graphs specify GRAPH and for OLE objects specify OBJECT for range, to indicate the value in the first row in column in the range specified for the Rows option.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:

Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.

Examples

This expression returns the first value in column 3 on the page:

First(#3 for page)

This expression returns the first distinct value in the column named dept_id in group 2:

First(dept_id for group 2 DISTINCT)

This expression returns the first value in the column named dept_id in group 2:

First(dept_id for group 2)

See also

Last

Description

Allows you to find the height of the font for a column or computed field. This function takes the column name as an argument. Use this function to set the minimum height to the size of the font.

Syntax

FontHeight ( column )

Return value

Long. Returns the height of the font for the column:

56 -- font size: 9

64 -- font size: 10

72 -- font size: 11

76 -- font size: 12

Example

dw_1.object.fname.Height = "0~tFontHeight(fname)"

Description

Returns the GDI context to which to draw.

Syntax

GetPaintDC ( )

Return value

UnsignedLong.

Usage

The clip region of the GDI context is guaranteed to be the same as the rectangle defined by GetPaintRectHeight, GetPaintRectWidth, GetPaintRectX, and GetPaintRectY.

You can also use the GetClipBox GDI function. You can compute the bounds inside the global rendering function itself.

The GetPaintDC expression is only valid in the context of the Paint expression function.

Examples

This example draws a cube with the text of the column emp_lname.

Paint
(
     MyDrawWPFCubeText
     (
          GetPaintDC(),
          GetPaintRectX(),
          GetPaintRectY(),
          GetPaintRectWidth(),
          GetPaintRectHeight(),
          emp_lname,
          GetRow()
     )
)

See also

GetPaintRectHeight

GetPaintRectWidth

GetPaintRectX

GetPaintRectY

Paint

Description

Returns the bounds of the computed field and the device context is clipped within those bounds. This expression is only valid in the context of the Paint expression function.

Syntax

GetPaintRectHeight ( )

Return value

Integer. Returns the height bounds for the computed field.

See also

GetPaintDC

GetPaintRectWidth

GetPaintRectX

GetPaintRectY

Paint

Description

Returns the bounds of the computed field and the device context is clipped within those bounds. This expression is only valid in the context of the Paint expression function.

Syntax

GetPaintRectWidth ( )

Return value

Integer. Returns the width bounds for the computed field.

See also

GetPaintDC

GetPaintRectHeight

GetPaintRectX

GetPaintRectY

Paint

Description

Returns the bounds of the computed field and the device context is clipped within those bounds. This expression is only valid in the context of the Paint expression function.

Syntax

GetPaintRectX ( )

Return value

Integer. Returns the X bound for the computed field.

See also

GetPaintDC

GetPaintRectHeight

GetPaintRectWidth

GetPaintRectY

Paint

Description

Returns the bounds of the computed field and the device context is clipped within those bounds. This expression is only valid in the context of the Paint expression function.

Syntax

GetPaintRectY ( )

Return value

Integer. Returns the Y bound for the computed field.

See also

GetPaintDC

GetPaintRectHeight

GetPaintRectWidth

GetPaintRectX

Description

Reports the number of a row associated with a band in a DataWindow object.

Syntax

GetRow ( )

Return value

Long. Returns the number of a row if it succeeds, 0 if no data has been retrieved or added, and -1 if an error occurs. Where you call GetRow determines what row it returns, as follows:

Examples

This expression for a computed field in the detail band displays the number of each row:

GetRow()

This expression for a computed field in the header band checks to see if there is data. It returns the number of the first row on the page if there is data, and otherwise returns No Data:

If(GetRow()= 0, "No Data", String(GetRow()))

See also

Example 3: creating a row indicator

CurrentRow

GetRow

Description

Obtains the text that a user has entered in a column.

Syntax

GetText ( )

Return value

String. Returns the text the user has entered in the current column.

Usage

Use GetText in validation rules to compare what the user has entered to application-defined criteria before it is accepted into the data buffer.

Examples

This validation rule checks that the value the user entered in the column is less than 100:

Integer(GetText()) < 100

See also

GetText

Description

Obtains the hour in a time value. The hour is based on a 24-hour clock.

Syntax

Hour ( time )

Return value

Integer. Returns an integer (00-23) containing the hour portion of time.

Examples

This expression returns the current hour:

Hour(Now())

This expression returns 19:

Hour(19:01:31)

See also

Minute

Now

Second

Hour in the section called “Hour” in PowerScript Reference

Description

Evaluates a condition and returns a value based on that condition.

Syntax

If ( boolean, truevalue, falsevalue )

Return value

The datatype of truevalue or falsevalue. Returns truevalue if boolean is true and falsevalue if it is false. Returns null if an error occurs.

Examples

This expression returns Boss if salary is over $100,000 and Employee if salary is less than or equal to $100,000:

If(salary > 100000, "Boss", "Employee")

This expression returns Boss if salary is over $100,000, Supervisor if salary is between $12,000 and $100,000, and Clerk if salary is less than or equal to $12,000:

If(salary > 100000, "Boss", If(salary > 12000, "Supervisor", "Clerk"))

In this example of a validation rule, the value the user should enter in the commission column depends on the price. If price is greater than or equal to 1000, then the commission is between .10 and .20. If price is less than 1000, then the commission must be between .04 and .09. The validation rule is:

(Number(GetText()) >= If(price >=1000, .10, .04)) AND
(Number(GetText()) <= If(price >= 1000, .20, .09))

The accompanying error message expression might be:

"Price is " 
+  If(price >= 1000, "greater than or equal to", "less than") 
+ " 1000. Commission must be between " 
+  If(price >= 1000, ".10", ".04") 
+ " and " 
+  If(price >= 1000, ".20.", ".09.")

See also

Example 1: counting null values in a column

Example 2: counting active and terminated employees

Example 3: creating a row indicator

Example 4: displaying all data when a column allows nulls

Case

Description

Gets the largest whole number less than or equal to a number.

Syntax

Int ( n )

Return value

The datatype of n. Returns the largest whole number less than or equal to n.

Examples

These expressions return 3.0:

Int(3.2)

Int(3.8)

These expressions return -4.0:

Int(-3.2)

Int(-3.8)

See also

Ceiling

Integer

Round

Truncate

Int in the section called “Int” in PowerScript Reference

Description

Converts the value of a string to an integer.

Syntax

Integer ( string )

Return value

Integer. Returns the contents of string as an integer if it succeeds and 0 if string is not a number.

Examples

This expression converts the string 24 to an integer:

Integer("24")

This expression for a computed field returns "Not a valid age" if age does not contain a number. The expression checks whether the Integer function returns 0, which means it failed to convert the value:

If (Integer(age) <> 0, age, "Not a valid age")

This expression returns 0:

Integer("3ABC")  // 3ABC is not a number

This validation rule checks that the value in the column the user entered is less than 100:

Integer(GetText()) < 100

This validation rule for the column named age insures that age contains a string:

Integer(age) <> 0

See also

IsNumber

Integer in the section called “Integer” in PowerScript Reference

Description

Tests whether a string value is a valid date.

Syntax

IsDate ( datevalue )

Return value

Boolean. Returns true if datevalue is a valid date and false if it is not.

Examples

This expression returns true:

IsDate("Jan 1, 99")

This expression returns false:

IsDate("Jan 32, 2005")

This expression for a computed field returns a day number or 0. If the date_received column contains a valid date, the expression returns the number of the day in date_received in the computed field, and otherwise returns 0:

If(IsDate(String(date_received)), DayNumber(date_received), 0)

See also

IsDate in the section called “IsDate” in PowerScript Reference

Description

Tests whether a node in a TreeView DataWindow with the specified TreeView level and that includes the specified row is expanded.

Syntax

IsExpanded(long row, long level)

Return value

Returns true if the group is expanded and false otherwise.

Usage

A TreeView DataWindow has several TreeView level bands that can be expanded and collapsed. You can use the IsExpanded function to test whether or not a node in a TreeView DataWindow is expanded.

Examples

This expression returns true if the node that contains row 3 at TreeView level 2 is expanded:

IsExpanded(3,2)

Description

Reports whether the value of a column or expression is null.

Syntax

IsNull ( any )

Return value

Boolean. Returns true if any is null and false if it is not.

Usage

Use IsNull to test whether a user-entered value or a value retrieved from the database is null.

Examples

This expression returns true if either a or b is null:

IsNull(a + b)

This expression returns true if the value in the salary column is null:

IsNull(salary)

This expression returns true if the value the user has entered is null:

IsNull(GetText())

See also

Example 1: counting null values in a column

Example 4: displaying all data when a column allows nulls

IsNull in the section called “IsNull” in PowerScript Reference

Description

Reports whether the value of a string is a number.

Syntax

IsNumber ( string )

Return value

Boolean. Returns true if string is a valid number and false if it is not.

Examples

This expression returns true:

IsNumber("32.65")

This expression returns false:

IsNumber("A16")

This expression for a computed field returns "Not a valid age" if age does not contain a number:

If(IsNumber(age), age, "Not a valid age")

To pass this validation rule, Age_nbr must be a number:

IsNumber(Age_nbr) = true

See also

Integer

IsNumber in the section called “IsNumber” in PowerScript Reference

Description

Reports whether the row has been modified.

Syntax

IsRowModified ( )

Return value

Boolean. Returns true if the row has been modified and false if it has not.

Usage

In a DataWindow object, when you use IsRowModified in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.

Examples

This expression in a computed field in the detail area displays true or false to indicate whether each row has been modified:

IsRowModified()

This expression defined in the Properties view for the Color property of the computed field displays the text (true) in red if the user has modified any value in the row:

If(IsRowModified(), 255, 0)

See also

GetRow

Description

Reports whether the row has been newly inserted.

Syntax

IsRowNew ( )

Return value

Boolean. Returns true if the row is new and false if it was retrieved from the database.

Usage

In a DataWindow object, when you call IsRowNew in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.

Examples

This expression defined in the Properties view for the Protect property of a column prevents the user from modifying the column unless the row has been newly inserted:

If(IsRowNew(), 0, 1)

See also

GetRow

GetItemStatus

Description

Determines whether the row is selected. A selected row is highlighted using reverse video.

Syntax

IsSelected ( )

Return value

Boolean. Returns true if the row is selected and false if it is not selected.

Usage

When you use IsSelected in bands other than the detail band, it reports on a row in the detail band. See GetRow for a table specifying which row is associated with each band for reporting purposes.

Examples

This expression for a computed field in the detail area displays a bitmap if the row is selected:

Bitmap(If(IsSelected(), "beach.bmp", ""))

This example allows the DataWindow object to display a salary total for all the selected rows. The expression for a computed field in the detail band returns the salary only when the row is selected so that another computed field in the summary band can add up all the selected salaries.

The expression for cf_selected_salary (the computed field in the detail band) is:

If(IsSelected(), salary, 0)

The expression for the computed field in the summary band is:

Sum(cf_selected_salary for all)

See also

GetRow

IsSelected

Description

Reports whether the value of a string is a valid time value.

Syntax

IsTime ( timevalue )

Return value

Boolean. Returns true if timevalue is a valid time and false if it is not.

Examples

This expression returns true:

IsTime("8:00:00 am")

This expression returns false:

IsTime("25:00")

To pass this validation rule, the value in start_time must be a time:

IsTime(start_time)

See also

IsTime in the section called “IsTime” in PowerScript Reference

Description

Finds a large value at a specified ranking in a column (for example, third- largest, fifth-largest) and returns the value of another column or expression based on the result.

Syntax

Large ( returnexp, column, ntop { FOR range { DISTINCT { expres1 
   {, expres2 {, ... } } } } } )

Return value

The datatype of returnexp. Returns the ntop-largest value if it succeeds and -1 if an error occurs.

Usage

If you specify range, Large returns the value in returnexp when the value in column is the ntop-largest value in range. If you specify DISTINCT, Large returns returnexp when the value in column is the ntop-largest value of the distinct values in column, or if you specify expresn, the ntop-largest for each distinct value of expresn.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows are as follows:

Examples

These expressions return the names of the salespersons with the three largest sales (sum_sales is the sum of the sales for each salesperson) in group 2, which might be the salesregion group. Note that sum_sales contains the values being compared, but Large returns a value in the name column:

Large(name, sum_sales, 1 for group 2)
Large(name, sum_sales, 2 for group 2)
Large(name, sum_sales, 3 for group 2)

This example reports the salesperson with the third-largest sales, considering only the first entry for each person:

Large(name, sum_sales, 3 for all DISTINCT sum_sales)

See also

Small

Description

Gets the value in the last row in the specified column.

Syntax

Last ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) 

Return value

The datatype of the column. Returns the value in the last row of column. If you specify range, Last returns the value of the last row in column in range.

Usage

If you specify range, Last determines the value of the last row in column in range. If you specify DISTINCT, Last returns the last distinct value in column, or if you specify expresn, the last distinct value in column where the value of expresn is distinct.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:

Examples

This expression returns the last distinct value in the column named dept_id in group 2:

Last(dept_id for group 2 DISTINCT)

This expression returns the last value in the column named emp_id in group 2:

Last(emp_id for group 2)

See also

First

Description

Finds the last position of a target string in a source string.

Syntax

LastPos ( string1, string2, searchlength )

Return value

Long. Returns a long whose value is the starting position of the last occurrence of string2 in string1 within the characters specified in searchlength. If string2 is not found in string1 or if searchlength is 0, LastPos returns 0. If any argument's value is null, LastPos returns null.

Usage

The LastPos function is case-sensitive. The entire target string must be found in the source string.

Examples

This statement returns 6, because the position of the last occurrence of RU is position 6:

LastPos("BABE RUTH", "RU")

This statement returns 3:

LastPos("BABE RUTH", "B")

This statement returns 0, because the case does not match:

LastPos("BABE RUTH", "be") 

This statement searches the leftmost 4 characters and returns 0, because the only occurrence of RU is after position 4:

LastPos("BABE RUTH", "RU", 2)

See also

Pos

Description

Obtains a specified number of characters from the beginning of a string.

Syntax

Left ( string, n )

Return value

String. Returns the leftmost n characters in string if it succeeds and the empty string ("") if an error occurs.

If n is greater than or equal to the length of the string, Left returns the entire string. It does not add spaces to make the return value's length equal to n.

Examples

This expression returns BABE:

Left("BABE RUTH", 4)

This expression returns BABE RUTH:

Left("BABE RUTH", 40)

This expression for a computed field returns the first 40 characters of the text in the column home_address:

Left(home_address, 40)

See also

LeftA

Mid

Pos

Right

Left in PowerScript Reference

Description

Obtains a specified number of bytes from the beginning of a string.

Syntax

LeftA ( string, n )

Return value

String. Returns the characters in the leftmost n bytes in string if it succeeds and the empty string ("") if an error occurs.

If n is greater than or equal to the length of the string, LeftA returns the entire string. It does not add spaces to make the return value's length equal to n.

Usage

LeftA replaces the functionality that Left had in DBCS environments in PowerBuilder 9. In SBCS environments, Left and LeftA return the same results.

See also

MidA

PosA

RightA

LeftA in PowerScript Reference

Description

Removes spaces from the beginning of a string.

Syntax

LeftTrim ( string )

Return value

String. Returns a copy of string with leading spaces deleted if it succeeds and the empty string ("") if an error occurs.

Examples

This expression returns RUTH:

LeftTrim(" RUTH")

This expression for a computed field deletes any leading blanks from the value in the column lname and returns the value preceded by the salutation specified in salut_emp:

salut_emp + " " +  LeftTrim(lname)

See also

RightTrim

Trim

LeftTrim in the section called “LeftTrim” in PowerScript Reference

Description

Reports the length of a string in characters.

Syntax

Len ( string )

Return value

Long. Returns a long containing the length of string in characters if it succeeds and -1 if an error occurs.

Examples

This expression returns 0:

Len("")

This validation rule tests that the value the user entered is fewer than 20 characters:

Len(GetText()) < 20

See also

LenA

Len in the section called “Len” in PowerScript Reference

Description

Reports the length of a string in bytes.

Syntax

LenA ( string )

Return value

Long. Returns a long containing the length of string in bytes if it succeeds and -1 if an error occurs.

Usage

LenA replaces the functionality that Len had in DBCS environments in PowerBuilder 9. In SBCS environments, Len and LenA return the same results.

See also

Len

LenA in the section called “LenA” in PowerScript Reference

Description

Gets the natural logarithm of a number.

Syntax

Log ( n )

Return value

Double. Returns the natural logarithm of n. An execution error occurs if n is negative or zero.

Examples

This expression returns 2.302585092:

Log(10)

This expression returns -.693147 ...:

Log(0.5)

Both these expressions result in an error at runtime:

Log(0)
Log(-2)

See also

Exp

LogTen

Log in the section called “Log” in PowerScript Reference

Description

Gets the base 10 logarithm of a number.

Syntax

LogTen ( n )

Return value

Double. Returns the base 10 logarithm.

Examples

This expression returns 1:

LogTen(10)

The following expressions both return 0:

LogTen(1)
LogTen(0)

This expression results in an execution error:

LogTen(-2)

See also

Log

LogTen in the section called “LogTen” in PowerScript Reference

Description

Converts the value of a string to a long.

Syntax

Long ( string )

Return value

Long. Returns the contents of string as a long if it succeeds and 0 if string is not a valid number.

Examples

This expression returns 2167899876 as a long:

Long("2167899876")

See also

Long in the section called “Long” in PowerScript Reference

Description

Obtains the display value in the code table associated with the data value in the specified column.

Syntax

LookUpDisplay ( column )

Return value

String. Returns the display value when it succeeds and the empty string ("") if an error occurs.

Usage

If a column has a code table, a buffer stores a value from the data column of the code table, but the user sees a value from the display column. Use LookUpDisplay to get the value the user sees.

Examples

This expression returns the display value for the column unit_measure:

LookUpDisplay(unit_measure)

Assume the column product_type has a code table and you want to use it as a category for a graph. To display the product type descriptions instead of the data values in the categories, enter this expression in the Category option on the Data page in the graph's property sheet:

LookUpDisplay(product_type)

Description

Converts all the characters in a string to lowercase.

Syntax

Lower ( string )

Return value

String. Returns string with uppercase letters changed to lowercase if it succeeds and the empty string ("") if an error occurs.

Examples

This expression returns castle hill:

Lower("Castle Hill")

See also

Upper

Lower in the section called “Lower” in PowerScript Reference

Description

Determines whether a string's value contains a particular pattern of characters.

Syntax

Match ( string, textpattern )

Return value

Boolean. Returns true if string matches textpattern and false if it does not. Match also returns false if either argument has not been assigned a value or the pattern is invalid.

Usage

Match enables you to evaluate whether a string contains a general pattern of characters. To find out whether a string contains a specific substring, use the Pos function.

Textpattern is similar to a regular expression. It consists of metacharacters, which have special meaning, and ordinary characters, which match themselves. You can specify that the string begin or end with one or more characters from a set, or that it contain any characters except those in a set.

A text pattern consists of metacharacters, which have special meaning in the match string, and nonmetacharacters, which match the characters themselves.

The following tables explain the meaning and use of these metacharacters:

The metacharacters asterisk (*), plus (+), and question mark (?) are unary operators that are used to specify repetitions in a regular expression:

Sample patterns

The following table shows various text patterns and sample text that matches each pattern:

Examples

This validation rule checks that the value the user entered begins with an uppercase letter. If the value of the expression is false, the data fails validation:

Match(GetText(), "^[A-Z]")

See also

Pos

Match in the section called “Match” in PowerScript Reference

Description

Gets the maximum value in the specified column.

Syntax

Max ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )

Return value

The datatype of the column. Returns the maximum value in the rows of column. If you specify range, Max returns the maximum value in column in range.

Usage

If you specify range, Max determines the maximum value in column in range. If you specify DISTINCT, Max returns the maximum distinct value in column, or if you specify expresn, the maximum distinct value in column where the value of expresn is distinct.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:

Null values are ignored and are not considered in determining the maximum.

Examples

This expression returns the maximum of the values in the age column on the page:

Max(age for page)

This expression returns the maximum of the values in column 3 on the page:

Max(#3 for page)

This expression returns the maximum of the values in the column named age in group 1:

Max(age for group 1)

Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the maximum of the order amount for the distinct order numbers:

Max(order_amt for all DISTINCT order_nbr)

See also

Min

Max in the section called “Max” in PowerScript Reference

Description

Calculates the median of the values of the column. The median is the middle value in the set of values, for which there is an equal number of values greater and smaller than it.

Syntax

Median ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )

Return value

The numeric datatype of the column. Returns the median of the values of the rows in range if it succeeds and -1 if an error occurs.

Usage

If you specify range, Median returns the median value of column in range. If you specify DISTINCT, Median returns the median value of the distinct values in column, or if you specify expresn, the median of column for each distinct value of expresn.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.

Settings for Rows include the following:

In calculating the median, null values are ignored.

Examples

This expression returns the median of the values in the column named salary:

Median(salary)

This expression returns the median of the values in the column named salary of group 1:

Median(salary for group 1)

This expression returns the median of the values in column 5 on the current page:

Median(#5 for page)

This computed field returns Above Median if the median salary for the page is greater than the median for the report:

If(Median(salary for page) > Median(salary), "AboveMedian", " ")

This expression for a graph value sets the data value to the median value of the sale_price column:

Median(sale_price)

This expression for a graph value entered on the Data page in the graph's property sheet sets the data value to the median value of the sale_price column for the entire graph:

Median(sale_price for graph)

Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the median of the order amount for the distinct order numbers:

Median(order_amt for all DISTINCT order_nbr)

See also

Avg

Mode

Description

Obtains a specified number of characters from a specified position in a string.

Syntax

Mid ( string, start {, length } )

Return value

String. Returns characters specified in length of string starting at character start. If start is greater than the number of characters in string, the Mid function returns the empty string (""). If length is greater than the number of characters remaining after the start character, Mid returns the remaining characters. The return string is not filled with spaces to make it the specified length.

Examples

This expression returns "":

Mid("BABE RUTH", 40, 5)

This expression returns BE RUTH:

Mid("BABE RUTH", 3)

This expression in a computed field returns ACCESS DENIED if the fourth character in the column password is not R:

If(Mid(password, 4, 1) = "R", "ENTER", "ACCESS DENIED")

To pass this validation rule, the fourth character in the column password must be 6:

Mid(password, 4, 1) = "6"

See also

Mid in the section called “Mid” in PowerScript Reference

Description

Obtains a specified number of bytes from a specified position in a string.

Syntax

MidA ( string, start {, length } )

Return value

String. Returns characters specified by the number of bytes in length of string starting at the byte specified by start. If start is greater than the number of bytes in string, the MidA function returns the empty string (""). If length is greater than the number of bytes remaining after the start byte, MidA returns the remaining bytes. The return string is not filled with spaces to make it the specified length.

Usage

MidA replaces the functionality that Mid had in DBCS environments in PowerBuilder 9. In SBCS environments, Mid and MidA return the same results.

See also

Mid

MidA in the section called “MidA” in PowerScript Reference

Description

Gets the minimum value in the specified column.

Syntax

Min ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )

Return value

The datatype of the column. Returns the minimum value in the rows of column. If you specify range, Min returns the minimum value in the rows of column in range.

Usage

If you specify range, Min determines the minimum value in column in range. If you specify DISTINCT, Min returns the minimum distinct value in column, or if you specify expresn, the minimum distinct value in column where the value of expresn is distinct.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include:

Null values are ignored and are not considered in determining the minimum.

Examples

This expression returns the minimum value in the column named age in group 2:

Min(age for group 2)

This expression returns the minimum of the values in column 3 on the page:

Min(#3 for page)

Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the minimum of the order amount for the distinct order numbers:

Min(order_amt for all DISTINCT order_nbr)

See also

Max

Min in the section called “Min” in PowerScript Reference

Description

Obtains the number of minutes in the minutes portion of a time value.

Syntax

Minute ( time )

Return value

Integer. Returns the minutes portion of time (00 to 59).

Examples

This expression returns 1:

Minute(19:01:31)

See also

Hour

Second

Minute in the section called “Minute” in PowerScript Reference

Description

Obtains the remainder (modulus) of a division operation.

Syntax

Mod ( x, y )

Return value

The datatype of x or y, whichever datatype is more precise.

Examples

This expression returns 2:

Mod(20, 6)

This expression returns 1.5:

Mod(25.5, 4)

This expression returns 2.5:

Mod(25, 4.5)

See also

Mod in the section called “Mod” in PowerScript Reference

Description

Calculates the mode of the values of the column. The mode is the most frequently occurring value.

Syntax

Mode ( column { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } )

Return value

The numeric datatype of the column. Returns the mode of the values of the rows in range if it succeeds and -1 if an error occurs.

Usage

If you specify range, Mode returns the mode of column in range. If you specify DISTINCT, Mode returns the mode of the distinct values in column, or if you specify expresn, the mode of column for each distinct value of expresn.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include:

In calculating the mode, null values are ignored.

Examples

This expression returns the mode of the values in the column named salary:

Mode(salary)

This expression returns the mode of the values for group 1 in the column named salary:

Mode(salary for group 1)

This expression returns the mode of the values in column 5 on the current page:

Mode(#5 for page)

This computed field returns Above Mode if the mode of the salary for the page is greater than the mode for the report:

If(Mode(salary for page) > Mode(salary), "Above Mode", " ")

This expression for a graph value sets the data value to the mode of the sale_price column:

Mode(sale_price)

This expression for a graph value entered on the Data page in the graph's property sheet sets the data value to the mode of the sale_price column for the entire graph:

Mode(sale_price for graph)

Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the mode of the order amount for the distinct order numbers:

Mode(order_amt for all DISTINCT order_nbr)

See also

Avg

Median

Description

Gets the month of a date value.

Syntax

Month ( date )

Return value

Integer. Returns an integer (1 to 12) whose value is the month portion of date.

Examples

This expression returns 1:

Month(2005-01-31)

This expression for a computed column returns Wrong Month if the month in the column expected_grad_date is not 6:

If(Month(expected_grad_date) = 6, "June", "Wrong Month")

This validation rule expression checks that the value of the month in the date in the column expected_grad_date is 6:

Month(expected_grad_date) = 6

See also

Day

Date

Year

Month in the section called “Month” in PowerScript Reference

Description

Obtains the current time based on the system time of the client machine.

Syntax

Now ( )

Return value

Time. Returns the current time based on the system time of the client machine.

Usage

Use Now to compare a time to the system time or to display the system time on the screen. The timer interval specified for the DataWindow object determines the frequency at which the value of Now is updated. For example, if the timer interval is one second, it is updated every second. The default timer interval is one minute (60,000 milliseconds).

Examples

This expression returns the current system time:

Now()

This expression sets the column value to 8:00 when the current system time is before 8:00 and to the current time if it is after 8:00:

If(Now() < 08:00:00, '08:00:00', String(Now()))

The displayed time refreshes every time the specified time interval period elapses.

If a static value of time is required (for example, the time when a report has been executed or the retrieve has started), you can use a static text field that you modify as follows:

//Set the time when the report was executed in 
//the text field t_now
dw1.Modify("t_now.text='"+ String(Now(),"hh:mm")+"'")
//execute the report
dw1.retrieve() 

See also

If

Year

Now in the section called “Now” in PowerScript Reference

Description

Converts a string to a number.

Syntax

Number ( string )

Return value

A numeric datatype. Returns the contents of string as a number. If string is not a valid number, Number returns 0.

Examples

This expression converts the string 24 to a number:

Number("24")

This expression for a computed field tests whether the value in the age column is greater than 55 and if so displays N/A; otherwise, it displays the value in age:

If(Number(age) > 55, "N/A", age)

This validation rule checks that the number the user entered is between 25,000 and 50,000:

Number(GetText())>25000 AND Number (GetText())<50000

Description

Gets the number of the current page.

Syntax

Page ( )

Return value

Long. Returns the number of the current page.

Examples

This expression returns the number of the current page:

Page()

In the DataWindow object's footer band, this expression for a computed field displays a string showing the current page number and the total number of pages in the report. The result has the format Page n of total:

'Page ' + Page() + ' of ' + PageCount()

See also

PageAbs

PageAcross

PageCount

PageCountAcross

Description

Gets the absolute number of the current page.

Syntax

PageAbs ( )

Return value

Long. Returns the absolute number of the current page.

Usage

Use this function for group reports that have ResetPageCount = yes. It returns the absolute page number, ignoring the page reset count. This enables you to number the grouped pages, but also to obtain the absolute page when the user wants to print the current page, regardless of what that page number is in a grouped page report.

Examples

This expression returns the absolute number of the current page:

PageAbs()

This example obtains the absolute page number for the first row on the page in the string variable ret:

string ret, row
row = dw1.Object.DataWindow.FirstRowOnPage
ret = dw1.Describe("Evaluate('pageabs()', "+row+")")

See also

Page

PageCount

PageCountAcross

Description

Gets the number of the current horizontal page. For example, if a report is twice the width of the print preview window and the window is scrolled horizontally to display the portion of the report that was outside the preview, PageAcross returns 2 because the current page is the second horizontal page.

Syntax

PageAcross ( )

Return value

Long. Returns the number of the current horizontal page if it succeeds and -1 if an error occurs.

Examples

This expression returns the number of the current horizontal page:

PageAcross()

See also

Page

PageCount

PageCountAcross

Description

Gets the total number of pages when a DataWindow object is being viewed in Print Preview. This number is also the number of printed pages if the DataWindow object is not wider than the preview window. If the DataWindow object is wider than the preview window, the number of printed pages will be greater than the number PageCount gets.

Syntax

PageCount ( )

Return value

Long. Returns the total number of pages.

Usage

PageCount applies to Print Preview.

Examples

This expression returns the number of pages:

PageCount()

In the DataWindow object's footer band, this expression for a computed field displays a string showing the current page number and the total number of pages in the report. The result has the format Page n of total:

'Page ' + Page() + ' of ' + PageCount()

See also

Page

PageAcross

PageCountAcross

Description

Gets the total number of horizontal pages that are wider than the Print Preview window when a DataWindow object is viewed in Print preview.

Syntax

PageCountAcross ( )

Return value

Long. Returns the total number of horizontal pages if it succeeds and -1 if an error occurs.

Usage

PageCountAcross applies to Print Preview.

Examples

This expression returns the number of horizontal pages in the Print Preview window:

PageCountAcross()

See also

Page

PageAcross

PageCount

Description

Takes a string expression argument and returns the same string, allowing you to paint inside a DataWindow object in a way that respect the position and z-order of other DataWindow objects.

Syntax

Paint ( expr )

Return value

String. The Paint expression function takes a string expression argument and returns the same string.

Examples

This example instantiates the drawing functions and, if the drawing function returns false, the text "No Pie" displays.

Paint
(
     MyDrawPieSlice
     (
          GetPaintDC()
          GetPaintRectX()
          GetPaintRectY()
          GetPaintRectWidth()
          GetPaintRectHeight()
          GetRow()*100/RowCount()
     )
)
 
Paint
(
     MyDrawPieSlice
     (
          GetPaintDC(),
          GetRow()*100/RowCount()
     )
)
 
Paint
(
 if MyDrawPieSlice(GetPaintDC()),"","No Pie")
)

Description

Gets the percentage that the current value represents of the total of the values in the column.

Syntax

Percent ( column { FOR range { DISTINCT { expres1 {, expres2  {, ... } } } } } )

Return value

A numeric datatype (decimal, double, integer, long, or real). Returns the percentage the current row of column represents of the total value of the column.

Usage

Usually you use Percent in a column to display the percentage for each row. You can also use Percent in a header or trailer for a group. In the header, Percent displays the percentage for the first value in the group, and in the trailer, for the last value in the group.

If you specify range, Percent returns the percentage that the current row of column represents relative to the total value of range. For example, if column 5 is salary, Percent(#5 for group 1) is equivalent to salary/(Sum(Salary for group 1)).

If you specify DISTINCT, Percent returns the percent that a distinct value in column represents of the total value of column. If you specify expresn, Percent returns the percent that the value in column represents of the total for column in a row in which the value of expresn is distinct.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range. Settings for Rows include the following:

Null values are ignored and are not considered in the calculation.

Examples

This expression returns the value of each row in the column named salary as a percentage of the total of salary:

Percent(salary)

This expression returns the value of each row in the column named cost as a percentage of the total of cost in group 2:

Percent(cost for group 2)

This expression entered in the Value box on the Data tab page in the Graph Object property sheet returns the value of each row in the qty_ordered as a percentage of the total for the column in the graph:

Percent(qty_ordered for graph)

Assuming a DataWindow object displays the order number, amount, and line items for each order, this computed field returns the order amount as a percentage of the total order amount for the distinct order numbers:

Percent(order_amt for all DISTINCT order_nbr)

See also

CumulativePercent

Description

Multiplies pi by a specified number.

Syntax

Pi ( n )

Return value

Double. Returns the result of multiplying n by pi if it succeeds and -1 if an error occurs.

Usage

Use Pi to convert angles to and from radians.

Examples

This expression returns pi:

Pi(1)

Both these expressions return the area of a circle with the radius Rad:

Pi(1) * Rad^2
Pi(Rad^2)

This expression computes the cosine of a 45-degree angle:

Cos(45.0 * (Pi(2)/360))

See also

Cos

Sin

Tan

Pi in the section called “Pi” in PowerScript Reference

Description

Finds one string within another string.

Syntax

Pos ( string1, string2 {, start } )

Return value

Long. Returns a long whose value is the starting position of the first occurrence of string2 in string1 after the position specified in start. If string2 is not found in string1 or if start is not within string1, Pos returns 0.

Usage

The Pos function is case-sensitive.

Examples

This expression returns the position of the letter a in the value of the last_name column:

Pos(last_name, "a")

This expression returns 6:

Pos("BABE RUTH", "RU")

This expression returns 1:

Pos("BABE RUTH", "B")

This expression returns 0 (because the case does not match):

Pos("BABE RUTH", "be")

This expression returns 0 (because it starts searching at position 5, after the occurrence of BE):

Pos("BABE RUTH", "BE", 5)

See also

LastPos

Left

Mid

PosA

Right

Pos in the section called “Pos” in PowerScript Reference

Description

Finds one string within another string.

Syntax

PosA ( string1, string2 {, start } )

Return value

Long. Returns a long whose value is the starting position of the first occurrence of string2 in string1 after the position in bytes specified in start. If string2 is not found in string1 or if start is not within string1, PosA returns 0.

Usage

PosA replaces the functionality that Pos had in DBCS environments in PowerBuilder 9. In SBCS environments, Pos and PosA return the same results.

See also

LastPos

LeftA

MidA

Pos

RightA

PosA in the section called “PosA” in PowerScript Reference

Description

Obtains the integer value of a setting in the specified profile file.

Syntax

ProfileInt ( filename, section, key, default )

Return value

Integer. Returns default if filename is not found, section is not found in filename, key is not found in section, or the value of key is not an integer. Returns -1 if an error occurs.

Usage

Use ProfileInt and ProfileString to get configuration settings from a profile file you have designed for your application. ProfileInt and ProfileString can read files with ANSI or UTF16-LE encoding on Windows systems, and ANSI or UTF16-BE encoding on UNIX systems.

Examples

This example uses the following PROFILE.INI file:

[MyApp]
Maximized=1
[Security]
Class = 7

This expression tries to return the integer value of the keyword Minimized in section MyApp of file C:\PROFILE.INI. It returns 3 if there is no MyApp section or no Minimized keyword in the MyApp section. Based on the sample file above, it returns 3:

ProfileInt("C:\PROFILE.INI", "MyApp", "minimized", 3)

See also

ProfileString

ProfileInt in the section called “ProfileInt” in PowerScript Reference

Description

Obtains the string value of a setting in the specified profile file.

Syntax

ProfileString ( filename, section, key, default )

Return value

String, with a maximum length of 4096 characters. Returns the string from key within section within filename. If filename is not found, section is not found in filename, or key is not found in section, ProfileString returns default. If an error occurs, it returns the empty string ("").

Usage

Use ProfileInt and ProfileString to get configuration settings from a profile file you have designed for your application. ProfileInt and ProfileString can read files with ANSI or UTF16-LE encoding on Windows systems, and ANSI or UTF16-BE encoding on UNIX systems.

Examples

This example uses the following section in the PROFILE.INI file:

[Employee]
Name="Smith"
[Dept]
Name="Marketing"

This expression returns the string for the keyword Name in section Employee in file C:\PROFILE.INI. It returns None if the section or keyword does not exist. In this case it returns Smith:

ProfileString("C:\PROFILE.INI", "Employee", "Name", "None")

See also

ProfileInt

ProfileString in the section called “ProfileString” in PowerScript Reference

SetProfileString in the section called “SetProfileString” in PowerScript Reference

Description

Obtains a random whole number between 1 and a specified upper limit.

Syntax

Rand ( n )

Return value

A numeric datatype, the datatype of n. Returns a random whole number between 1 and n.

Usage

The sequence of numbers generated by repeated calls to the Rand function is a computer-generated pseudorandom sequence.

You can control whether the sequence is different each time your application runs by calling the PowerScript Randomize function to initialize the random number generator.

Examples

This expression returns a random whole number between 1 and 10:

Rand(10)

See also

Rand in the section called “Rand” in PowerScript Reference

Randomize in the section called “Randomize” in PowerScript Reference

Description

Converts a string value to a real datatype.

Syntax

Real ( string )

Return value

Real. Returns the contents of a string as a real. If string is not a valid number, Real returns 0.

Examples

This expression converts 24 to a real:

Real("24")

This expression returns the value in the column temp_text as a real:

Real(temp_text)

See also

Real in the section called “Real” in PowerScript Reference

Description

Obtains the date that occurs a specified number of days after or before another date.

Syntax

RelativeDate ( date, n )

Return value

Date. Returns the date that occurs n days after date if n is greater than 0. Returns the date that occurs n days before date if n is less than 0.

Examples

This expression returns 2005-02-10:

RelativeDate(2005-01-31, 10)

This expression returns 2005-01-21:

RelativeDate(2005-01-31, -10)

See also

DaysAfter

RelativeDate in the section called “RelativeDate” in PowerScript Reference

Description

Obtains a time that occurs a specified number of seconds after or before another time within a 24-hour period.

Syntax

RelativeTime ( time, n )

Return value

Time. Returns the time that occurs n seconds after time if n is greater than 0. Returns the time that occurs n seconds before time if n is less than 0. The maximum return value is 23:59:59.

Examples

This expression returns 19:01:41:

RelativeTime(19:01:31, 10)

This expression returns 19:01:21:

RelativeTime(19:01:31, -10)

See also

SecondsAfter

RelativeTime in the section called “RelativeTime” in PowerScript Reference

Description

Replaces a portion of one string with another.

Syntax

Replace ( string1, start, n, string2 )

Return value

String. Returns the string with the characters replaced if it succeeds and the empty string ("") if it fails.

Usage

If the start position is beyond the end of the string, Replace appends string2 to string1. If there are fewer characters after the start position than specified in n, Replace replaces all the characters to the right of character start.

If n is zero, then in effect Replace inserts string2 into string1.

Examples

This expression changes the last two characters of the string David to e to make it Dave:

Replace("David", 4, 2, "e")

This expression returns MY HOUSE:

Replace("YOUR HOUSE", 1, 4, "MY")

This expression returns Closed for the Winter:

Replace("Closed for Vacation", 12, 8, "the Winter")

See also

ReplaceA

Replace in the section called “Replace” in PowerScript Reference

Description

Replaces a portion of one string with another.

Syntax

ReplaceA ( string1, start, n, string2 )

Return value

String. Returns the string with the bytes replaced if it succeeds and the empty string ("") if it fails.

Usage

If the start position is beyond the end of the string, ReplaceA appends string2 to string1. If there are fewer bytes after the start position than specified in n, ReplaceA replaces all the bytes to the right of character start.

If n is zero, then in effect ReplaceA inserts string2 into string1.

ReplaceA replaces the functionality that Replace had in DBCS environments in PowerBuilder 9. In SBCS environments, Replace and ReplaceA return the same results.

See also

Replace

ReplaceA in the section called “ReplaceA” in PowerScript Reference

Description

Calculates the long value that represents the color specified by numeric values for the red, green, and blue components of the color.

Syntax

RGB ( red, green, blue )

Return value

Long. Returns the long that represents the color created by combining the values specified in red, green, and blue. If an error occurs, RGB returns null.

Usage

The formula for combining the colors is:

Red + (256 * Green) + (65536 * Blue)

Use RGB to obtain the long value required to set the color for text and drawing objects. You can also set an object's color to the long value that represents the color. The RGB function provides an easy way to calculate that value.

Determining color components

The value of a component color is an integer between 0 and 255 that represents the amount of the component that is required to create the color you want. The lower the value, the darker the color; the higher the value, the lighter the color.

The following table lists red, green, and blue values for the 16 standard colors:

Examples

This expression returns as a long 8421376, which represents dark cyan:

RGB(0,128,128)

This expression for the Background.Color property of a salary column returns a long that represents red if an employee's salary is greater than $50,000 and white if salary is less than or equal to $50,000:

If(salary>50000, RGB(255,0,0), RGB(255,255,255))

See also

Example 3: creating a row indicator

RGB in the section called “RGB” in PowerScript Reference

Description

Takes as argument a string expression interpreted as RTF and renders it as such. If the argument is not RTF nothing is rendered.

Syntax

RichText ( string )

Return value

None.

Examples

This expression displays the contents of the short_desc column's as rich text.

RichText( short_desc )

Description

Takes as argument a string expression interpreted as a RTF file name and renders the contents. If the argument is not a RTF file nothing is rendered.

Syntax

RichTextFile ( string )

Return value

None.

Examples

This expression displays the contents of the richtext.rtf file as rich text.

RichTextFile("richtext.rtf")

Description

Obtains a specified number of characters from the end of a string.

Syntax

Right ( string, n )

Return value

String. Returns the rightmost n characters in string if it succeeds and the empty string ("") if an error occurs.

If n is greater than or equal to the length of the string, Right returns the entire string. It does not add spaces to make the return value's length equal to n.

Examples

This expression returns HILL:

Right("CASTLE HILL", 4)

This expression returns CASTLE HILL:

Right("CASTLE HILL", 75)

See also

Left

Mid

Pos

Right in the section called “Right” in PowerScript Reference

Description

Obtains a specified number of characters from the end of a string.

Syntax

Right ( string, n )

Return value

String. Returns the rightmost n characters in string if it succeeds and the empty string ("") if an error occurs.

If n is greater than or equal to the length of the string, RightA returns the entire string. It does not add spaces to make the return value's length equal to n.

Usage

RightA replaces the functionality that Right had in DBCS environments in PowerBuilder 9. In SBCS environments, Right and RightA return the same results.

See also

LeftA

MidA

PosA

Right

Description

Removes spaces from the end of a string.

Syntax

RightTrim ( string )

Return value

String. Returns a copy of string with trailing blanks deleted if it succeeds and the empty string ("") if an error occurs.

Examples

This expression returns RUTH:

RightTrim("RUTH ")

See also

LeftTrim

Trim

RightTrim in the section called “RightTrim” in PowerScript Reference