formula for a field which may be null

[GCCkjn]

I have a fairly complex (for me) report pulling info from various tables in our database. Then for each of these records (or clients), I have inserted a subreport identifying the initial assessment info and the first intervention info. Everything is working so far. Except now, I want to identify where the visits in the subreport took place. If it is identified on the visit activity, everything is fine. However, there are some visits where the location is not filled in (we assume our main site in these cases). If I add that field to the report I only get returns where the location has been filled in. I tried my hand at a formula: if isnull({LookUp.Value}) then
"GCC-Oshawa" else {LookUp.LongDesc} However, now for the records where there is no location, everything else (i.e. date, provider, activity name, etc) is blank EXCEPT the 'GCC-Oshawa'.

Thanks for your help. (I am working with version 9 of Crystal)

 

[lbass]

How did you link your tables in the sub to each other? You need a left join from the main table to the lookup table.

-LB

 

[GCCkjn]

Within the subreport, I do have a left outer join from the activity table to the lookup table. The subreport is linked to the main report by chart number (cpino).

 

[GCCkjn]

To add to the confusion, the lookup table is also used in the main report but for something different. However, I have also made the join to the lookup table in the main report a left outer join.

I guess I can't put a subreport within a subreport? When I run the subreport on it's own with the lookup info as a subrept, it works fine. But when I put it back into the main report the 'sub sub report' is removed.

 

[lbass]

No, you can't nest subreports within a subreport, but I'm not sure how that relates to the issue.

-LB