Is there a way to interrogate the current database within a formula field?

The situation is that I need to obtain a value from a lookup table within the current database, based on a field within my current record. However, I can’t link the lookup table because the field in my current record is a string even though the value is numeric, whereas the link field in the lookup table is a number (integer). Trying to link throws up a type error.

The second problem is that there’s a value that the current record field can have, that is not represented in the lookup table. That’s a “-1” to indicate a special case. I realise that if I could link tables then I would need an outer join.

I can probably handle this by using a hidden subreport with shared variables, but that seems a bit cumbersome. I wondered if there was another way, using an SQL Expression or an SQL Command perhaps. Or some way of running an SQL query in a formula field.

Regards, John

One of the UFL's (User Function Libraries) listed at http://kenhamady.com/bookmarks.html allows you to use a Crystal formula to dynamically construct an SQL statement, execute it against any ODBC data source, and return the result as a single or concatenated value.