SQL Fields in UFL 1

[BettyM]

Is there a way to call a SQL field in a formula without making it part of the main report query?

Here is our problem. We need to group a report by a field with 3 letter codes which cross reference to office names in an office table.

Unfortunately some 3 letter codes don't have an office name assigned so if we filter to provide an office name for every 3 letter code . . . our filter will filter out the offices not specified in the table.

So as a workaround, we group by the 3 letter code and don't call the office table in our filter to get 100% of the records.

We generated a list of offices with comma seperated values in an UFL. When we assign these values to a formula, it is truncating to 255 characters. We could have thousands of characters of office names so pulling them 255 characters at a time would be impossible.

Our plan was to use an if statement in the formula to name the office. If the 3 character value was found, we provide an office name. If not, just state the office name as the 3 character value.

We are stumped how to solve our problem.

 

[PatrickIRL]

The easiest way to do this is to make your join on the tables a left outer join. This will bring back all the codes from the table whether or not they have a corresponding office code.

Patrick

 

[chelseatech]

I've done something similar in my Text Lookup UFL. You could use a text file or retieve a value from a SQL table.

Inside a UFL you can do anything you like with the code as it's just VB at that point.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm