Use Serial Numbers with Optional Fields

Im not sure how that would work. 

To be clear each item may have several of many specifications associated with it. different lots of the same item may have different specifications, some may over lap, and others may not.  An lot will have 36 specifications associated with it.

An example may be something along these terms:  

Item1234  Lot# ABCD has spec 1, spec 4, spec 5

Item1234 Lot# ABCF has spec 1, spec 4, spec 7, spec 10

Item1234 Lot# ABCY has spec 23

Serials, not lots.

Each order of items will be from the same LOT. All items in the LOT will have the same Specs. Items from different LOTS may have different specs. 

Im not sure how serials will help. 

The biggest problem I am trying to wrap my head around is having multiple options selectable for something like an optional field. 

My answer doesn't change.  We use it at a client that requires FDA compliance.

You can have serialized items come from the same lot.  Lot X can have serial number A, B and C.  Each serial can have it's down optional field values separate from other items from the same lot.

Optional fields can be configured to only allow you to pick from a specific list of options or they can be free form.

If you need logic that says if option A is 'Blue' then option B is irrelevant and should be be set and option C must be filled in then you should look at a macro to facilitate that business logic or look at Orchid's Extender product which would allow you (or a developer) to build that business logic into Sage.

But all items in a LOT will have the same set of specifications and certifications.  And I don't believe you can have multiple serials for the same item.

The biggest hurdle I am trying to find a solution to is to have a list of options for a particular item that we can associate for all items in that lot. From what I have seen in the optional fields (Lot or serial) is that it is one value for each.  

If we have an order that comes in that the customer says it must be certified to meet Spec 10, then I know I need to pull it from Lot# ABCF sicne Lot# ABCD and ABCY doesn't meet that specification. If the next order they want Spec 1 then I know I can pull from either lot ABCD or ABCF, but not from lot ABCDY.

Each lot could have hundreds of items in it.

All of our tracking goes back to Lot number for aerosoace compliance. We also have some product such as carbon fiber or breather film that we buy by the roll or LB and sell by yard so the lot gets traced through.

Here is an example, This PO we ordered 72of this item and had requested the specifications in the description. All of these 72 items are certified to the specifications listed (146, 221, 465, 494, 541, 553, etc)

We are looking for a way to track all of those specifications through the lifecycle so if we get an order for this item and the customer needs certification to spec 465 for example we can tell yes we have it instock and it is in this Lot.

We may order this same product from the same vendor another time without it being certified to those specs.

A serial number represents a single instance of a given item number. A particular widget that you can hold in your hand. The single widget can be distinguished by assigning it a serial number and that serial number can have optional field values associated with it. A different widget with a different serial number can have it's own optional field values associated with it.

An item number can be tracked in lots and by serial number at the same time.  Thus a lot can hold, say, fifty widgets and because you'll want to track each of those individual widgets' specifications, you'll track those via serial numbers.

If you get 40 widgets in a lot from a vendor, all 40 of the widgets in that lot can all be assigned a serial number optional field of certification 456. If you get a different shipment from the vendor with different specifications then you will put those into a different lot and each serial number that you're tracking - thus each individual widget in this new lot will all be tagged with the correct specification/certification codes.

If the customer returns the item and they have the serial number - from that you can tell what the specifications are. And you'd be able to tell what lot they came from.

How you set up the serial number optional fields is probably the part that will require the most amount of thought.

But the hurdle that I am having is there is not 1 specification for each wiget. There are a list of 30-50 that they are qualified for.  Each lot may have different certified specifications, but they will be the same in each lot.

All widgets in the lot have the same set of certified specifications, so I am not seeing a benefit of getting granular to the widget and not just the lot.

If there was one Specification for each widget or lot then its easy.... but as you can see from the sample PO I posted earlier that we have 1 lot of 72 and this lot had several specifications it was certified to. I believe (please correct me if I am wrong) that an optional field can only hold one value and not multiple values.

You're correct - one optional field can hold one value (up to 60 characters but I wouldn't get fancy trying to stuff the field).  I'd consider multiple optional fields, one per specification.  Something like:

A separate y/n optional field for each spec
SPEC435 = Yes
SPEC455 = Yes

This means lots of optional fields but is easier to query against.

or

Generic place holders
Spec001 = 435
Spec002 = 455
Which means fewer placeholders but it's a little harder to query against.