Table left outer join to another returns nulls - can I calc on that

[etienne123]

Hi

Table A is linked to Table B via a left outrer join. Table B therefore only returns records in cases where a record in both tables extist. I need to do a formula that cuts across the whole dataset (I.e. where records are returned - where there was a match; and where no records where returned - where there was no match and thus a null field). The example shows col1 (from table A), col2 (the matching column where applicable from table B), col3 (from table B - contents is always AFFS), the result of the formula herewith, and the Ideal result.

If col3 = 'AFFS' then 'AFFS' else
If isnull (col3) then 'Other' else
If col3 <> 'AFFS' then 'Other' else
'Other'

col1 col2 col3 @formula @ideal
1 Other
2 2 AFFS AFFS AFFS
3 Other
4 Other
5 5 AFFS AFFS AFFS
6 6 AFFS AFFS AFFS
7 Other

Is this at all possible. I have tried many permutations in tghe formula such as startswith ' ', like '*', etc

 

[Turkbear]

Hi,
In Crystal, always test for NULLs first in any formula, so try:

If ( isnull({col3}) or trim({col3}) = "" )
then 'Other' 
else
{col3}

To Paraphrase:"The Help you get is proportional to the Help you give.."