Display values from 2 tables that do not have common data

I have two simple tables.
Table 1 fields:
project_id         wds
building_id       001
unit_id              0002
activity             30000
user                 Joe Smith

Table 2 fields:
project_id       wds
building_id     001
unit_id            0002
activity           30000
complete       True

Table 2 only contains values if an activity has been marked complete.  I've been able to create a report connecting the two tables with Full Outer Join, Not Enforced, Link Type "=".  This allows the report to display activities that have not yet been marked complete and because they are not marked complete, the values are only in Table 1.  

When the report renders, any job activities that have not been marked complete will display on the report and the complete field is blank - as there is no data in table 2 for that job.

Here's my question / problem:  I want to filter the report by complete <> True, so that I can see a list of activities that have not been marked complete.  When I do that, the report does not return anything.  I'm assuming this is happening because Table 2 only includes job values that have been completed = True.  

I've tried all of the various combinations for linking the tables, so that it displays values from Table 1, that don't yet exist in Table 2.
How can I display values from Table 1, if I want to filter based on a field in Table 2 if the values do not match between tables?

I've also tried using a formula: if {vnd_CompletedActivity.complete_} = True then 1 else 2, but  the only thing that displays on the report are "1", no "2".
table-linking.pdf
GStonerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
You can't filter on table 2.  Crystal will turn the join to an INNER since NULL cannot match anything.

Are you trying to get all open projects and some of the completed ones?

mlmcc
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use the EXCEPT keyword:
SELECT project_id, building_id, unit_id, activity
FROM table1
EXCEPT  
SELECT project_id, building_id, unit_id, activity
FROM table2

Open in new window

GStonerAuthor Commented:
mlmcc -
I'm trying to display ONLY the activities that are not complete.

I am able to combine the two tables and show both Complete = True and Complete = "",  see the attached example, but when I try to filter on the Complete field by using Complete <> True, nothing is returned.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Vitor MontalvãoMSSQL Senior EngineerCommented:
No example attached.
Did you try my suggestion?
GStonerAuthor Commented:
Vitor,
At the bottom of my original post, there is a pdf file link.
I'm not sure how to apply your suggestion.  Should I use that in the Select Expert in Crystal Reports?  I just don't know where to start with your suggestion.
GStonerAuthor Commented:
As a work around, I Grouped the report by the Complete field.  That does work for separation, as the first group is the records with no completion date and the second group is Complete = True.  I'm now trying to suppress or hide the second group.  

Same thing is happening.
If I use the Section Expert and try to suppress the details field by Group using this:  {vnd_CompletedActivity.complete_} = True
All of the records are suppressed, not just the complete = True records.
GStonerAuthor Commented:
Another side note:
I've found that I can use Format Editor on individual fields.  I can change font color using:
if {vnd_CompletedActivity.complete_} = True then crWhite
and that only changes the records where Complete = True

But, if I use the same logic to Suppress:  if {vnd_CompletedActivity.complete_} = True
everything is suppressed, not just Complete = True
mlmccCommented:
Try using

   IsNull({vnd_CompletedActivity.complete_)

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GStonerAuthor Commented:
IsNull({vnd_CompletedActivity.complete_}) does not work for Group suppression.  Everything gets suppressed.
mlmccCommented:
Try using that as the selection formula

mlmcc
GStonerAuthor Commented:
Once again, your solution has saved me.  You're the man.
Adding IsNull({vnd_CompletedActivity.complete_})  to the selection formula did the trick.

Thanks for your help!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shouldn't you choose mlmcc's comment instead of yours?
GStonerAuthor Commented:
My mistake.  I have selected mlmcc's solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.