Maintain Table Schema within Customization Project

Hi There,
Today I want to stop your attention on how Acumatica handles updates for custom tables/columns.

In Case you doing medium to complex customization to Acumatica, you most probably will have a lot of custom columns.
You know you can add all these custom columns to the standard tables in Acumatica, but in this case you several issues

• Each new column has to have an Usr prefix to each column. That is required to split standard and custom columns in one table
• You columns depend on Acumatica table. In case Acumatica have any changes in the tables, you columns may also be affected. But do not worry, they are never be deleted.

So in case of multiple custom columns it might be good idea to split them in custom table. Using Acumatica framework you easily can create as many custom tables as you wish.
Also even if you need to link new columns to original records, you always can use Extension Tables, that actually give you ability to have benefits of custom columns and new table.

Ok, now we have a new table, but how to support it in case we need to add a new column there?

Adding Table to Customization Project
First of all what we need to do – add custom table to Acumatica Customization Project

During that operation Acumatica will read table definition form SQL server and generate XML schema for that.

That schema is version and platform independent – so Acumatica can restore that table on both – SQL Server and My SQL.

Changing of Columns
Now you can do any changes, and as my personal experience the best and easiest way to do changes is actually directly use MS Sql Server Management Studio. You can use SQL to add/change columns or directly use table designer, the main thing is just to have a new table definition in DB.

If you are finished with changes and ready to commit, you can just go to Customization Browser and update table definition form database.

Please note that in that case table schema will be updated in customization project.
Also note that for performance reasons Acumatica caches table schema in memory and refresh is only once in a while. So that means that sometimes you need to restart site to load latest changes from DB.

Publication Process
During customization publication Acumatica will automatically adjust table columns. So

• If the table does not have column defined in project, it will be added
• If there is a column in DB, that does not exists in schema, it will be deleted
• If there is column with ‘USR’ prefix, that it will be used as custom column and will not be deleted from table in any case.
• Acumatica, will rearrange columns sequence to match table definition

So that means that you do not need to cary about table schema at all.

Custom Scripts
In case you want to have a data manipulation script (like “UPDATE table SET … WHERE …”), that you can have a custom SQL scripts.

In that case Acumatica executes update accordingly to following flow:

• Add new columns in all tables defined in customization project
• Run custom scripts to manipulated with data
• Recreate all tables to delete removed columns and update columns sequence.

So when you writing that script you should not worry about new columns, they will be automatically added on previous step and your custom scripts will be executed always in the middle.

Also great news – it will work fine with SQL and MySQL, because Acumatica actually parse your SQL before executing it and does some optimization/transformation.

Have a nice customization!