|
IMPORTANT
This section is about "Advanced Conditions" in MyAssistant; created by clicking "Convert to Advanced" in the Condition Step of Task Setup. For information on standard Conditions, click here.
|
Functions are built-in procedures or subroutines used to evaluate, make calculations on, or transform data. You can use one or more functions when you define a condition.
IN - Test a field for multiple values
CURDATE() - Current Date
DAYOFMONTH() - Day of the month
DAYOFWEEK() - Day of the week
MONTH() - Month of the year
YEAR() - Year
LENGTH() - Length of text
CONCAT() - Concatenate
LTRIM() - Remove space characters from the left side of a text field
RTRIM() - Remove space characters from the right side of a text field
SUBSTRING() - Return a portion of a text field
LOCATE() - Find a substring in a text field
LEFT() - Display a given number of characters from the left of a text field
RIGHT() - Display a given number of characters from the right of a text field
CONVERT() - Convert a given field to another data type
IS NULL/IS NOT NULL - Determine if a field is (or is not) blank
NOT() - Switch boolean value of a field
UCASE() - Convert text to UPPER CASE
LCASE() - Convert text to lower case
IN - Test a field for multiple values
The IN operator tests a given field against a list of values, and returns true if the field matches an item in the list. You can wrap an expression in the NOT() function to determine if a field is not one of the set of values.
Syntax: IN (comma separated list)
Examples:
To show all GL Accounts of type Current assets and Noncurrent assets:
"GLM_MASTER__ACCOUNT"."Account_Type" IN ('Current assets','Noncurrent assets')
To show all GL Accounts that are not Suspense or Units accounts:
NOT("GLM_MASTER__ACCOUNT"."Account_Type" IN ('Suspense','Units'))
CURDATE() - Current Date
The CURDATE() function returns the current system date. This value can then be compared to other database date fields or can be part of an expression. Adding or subtracting a number from CURDATE() changes the result by the specified number of days.
Syntax: CURDATE()
The function is used with no parameters.
Examples:
To show all posted GL transactions for today:
"GLT_CURRENT__TRANSACTION"."Accounting_Date" = CURDATE()
To show all JC Jobs that started in the last 7 days:
"JCM_MASTER__JOB"."Actual_Start_Date" >= CURDATE() – 7
DAYOFMONTH() - Day of the month
The DAYOFMONTH() function returns the day of the month (1 through 31) from the given date parameter.
Syntax: DAYOFMONTH(<date>)
The function takes one parameter, a date field. This can be a database field or the CURDATE() function.
Examples:
To show posted JC transactions that occurred on the first day of any month:
DAYOFMONTH("JCT_CURRENT__TRANSACTION"."Accounting_Date") = 1
This function could be combined with the MONTH() function to see if today is an employee’s employment anniversary date:
MONTH("PRM_MASTER__EMPLOYEE"."Hire_Date") = MONTH(CURDATE()) AND DAYOFMONTH("PRM_MASTER__EMPLOYEE"."Hire_Date") = DAYOFMONTH(CURDATE())
DAYOFWEEK() - Day of the week
The DAYOFWEEK() function returns the day of the week (1 through 7, Sunday is the first day of the week) from the given date parameter.
Syntax: DAYOFWEEK(<date>)
The function takes one parameter, a date field. This can be a database field or the CURDATE() function.
Example:
To show AP Invoices that have an Invoice date that falls on a Monday:
DAYOFWEEK("APM_MASTER__INVOICE"."Invoice_Date") = 2
MONTH() - Month of the year
The Month() function returns the month of the year (1 through 12) from the given date parameter.
Syntax: MONTH(<date>)
The function takes one parameter, a date field. This can be a database field or the CURDATE() function.
Examples:
To show any AP checks that were written in the month of June:
Month("APM_MASTER__CHECK"."Check_Date") = 6
This function could be combined with the DAYOFMONTH() function to see if today is an employee’s employment anniversary date:
MONTH("PRM_MASTER__EMPLOYEE"."Hire_Date") = MONTH(CURDATE()) AND DAYOFMONTH("PRM_MASTER__EMPLOYEE"."Hire_Date") = DAYOFMONTH(CURDATE())
YEAR() - Year
The YEAR() function returns the year from the given date parameter.
Syntax: YEAR(<date>)
The function takes one parameter, a date field. This can be a database field or the CURDATE() function.
Examples:
To find CM Transactions entered in the current year:
YEAR("CMT_REGISTER__TRANSACTION"."Accounting_Date") = YEAR(CURDATE())
To find CM Transactions from last year that should be moved to the history file:
YEAR("CMT_REGISTER__TRANSACTION"."Accounting_Date") = YEAR(CURDATE()) – 1
LENGTH() - Length of text
The LENGTH() function returns the number of characters in a string of text.
Syntax: LENGTH(<text>)
The function takes one parameter, a text field, and returns the number of characters in it (including spaces).
Example:
To show any JC Jobs with a description longer than 20 characters (which may not appear well on some reports):
LENGTH("JCM_MASTER__JOB"."Description") > 20
CONCAT() - Concatenate
The CONCAT() function puts two text fields together.
Syntax: CONCAT(<text1>,<text2>)
The function takes two parameters, both are text fields, and returns them joined together.
Example:
For a JC Job record where Job='03-001' and Description='NW Food Warehouse', then using the function like this:
CONCAT("JCM_MASTER__JOB"."Job", "JCM_MASTER__JOB"."Description")
would return the value: 03-001NW Food Warehouse.
LTRIM() - Trim space characters from the left side of a text field
The LTRIM() function removes preceding spaces from the beginning of a text field.
Syntax: LTRIM(<text>)
The function takes one parameter, a text field, and returns the text field with spaces at the beginning removed.
Example:
This formula could be used to return records where a name was accidentally entered with a space in front of it (which would prevent that field from sorting correctly):
LTRIM("APM_MASTER__VENDOR"."Name") <> "APM_MASTER__VENDOR"."Name"
RTRIM() - Trim space characters from the right side of a text field
The RTRIM() function removes succeeding spaces from the end of a text field.
Syntax: RTRIM(<text>)
The function takes one parameter, a text field, and returns the text field with spaces at the end removed.
Example:
This formula could be used to return records where a name was accidentally entered with a space at the end of it:
RTRIM("APM_MASTER__VENDOR"."Name") <> "APM_MASTER__VENDOR"."Name"
SUBSTRING() - Return a portion of a text field
The SUBSTRING() function returns a specified amount of text from a specified position of the given text field.
Syntax: SUBSTRING(<text>, starting position [, length])
The function can take 2 or 3 parameters.
The first parameter is the text field to be searched
The second parameter is the starting position of the substring (The first character of the text field is position 1)
The third parameter is optional. It is the number of characters to get from the starting position. If this parameter is left out, the function will return text starting from the starting position all the way to the end of the text field.
Examples:
If you know the prefix length of an account in GL is 9 characters (including dashes), and there was no suffix, you can use this function to get just the base account. If you wanted to show all posted transactions to base account 5120 (and the account format was xxx- xxxx-xxxx):
SUBSTRING("GLT_CURRENT__TRANSACTION"."Account",10) = '5120'
In the same situation as above, but the account format contained a suffix that you wanted to ignore (xxx-xxxx-xxxx.xx):
SUBSTRING("GLT_CURRENT__TRANSACTION"."Account",10,4) = '5120'
LOCATE() - Find a substring in a text field
The LOCATE() function performs a case-sensitive search of a text field for a given string of text, and returns the position of the text being searched. If the text you are looking for is not found, then this function returns 0.
Syntax: LOCATE(<text to find>, <field to search> [, starting position])
The function can take 2 or 3 parameters.
The first parameter is the text you want to find.
The second parameter is the text field you want to search.
The third parameter is optional. It is the position where you want to start looking (the first character is at position 1). If this field is left out, then it will start looking at the beginning of the text field.
Examples:
If you want to show all AP Vendors that have "LLC" anywhere in their name:
LOCATE('LLC', "APM_MASTER__VENDOR"."Name")
If you want to show all PM Properties that have the word "Garage" anywhere in the property name, except for properties that begin with the word "Garage":
LOCATE('Garage', "PMP_PROPERTY__PROPERTY"."Name", 2)
LEFT() - Display a given number of characters from the left of a text field
The LEFT() function returns a specified number of characters from the left side of a text field.
Syntax: LEFT(<text>, length)
The function takes 2 parameters. The first parameter is the text field to use. The second parameter is the number of characters to return from the left side of the string.
Example:
If the JC Cost Code field is formatted xxx- xxxx, where the first 4 characters are the Group Cost Code, and you wanted to use that to show all cost codes in the 400 group:
LEFT("JCM_MASTER__COST_CODE"."Cost_Code",3) = '400'
RIGHT() - Display a given number of characters from the right of a text field
The RIGHT() function returns a specified number of characters from the left side of a text field.
Syntax: RIGHT(<text>, length)
The function takes 2 parameters. The first parameter is the text field to use. The second parameter is the number of characters to return from the RIGHT side of the string.
Example:
If the JC Job field is formatted xxxx-xxxx, where the last 4 characters represent the year the job started, and you wanted to use that to show all jobs that started in 2005:
RIGHT("JCM_MASTER__JOB"."Job", 4) = '2005'
CONVERT() - Convert a given field to another data type
The CONVERT() function changes the type of a field to a specified type.
Syntax: CONVERT(<field>, <type>)
The function takes two parameters. The first parameter is the field to be converted. The second parameter is the data type to convert to. The following is a list of those types:
SQL_BIT (1 = TRUE, 0 = FALSE)
SQL_DATE (year, month, day)
SQL_DECIMAL
SQL_DOUBLE
SQL_REAL
SQL_INTEGER (use this if you want to drop the decimal part of a number, not to round).
SQL_VARCHAR (use this to convert to text)
Examples:
If the JC Job field was formatted as xx-xxxx, where the last four characters represented the year in which the job started, using the RIGHT() function to get the year would return the year as text. If you wanted to compare that value to the current year using YEAR(CURDATE()), the year would be returned as an integer. You can convert the number to text and then compare:
RIGHT("JCM_MASTER__JOB"."Job", 4) = CONVERT(YEAR(CURDATE()),SQL_VARCHAR)
To create a date from text:
CONVERT('2006-01-31', SQL_DATE)
IS NULL/IS NOT NULL - Determine if a field is (or is not) blank
These expressions are used to determine if a field is or is not null (empty). Dates can be empty, as can many text or numeric fields before they are given a value. A field is Null is different from it containing a 0 or blank.
Syntax: <field> IS NULL –or– <field> IS NOT NULL
Use this expression to determine if the field is empty.
Examples:
To show all PR Employees who have not been terminated:
"PRM_MASTER__EMPLOYEE"."Terminate_Date" IS NULL
To show all AP Invoices that have a Discount Date entered:
"APM_MASTER__INVOICE"."Discount_Date" IS NOT NULL
To see CM Transactions where the payee field is blank:
"CMT_REGISTER__TRANSACTION"." Payee" IS NULL OR "CMT_REGISTER__TRANSACTION"."Payee" = ''
NOT() - Switch boolean value of a field
The NOT() function will reverse the value of the expression within the parentheses. If the expression results to TRUE, then the resulting value after the NOT operation will be FALSE. If the expression results to FALSE, then the resulting value after the NOT operation will be TRUE.
Syntax: NOT(<logical expression>)
Reverses the value of the expression.
Examples:
To show all AP vendors who are not 1099 recipients:
NOT("APM_MASTER__VENDOR"."Receives_Form_1099" = 1)
To show all GL Accounts that are not Suspense or Units accounts:
NOT("GLM_MASTER__ACCOUNT"."Account_Type" IN ('Suspense','Units'))
UCASE() - Convert text to UPPER CASE
The UCASE() function makes the specified text field all upper case.
Syntax: UCASE(<text field>)
Converts the specified text field to upper case.
Examples:
To show all AP Vendor IDs that are not in all upper case letters:
UCASE("APM_MASTER__VENDOR"." Vendor") <> " APM_MASTER__VENDOR"."Vendor"
LCASE() - Convert text to lower case
The LCASE() function makes the specified text field all lower case.
Syntax: LCASE(<text field>)
Converts the specified text field to lower case.
Examples:
To show all Invoices with the word "window" in the description:
LOCATE('window', LCASE("APM_MASTER__INVOICE"."Description")
|
