What is a function?

You can think of an ACL function as a small tool that performs a specific, useful task. For example, you can use a function to standardize upper and lowercase in a piece of inconsistently formatted text:

Function and input Output
John Smith

Another way to think of a function is as a "black box". The input goes in one side, something happens inside to transform it, and the output comes out the other side.

The three basic parts of a function

The example PROPER("john SMITH") demonstrates the three basic parts of any function:

  • The function name in this case, PROPER
  • A set of parentheses an opening parenthesis ( and a closing parenthesis )
  • The function input everything inside the parentheses: in this case, "john SMITH", including the quotation marks


Throughout ACL documentation, function names are presented in uppercase, which is simply a formatting convention. ACL does not require that functions are entered in uppercase.

Input and output

A slightly more technical description of a function is that it's a calculation or operation performed by a computer that accepts an input and returns an output.

In the example above, the input is "john SMITH", the output is John Smith, and the operation performed by the PROPER( ) function is to transform all words to proper case (initial capital letter followed by lowercase).

Functions never alter source data

A function never alters the source data used as input. It uses the input data to calculate the output results, and the results are stored in your computer's memory so you can make use of them.

In the example above, the physical data john SMITH remains unchanged on your computer. You can think of the output John Smith as "virtual data", which exists in memory, and can be used in subsequent operations.

We revisit this point in the discussion about using functions to created computed fields.

The scope of functions

The calculation or operation performed by any particular function is narrow in scope. The PROPER( ) function does nothing more than convert the case of text. But as you'll see throughout this set of tutorials, even though the scope of each function is small, functions are powerful, and crucial to data analysis in ACL.

The difference between a function and a command

ACL functions and commands both perform calculations or operations on data, but functions are narrow in scope, whereas commands are often broad in scope. For example:

  • narrow scope the PROPER( ) function converts the case of text
  • broad scope the SUMMARIZE command groups all the records in a table

Functions can provide input for commands. For example, you might use the PROPER( ) function to convert the case of a Name field, and then use the SUMMARIZE command to group records by the now-standardized Name field.

The reverse is not true. Commands cannot be used as inputs for functions.

Some more examples

You can use a function to remove leading and trailing spaces from text, to remove hyphens from an ID number, or to find records with dates in a particular date range.


Here are examples of what three different functions can do:

  • the ALLTRIM( ) function
  • the EXCLUDE( ) function
  • the BETWEEN( ) function
Function and input Output
ALLTRIM(" Chicago ")

Letters only, no leading or trailing spaces.

EXCLUDE("VT-123-45", "-")

Letters and numbers only, no hyphens.

BETWEEN(`20170701`, `20170101`, `20171231`)

In the function input:

  • the first date is the one being tested
  • the second date is the start date of the range
  • the third date is the end date of the range

Returns T for True because 01 July 2017 is between 01 January and 31 December 2017.

Literal values versus fields as function input

The examples above use actual, or literal, input values so that you can see exactly what each function does. In ACL, you typically use a field, or a variable, as the primary input for a function.

A field as function input is shown below. Variables as function input are explained in a subsequent tutorial.


Here is the BETWEEN( ) example from above, but now with a date field as input rather than a literal date value.

BETWEEN(Invoice_Date, `20170101`, `20171231`)

The function returns T for True for every date in the Invoice_Date field that falls in the year 2017, and F for False for dates in other years.


The two boundary values you specify for the BETWEEN( ) function are inclusive. Small details like this one are included in the Help topic for each function.

Where to next?

Learn how to quickly and easily familiarize with any ACL function: Familiarizing with different functions

[ Back to top ]

(C) ACL Services Ltd. All Rights Reserved. Thursday, October 4, 2018