Error “Sales Transaction Entry Window Cannot Find A Unique Number”
Error “Sales Transaction Entry Window Cannot Find A Unique Number”
Being in the wholesale distribution industry I have seen this error numerous times throughout the last several years. As our order volume increases it seems that we see the error more and more often. I found that the volume of phone sales orders being manually entered combined with e-connect integrating website orders into sales order processing at the same time, our master numbers started getting out of sequential order. After a certain number of orders (this I haven’t determined as of late) GP will go back and try to fill in the missing master numbers. This is when you will get the error “sales transaction … cannot find a unique number”. The error is triggered when GP steps backwards to fill in the missing master number and then tries to advance by 1 number in the numerical sequence and the master number already exists.
There are two things needed to fix the issue First you need to navigate to the Sales Ordering Processing set up screen by clicking on Great Plains ->Tools -> Setup -> Sales -> Sales Order Processing. In this screen you will see where GP references the master number. You will have to change the displayed master number to the highest master number recognized in the DB by running a script in SQL. You can find the script on Microsofts GP forums and other websites out there so I figure the script is in the public domain so I will list it here as well.
Second, you need to run this script against your DB: select MAX(a.MSTRNUMB) from (select MSTRNUMB from SOP10100 union all select MSTRNUMB from SOP30200) a . Once you have the master number from the script insert it into your sales order processing window and you will be able to enter orders in the sales order processing window again.
A step beyond
In the last few months we started seeing this error repeatedly showing up during the most in-opportune times (a sales rush or at the end of the day during invoicing when shipping is trying to get out the last packages of the day) and myself or the other GP admin would be unavailable. To avoid losing sales or missing shipping deadlines I created a SQL view from the script and then created a simple Excel pivot table to display the highest master number in the DB. I then created a user in SQL for both the sales manager and shipping manager and granted them limited permissions in SQL with a username and password (username and password are needed to refresh the data in the pivot table). Then I placed a copy of the pivot table on each users desktop with instructions on how to clear the error. Now when the error shows up either user can easily fix the issue without the need of calling a GP admin.