Unique IDs with matching prefixes

[adrsub]

We use our database to track purchase orders that come into our office and are then sent to vendors. The "Product Catalog" table uses a UPC as the primary key which is linked to the "Purchase Order" table. Users create a PO and enter the UPC which then populates item description, price, etc. They then enter how much of each item is ordered. Majority of our products have unique UPCs which work perfectly as the field is set to require a unique value. However, there are a few instances where we sell a branded item to multiple customers, meaning I need to somehow differentiate the UPC between each customer. My solution for this was simply to add three letters to the end of the UPC to identify which customer this product needs to be linked to. Unfortunately when I try to achieve this I'm told that the field needs to be unique. If I turn off the "require unique value" setting, the sales between all customers reflect on any customer that sells this product. Any ideas on how to resolve this?

[comment]

1 hour ago, adrsub said:

My solution for this was simply to add three letters to the end of the UPC to identify which customer this product needs to be linked to. Unfortunately when I try to achieve this I'm told that the field needs to be unique.

Is it possible that the field is defined as a Number field?

[adrsub]

It was, but I changed to a text field.

[comment]

12 minutes ago, adrsub said:

I changed to a text field.

That should solve the problem. If not, try reindexing the field.