Changes in the Database Schema

The MYOB Acumatica Customization Platform permits the following changes to the database in the scope of a customization project:

Creation of Custom Tables

We recommend that you create a custom table in the database of your development environment by using a database administration tool, such as SQL Server Management Studio, and then import the table schema from the database to a customization project by using the Customization Project Editor. The project keeps the schema in XML format. While publishing the customization project, the platform executes a special procedure to create the table by the schema, while meeting all the requirements of MYOB Acumatica.

Creation of Custom Columns in Existing Tables

To create a custom database-bound field, you add a column to the database table and declare the field in the extension of the base (original) DAC.

The new column is appended to the original table by altering the table schema. When you create the database-bound field by using the Data Class page, the platform generates the DAC extension code for the new field and adds to the customization project the XML definition of the new column to be created in the database. To be able to create a UI control for the new field to display on a form, you have to publish the project to make the system create the column in the database table and compile the customization code. After the publication, you can add the control for the new field to the form by using the Screen Editor page.

Creation of Views, Indexes, and Other Database Objects

With the MYOB Acumatica Customization Platform, you can add to a customization project an SQL script to be executed during the publication of the project. However, we recommend that you avoid doing this.
Warning: A possible result of a custom SQL script is the loss of the integrity and consistency of the application data.
If you do add a custom SQL script, you must adhere to the following requirements for the script:
  • Because MYOB Acumatica supports multitenancy, you must create an SQL script that correctly creates a database object that properly specifies and uses the company mask.
  • You must correctly specify the attributes for the script so that the script can be executed on the target database servers: MySQL Server, Microsoft SQL Server, or both. For details about attributes, see Using the SQL Script Attributes.
Important: If you have created custom database tables or custom fields for a tenant and have created the full-text search index for these elements, you need to add these tables to the pp_DisableFullText and pp_EnableFullText stored procedures in your customization project. This step is necessary to avoid errors when administrators copy tenants or create and restore snapshots.