• Functions and Codes
  • Calculation Operators
  • CLP (Linkage) Codes
  • dBase Filter
  • DDE Functions
  • UDF Functions
  • Function Arguments
  • Numerical Functions
  • Text Functions

Text Functions

Use text functions to make database filters more flexible and specific.

• IN Function
• INANN Function
• LEFT Function
• LOWER Function
• LTRIM Function
• MID Function
• RIGHT Function
• SCANSTR Function
• SUBSTR Function
• TRIM Function
• UPPER Function

IN function

The IN function evaluates the position of one string value (value 2) within another string value (value 1).

Syntax

IN(value 1, value 2)

Examples

IN("Toronto, Ontario","Ontario")

• Returns a value of 10.

IN("Toronto, Ontario","Alberta")

• Returns a value of 0.

Database Filter Syntax

IN (DATABASE--> FIELD) OPERATOR VALUE

• If the IN function finds the matching string and returns a value for the position of that string. Therefore, if a string exists then the value returned will NOT be 0.

Example

IN(GL->JE_DESC,"BUDGET")>0

• Returns any journal entry with a description line that contains, and matches exactly to the text string "BUDGET".

INANN function

The INANN function tests whether there is an associated tickmark or reference annotation.

Syntax

INANN( type, ID , database )

• Where...

  Type	Tickmark (T) or Reference (R)
  ID	The textual ID of the tickmark or reference. Textual IDs can be found in the Text column in Engagement|Tickmarks.
  Database	The alias of the database such as "AM, "GL", etc.

Example

INANN("R", "", "AM") = 0 & INANN("T","", "AM") = 0

• Only accounts in the AM database that contain a reference and tickmark will display. The ID field has been left blank in both instances to return all tickmarks and references. The & operator is used to join both conditions.

LEFT function

The LEFT function selects a specified number of characters starting from the leftmost character of a character expression.

Syntax

LEFT(expression , number)

Example

LEFT("ABCDEF",3)

• The cell displays "ABC".

Database Filter Syntax

LEFT(DATABASE -> FIELD, number) OPERATOR VALUE

Example

GL->LEFT(JE_DESC),4="BANK"

• Returns all journal entries with journal descriptions that start with, and matches exact, the first four letters "BANK."

LOWER

The LOWER function converts a string to lower case.

Syntax

LOWER(expression)

Example

LOWER("WXYZ")

• Displays the string "wxyz".

Database Filter Syntax

LOWER(DATABASE -> FIELD) OPERATOR VALUE

Example

GL->LOWER(JE_DESC)="PCASH"

• Returns all journal entries with journal descriptions that start with "PCASH" regardless of case.

LTRIM Function

The LTRIM function removes leading spaces (i.e. those to the left) from any variable to which it is applied (i.e. text string, database field, etc.)

Syntax

LTRIM(expression)

Example

LTRIM("TEST")

• Returns "TEST" with the spaces to the left trimmed from the string.

Database Filter Syntax

LTRIM(DATABASE -> FIELD) OPERATOR VALUE

Examples

LTRIM(AM->AC_NO)="108"

• Returns only records with account number 108.

(LEFT(LTRIM(AM->AC_NO),2)="11"

• Returns only accounts that start with "11" (i.e. 11, 110, 111, 112, ..., 1100, 1101, 1102, ..., etc.). Similar to using a wildcard grouping mask 11??.

MID function

The MID function selects a portion of the string to display beginning at value 1 characters from the left of the string for value 2 characters into the string. Value 2 is optional. If value 2 is not supplied, the function returns all remaining characters. This function is only applicable for alphanumeric cells.

Syntax: MID(expression, value 1<,value 2>)

Example

MID("Prince Edward Island",8,6)

• Where 8 characters into the string is the E of Edward and 6 characters from the E of Edward is the last D of Edward. Therefore, the cell displays Edward.

Database Filter Syntax

MID(DATABASE -> FIELD, number), OPERATOR VALUE

Example

MID(GL->JE_DESC,20,4)="BANK"

• Returns all journal entries that have, and match exactly, the four letters "BANK." at the 20th position counted from the left side in the journal description.

MID(LTRIM(AM->AC_NO),5,2)="22"

• Returns all accounts with account numbers that have "22" in the 5th position from the left.

RIGHT function

The RIGHT function selects the rightmost specified number of characters of a character expression.

Syntax

RIGHT(expression, number)

Example

RIGHT("ABCDEF",3)

• Equals DEF

Database Filter Syntax

RIGHT(DATABASE -> FIELD), number) OPERATOR VALUE

Example

RIGHT(TRIM(GL->JE_DESC),7)="expense"

• Returns any journal entry that has the word "expense" in the last seven characters of the journal entry description.

SCANSTR function

The SCANSTR function evaluates the position of one string value (value 1) within another string value (value 2).

Syntax

SCANSTR(value 1, value 2)

Examples

SCANSTR("Ontario","Toronto, Ontario")

• Returns a value of 10.

SCANSTR("Alberta","Toronto, Ontario")

• Returns a value of 0.

Database Filter Syntax

SCANSTR(DATABASE -> FIELD, string), OPERATOR VALUE

• If the SCANSTR function finds the matching string, it returns a value for the position of that string. Therefore, if the string exists in a field the value returned will NOT be 0.

Example

SCANSTR(GL->JE_DESC,"BUDGET")>0

• Returns any journal with a description line that contains, and matches exactly, the text string "BUDGET". The IN function could be used in the same way.

SUBSTR function

The SUBSTR function selects a portion of a specified string (text) to display beginning at a selected distance from the left of the string (count) for as many characters are specified (length) into the string. The length parameter is optional. If length is not supplied, the function returns all remaining characters. This function is only applicable for alphanumeric cells.

Syntax

SUBSTR(text, count, <length>)

Examples

SUBSTR("Prince Edward Island",8)

• Where 8 characters into the string is the E of Edward. Therefore, returns the value "Edward Island".

SUBSTR("Prince Edward Island",8, 6)

• Where 8 characters into the string is the E of Edward and 6 characters from the E of Edward is the last D of Edward. Therefore , returns the value "Edward".

Database Filter Syntax

SUBSTR(DATABASE -> FIELD, number), OPERATOR VALUE

Example

SUBSTR(GL->JE_DESC,20,4)="BANK"

• Would filter for all journal entries that have, and match exactly, the four letters "BANK." at the 20th position counted from the left side in the journal description.

SUBSTR(LTRIM(AM->AC_NO),5,2)="22"

• Would filter all accounts with account numbers that have "22" in the 5th position from the left.

TRIM function

The TRIM function trims trailing spaces from a string. All the spaces following the string will be removed.

Syntax

TRIM(expression)

Example

TRIM("ABCDEF ")

• Displays the string "ABCDEF".

Database Filter Syntax

TRIM(DATABASE--> FIELD) OPERATOR VALUE

Example

TRIM(GL->JE_DESC),7)="expense"

• Removes any trailing spaces from the string and returns any journal entry that has the word "expense".

UPPER function

The UPPER function converts a string to upper case.

Syntax

UPPER(expression)

Example

UPPER("abcdef")

• Displays the string "ABCDEF".

Database Filter

UPPER(DATABASE--> FIELD) OPERATOR VALUE

Example

GL->UPPER(JE_DESC)="MELVIN"

• Returns all journal entries with journal descriptions that start with "MELVIN" regardless of case.