Allowing of the same Bar code as a Cross Reference for multiple Inventory codes

Yes @Jp89  

Acumatica allows you to enter the multiple bar code with different Alternate IDs.  If you provide the same “Alternate ID”, it will give you an error. Please find the screenshot for reference.

So, Bar Code Alternate ID is unique for each Inventory ID. 

 

I dont think that your are understanding the issue. Yes it prevent you form using the barcode twice on the same inventory code. But if I use barcode 1234 on Product A, I can also enter bar code 1234 on product B. This will create huge problems - we require no duplicate bar codes across all Inventory Items

Yes, your question bit confusing to the readers. Hence I was thought in another way.

If this example if you had mentioned in the question itself that will help the readers to understand the question properly to provide the inputs.

The below info may helpful …....….......….

any which ways, that is the beauty of Acumatica... technically, the combination of Alternate type and Alternate IDs are linked to the particular Inventory ID.

Barcode ==> Inventory + Alternate Type + Alternate ID  → This is uniquenbr in Acumatica.

Below links that will provide you understand of how Cross-Reference will work in Acumatica.

• https://help-2020r1.acumatica.com/Wiki/ShowWiki.aspx?pageid=07b78589-6e40-4fb0-826c-7ed4d1e08e91
• https://help-2020r1.acumatica.com/(W(42))/Help?ScreenId=ShowWiki&pageid=77786a70-1f1e-4d63-ad98-96f98e4fcb0e (check the section Cross- Reference Tab)

Thanks very much

“The system does not verify the uniqueness of alternate IDs in the system” is what I just read in the manual. So if we enter the same bar code on different Inventory ID’s - when scanning a barcode how will the system know which barcode’s Inventory ID should be used?

Yes @Jp89 

The system will not verify the uniqueness of alternate IDs in the system.

I understood and when you are dealing with only AlternateID, the system is NOT behaving as expected.

Below is what I observed that if products having the same Barcode info on the CrossRef Tab.

When we scan with AlternateID (Code123), then it is taking the 1st record in the system, which may lead to the issues.

 We may have some other option, but better if we hear this info from officials on this and confirm.

I remember receiving the same request by a customer, and we ended up adding a unique index in the INItemXref table to make sure the same cross reference couldn’t be used by multiple items at once… that’s simple to setup.

I checked in few production instances and Cross Reference Info configured asunique for each SKU.

But, NOT sure why Acumatica is allowing same cross reference info for the multiple SKUs.

Please find out how we can ensure that we achieve a unique bar code across inventory codes. This is kind of fundamental for a customer that Im busy presenting the Acumatica solution to.

@Jp89 -- here’s the index needed:

CREATE UNIQUE NONCLUSTERED INDEX Usr_INItemXRef_UniqueAlternateID ON dbo.INItemXRef
(
    CompanyID ASC,
	AlternateType ASC,
	AlternateID ASC
)
WHERE AlternateType='BAR'

It will look for duplicate cross-references of type “Barcode”. Here’s what happens if you try to insert a duplicate barcode:

I have packaged this into a customization project -- note that it will NOT publish successfully if you have duplicates already.

Thanks very much this is superb

Thanks again @Gabriel Michaud 

Such a simple, yet effective fix for something which might have costed me a few lines of code today 

And for the MySQL lovers out there, since we’re not able to make use of filtered indexes, a trigger does the trick too.

Feel free to optimise, and add in a common procedure… But this was good enough for me…

DELIMITER $$
CREATE TRIGGER `checkDuplicateBarcodeInsert`
BEFORE INSERT ON `INItemXRef` FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT 1
        FROM `INItemXRef`
        WHERE `INItemXRef`.`CompanyID` = NEW.`CompanyID`
            AND `INItemXRef`.`AlternateID` = NEW.`AlternateID`
            AND `INItemXRef`.`InventoryID` <> NEW.`InventoryID`
            AND `INItemXRef`.`AlternateType` = 'BAR'
	) 
    THEN
		SELECT CONCAT('Barcode ', NEW.`AlternateID`, ' has already been assigned to another product!') INTO @error_text;
		SIGNAL SQLSTATE '45000' SET message_text = @error_text;
    END IF;
END$$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER `checkDuplicateBarcodeUpdate`
BEFORE UPDATE ON `INItemXRef` FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT 1
        FROM `INItemXRef`
        WHERE `INItemXRef`.`CompanyID` = NEW.`CompanyID`
            AND `INItemXRef`.`AlternateID` = NEW.`AlternateID`
            AND `INItemXRef`.`InventoryID` <> NEW.`InventoryID`
            AND `INItemXRef`.`AlternateType` = 'BAR'
	) 
    THEN
		SELECT CONCAT('Barcode ', NEW.`AlternateID`, ' has already been assigned to another product!') INTO @error_text;
		SIGNAL SQLSTATE '45000' SET message_text = @error_text;
    END IF;
END$$
DELIMITER ;

I agree that this is a great workaround.  Is this something Acumatica is considering adding to the system as an option (keep unique or not) similar to serial numbers?

Also, if my client is on Acumatica SaaS how does this get applied to the database?

Thanks...great stuff as always Gabriel.

@JGabrys11 in my reply above I attached a customization project that packages the DB script… just publish it and you’re good to go!

Got it.  Thanks!

@Jp89 -- here’s the index needed:

CREATE UNIQUE NONCLUSTERED INDEX Usr_INItemXRef_UniqueAlternateID ON dbo.INItemXRef
(
    CompanyID ASC,
	AlternateType ASC,
	AlternateID ASC
)
WHERE AlternateType='BAR'

It will look for duplicate cross-references of type “Barcode”. Here’s what happens if you try to insert a duplicate barcode:

I have packaged this into a customization project -- note that it will NOT publish successfully if you have duplicates already.

will this work with 24R2, and i see that it focuses on barcode?  what if the duplicate cross-ref is spread across barcodes, GTIN and global?

@Jp89 -- here’s the index needed:

CREATE UNIQUE NONCLUSTERED INDEX Usr_INItemXRef_UniqueAlternateID ON dbo.INItemXRef
(
    CompanyID ASC,
	AlternateType ASC,
	AlternateID ASC
)
WHERE AlternateType='BAR'

It will look for duplicate cross-references of type “Barcode”. Here’s what happens if you try to insert a duplicate barcode:

I have packaged this into a customization project -- note that it will NOT publish successfully if you have duplicates already.

will this work with 24R2, and i see that it focuses on barcode?  what if the duplicate cross-ref is spread across barcodes, GTIN and global?

Good Question? Along these lines, is there anyone out there that is populating GTIN based upon a specific prefix for each UOM?