1In this month’s ACL Tips & Scripts we will be exploring 3 useful yet underutilised ACL functions – CDOW(), RECOFFSET() and MAP().
The CDOW() function returns the name of the day of the week for the specified date.
Syntax
CDOW(date, length)
Where:
“date” is the date field or the date value you would like to convert.
“length” is a numeric constant. This is a value between 1 and 9 that specifies the length of the output string. For example for Saturday, a length of 1 will return “S” and a length of 3 will return “Sat”.
Sample Usage
You can use the CDOW() function to identify transactions or entries that occured on a weekend. Or you could create a computed field using the CDOW() function and then Classify or Summarize the field to get a summarised report per day of week.
The RECOFFSET() function looks at the records above or below our current record and returns a field value.
Syntax
RECOFFSET(field, number_of_records)
Where:
“field” is the name of the field to retrieve the value from.
“number_of_records” is a numeric constant. Specifies the number of records from the current record. A positive number specifies a record after the current record, and a negative number specifies a record before the current record.
Sample Usage
From identifing duplicates to creating a running total, the possibilities are endless with the RECOFFSET() function. However, one of the most popular usages of this function is to identify split transactions. For the following filter to work, your table has to be sorted on the transaction number and then the transaction date. The filter will check if the record below is a split transaction. The date difference between the two transactions is less or equal to three days.
The MAP() function indicates if a string matches a specified format.
Syntax
MAP(string, format)
Where:
“string” is the value to test.
“format” is The data pattern to identify in string. The format string must be in double quotes. The format parameter can contain literal characters, which only match if the same character exists in the same position in string. You can also specify any of the format types listed in the table below, or you can use a combination of literal characters and format types.
| Format type | Description |
| X or x | Match any alphabetic character (a-z, A-Z, European characters)
This format type is not case-sensitive. You can use X or x. |
| 9 | Match any number (0-9) |
| ! | Match any non-blank character |
| ? | Match any character |
| \ | An escape character that lets you specify that the following character is a literal, regardless of its value. This is useful if you want to match on any of the format characters listed above. |
Sample Usage
If we are dealing with a field that follows a certain pattern then we can use the MAP() function to identify exceptions. For example, we know that invoice numbers have a length of 9 characters:
The following filter will highlight invoice numbers that do not follow this pattern:
