2 values for 1 field?

[Joel Saltzman]

I have a Point of Sale system that I am trying to modify that I made. There are 3 tables

Order

LineItems

Inventory

The LineItems is a portal on the POS form. Each product has a custom barcode printed for it. The barcode simply is the product's SKU number. When the SKU is scanned into the SKU field of the portal, it retrieves the ItemName and Price from the INVENTORY table.

Is there some way to make it so that the clerk can scan either the custom barcode on the package OR the UPC barcode on the package? All UPCs can be used as primary keys in the system. I want to be able to scan either one into ONE field and not worry. UPCs and SKUs do not EVER have a possibilty of being the same...

I was thinking of something with a sku_upc field and self referencing relationship but dont know how i could do that....

ANY IDEAS??

[mr_vodka]

Try creating a new table called 'barcodes' and have it be a one to many from Inventory to Barcodes. Then in your line items table, you can reference the Barcodes table instead of directly to the Invetory table.

[Joel Saltzman]

Im not sure if I understand your idea right....

So, I would have a table with UPCs and SKUs that is linked to the inventory table and have redundant info from the duplicate SKUs?

[comment]

Assuming each item in Inventory has both a unique SKU number and a unique UPC number, you could define a calculation field (in Inventory) =

SKU & ¶ & UPC

and change your lookup relationship to match this field.

[Joel Saltzman]

Ah! that sounds like what I wanted to do. What would the relationship look like though. I am having a brain fart on this one for some reason....

[comment]

The same as now, except for the change of match field in Inventory, i.e.

LineItems::ScannedProductID = Inventory::cCombinedProductID

[Joel Saltzman]

COOL! Thanks!!!!