Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help w/ Select Formula 1

Status
Not open for further replies.

byrne1

Programmer
Aug 7, 2001
415
US
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.
 
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
 
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.

[yinyang] Madawc Williams (East Anglia, UK) [yinyang]
 
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
 
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!!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top