2 Answers

  1. Philip- Reply

    2019-11-16

    This can easily be done by creating a customization project containing a database script. This guide will be specific to InventoryItem.InventoryCD, but the idea can be applied to any fields. See the Notes sections at the end for more info.

    For InventoryCD, here are the 3 steps you need to follow.

    1. Database script in a customization project

    Navigate to the Customization Projects screen (SM204505) and create a new customization project. Go to the DB Scripts section, click on Add and choose Script.

    Copy the script matching with your database and click OK :

    SQLServer

    DROP INDEX [Inventory_InventoryCD] ON [InventoryItem]
    ALTER TABLE InventoryItem ALTER COLUMN InventoryCD nvarchar(45) NOT NULL
    CREATE UNIQUE NONCLUSTERED INDEX [Inventory_InventoryCD] ON [dbo].[InventoryItem]
    (
        [CompanyID] ASC,
        [InventoryCD] ASC
    )
    

    MySQL

    ALTER TABLE InventoryItem 
    DROP INDEX Inventory_InventoryCD;
    
    ALTER TABLE InventoryItem CHANGE InventoryCD InventoryCD NVARCHAR(45) NOT NULL;
    
    CREATE UNIQUE INDEX Inventory_InventoryCD
    ON InventoryItem (CompanyID, InventoryCD);
    

    enter image description here

    These scripts only alter the columns we need but to do so, we drop the existing index and recreate it at the end. You can run these commands manually to test them before adding them to your customization.

    2. Publish project and restart application

    We have all we need in this customization project. Publish it by going to the Publish menu and click on Publish with Cleanup. When it successfully publishes, navigate to System > Management > Process > Apply Updates screen (SM203510). We are going to Restart Application which will restart the whole website. Make sure to notify all your users to save their work before doing it! When you are ready, click on Restart Application.

    This step will ensure that the framework discards the previous database schema and loads the up-to-date schema in memory.

    3. Change segment length

    The last step will be to modify the INVENTORY segment to allow longer length on our InventoryCD. Navigate to Configuration > Common Settings > Segmented Keys > Segmented Keys screen (CS202000) and select INVENTORY as the Segmented Key ID. Apply the new length to the segment in the grid and Save. You are now ready to test !

    enter image description here

    Notes

    If you want to modify any out-of-the-box fields in Acumatica, step 3 will have to be adapted to the field.

    3a. Alternate DAC modifications

    In this steps , you would need to make sure that the DAC field type attribute matches the new columns length. As an example, a DAC string field could have been changed from

    [PXDBString(30, IsUnicode = true)]

    to

    [PXDBString(45, IsUnicode = true)]

    You can find more info on DAC fields attributes modifications at this link :

    https://help.acumatica.com/(W(11))/Wiki/ShowWiki.aspx?pageid=1911428f-d4ca-4207-9396-a744db21cdfb

  2. Phoebe- Reply

    2019-11-16

    Be aware that increasing field length may corrupt data during upgrade as database schema that is used for upgrade procedure is defined inside %AcumaticaPath%/Database/database_schema.xml For smooth upgrade don't forget to modify database_schema.xml:

    <col name="InventoryCD" type="NVarChar(30)" />
    

    to

    <col name="InventoryCD" type="NVarChar(45)" />
    

    Otherwise you may receive "string will be truncated" errors followed by data corruption.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>