Record selection formula and parameter field 1

[ramc2000]

Hi, I need to know how to use a parameter field to select records based on a related field, here's some sample data and an explanation:

We need to print shipping documents whenever our warehouse ships an order , we use the order number as our parameter field to do this. Now, we want to be able to use any box number to print the shipping documents for the whole order, so, in the example below the user will input box number 3 (or 4 or 9, etc) without needing to know the order number, and still be able to print shipping documents for the whole order. Thank you for your help.

Box number Order
3 40
4 40
5 40
6 40
9 40
12 40
15 40

 

[pgtek]

hi
what's your field that contains the box_id

do you know how to create a parameter?

cheers

pgtek

 

[IdoMillet]

Add the Order table (a second time to the same report if you are already using it in that report) and join it acording to Order_N.

Cheers,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ramc2000]

Hi pgtek, a little clarification is in order:
The two fields are in the same table, box_num and ord_num. The table is keyed on a 3 field combination that includes both box_num and ord_num plus the line number (line_num) of the order. I can create a parameter field and I know how to use the select expert, but in this case, as you can see, I can't use the box_id field directly in the select expert.

IdoMillet, I tried adding the order table a second time, CR asked me if I wanted to add it as an alias which I did... but still, if I choose a box_num, I only get that specific box number in my report but not the rest of the order. I'm connecting to Oracle 8 thru ODBC.

 

[pgtek]

hi
Do like Ido say
have the joint set to left other or revers the link and see
what happens

pgtek

 

[IdoMillet]

No need for a LEFT OUTER join.

Your record selection criterion should be on the box number in the FIRST Order table instance.

You should then drag & drop fields from the SECOND instance of that table onto the report. This would result in showing the data for all order records associated with that order.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Using Ido's approach you would link the tables by order number (NOT by box number) and then set the parameter equal to the box number of one table, and then the related orders would automatically be the only ones available in the other table. The joins should be equal.

Another approach, using just one table, would be to limit the set of orders by some other criteria, maybe date, group on order number, and then create a formula like:

if {table.boxnumuber} = {?boxno} then 1 else 0

Then go to report->edit selection formula->group and enter:

sum({table.boxnumber}, {table.orderID}) > 0

This would give you the subset of orders with the boxnumber = to your parameter.

-LB

 

[ramc2000]

Thank you all for your help... I finally went with lbass' approach and my report is working as expected.