Hello everybody,
today I want to describe how at code level you can add User defined fields in Acumatica to any entity. Sequence will be this:
- In PXDataSource add attribute EnableAttributes. It may look like this:
<px:PXDataSource EnableAttributes="true" ID="ds"
2. For target entity create table with same name, but with suffix KvExt. Query for creation of such a table may look like this:
CREATE TABLE [[TargetTable]KvExt](
[CompanyID] [int] NOT NULL,
[RecordID] [uniqueidentifier] NOT NULL,
[FieldName] [varchar](50) NOT NULL,
[ValueNumeric] [decimal](28, 8) NULL,
[ValueDate] [datetime] NULL,
[ValueString] [nvarchar](256) NULL,
[ValueText] [nvarchar](max) NULL,
CONSTRAINT [[TargetTable]KvExt_PK] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC,
[RecordID] ASC,
[FieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [[TargetTable]KvExt] ADD DEFAULT ((0)) FOR [CompanyID]
GO
just replace [TargetTable] with necessary DAC class. For example I wanted to create User defined fields for entity CovidQuizKvExt:
CREATE TABLE [CovidQuizKvExt](
[CompanyID] [int] NOT NULL,
[RecordID] [uniqueidentifier] NOT NULL,
[FieldName] [varchar](50) NOT NULL,
[ValueNumeric] [decimal](28, 8) NULL,
[ValueDate] [datetime] NULL,
[ValueString] [nvarchar](256) NULL,
[ValueText] [nvarchar](max) NULL,
CONSTRAINT [CovidQuizKvExt_PK] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC,
[RecordID] ASC,
[FieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[CovidQuizKvExt] ADD DEFAULT ((0)) FOR [CompanyID]
GO
As outcome I've got on my page something like this:
Summary
As you can see, it's very easy from development standpoint to add user defined fields to any Acumatica form