selecting only one item

[sorchard2000]

I have a Crystal 8.5 report that lists items in the detail band. Some items have more than 1 inventory location. I have tried to use the select expert to select either inventory location 1 OR 4. Instead, it selects both. I need it to choose only one of the inventory locations. Does anyone have any suggestions on how to make the report choose only one inventory location?

item_id Qty Desc InvLoc. CDM
4007 1 pack 1 4335200
4008 1 knife 1 4335858
4008 1 knife 4 4335858

Thanks,
sorchard2000

 

[hilfy]

Assuming you're keying off the Item_ID field, you could add a group on that field and move your data out of the detail into the group header (if you just want location 1 when there's more than one location) or group footer (if you just want location 4 when there's more than one location). Using the Item_ID as a group will automatically get you either just the first or just the last record for that item.

-Dell

 

[Turkbear]

Hi,
How are you trying to get it to select one or the other?

Try creating a parameter and use that to base your selection criteria on..

 

[synapsevampire]

Rather than using the group header/footer, use the following:

Group by Item_ID

In the Report->Edit Selection Formula->Group place:

{table.invloc}= maximum({table.ivnloc},{table.item_id})

This will pull the higher numbereed location only (or you can use a minimum).

If you're pulling from inventory, then you might use a FIFO to grab the oldest inventory by using the date instead, as there's probably a date field for the date the items were placed:

{table.date}= minimum({table.date},{table.item_id})

-k

 

[sorchard2000]

hilfy:
Thanks so much for your help. Your solution DID eliminate the problem, but now a new problem has emerged: For every one of those items, there was a cost associated with it (which I probably should have told you in the first place...). When I put these items in the header, it does select the first item out of 2 inventory locations, but the Total Cost still references both inventory locations.

Originally:
item_id Qty Desc InvLoc. CDM price
4007 1 pack 1 4335200 $5.00
4008 1 knife 1 4335858 $2.00
4008 1 knife 4 4335858 $2.00
Total cost: $9.00

Currently:
item_id Qty Desc InvLoc. CDM price
4007 1 pack 1 4335200 $5.00
4008 1 knife 1 4335858 $2.00
Total cost: $9.00

Any other thoughts?

Thanks!
sorchard2000

 

[synapsevampire]

That's why I suggested eliminating them from the report.

Read my post, it solves your problem and the report should be faster as well.

-k

 

[sorchard2000]

synapsevampire:
I must be doing something wrong--I did exactly as you suggested, and it did great for eliminating the second inventory location (thanks SO much for your help!) but my costs are still off. It still sees the cost from that second inventory location. Any suggestions?

Sandy

 

[synapsevampire]

Hmmm, that's right, I guess the grouping isn't being done on the server...

You'll have to code around it I guess.

Use the 3 formula method:

Group header:
whileprintingrecords;
numbervar TotCost:=0

Details:
numbervar TotCost;
if {table.invloc} = maximum({table.ivnloc},{table.item_id}) then
TotCost:=TotCost+{table.cost}

Group Footer:
whileprintingrecords;
numbervar TotCost

-k

 

[lbass]

You could also use the running total editor. Choose {table.cost}, sum, evaluate for each record, reset never (for a grand total). Place the running total in the report footer.

If you want a subtotal at the group level, reset based on the group and place the running total in the group footer. This will give you the same result as SV's solution--it's just a little simpler.

-LB

 

[sorchard2000]

Thanks to all of you for your help. I ended up using synapsevampire's suggestion. thanks SO much!