If...Then formula on fields with left outer join

[cf53bank]

I need help creating a formula to be grouped on. I am working on Crystal 8.5 I have a table listing all course completions. In my database there are two types of courses and each type has its own table which list details about the courses. Here is how I have the tables linked

{Complete_Table.Course_ID} linked to {Courses.ID} with the properties set to left outer join

then I have

{Complete_Table.Course_ID) linked to {Products.ID} with the properties set to left outer join

Then within both the Courses and the Products table there is a field = Category. I would like to group on this field but since it resides it two separate tables I need to create a formula that displays this value for both courses and products so I can group on it. I have tried if then else statements but the formula only supplies a vaule for the first statement, ex.

If {Complete_Table.Course_ID} = {Courses.ID} then {Courses.Category} else
If {Complete_Table.Course_ID} = {Products.ID} then {Products.Category}

This formula only populates the category for courses. If I list the Product table first I only get values for products.

Any help or suggestions would be greatly appreciated.

 

[synapsevampire]

I guess what you're saying is that for each course_id you have both a courses and product category?

Rather than post descriptions, try posting technical information:

Database/connectivity used and can you create database objects such as Views?
Example data?
Expected output?

It sounds like you'll want to create a Union All query, but it's hard to say without technical information.

-k

-k