Inventory Sheets

[BryanLane]

Our system has five bin location fields for recording where an item may be stocked in each store. At this time we only use up to three of these, but that could change at any time to go up to four or five. They are BINLOC1, BINLOC2, BINLOC3, BINLOC4 and BINLOC5. I need to be able to create count sheets page-broken by Bin Location. All they will have on the count sheet is the Supplier, Part#, BinLocation and Quantity Type (each, box, pounds, etc) and a place for the employee to enter the count.

So for example, Part#1 could be BINLOC1 = STORE1A indicating it is on shelf A1, BINLOC2 = WARE3A indicating that the extra stock for the store is in the back on shelf 3A and BINLOC3 = COUNTER indicating that there is a display box at the service counter to attract POS attention.

Part#2 could be just BINLOC1 = STOREB7

Part#3 could have no bin location (usually large items like welders that are sitting on the floor).

I would need to have Part#1 show up on three different count sheets, part#2 to show up on just STOREB7 and Part#3 to be listed on the page for items with no bin location.

We can not have one of the higher numbered Bin Locations populated if any of the lower ones are null. For example, there can not be a null in BINLOC1 and a value in BINLOC2.

I am using Crystal version 11.5.8.826


How do I do it?

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[briangriffin]

You need to show how the inventory data is stored in your database - tables, field names, etc. Some sample data would be good.

 

[BryanLane]

The fields I am interested in for this report are invidx.sup [Supplier], invidx.part [Part Number] invidx.loc [store code] invidx.cyccnt [Cycle Count code] invidx.unitme [Unit of Measure] invidx.binloc1 invidx.binloc2 invidx.binloc3 invidx.binloc4 invidx.binloc5


invidx.sup ABC DEF XYZ
invidx.part 123456 2345678 WELDER
invidx.loc 03 03 03
invidx.cyccnt 111 111 111
invidx.unitme EACH BOX
invidx.binloc1 STORE1A WARE3B
invidx.binloc2 WARE3B
invidx.binloc3 COUNTER
invidx.binloc4
invidx.binloc5

So the report should look like
Bin Sup Part# Unit of Measure Count
[nullvalue] XYZ WELDER each ______
...
...
...
{page break}

Counter ABC 123456 each ______
...
...
...
{page break}

Store1a ABC 123456 each ______
...
...
...
{page break}

Ware3B ABC 123456 each ______
Ware3B DEF 2345678 box ______
...
...
...
{page break}

My problem lies in associating the BinLoc1 through BinLoc5 with the Sup and Part to that I end up with multiple lines if Binloc2 through BinLoc5 are populated.


"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[IdoMillet]

Use a UNION Query to bring the 5 location into a single data set with Part#, Location#, Quantity.

The rest is trivial.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[BryanLane]

Thank you very much. I knew there had to be a way.

Had to play with it a bit once I got the command built to be able to eliminate the blank records if they came from BinLoc 2 through BinLoc5.

But I got it working.

Thanks for the help!

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein