Crystal reports formulas are very good at manipulating data within the report.  And, you can often expand these capabilities by installing a User Function Library (UFL) that adds formula functions not already provided by Crystal Reports.  This article will introduce you to several powerful UFLs that will give your formulas the ability to read, write and run files outside the normal boundaries of your reports.

These UFLs are available from five sources and sometimes the same function is found in multiple UFLs.
The vendors are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (Cut Light)
Chelsea Technologies (File Mgt, Text, and others)
CrystalKiwi (Export Data/Table of Contents)

Note that the price for a UFL doesn’t correspond to the number of functions it provides.  The two UFLs that provide the highest number of functions are either $50 or free.  Then there are two vendors that sell several very specific UFLs with only a handful of functions each.  These are mostly priced at around $100.

Of course, the only functions that really matter are the ones that make your job easier.  So lets start with the twelve functions that I think will interest you the most.   The first three are ones I use regularly.  See this feature matrix (xls) to see all 60+ functions that are available in these UFLs.

1) Carry values from today’s report to tomorrow’s report, or 2)  from one report to another.
I periodically generate a list of credit card payments to post in my AR program.  It speed things up if I can remember where I left off the last time.  So my report now has two formulas that can read and write to a text file.  One formula takes today’s date and writes that value to a text file so that it can be read the next time the report is run.   The second formula reads the date stored in the file the last time I ran the report, showing me where I left off.  You can even use this function to store a series of values.  If you store each value with a second “key” value, you can use the key to retrieve specific elements of the series.

3) Append lines to a text file.
I have been asked many times to turn report data into rows of fixed length text. Normally you would export from Crystal.  But if the row is very wide you run into limits based on the page width, even though the export doesn’t  go to a physical printed page.  With CR 2008 the widest ‘fixed width’ text export I could get was 377 characters, but this varied widely by version.  Recently I realized that I can use the “append line” function to add each row of the report directly to a text file.  Now I can create a string of any length because I am bypassing CR’s export function completely.  It is a bit slower to create the file than an export would be but it is easier to set up and works the same in all versions.  And since many different reports can add lines to the same file, you can use this function to build a log of reports run, or of exceptions found.

4) Automatically copy a value to the clipboard.
I use Crystal to generate a PDF invoice for my customers.  When someone wants me to invoice them immediately I run the report and export it to a PDF.  I generate a file name using a formula I placed on the page.  That way I could simply copy the formula value in preview and paste it into the “save as” box.  But recently I added another formula that automatically copies the invoice name formula value to the clipboard whenever the report is run.  Now when I refresh the report the “copy” step is automatic and all I have to do is “paste” the file into the box when I am ready to save the exported file.
So read through the rest of the list and see if any of these capabilities would make your life easier.  If you have any creative uses for these features, let me know.

5) Check the login of the user running the report.
This allows you to write a formula that does different things based on who is running the report (user login).  One use of this is to filter a report’s records specifically to that user, for instance automatically limiting each sales rep’s reports so that he only sees his own customers.

6) See if a file or folder exists – either on your network or on the internet (http).
7) Rename/copy/delete a file on your hard drive or network driver.
8)Launch an application or run a batch file.
9) Execute a SQL statement.
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or between lat./long. coordinates.