Crystal Reports: Converting Domain Names to Full Description

The report we developed in for a project to identify domain names from an Outlook address book was looking pretty good, but some of the top level domain codes were a little cryptic.

So we wanted to see the Description of your domain (New Zealand instead of nz). We found a text file that listed all the top level domains, and then with some copy and paste we created a long formula to display the descriptions.

If {@TLD}=”nz” then “New Zealand” else 
If {@TLD}=”au” then “Australia” else 
If {@TLD}=”uk” then “United Kingdon” else 
If {@TLD}=”ca” then “Canada” else “Elsewhere” 
…. 
However, we also think a Select Case statement might be a tidier solution:

Select {@TLD} 
case ”nz”: “New Zealand 
case ”au”: “Australia” 
case ”uk” :“United Kingdon” 
case ”ca” : “Canada” default: “Elsewhere” 

Or see if you like the alternate solution we’ve developed below. 
. . . utility hint . . . 

Text File Lookup via a User Function Library 

The domain description lookup formula we developed on the previous page was several hundred lines in length. Some domains might not be in the list now, but they could be in future data. 

If we need this formula in multiple reports, the entire list needs to be copied into every formula in every report. The domain descriptions don’t change very often, but what if you had a description list you needed that did require frequent revision. Ideally these things should be in your database, but sometimes this isn’t possible. 

We’ve developed a User Function Library that can open and search through a text file for a line and to retrieve a value from that file. It is very useful if you have a CSV or Tab separated file as the library includes functions that extract an individual column from a row of data. 

The complex and lengthy formula statement in the previous example is reduced to a simple text file lookup with

TextLookupColumn ("domain.txt",{@TLD}, 1) 

A trial version of our text file lookup function is available here.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.