Missing records - 3 table join 1

[jlsmithhartfiel]

Hi!

I've searched for this, and have found a few issues that relate, but still not able to resolve this.

I have 3 tables: problems, project_phases and phases.
problems--> project_phases (left outer join)
project_phases --> phases (left outer join)

This shows me all the records I want...until I add phases.name to the report.

I created a formula:
if isnull({problems.proj_phase_id}) then "no phase" else "phase"

This correctly shows me no phase or phase, however, if instead of "phase" I try to show {phases.phase_name}, my recordset goes down to only those that have a problems.proj_phase_id filled in.

What am I missing?

Thanks in advance, Jessica

 

[lyanch]

Do you have anything about phases in your selection formula? Crystal doesn't handle things well if your selection criteria is "on the right" of a left outter join. There are ways to deal with it however. Give us a little more information and we should be able to help you.

Lisa

 

[jlsmithhartfiel]

Thanks Lisa!

Actually I created a test report to cut out all of the criteria & other stuff in my final report to try to isolate the problem. So, no criteria at all, no grouping. Only reference to phases table is when I want to display the name rather than the id (or text "phase" in above formula).

Puzzled, Jessica

 

[lyanch]

What happens if you change the formula to:

if isnull({phases.phase_name})then "no phase" else {phases.phase_name}

What happens if you just put the field {phases.phase_name} on the report.

What happens if you add a single field from the project_phases table.

Last but not least.. are you SURE you have a left outer join? This is symptomatic of adding a field from a table that needs a left outer join.

Lisa

 

[jlsmithhartfiel]

Ok, (shame-faced) my original report had the left joins, but forgot to change to left join on the phases table. Duh. Now I have to figure out why it's not working in the same report.

Thanks for the tips!
Jessica Jessica