SOP Master Numbers not being assigned properly

A unique document number could not be found. please check setup.

I been with a situation where my customer were getting the above messages, noticed that the system is updating the master number in SOP40100 to a smaller number which sometimes being exist in SOP10100 or SOP30200.

I workaround this by creating a trigger on SOP10100 and SOP30200 to update the SOP40100 once saving or updating transactions and set the next master number to be current maximum master number plus 1!

Below the scripts I used:

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

Enjoy!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com