Fixing Duplicate Invoice Numbers in Sage 200 and Sage 100 Evolution

If you’ve worked with Sage 200 Evolution or Sage 100 Evolution since version 10, you may have encountered a rather annoying bug: duplicate invoice numbering. Even when you’ve set your database to use manual invoice numbering and checked the option to disallow duplicates, the system can still sneakily allow duplicate invoice numbers.

Here's a screenshot of the configuration option where the bug seems to take effect:

Frustrating, right? Even with subsequent updates, this issue persists. Over time, I realized I couldn’t keep waiting for Sage to fix it—I had to take matters into my own hands for the sake of my clients.

So, I came up with a solution: a SQL trigger that ensures no duplicate invoice numbers are allowed when saving or posting an invoice. Let me walk you through the process.

How the Fix Works

The bug exists because the system doesn’t effectively validate invoice numbers when saving a document to the database. To counter this, I created a trigger on the INVNUM table (this is where Sage Evolution stores inventory-related documents, such as invoices).

Here’s what the trigger does in simple terms:

1. Focus on Incomplete Documents: The trigger works specifically for documents with DocType = 0. This indicates an unsaved or "fresh" document.
2. Check for Duplicates: When a new document is saved or updated, the trigger compares the InvNumber of the incoming row with existing records in the table.
3. Exclude Current Row: To ensure the system doesn’t falsely flag the row being processed, it excludes the current row from the check using the primary key (AutoIndex in this case).
4. Raise an Error if a Duplicate Exists: If duplicates are found, the trigger raises an error, prevents the document from saving, and rolls back the transaction.

The SQL Code

    
    -- =============================================
    -- Author:      Akwasi Adu-Kyeremeh
    -- Create date: 27 August 2024
    -- Modified date: 28 August 2024
    -- Description: Check for existing Invoice Numbers to avoid duplicates when processing invoice where manual invoice numbering is enabled
    -- =============================================
    
    CREATE TRIGGER trg_CheckInvNumber
    ON INVNUM
    AFTER INSERT, UPDATE
    AS
    BEGIN
        DECLARE @ConflictCount INT;
    
        -- Check for duplicates only when DocType is 0, and exclude the inserted/updated rows themselves
        SELECT @ConflictCount = COUNT(*)
        FROM inserted i
        JOIN INVNUM n 
            ON i.InvNumber = n.InvNumber 
            AND i.DocType = 0 -- Ensure we are only checking for DocType 0
            AND n.DocType = 0 -- Ensure the comparison is only with DocType 0 rows
            AND n.AutoIndex != i.AutoIndex;  -- Replace with your primary key column to exclude the current row
    
        IF @ConflictCount > 0
        BEGIN
            RAISERROR ('Duplicate Invoice Number detected. Please check!', 16, 1);
            ROLLBACK TRANSACTION;
        END
    END
    GO
    
            

Why This Works

By introducing this trigger, we ensure that the system performs the necessary validation at the database level. Even if Sage’s application logic fails to catch duplicates, this safety net ensures your records stay clean and consistent.

Closing Thoughts

This workaround isn’t perfect—it’s more of a patch than a permanent fix. Ideally, Sage should address this bug in future releases. But until they do, this solution has proven effective for my clients, and it might help you too!

If you have any questions or want to share how you’ve tackled similar bugs, feel free to drop a comment below.