SQL Table Lookup within a Formula Field 1

[jrisch]

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

 

[lbass]

You could add a subreport and link the fields by creating a formula in the main report:

tonumber({table.string})

...and use that to link to a subreport that only contains the lookup table. This shouldn't actually take up that much time even in the detail section. You don't have to pass data; just suppress all unused sections of the sub and display it directly in the detail section.

You could create a command to use as your datasource, as another alternative, where you convert the string to a number in the join.

-LB

 

[jrisch]

Thanks, lbass. I was trying to avoid using a subreport as the current record may already be in a subreport. Essentially, I've main records which have a number of audit records each and the audit records have multiple types which are identified in the lookup table.

The idea of using an SQL command could work though. I'll see if I can develop that idea.

Regards, John.

 

[lbass]

If you go that route, you should use the command as your sole datasource, and set it up something like this:

select table1.code, lookup.desc, table1.otherfield
from table1
left outer join lookup on
to_number(table1.code) = lookup.code

//etc.

-LB

 

[IdoMillet]

One of the User Function Libraries (UFL) listed at

http://www.kenhamady.com/bookmarks.html

allows you to create a regular formula in Crystal that returns the result (single or delimited values) of a dynamically constructed (using information in the report) SQL statement.

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com