1

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 ?

Simon ML
  • 1,819
  • 2
  • 14
  • 32
  • You can try to update the database record for `INVENTORY` segmented key to set it's max length to 45. But after that you will have to modify every field in the system which contains `InventoryCD` to have at least **45** characters length. – Samvel Petrosov Feb 27 '18 at 12:57

2 Answers2

5

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

Simon ML
  • 1,819
  • 2
  • 14
  • 32
1

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.

Ilya Sh
  • 96
  • 4