Added additional table - Now have undesirable results

[Bennie47250]

Using Crystal 7.0 against a MS SQL database

Need to improve an existing report so I added an additional table and created the link. Sometimes the field that was linked to the new table will be empty (ether a null or a zero length field) or will not have a matching record in the table so I did a left outer join on the field (so al the records are returned) and I’m not getting the desired results in a new formula.

To be able to group records, I’m using the new formula “if Table1.Group = 1 then “HD” else Table2.Agent”

This works fine if there is a match on the linked field with the added table, however it the field is empty (ether a null or a zero length field) or does not have a matching record in the table, the formula returns a blank field where I would have expected the Table2.Agent value to be. I have added the field Table2.Agent to the report and it is always populated.

Any ideas on how to always have ether the HD or Table2.Agent populate this formula?

Thanks
Bennie

 

[Charliy]

Try this:
if Table1.Group = 1 then “HD” else
(if isnull(Table2.Agent) then "No Agent" else Table2.Agent)