Print Page | Close Window

Formula to display records not in table?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13831
Printed Date: 29 Apr 2024 at 5:00pm


Topic: Formula to display records not in table?
Posted By: JFinzel
Subject: Formula to display records not in table?
Date Posted: 21 Jul 2011 at 7:08am
I am selecting records based on head of household/spouse, but I want to still include records that have no spouse at all.

The following record selection formula returns all head of household records:
(relationship-headofhousehold)=FALSE

However, it eliminates anyone without a spouse.  I have tried something like this to make it work:

(relationship-headofhousehold)=FALSE OR (spouse link) = ' ' (or 0)

Nothing ever works when I try this.  I either end up with no records, or I end up only with records that actually have a spouse.

In general, the question is: How do I specify records I want to see within a table, but also show records that are not even in the table I am selecting on?

Any help is greatly appreciated, again.


Replies:
Posted By: lockwelle
Date Posted: 21 Jul 2011 at 7:43am
if you have 2 tables, and you want all of one table and part of another, use an outer join (left or right, I don't know how CR defines them). 
If you are looking for 2 different pieces of information in the same table....joining straight to the tables, I am not sure...you should be able to do an OR in the filtering criteria, or a self-join or Union if using a stored proc.
 
I am not familiar with any of the sql selects in CR as all my reports gather their data from Stored Procs.
 
If the data is truly not in the table, then you can't get it.  CR only reads that data that is there, and either includes or excludes it from the report.
 
HTH

Posted By: JFinzel
Date Posted: 21 Jul 2011 at 8:44am
Let me see if I understand you correctly.

Suppose my main constituent table goes up to number 483.
I also have a table that has spouses.  This table goes to 456.

I want to find people who are in table 1, but have no spouse in table 2.

I also need to find who is head of household from table 1, but I already know how to do this with another condition and table that I have.

Are you saying that I can't get the information by doing a formula like so:
(Const. is in table 1) and (Const.Spouse is not in table 2) [i.e. Const. has no record in table 2 for spouse]

?

I have already run into many cases where this would be helpful.  Is it not possible to do a formula like this?

Thank you!

Posted By: lockwelle
Date Posted: 21 Jul 2011 at 11:13am
if the numbers 'identify' the rows, so number 1 in both table refer to the same 'couple', then you can do outer join.
 
In SQL it would be select * from table1 as t1
  left outer join table2 as t2
    on t1.number = t2.number
where t2.number is null
 
this is return all rows in table1, and any matching rows from table2.  If there is not a match in table2, then the value is NULL.
 
you can link tables in CR, and there is the ability to do an outer join (right click on the link line).  I don't know how CR determines left and right.
 
without the link, or using an inner join, then the formula that you propose won't work.  With link, it would be ISNULL(Const.Spouse)  (because it returns True if the spouse is missing ie null, and false if they exist)
 
HTH


Print Page | Close Window