1 to many table exception Report

[yctsai1]

I have two tables

Table1.applicantid
Table1.state

Table2.applicantid
Table2.deductions

applicantid State deductions (a-z)
1 NY D < hide
1 NY N < hide

2 NY N < hide

3 NY O < show

4 NJ D < show

5 NJ D < hide
5 NJ J < hide

I need a report that shows only applicants in NY that does not have deduction N and applicants in NJ that does not have deduction J. Each applicant have multiple deductions in deductions table.
I can hide all State = ny and Deduction = N but the same applicant may have other deductions such as D and it shows up.

 

[Madawc]

Do a left-outer link from applicant to deduction. This will give you applicants with no deductions. It will, unfortunatley, stop you selecting just the deductions you want.

Group by applicant. Suppress group header and details. Do a running total count that looks for the disqualifying deductions. Suppress the group footer if this count is greater than zero.

(Right-click on a section and choose Section Expert. Then choose the formula icon (x+2 and a pencil) for suppression. Enter a formula.)

It helps to give your Crystal version, though in this case it should not matter.

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

 

[lbass]

Create a formula {@meetscriteria}:

if
(
{table.state} = "NY" and
{table.deduction} = "N"
) or
(
{table.state} = "NJ" and
{table.deduction} = "J"
) then 1

Insert a group on applicant ID and then go to report->selection formula->GROUP and enter:

sum({@meetscriteria},{table.applicantID}) = 0

If you then need to do any calculations across applicants, be sure to use running totals, since non-group selected records would contribute to inserted summaries.

-LB

 

[yctsai1]

Thanks lbass,

1 more issue,

If {table.state} = "NY" and there are no deductions at all, it needs to appear.

With the above conditions, where {table.state} = "NY" and there are no deductions to compare to, it is skipped. and doesn't show on report.

Tried to add "or {table.state} = "NY" and
{table.deduction} = "" but doesn't work.

applicantid State deductions (a-z)
1 NY D < hide
1 NY N < hide

2 NY N < hide

3 NY < show (missing deduction N)

 

[lbass]

Change the formula to:

if
(
{table.state} = "NY" and
(
isnull({table.deduction}) or
{table.deduction} <> "N"
)
) or
(
{table.state} = "NJ" and
(
isnull({table.deduction}) or
{table.deduction} <> "J"
)
) then 0 else 1

Then use the same group selecton formula as before.

-LB