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.
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.
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!
5 Replies to “Maintain Table Schema within Customization Project”
Hi! how to delete a custom field from database.
There is no build-in function to delete custom field, but you can use SQL script, like drop column.
Please be very careful with that as this is not possible to restore deleted data.
To extend field length use approach from this article:
Very useful article. Just would have one question please.
Let's say that I want to increase the size of a standard Acumatica field and I follow the approach you described in this blog post. Is the size retained after an Acumatica version upgrade?
For example, I want to increase the First Name of the Vendor Contact, from 50 to 100 (and also update the length of BAccount.AcctName).
If this update is done in Acumatica v6, and eventually I update to Acumatica 2018, would the Contact still remain as 100 characters long in the DB, or will the data be truncated?
Thanks for you assistance.