Acumatica - Unique Check

Question

What attribute can be used to allow unique values, we used [PXUniqueCheck(typeof(Name))] But if List view is used and records are updated with same name, it allows the same name values

More Details - HI, Column is marked with IsKey = True for Name, My View is ListView, with 2 fields Name and Description where Name has IsKey =True and another column ID has DBIdentity Attribute. SO i think there is everything OK with the DAC, UI Add Behavior - when existing value is added then it updates the old row with newly added value description as Name is Same. Update Behavior - Change name column to the same value as of other then it allows to have 2 rows with same Key, but both rows shows the same description on UI, but in DB there are 2 rows with same Name value and different Description

DAC -

[System.SerializableAttribute()]
[PXPrimaryGraph(typeof(TestCategoryMaint))]
public class TestCategory : PX.Data.IBqlTable
{

    #region TestCategoryID
    public abstract class testCategoryID : PX.Data.BQL.BqlInt.Field<testCategoryID>
{
    }
    protected int? _TestCategoryID;
    [PXDBIdentity()]
    [LicenseExpiration]
[PXReferentialIntegrityCheck]

public virtual int? TestCategoryID
    {
        get
        {
            return this._TestCategoryID;
        }
        set
        {
            this._TestCategoryID = value;
        }
    }
    #endregion

    #region Name
    public abstract class name : PX.Data.IBqlField
    {
    }
    protected string _Name;
    [PXDBString(50, IsKey = true, IsUnicode = true, InputMask = "")]
    [PXDefault()]        
    [PXUIField(DisplayName = "Test Category")]
    [PXCheckUnique(typeof(name))]
    public virtual string Name
    {
        get
        {
            return this._Name;
        }
        set
        {
            this._Name = value;
        }
    }
    #endregion

    #region Description
    public abstract class description : PX.Data.IBqlField
    {
    }
    protected string _Description;
    [PXDBString(255,IsUnicode =true)]
    [PXUIField(DisplayName = "Description")]
    public virtual string Description
    {
        get
        {
            return this._Description;
        }
        set
        {
            this._Description = value;
        }
    }
#endregion

Answer 1

Without any code samples of what you are attempting, it is difficult to ensure that the answer fits your question, but I'll try to answer in a broad scope.

Building on the comments above, an identify field is such specifically because it is defined as identity in the database. The corresponding field in the DAC should be marked as PXDBIdentity. In Acumatica, this generally is defined as the identity "ID" field. The field you display that should be kept unique tends to be the code "CD" field.

The sample below shows how to setup the pair, complete with the commonly used attributes. In this example, the CD field is auto-numbered, but it doesn't have to be. The PXSelector on the field itself helps this to be maintained as a unique value because it will allow you to select from an existing value, enter a new value, or retrieve the value that was keyed in. The actual uniqueness is controlled by the combination of all fields marked IsKey = true.

// This is the Identity field in the database (Use PXDBIdentity)
#region RequisitionID
[PXDBIdentity]
public virtual int? RequisitionID { get; set; }
public abstract class requisitionID
    : PX.Data.BQL.BqlInt.Field<requisitionID> { }
#endregion

// This is the user friendly Code (CD) to be unique (IsKey = true)
#region RequisitionCD
[PXDBString(30, IsKey = true, IsUnicode = true, InputMask = "")]
[PXDefault]
[AutoNumber(typeof(SSRQSetup.requisitionNumberingID),
    typeof(AccessInfo.businessDate))]
[PXSelector(
    typeof(SSRQRequisition.requisitionCD),
    typeof(SSRQRequisition.requisitionCD),
    typeof(SSRQRequisition.descr)
    )]
[PXUIField(DisplayName = Messages.FldRequisitionCD)]
public virtual string RequisitionCD { get; set; }
public abstract class requisitionCD
    : PX.Data.BQL.BqlString.Field<requisitionCD> { }

Similarly, you can refer back to this combination so that the unique CD field is used in interaction with the user, but the ID value is stored in the record. This is the best practice to use an ID field to be shared across all tables where used to normalize the database and allow changing the displayed (CD) value without having to update potentially dozens of other tables. This means that any unique object (item, vendor, customer, etc.) will be maintained in its own record where the IsKey value uniquely identifies the exact object. If you need your specific table to maintain this value as the uniqueness, then it would be the only field marked IsKey = true and then the identity field would be used in other tables referencing this data.

To use this key data in other tables, you would store the ID field value, but use PXSelector to present the CD value to the user. This allows storing a very efficient numeric value in the database in related tables rather than a value that could be a few characters or thousands of characters (in an extreme case).

#region RequisitionID
[PXDBInt]
[PXSelector(
    typeof(SSRQRequisition.requisitionID),
    typeof(SSRQRequisition.requisitionCD),
    typeof(SSRQRequisition.descr),
    SubstituteKey = typeof(SSRQRequisition.requisitionCD)
    )]
[PXUIField(DisplayName = Messages.FldRequisitionID)]
public virtual int? RequisitionID { get; set; }
public abstract class requisitionID
    : PX.Data.BQL.BqlInt.Field<requisitionID> { }
#endregion

It is very important to understand that the use of IsKey will drive uniqueness based on every field where you have specified it. IsKey will only ensure uniqueness for the combination of all fields marked with IsKey = true. The PXSelector seems to ensure that the field itself is kept unique, and also the field specificed by SubstituteKey when used as the selector will retrieve the record that has the value you entered. I have used that only in cases of ID vs CD, but you could experiment with PXSelector and IsKey if you have some use case outside the scope of this answer.

Answer 2

The below code will help you to add only unique values and when you try to add duplicate values, this throws an error.

[PXCheckUniqueCustomError("Order already exists with Customer Order Nbr {0} and External Ref.Nbr {1}", typeof(SOOrder.customerOrderNbr), typeof(SOOrder.customerRefNbr),  
Where = typeof(Where2<Where<SOOrder.orderType, Equal<Current<SOOrder.orderType>>,
    And<SOOrder.customerOrderNbr, Equal<Current<SOOrder.customerOrderNbr>>,
        And<SOOrder.customerRefNbr, Equal<Current<SOOrder.customerRefNbr>>>>>,
And<SOOrder.status, NotEqual<SOOrderStatus.cancelled>>>))]