Solved

Duplicate Purchase OrderNbr

chris49
Rank
Userlevel 4
Badge

 

Hi,

We currently have 2 sets of Purchase Orders (i.e. 4 purchase orders in total) which have the same OrderNbr. 

Fortunately, this isn’t causing any issues in the database, since the 2 sets of PO’s have different types, i.e. 1 Drop-Ship and 1 regular PO, and luckily this has only happened twice in more than 2 years, but I don’t think this is normal behaviour, is it? Note that we are using MySQL as database.

We’re using the numbering sequences with auto incremental values, so technically these 4 POs should all have had different reference numbers, right?

 

Is there perhaps something we can do to enforce that this doesn’t happen? It is causing a little bit of confusion in our operations department when they see 2 POs with the same reference number.

icon

Best answer by Gabriel Michaud 7 May 2021, 15:36

View original
Everything is possible, if you try hard enough.

3 replies

Gabriel Michaud
Rank
Userlevel 7
Badge +10

@chris49 at first I thought it was because you had setup distinct numbering sequences for the Drop Ship and Regular order types, but then I realized that Acumatica doesn’t let you configure it by order type on the PO side…

A simple fix would be to add a unique index in the DB (Company,PONbr), however I don’t type MySql supports filtered index (with a WHERE clause) so you will first need to deal with the duplicates you have.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
chris49
Rank
Userlevel 4
Badge

Thanks, @Gabriel Michaud 

I was thinking of doing that but was a little concerned about what issues it would cause when such an insert would happen.

I reckon this is a balance between having the super rare duplicate order number on occasion or getting an exception when such an occurrence occurs.

Everything is possible, if you try hard enough.
Gabriel Michaud
Rank
Userlevel 7
Badge +10

@chris49 insert will fail cleanly -- error message returned by MySql will likely bubble up to the user. If this is an issue with the numbering sequences my suggest is that clicking Save a second time after the error message will succeed, and user won’t lose anything.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved