Serial Numbers and Lot Numbers in Inventory

[onestopbusiness]

I have a client who needs an inventory tracking and CRM system. The CRM part is a no brainer, however his inventory is a bit of an issue.

He sells cards that all have individual serial numbers on them. The cards come in blocks of 250 in the boxes with the beginning and ending serial number printed on the outside of the box.

Typically, the process is this, a client will call in and order say 1000 cards. They create the invoice, charge the client's credit card and send the "pick ticket" to the back of the house for fulfillment. Problem is the back of the house will grab 5 random boxes to fill the order.

My thought was to have them scan the beginning barcode and the ending barcode of each box for that order. My client wants to be able to look up who purchased what by looking up the serial number in the database. My question is...what's the best way to assign all of the serial numbers to a particular customer when I have only the first and ending serial number from each box? A calculation that counts up to 250 for each ending scan and then creates an individual record in that customer's order table, etc..etc? There has to be a cleaner and more efficient way to accomplish this.

Edited January 5, 2010 by Guest

[Peter (duksis3)]

Hi,

You say - "My client wants to be able to look up who purchased what by looking up the serial number in the database. "

Imagine - you have start and end card serial number.

Make a search field where to type in serial number. This field is related to start and end serial numbers.

Returned result must show customer, start and end serial numbers.

Here is a quick sample

Serials.fp7.zip

[onestopbusiness]

I appreciate the speedy reply and the very nice example file. The problem that I am having is A) How to assign the cards to a particular client when the order requires more than one box. ie. client orders 1000 so 4 four boxes must be scanned one after the other to complete the order. Do I have my system create a new record in my orders table for that client after each scan?

And then B.) what to do when a client orders say 1001 cards.

Now I have a leftover box with 249 cards in it and the barcodes on the outside of the box become worthless.

I should have specified that the cards won't always be sold in lots of 250...

That's just how they arrive from the printer.

Edited January 5, 2010 by Guest

[Peter (duksis3)]

That what you saying isn't acceptable in work. If there is 1000 cards, serials must be in order - think about customer too. Nobody are interested to buy "something somewhere". Not good impression.

But if that happen or customer order - make a record duplicates for each bulk serials by 250.

249 or 251 etc isn't acceptable in general - different packaging and so on. Every such case is "extra" so extra means must be used - practically the whole stock must be re-packed.

To avoid this - make "spare serials" or a list of Free serials to prepare the order.

This is not easy question and stock must be managed by accurate person.

[comment]

Are you able to write down the first number and the amount of cards (or the last number) in each order (or in each ordered series, if there are several)?

[onestopbusiness]

The way their process works is that a salesman sells the cards and the back of the house is responsible for filling the order and shipping it.

So let's say a client calls in and orders 275 cards. The fulfillment people run over and pick up a box of 250, they scan in the the two barcodes on the box...no problem. Now they have to run over and open up another box and take out 25 to complete the order. They can type in the first serial and the last serial of those 25 into the system. Now the client ends up with two different sets of serial numbered cards. Say #1-250 and another set #750-775. Now at some point in the future my client needs to be able to go back into the system and see who was sold card number 231 for instance. I'm having difficulty in figuring out how I'm going to create a complete order but with a range of different numbers from different boxes and track it effectively.

[onestopbusiness]

I should probably explain it a little better. My client buys these cards by the hundreds of thousands and then resells them. He actually "repacks" each order for the customer. So if a customer calls in and orders 241, his fulfillment people pull 241 cards out of the "bulk" boxes and repack them for the client to be shipped.

[comment]

Now the client ends up with two different sets of serial numbered cards. Say #1-250 and another set #750-775. Now at some point in the future my client needs to be able to go back into the system and see who was sold card number 231 for instance.

They can see this instantly through a relationship, or they can perform a find.

[onestopbusiness]

Agreed, the Search isn't the part that I'm stumbling over. It's how to enter in those different sets of cards and assigning them to the client. Should I create a new record for each set for the client or is there a way to store each set in an array..etc..etc.

[comment]

I believe it would be best to have an OrderItems (or perhaps ShipmentItems ?) table, with fields for:

ItemID (serial number)

OrderID

StartNumber

EndNumber

[onestopbusiness]

could you be a little more specific? Sorry I'm just not catching how the table/field structure would resolve my problem.

[comment]

When the order is being fulfilled, the people doing this should create a new record for each series shipped. This record should be a child of Orders, so probably the best thing for them to do would be to just punch the start and end numbers into a portal on a layout of Orders.

[onestopbusiness]

hmmm..I had originally envisioned a screen where they pulled up "pending orders" and clicked on a client who's order needed to be filled. At that point it would show how many cards had been purchased. They would grab boxes and scan the first barcode and the last barcode number on each box...then a "handful of cards" to finish up the last amount needed and scan in the first and last of that "handful". Meanwhile in the window, a counter would show how many more was needed to complete the order.

Does that make sense?

[comment]

Yes, as long as they create a new record for each series.

[onestopbusiness]

ok, so that kind of leads me back to my way of original thinking and what I put in my posts...for every "set" of cards...I should create a new record in the "orders" table for that client? My only concern with that method is that with my client literally selling hundreds of thousands of these cards...how long before my Db becomes too bloated to manage?

Edited January 6, 2010 by Guest

[comment]

for every "set" of cards...I should create a new record in the "orders" table for that client?

No, these records need to be created in an OrderItems table - a child of the Orders table. To use your earlier example:

A client calls in and orders 275 cards.The fulfillment people run over and pick up a box of 250, they scan in the two barcodes on the box, say 1 and 250 into the first portal row.

Now they run over and open up another box and take out 25 to complete the order. They type in the first serial and the last serial of those 25 into the NEXT ROW of the portal - so you end up with two records in the OrderItems table.

[onestopbusiness]

ok, don't want to sound too stupid..but how do you "scan into a portal" never tried that before...

[comment]

You scan into portal the same way you type into it. We're now getting into user interface and workflow, and it's very difficult to advise on such things without seeing the entire picture. Perhaps you should offer them a script that waits for input and does the rest automatically?

[Peter (duksis3)]

14 years ago Mr. Jobs said - "What is wrong with education cannot be fixed with technology".

Looking back to my experience in printing industry, direct mailing and production workflow organization and this case I say - "what is wrong with process cannot be fixed with technology".

Think again - why you do all this? How the task is managed today? What is OK an what must be changed? What is the easiest and best way to make changes? What I need from technology?

May be cheaper is initially make light packing by 25 or 50 cards and make complete packaging before shipment.

May be cheaper is print serials directly before order packing and shipment.

May be...

The company is producer and seller or reseller...

There is at least 10th of variants. ROI and TCO calculation is important.

Go to production and warehouse and try process steps by yourself - this can help more than hundreds of books.

And then open FMP and make the best application.

I'm not in this process so I can't say what is the best.

But as I have mentioned - for packing, repacking, different order sizes, information recording etc. - accurate personal is essential. This is the "hardest" point. An then only FMP.

Look again on CRM solution - in practice about 75% of solutions - very nice from IT designer point of view and nice from customer/company view sometimes create a lot of useless "client cards".

I write all this just to point out that sometimes is better to laid back and think again. :)

[onestopbusiness]

well I offered the interface to illustrate my ideas regarding the processes. I've written a FMP solution for a client that uses the Amazon ECS based on the ISBN or Barcode scan and parses the returning XML. It's pretty straightforward in that you scan a book and Amazon gives you all it's data related to the ISBN. No brainer...this one however has me a little befuddled.

In that system, my customer pushes a button, a window pops up with the scan/isbn barcode field waiting to accept input. They scan the book, window closes and of course the amazon scriptruns...tada! Done.

I'm thinking of using a similar technique for this card barcode inventory problem.

Fulfillment personnel see a pending order on the screen. Maybe flashing little red button, (idk, the details aren't important) they push the button and up pops a window with TWO fields..Beginning serial and Ending serial. They scan the first barcode, system tabs over to the ending field and they scan the second barcode. At this point...the system counts the difference say 100-150...and applies 50 cards to the total needed..now the screen says 200 needed, etc..etc.. and they continue on in that fashion until the count needed reaches 0 and the order is complete.

Now my only question is..the process of storing those numbers and assigning them to the client. Should I have the system do a count and make a new record for each serial number? Is there a way to simply store the ranges that were entered for that client and still be able to find an individual serial, etc?

I wasn't really looking to delve into philosophical discussions regarding workflow and processes. I started off in printing 18 years ago and I can tell you there are limitations that one must work around. Such as, serialized overages...in an order of say 100,000 cards or printed booklets you'll have 3-5% overages on average. which makes it impossible to reverse feed serial numbers into an imprint machine which would allow you to reverse stack a palette so that everything was in perfect order, etc...I'll not delve too far into the various logistical aspects of it...needless to say...the printer delivers the products the way they can.

My task is to deliver a solution that will allow them to assign those numbers as they are pulled off the palettes to specific customers so that customer service and the sales people can look up that number later and know who purchased it.

So....how best to input those numbers and assign them. I have only ranges to work with. My thought was to have FMP look at the first and last number that is scanned and do a calculation:

First number scanned

100000250 - new record

100000251 - new record

100000252 - new record

100000253 - new record

100000254 - new record

100000255 - new record

Last number scanned

This seems a rather inelegant solution and with a million cards would bloat the system rather quickly.

Edited January 6, 2010 by Guest

[comment]

I have the feeling we are not making much progress here. There's no need (that I can see) to create a record for each number. There's no need even to calculate each number in a range. All that's needed is to create a record for each range, recording the first and the last number in the range.

[onestopbusiness]

well that would certainly be more efficient...my only question becomes how to find a particular serial number for a client when only ranges are stored?

[comment]

Enter Find Mode [ ]

Set Field [ StartNumber; "≤" & gSearchNumber ]

Set Field [ EndNumber; "≥" & gSearchNumber ]

Perform Find [ ]

Alternatively, you could define a relationship using the same predicates - this would display the exact range record (or its parent order) as soon as you enter the search number.