Results 1 to 7 of 7
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    How to generate custom purchase order numbering in MS SQL?

    I have multiple suppliers from which I purchase items. I want to automatically generate my internal purchase order codes in Microsoft SQL Server for each supplier, like this:


    Supplier A:




    • 2023_A_001
    • 2023_A_002


    Supplier B:


    • 2023_B_001
    • 2023_B_002


    (first current year, then the supplier's unique code and then number for that supplier for that year).


    How do I accomplish this? Thank you.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Store each in a table and use DMax() to get the latest that you will increment.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Is there any way to do this on the back end side, in MS SQL?

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Not sure how that affects it, just a table after all?
    I would just create a function to obtain/set custom value.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Generating a custom unique identifier is a common topic.

    I will guess that by "MS SQL" you mean SQLServer. A stored procedure is only way I imagine could be done if at all. I don't know SPs well enough.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or could use a table level trigger.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm out of practice with sql server but thought it would be fun to see what chatgpt has to say.
    I have not reviewed this and cannot vouch for it!

    My prompt:
    Write a table trigger for microsoft sql server that for a new record will set a field's value. The field is called purchase_order_code. The field is a string data type. The field will be comprised of three parts, the first part is the current four digit year, the second part is a unique code to the supplier, the third part is the order count for that supplier for that year. The third part is also left zero padded to 3 digits. The three parts should be separated by an underscore. For example, if a new record is being entered for supplier 'ACME', and this is the 3rd purchase for this year, the trigger should look up the suppliers unique code which is 'A', and lookup the number of previous purchases this year, and finally set the purchase_order_code field to '2023_A_003'
    ChatGPT response:
    Here's an example of a table trigger in Microsoft SQL Server that sets the value of the `purchase_order_code` field based on the requirements you provided:

    Code:
    CREATE TRIGGER trg_SetPurchaseOrderCode
    ON YourTableName -- Replace YourTableName with the actual name of your table
    AFTER INSERT
    AS
    BEGIN
        -- Variables to store the parts of the purchase_order_code
        DECLARE @year CHAR(4)
        DECLARE @supplierCode CHAR(1)
        DECLARE @orderCount INT
    
        -- Get the current year
        SET @year = YEAR(GETDATE())
    
        -- Get the supplier code for the newly inserted record
        SELECT @supplierCode = supplier_code
        FROM inserted
    
        -- Calculate the order count for the supplier and year
        SET @orderCount = (
            SELECT COUNT(*)
            FROM YourTableName -- Replace YourTableName with the actual name of your table
            WHERE YEAR(order_date) = @year
            AND supplier_code = @supplierCode
        )
    
        -- Generate the purchase_order_code by concatenating the parts
        DECLARE @purchaseOrderCode VARCHAR(50)
        SET @purchaseOrderCode = CONCAT(
            @year, '_', @supplierCode, '_', RIGHT('000' + CAST(@orderCount AS VARCHAR(3)), 3)
        )
    
        -- Update the inserted record with the generated purchase_order_code
        UPDATE YourTableName -- Replace YourTableName with the actual name of your table
        SET purchase_order_code = @purchaseOrderCode
        FROM inserted
        WHERE YourTableName.PrimaryKeyColumn = inserted.PrimaryKeyColumn -- Replace PrimaryKeyColumn with the actual primary key column of your table
    END
    Make sure to replace `YourTableName` with the actual name of your table and `PrimaryKeyColumn` with the actual primary key column of your table.

    This trigger will be fired after an `INSERT` operation on the table. It calculates the current year and retrieves the supplier code from the newly inserted record. Then, it counts the number of orders for the supplier within the current year. Finally, it generates the `purchase_order_code` by concatenating the parts and updates the inserted record with the generated value.
    EDIT
    NOTE: I notice that the supplierCode above looks wrong. I assume you'll want to add a call to lookup your supplier code in a related table.

    Apologies for the oversight. If the supplier code is stored in a separate table and needs to be looked up based on the supplier foreign key field in the record, you can modify the trigger as follows:

    Code:
    CREATE TRIGGER trg_SetPurchaseOrderCode
    ON YourTableName -- Replace YourTableName with the actual name of your table
    AFTER INSERT
    AS
    BEGIN
        -- Variables to store the parts of the purchase_order_code
        DECLARE @year CHAR(4)
        DECLARE @supplierCode CHAR(1)
        DECLARE @orderCount INT
    
        -- Get the current year
        SET @year = YEAR(GETDATE())
    
        -- Get the supplier code for the newly inserted record
        SELECT @supplierCode = s.supplier_code
        FROM inserted i
        INNER JOIN SuppliersTable s ON i.supplier_foreign_key = s.supplier_id -- Replace SuppliersTable, supplier_foreign_key, and supplier_id with the actual names of your supplier table and columns
        WHERE i.PrimaryKeyColumn = inserted.PrimaryKeyColumn -- Replace PrimaryKeyColumn with the actual primary key column of your table
    
        -- Calculate the order count for the supplier and year
        SET @orderCount = (
            SELECT COUNT(*)
            FROM YourTableName -- Replace YourTableName with the actual name of your table
            WHERE YEAR(order_date) = @year
            AND supplier_foreign_key = i.supplier_foreign_key
        )
    
        -- Generate the purchase_order_code by concatenating the parts
        DECLARE @purchaseOrderCode VARCHAR(50)
        SET @purchaseOrderCode = CONCAT(
            @year, '_', @supplierCode, '_', RIGHT('000' + CAST(@orderCount AS VARCHAR(3)), 3)
        )
    
        -- Update the inserted record with the generated purchase_order_code
        UPDATE YourTableName -- Replace YourTableName with the actual name of your table
        SET purchase_order_code = @purchaseOrderCode
        FROM inserted
        WHERE YourTableName.PrimaryKeyColumn = inserted.PrimaryKeyColumn -- Replace PrimaryKeyColumn with the actual primary key column of your table
    END
    Ensure that you replace `YourTableName` with the actual name of your table, `SuppliersTable` with the actual name of your supplier table, and `supplier_foreign_key` and `supplier_id` with the corresponding foreign key and primary key columns in your tables.

    This updated trigger will retrieve the supplier code by joining the `inserted` table with the `SuppliersTable` based on the supplier foreign key. The rest of the trigger logic remains the same as in the previous example.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Purchase Order Total
    By PandoraGirl in forum Queries
    Replies: 12
    Last Post: 12-02-2022, 08:52 AM
  2. Replies: 5
    Last Post: 12-01-2015, 03:09 PM
  3. Creating a Purchase Order
    By bnecrush in forum Access
    Replies: 12
    Last Post: 11-20-2012, 06:27 PM
  4. Forms to generate Purchase Orders(PO)
    By 555Rage in forum Access
    Replies: 21
    Last Post: 09-13-2012, 05:00 AM
  5. Help With Purchase Order Form
    By SpeedyApocalypse in forum Forms
    Replies: 29
    Last Post: 04-09-2010, 07:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums