Hello everybody,
Today I want to describe PXDimensionSelector attribute usage in Acumatica. According to manual PXDimensionSelector attribute has following purpose:
Defines an input control that combines the functionality of the PXDimenstion attribute and the PXSelector attribute. A user can view the data set defined by the attribute and select a data record from this data set to assign its segmented key value to the field or to replace it with the surrogate key.
After reading such purpose, I've decided to read purpose of PXDimension attribute:
Defines an input control that formats the input as a segmented key value and displays the list of allowed values for each key segment.
If those two descriptionss made you understand their pupose and way of usage, then congratulations, but for me it took muuuuuuuch longer.
Business case ( some details intentionally changed )
I was asked to create table, which has some information about Accounts and their belonging to branches ( later I'll provide sql for table creation as well as filling of created table it with some data ). Then page Sales orders has column Branch, Account, SubAccount should filter list of Accounts and SubAccounts as you can see on the screenshot:
requirement is the following: values at column Account should be filtered by what is selected in the Column Branch by means of showing only those Accounts, which has the same branch in table CompanyAccount.
Preparation
In order to give you a better idea how data was looking like, I've created some sample data with DAC class. Also in order to have some data to play around, I've installed Acumatica with SalesDemo data in order to avoid headache of configuring all from scratch.
Below goes SQL for table creation and filling with some data.
Table creation sql:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyAccount](
[CompanyID] [int] NOT NULL,
[CmpBranchID] [int] NOT NULL,
[AccountID] [int] NOT NULL,
[Description] [nvarchar](60) NULL,
[tstamp] [timestamp] NOT NULL,
[CreatedByID] [uniqueidentifier] NOT NULL,
[CreatedByScreenID] [char](8) NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[LastModifiedByID] [uniqueidentifier] NOT NULL,
[LastModifiedByScreenID] [char](8) NOT NULL,
[LastModifiedDateTime] [datetime] NOT NULL,
[NoteID] [uniqueidentifier] NULL,
CONSTRAINT [PK_CompanyAccount] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC,
[CmpBranchID] ASC,
[AccountID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CompanyAccount] ADD CONSTRAINT [DF_CompanyAccount_CompanyID] DEFAULT ((0)) FOR [CompanyID]
GO
ALTER TABLE [dbo].[CompanyAccount] ADD CONSTRAINT [DF_Table_1_CNBranchID] DEFAULT ((0)) FOR [CmpBranchID]
GO
Filling with data of CompanyAccount table:
INSERT INTO [dbo].[CompanyAccount] ([CompanyID],[CmpBranchID],[AccountID],[Description],[CreatedByID],[CreatedByScreenID],[CreatedDateTime]
,[LastModifiedByID],[LastModifiedByScreenID],[LastModifiedDateTime],[NoteID])
VALUES (
2, 16,1195, 'Discount Taken', 'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550',
'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550', NULL)
INSERT INTO [dbo].[CompanyAccount] ([CompanyID],[CmpBranchID],[AccountID],[Description],[CreatedByID],[CreatedByScreenID],[CreatedDateTime]
,[LastModifiedByID],[LastModifiedByScreenID],[LastModifiedDateTime],[NoteID])
VALUES
(
2,16,1223,'Office Expense', 'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550',
'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550', NULL
)
INSERT INTO [dbo].[CompanyAccount] ([CompanyID],[CmpBranchID],[AccountID],[Description],[CreatedByID],[CreatedByScreenID],[CreatedDateTime]
,[LastModifiedByID],[LastModifiedByScreenID],[LastModifiedDateTime],[NoteID])
VALUES
(
2,16,1224,'Postage and Delivery', 'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550',
'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550', NULL
)
INSERT INTO [dbo].[CompanyAccount] ([CompanyID],[CmpBranchID],[AccountID],[Description],[CreatedByID],[CreatedByScreenID],[CreatedDateTime]
,[LastModifiedByID],[LastModifiedByScreenID],[LastModifiedDateTime],[NoteID])
VALUES
(
2,21,1166,'Computer & Office Equipment', 'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550',
'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550', NULL
)
INSERT INTO [dbo].[CompanyAccount] ([CompanyID],[CmpBranchID],[AccountID],[Description],[CreatedByID],[CreatedByScreenID],[CreatedDateTime]
,[LastModifiedByID],[LastModifiedByScreenID],[LastModifiedDateTime],[NoteID])
VALUES
(
2,21,1167,'Machinery & Equipment', 'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550',
'B5344897-037E-4D58-B5C3-1BDFD0F47BF9', 'AR301000', '2018-12-13 00:52:31.550', NULL
)
As you can see from the sql, Branch with id 16
DAC class for CompanyTable:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PX.Data;
using PX.Objects.GL;
namespace AcumaticaSamples
{
[Serializable]
public class CompanyAccount : IBqlTable
{
#region NoteID
public abstract class noteID : PX.Data.IBqlField
{
}
[PXNote]
public virtual Guid? NoteID { get; set; }
#endregion
#region BranchID
public abstract class cmpBranchID : IBqlField
{
}
[PXDBInt(IsKey = true)]
[PXDefault]
[PXUIField(DisplayName = "Branch ID")]
public virtual int? CmpBranchID { get; set; }
#endregion
#region AccountID
public abstract class accountID : IBqlField
{
}
[PXDBInt(IsKey = true)]
[PXDefault]
[PXUIField(DisplayName = "Account", IsReadOnly = true)]
[PXSelector(typeof(Search<Account.accountID>), SubstituteKey = typeof(Account.accountCD))]
public virtual int? AccountID { get; set; }
#endregion
#region Description
public abstract class description : IBqlField
{
}
[PXDBString(60)]
[PXUIField(DisplayName = "Description")]
public virtual string Description { get; set; }
#endregion
#region CreatedByID
public abstract class createdByID : IBqlField
{
}
protected Guid? _CreatedByID;
[PXDBCreatedByID()]
public virtual Guid? CreatedByID
{
get
{
return this._CreatedByID;
}
set
{
this._CreatedByID = value;
}
}
#endregion
#region CreatedByScreenID
public abstract class createdByScreenID : PX.Data.IBqlField
{
}
protected String _CreatedByScreenID;
[PXDBCreatedByScreenID()]
public virtual String CreatedByScreenID
{
get
{
return this._CreatedByScreenID;
}
set
{
this._CreatedByScreenID = value;
}
}
#endregion
#region CreatedDateTime
public abstract class createdDateTime : IBqlField
{
}
protected DateTime? _CreatedDateTime;
[PXDBCreatedDateTime()]
public virtual DateTime? CreatedDateTime
{
get
{
return this._CreatedDateTime;
}
set
{
this._CreatedDateTime = value;
}
}
#endregion
#region LastModifiedByID
public abstract class lastModifiedByID : IBqlField
{
}
protected Guid? _LastModifiedByID;
[PXDBLastModifiedByID()]
public virtual Guid? LastModifiedByID
{
get
{
return this._LastModifiedByID;
}
set
{
this._LastModifiedByID = value;
}
}
#endregion
#region LastModifiedByScreenID
public abstract class lastModifiedByScreenID : PX.Data.IBqlField
{
}
protected String _LastModifiedByScreenID;
[PXDBLastModifiedByScreenID()]
public virtual String LastModifiedByScreenID
{
get
{
return this._LastModifiedByScreenID;
}
set
{
this._LastModifiedByScreenID = value;
}
}
#endregion
#region LastModifiedDateTime
public abstract class lastModifiedDateTime : IBqlField
{
}
protected DateTime? _LastModifiedDateTime;
[PXDBLastModifiedDateTime()]
public virtual DateTime? LastModifiedDateTime
{
get
{
return this._LastModifiedDateTime;
}
set
{
this._LastModifiedDateTime = value;
}
}
#endregion
#region tstamp
public abstract class Tstamp : PX.Data.IBqlField
{
}
protected Byte[] _tstamp;
[PXDBTimestamp]
public virtual Byte[] tstamp
{
get
{
return this._tstamp;
}
set
{
this._tstamp = value;
}
}
#endregion
}
}
Limit Accounts
When I was asked to limit Accounts selector in Sales orders, I thought it would be easy task, so I went to definition of SOLine, and found the following C# code:
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[Account(typeof (SOLine.branchID), Visible = false)]
public virtual int? SalesAcctID
{
get
{
return this._SalesAcctID;
}
set
{
this._SalesAcctID = value;
}
}
Every time I see custom attributes of Acumatica during development I have some thought in background like ups, surprises will come for sure.
And they come. But let's try to make substitution for SalesAcctID with help of ordinary PXSelector in SOORderEntryExt:
public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
{
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXDBInt]
[PXSelector(typeof(Search2<Account.accountID,
InnerJoin<CompanyAccount, On<Account.accountID, Equal<CompanyAccount.accountID>>>,
Where<CompanyAccount.cmpBranchID, Equal<Current<SOLine.branchID>>>>),
typeof(Account.accountID), typeof(Account.accountCD), typeof(Account.description))]
[PXUIField(DisplayName = "Account CD")]
protected void SOLine_SalesAcctID_CacheAttached(PXCache sender)
{
}
}
nothing fancy. Take a look at final result:
as you can see from screen recording, filtering works wonderful, but after selection of Account, field remains empty. How to deal with that? In that case on rescue comes PXDimensionSelector!
I will tell you mine own definition of PXDimensionSelector: Attribute, that allows you to modify any custom Acumatica selector. For example you can use PXDimensionSelector for filtering Accounts, SubAccounts ( but for that you'll need to play with Segmented keys), etc.
Take a look on the code, which replaces PXSelector with PXDimensionSelector:
public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
{
[PXUIField(DisplayName = "Account", FieldClass = "ACCOUNT", Visibility = PXUIVisibility.Visible)]
[PXDBInt]
[PXDimensionSelector(AccountAttribute.DimensionName,
typeof(Search2<Account.accountID, InnerJoin<CompanyAccount, On<Account.accountID, Equal<CompanyAccount.accountID>>>,
Where<CompanyAccount.cmpBranchID, Equal<Current<SOLine.branchID>>>>), typeof(Account.accountCD),
new Type[] { typeof(Account.accountCD), typeof(CompanyAccount.description) })]
protected void SOLine_SalesAcctID_CacheAttached(PXCache sender)
{
}
}
And final result:
As you can see from the screen recording, PXDimensionSelector allows you to filter account as well as select, while PXSelector only filters accounts, but doesn't allow you to select proper account