field select based on table

[poconoski]

Hello,

I am new to Crystal and would appreciate any help you can offer.

I have a report with a field called PPR which can be null or hold a number. This resides in table A.

In Table B the fields are Table, Code, Field, Desc.

Based on table A PPR, the description field needs to be pulled from table B. I have done the insert field and I can not figure out the FORMULA.

If this were SQL I would do something like -

If not isnull({Master.PPR}) then
select {CodeLookupsActive.Description} where {CodeLookupsActive.TableName}="MASTER" and
{CodeLookupsActive.FieldName}="PPR" and {CodeLookupsActive.Code}={Master.PPR}

I hope this is clear enough.

 

[BlurredVision]

I hope this is what your looking for..

if {Master.PPR} <> '' then
if {CodeLookupsActive.TableName}=&quot;MASTER&quot; and
{CodeLookupsActive.FieldName}=&quot;PPR&quot; and
{CodeLookupsActive.Code}={Master.PPR} then
{CodeLookupsActive.Description} Brian

http://www.crystalinfo.net

 

[poconoski]

Thanks Brian. I do get an error.
&quot;A number is required here&quot;
Should I ignore it?
Joe

 

[BlurredVision]

can't really ignore it
Sounds as if a number field is being compared to a string.
can do something like
if {number_field} = tonumber({string_field}) then
true

Brian

http://www.crystalinfo.net

 

[Naith]

If {Master.PPR} is a string, then the first line of your formula should probably be

If not isnull({Master.PPR}) and {Master.PPR} <> '' then...

Nulls and '' are treated differently in some databases, which is then echoed in Crystal.

When you get your error, where does the cursor jump to in your formula? I get the impression that {Master.PPR} is actually a number field, and that this is where the cursor flashes. If this is in fact the case, then your first line should then be like you had it in SQL, as '' is a text string.

If not isnull({Master.PPR}) then ...

Naith

 

[poconoski]

Error is fixed. Added Cstr. Code is now->

if not (isnull({Master.PPR})) then
if {CodeLookupsActive.TableName}=&quot;MASTER&quot; and
{CodeLookupsActive.FieldName}=&quot;PPR&quot; and
{CodeLookupsActive.Code}=Cstr({Master.PPR}) then
{CodeLookupsActive.Description}

Master.PPR is a number and CodeLookupsActive.Code is a string.

In CodeLookupsActive there are a possible 0 to 99 matches.
The table name MASTER and the FieldName PPR are listed 99 times. For each record there is a different Description.

I am selecting a single record from the Master table.

When I run it I get a blank page.

Thanks again for your help.

Joe

 

[poconoski]

Will this code filter through table B and select a match?
When I run it with this formula I get a blank page.
With out the formula the correct record is selected and displayed.