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.