How to select transactions which include specific products

[dannyb29]

Good Afternoon. I hope all is well.

I am using Cystal 2008.

How do i bring back all transaction details which include product code: 5001, 5005, 5010, 5015

for example
transaction 1
5001 (kit kat)
5503 (mars bar)

transaction 2
5005 (m&m)
5001 (kit kat)
2015 (coff8e)

So all transactions that do not include any of the listed products I will need to exclude.
However i need to see all parts of the transaction where 1 or more items from my list above are present.

I hope that makes sense.

Many Thanks

Dannyb

 

[Charliy]

{table.product_code} IN [5001, 5005, 5010, 5015] if the code is numeric

{table.product_code} IN ['5001', '5005', '5010', '5015'] if the code is character

 

[dannyb29]

Hi,
Thanks but this only brings back that particular record. If the transaction containing one of the specified products and contains other items i also need to show the additional products, but only if the transaction contains products from my original list.

1 transaction can contain many items, but i only want to see full transactions that contain one or more of my listed items.

Thanks

Dannyb

 

[CoSpringsGuy]

use the code Charliy gave above with a slight modification to create a formula called prodcodesel

if {table.product_code} IN ['5001', '5005', '5010', '5015'] then 1 else 0 //fix if numeric

in GROUP selection formula (not record selection)

sum({@prodcodesel},{yourtranscodefield}) > 0

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[hilfy]

So, if I understand you correctly, you want to show all of the details of any transaction that contains the specified codes. Is that correct?

Try this:

1. Add the transaction details table a to the report a second time. Crystal will throw you a warning and ask if you want to "alias" the table. It will then add the table with "_1" on the end of the name.

2. Do an Inner Join from the Transaction parent table to this new copy of the details table. This has the potential to cause duplicate records which we'll take care of below with groups. NOTE: It is VERY important that you do NOT use any fields from this table outside of the Select Expert!

3. Do your filter for product code on this new copy of the details table, NOT on the original details table.

4. Group on the Transaction and also on the product code (or name) of the details from the original details table (without the "_1" on the table name.

5. Do not put your detail data in the details section - use the group header or footer for the details data. This data will come from the original details table.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[dannyb29]

Hi,
Thank you all for your help, but the results only bring back the selected items and not the items that were purchased alongside them. Do you have any other solutions?

Dannyb

 

[Charliy]

Use my original formula as your select.
Group on Transaction number.
Suppress Group Header and Detail.
In the group footer put a subreport linked by transaction number that retrieves all records with the trransaction number.