Exception Reporting Crystal XI

[lisa626]

I have the following situation:

Table A
Table B

Table B includes ALL fields in Table A PLUS there are approx 20 custom fields added based on a DTS process that runs.

What I am wanting to do is create some validation/exception reports to show...

1. SHOW Ticket # where {TableA.Field1}<>{TableB.Field1}, for some reason if I just try to do this in a formula it doesn't work.

2. SHOW Ticket # where any of the custom fields on Table B are null, thinking something like

if isnull ({tableb.customfield1})
then "NULL Custom Field 1"
or if isnull ({tableb.customfield2})
then "NULL Custom Field 2"
or if isnull ({tableb.customfield3})
then "NULL Custom Field 3"

and so on and so on, would like to be able to do this on 1 report that looks like

Ticket #
12345678 NULL Custom Field 1
23456789 NULL Custom Field 1, NULL Custom Field 2
34567890 NULL Custom Field 1, NULL Custom Field 2, NULL Custom Field 3

etc.....

 

[Madawc]

How are the two tables linked? It should be left-outer, which will allow table A records to show when they have no equivalent table B entry.

Note that Left Outer fails when you also do a selection for the second table.

It helps to give your Crystal version.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lisa626]

Sorry, thought I gave version in Subject Line, it's Crystal XI.

So, for the 1st report I need to join to tables with a left outer on all fields or just one???

What do you mean by "Note that Left Outer fails when you also do a selection for the second table"

Thanks.

 

[Madawc]

Sorry, yes, you did give your version.

Join the tables with whichever fields define the link. It might be just an account number, or it could be more, depending on your data.

When Crystal does a test on Table B, it also rejects Table A records that don't have a linked Table B record. I've been told you can get round this by first testing for null, since what it finds is null when there is no Table B record. But it's never worked when I tried it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lisa626]

Sorry, not trying to be dense here but...

1. So join the tables

2. Do I put

TABLE1.Field 1 Table2.Field2

or should I set up individual formulas for each field?? If you know where I could find any good examples of this that would be a huge help.