Skip to main content
Solved

Duplicate Purchase OrderNbr


chris49
Varsity II
Forum|alt.badge.img

 

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.

Best answer by Gabriel Michaud

@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.

View original

Gabriel Michaud
Captain II
Forum|alt.badge.img+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.


chris49
Varsity II
Forum|alt.badge.img
  • Varsity II
  • May 10, 2021

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.


Gabriel Michaud
Captain II
Forum|alt.badge.img+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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings