Search code examples
acumaticaacumatica-kb

How to increase the length of out-of-the-box fields?

Currently, we are using the full 30 characters of the InventoryItem.InventoryCD field, but we need more. How can we increase this field length to 45 characters ?

Solution

  • 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