Acumatica定制中创建用户自定义表的方法及VS操作咨询

Hey there! I totally get where you're coming from—switching from Management Studio to Visual Studio for Acumatica table creation can feel a bit jarring at first, but let’s break down exactly how to do this the right way.

The error you’re seeing (This backend version is not supported to design diagrams or tables) happens because Acumatica’s database relies on a specialized metadata layer that SSMS doesn’t understand. Directly creating tables in SSMS bypasses this layer, which breaks Acumatica’s core functionality. Instead, we use Data Access Classes (DACs) in Visual Studio to define tables—this ensures everything syncs with Acumatica’s framework.

Prerequisites #

• Make sure you have Visual Studio 2019 or later with the Acumatica ERP Development Tools installed (this includes all the templates and integration you need).
• You should have an existing Acumatica ERP project connected to your database, or be ready to create one.

1. Set Up Your Acumatica Project (If You Don’t Have One) #

• Open Visual Studio, click Create a new project, and search for Acumatica ERP Project.
• Follow the wizard to connect to your Acumatica instance—you’ll need your database credentials and instance URL here.

2. Add a New DAC (Data Access Class) #

DACs are Acumatica’s way of defining database tables and their relationships. This is the official method for table creation:

• Right-click your project in Solution Explorer > Add > New Item.
• In the template window, search for Acumatica DAC and select it.
• Name your DAC (this will be the name of your database table—stick to Acumatica’s naming convention, like PX.Objects.[YourModule].[YourTableName] for consistency).
• Click Add—you’ll get a boilerplate DAC class with all the required base code.

3. Define Your Table Structure #

Edit the generated DAC class to add your columns, using Acumatica’s attributes to set data types, keys, and UI behavior. Here’s an example:

using PX.Data;

namespace PX.Objects.YourCustomModule
{
    [Serializable]
    [PXCacheName("Customer Feedback")]
    public class CustomerFeedback : IBqlTable
    {
        #region FeedbackID
        [PXDBIdentity(IsKey = true)]
        [PXUIField(DisplayName = "Feedback ID")]
        public virtual int? FeedbackID { get; set; }
        public abstract class feedbackID : PX.Data.BQL.BqlInt.Field<feedbackID> { }
        #endregion

        #region CustomerID
        [PXDBInt]
        [PXUIField(DisplayName = "Customer ID")]
        [PXSelector(typeof(Customer.bAccountID), SubstituteKey = typeof(Customer.acctCD))]
        public virtual int? CustomerID { get; set; }
        public abstract class customerID : PX.Data.BQL.BqlInt.Field<customerID> { }
        #endregion

        #region FeedbackText
        [PXDBString(1000, IsUnicode = true)]
        [PXUIField(DisplayName = "Feedback Text", Required = true)]
        public virtual string FeedbackText { get; set; }
        public abstract class feedbackText : PX.Data.BQL.BqlString.Field<feedbackText> { }
        #endregion

        #region CreatedDate
        [PXDBDate()]
        [PXUIField(DisplayName = "Created Date", Enabled = false)]
        [PXDefault(typeof(AccessInfo.businessDate))]
        public virtual DateTime? CreatedDate { get; set; }
        public abstract class createdDate : PX.Data.BQL.BqlDateTime.Field<createdDate> { }
        #endregion
    }
}

• Each property with a PXDB* attribute maps to a database column (e.g., PXDBString for a varchar column, PXDBInt for an integer).
• The IBqlTable interface marks this class as a valid DAC, and [Serializable] is required for Acumatica’s caching system.
• Attributes like PXSelector add UI functionality, like dropdowns linked to other tables.

4. Publish Your Changes to Create the Table #

Once your DAC is ready, deploy it to your Acumatica instance:

• Right-click your project in Solution Explorer > Publish.
• Select your Acumatica instance from the list, then follow the wizard to deploy the changes.
• Acumatica will automatically create the corresponding database table (and all associated metadata) during the publish process.

5. Verify the Table (Optional) #

After publishing, you can head back to SSMS to confirm the table was created—look for a table named CustomerFeedback (or whatever you named your DAC) in your Acumatica database.

Why This Works #

Acumatica doesn’t just use SQL Server as a data store—it uses a metadata layer to tie tables to the ERP’s UI, business rules, and security. By using DACs in Visual Studio, you’re working within this framework, ensuring your table integrates seamlessly with the rest of the system.

内容的提问来源于stack exchange，提问作者T Hoecherl