St. Louis ACL User Group

Calculate Working Days Between Two Days

There may be times when you need to determine the number of working days between two dates. This result can be achieved within ACL by creating a conditional computed field involving multiple ACL functions.

Suppose that start_date and end_date are two date-type fields that are already defined in ACL. Calculating the number of working days between these two dates involves testing for different conditions. For example:

• If the starting date is, for some reason, after the ending date.
• If the starting and ending date both fall on a Saturday or Sunday.
• If either the starting or ending date fall on a Saturday or Sunday.
• If the weekday of the ending date falls before or after the starting date.
• If you need to take into account long weekends or holidays such as Christmas Day, New Year”s Day, and so on.

Read more »

In case you missed the happy hour fun, here are the

handouts.  They include numeric/character

tips, variable tricks, and append hints.

Happy Hour Handouts

Print a table layout cheat sheet

This is convenient when relating and joining multiple tables. If you print the table layouts for each table, you can have the length, type, etc. at your fingertips.

Click on File -> Print Project Contents…

Select the table layouts that you want to print.

You can also print table history, scripts, and view details.

Combining Multiple Files

• 12 monthly bank statements in txt format all named after their month (e.g. “January.txt”)
• Needs to be imported via print image
• Combine all months into 1 file for analysis

Working with leading zeros

Using some sample data, we’ll learn how to add and subtract leading zeros from a field. Leading zeros can be common in document numbers, inventory codes, etc. Below is a mix of numbers – some have leading zeros and some don’t.

Read more »

Calculate working days between two dates

Extracting the Last Name from a Full Name field

Often, a full name field contains more than just a first and last name, and sometimes the naming convention isn’t always the same. For example if you have a name “John H. Doe Jr.” – how do we extract out the last name when other names might appear with only a first and last name? The instructions below might be a good solution: Read more »

Join, Relate, & Append

When to use the multi-file commands

• Use Join when you want to compare the contents of two files.  Join lets you choose what combination of matched or unmatched records you need and create a new file with the desired results.
• Use Relate when you want to complete a file by adding a field (column) from another file.  You do not create a new file, you give yourself access to everything in another file as it relates on your key field.
• Use Append when you want a longer file (more records).  It’s like copying and pasting one file on the bottom of another. Read more »

This will find transactions/records that occur on weekends (Saturday and Sunday). This could be useful in finding payments that normally should not occur on weekends:

Filter:
MATCH(CDOW(INVOICE_DATE,3),'Sat','Sun')

Use in a Script:
SET FILTER MATCH(CDOW(INVOICE_DATE,3),'Sat','Sun')