Help w/ Select Formula 1

[byrne1]

I have a rather complicated question. I am pulling all records from the ITEMS table with VENDORNUM=1234. Each of these records will have the field SERFLAG=''. Also, each of these records will have data in the field VENDITEMNUM.

So, my select formula is "ITEMS.VENDORNUM=1234" and my report fields are: ITEMNUM, VENDORNUM, VENDITEMNUM, SERFLAG.

OK, now here comes the complicated part. Some, but not all, of these records will have a matching record in the same ITEMS table (matched on VENDITEMNUM) where SERFLAG='R' (the VENDORNUM field will be different as well). I need to print the ITEMNUM field for these records if and when they exist.

How can I do this.

 

[Naith]

You need to add another instance of the same table, and create an outer join from Items1 (with the 1234 clause) to Items2.

Don't include SERFLAG = "R" in the selection criteria, or you'll force the join to equi, instead of outer. Use a formula or grouping to handle the revealing of the R SERFLAG record.

Naith

 

[Madawc]

Add the table again, as an alias

Link the two using 'left outer', which means that records without an entry in the alias table will still be shown.

Unfortunately you can't get left-outer to work in Crystal if you also do a selection on that table. (At least I've never been able to get it to work, not even if I include 'null' in the selection.)

You'll probably need to do field-supression to avoid unwanted linked itmes.

Madawc Williams (East Anglia, UK)

 

[lbass]

You could add the Items table again as an alias table Items_1, with Items_1 linked to the Items table on VendItemNum. Then create a formula:

if {Items_1.serflag} = "R" then {Items_1.ItemNum} else ""

Or you could add a subreport containing the ItemNum which is linked to the main report on VendItemNum.

-LB

 

[byrne1]

Thank you all for the help. I ended up adding the subreport as suggested by lbass. I guess I should have thought of that but, hey, that's what this web sites all about.

Thanks again!!!!