Multiple Bin Inventory Report

[BryanLane]

Crystal Version 11.5.0.313

I am trying to create a Physical Inventory Count Sheet by inventory location (Bin #). Our system has five bins available for each part (BinLoc1 through BinLoc5). We only use up to BinLoc3 at this time, but may go to the 4 or 5 bin in the future.. Every part must have a BinLoc1 before it can have a BinLoc2 and a BinLoc2 before it can have a BinLoc3, etc.

My dilemma is how do I create a report that list everything for each BinLoc when it could appear in BinLoc1, BinLoc2 or BinLoc3 for any given part. There is not table of BinLocs in the system (there should be!) and nothing to prevent someone from either misspelling or creating their own on the fly, so it has to be dynamic in its generation of the list of bins.

My data:
Branch Supplier Part# BinLoc1 BinLoc2 BinLoc3 QtyonHand.
05 ABC 123 CTR STOR1 STOR4 100
05 DEF 254 STOR5 CTR 10
05 XYZ 987 STOR1 1

The table does not keep track of how many are in each bin, just the total quantity on hand.

I need to prepare count sheets by BinLoc, then Supplier and Part#. So for the part number ABC 123 above I would want it to appear on the count sheets for CTR, STOR1 and STOR4.

I have been rewarded with many valuable tips and tricks from reading through the posts and responses in these Crystal Forums.

Thanks in advance for whatever help or incite that can be provided.

Bryan

 

[lbass]

I would set this up in a command so that the bins were all in one field, like this:

select 'Bin1' as whichbin, table.`binloc1` as binloc, table.`branch`, table.`supplier`, table.`part#`, table.`qtyonhand`
from table
union all
select 'Bin2' as whichbin, table.`binloc2` as binloc, table.`branch`, table.`supplier`, table.`part#`, table.`qtyonhand`
from table
union all
select 'Bin3' as whichbin, table.`binloc3` as binloc, table.`branch`, table.`supplier`, table.`part#`, table.`qtyonhand`
from table

You can then insert a group on {command.binloc} and insert summaries on the part #'s, e.g., a distinctcount on the part#, at the binloc group level. If you need to which binloc, you can use the whichbin field in the detail section.

-LB

 

[BryanLane]

Thank you LBass. I am going to grab some lunch and then digest this.

 

[BryanLane]

LBass...Thank you again.

Worked great. I now have a new item in my bag of tricks.

I have posted two items and you have come through both times for me. I bow to the master.