Acumatica - Composite primary key

Question

There is master-child table and master record has composite key on OrderNbr and RevisionNbr, On UI we want to present the Selector for both the fields OrderNbr with Max Revision number for Order number selector Revision Number selector based on order Number selected ON UI.

also on UI we have function to create New revision, How we can create revision by Code, I am getting error of MUlti part query, when I defined IsKey= true for both the fields and tried to Save the data By

Graph.primaryView.Insert()
ForEach(UIRow){
      Graph.ChildView.Insert
}
Graph.Persist();

Note the MAster table has Identity column in the SQL server has Key

Update- Parent Table DAC -

#region TestSuiteID
public abstract class testSuiteID:PX.Data.IBqlField {
}
protected int? _TestSuiteID;
[PXDBIdentity()]
public virtual int? TestSuiteID {
  get {
    return this._TestSuiteID;
  }
  set {
    this._TestSuiteID = value;
  }
}
#endregion
#region TestSuiteCD
public abstract class testSuiteCD:PX.Data.IBqlField {
}
protected string _TestSuiteCD;
[PXDBString(50, IsKey = true, IsUnicode = true, InputMask = ">CCCCCCCCCCCCCCC")]
[PXDefault()]
[PXUIField(DisplayName = "Test Group ID")]
[PXSelector(typeof(EWQCTestSuite.testSuiteCD), typeof(EWQCTestSuite.testSuiteCD), typeof(EWQCTestSuite.revisionNo), typeof(EWQCTestSuite.name))]
public virtual string TestSuiteCD {
  get {
    return this._TestSuiteCD;
  }
  set {
    this._TestSuiteCD = value;
  }
}
#endregion
#region RevisionNo
public abstract class revisionNo:PX.Data.IBqlField {
}
protected int? _RevisionNo;
[PXDBInt(IsKey=true)]
[PXDefault(1)]
[PXUIField(DisplayName = "Revision No")]
public virtual int? RevisionNo {
  get {
    return this._RevisionNo;
  }
  set {
    this._RevisionNo = value;
  }
}
#endregion

Child Table Dac -

#region TestSuiteVariableID
public abstract class testSuiteVariableID:PX.Data.IBqlField {
}


protected int? _TestSuiteVariableID;
[PXDBIdentity(IsKey = true)]
public virtual int? TestSuiteVariableID {
  get {
    return this._TestSuiteVariableID;
  }
  set {
    this._TestSuiteVariableID = value;
  }
}
#endregion

#region TestSuiteID
public abstract class testSuiteID:PX.Data.IBqlField {
}
protected int? _TestSuiteID;
[PXDBInt()]
[PXDBDefault(typeof(EWQCTestSuite.testSuiteID))]
[PXParent(typeof(Select<EWQCTestSuite, Where<EWQCTestSuite.testSuiteID, Equal<Current<EWQCTestSuite.testSuiteID>>>>))]
public virtual int? TestSuiteID {
  get {
    return this._TestSuiteID;
  }
  set {
    this._TestSuiteID = value;
  }
}
#endregion

Note - Child table does not have MAster tab CD and revision number column as I added Identity column fr reference and have PXParent with Identity column. Both Dac has other fields that I have not added here.

Other issue that I am facing is When I am Deleting the record I am getting some Primarykey reference error (Delete by default acumatica delete button on Primary DataView)

you should include the important fields as you have them defined in your dac. Do you have a PXDBDefault on your child table pointing to the parent fields? do you have a PXParent setup on the child record? You should avoid an identity column when you have multiple keys on a table (although should work). Use a numbering sequence field vs identity column. For the selector you can use a PXProjection on the child table to just get a max revision order by ordernbr and use that in your selector. I can try to do a real answer later but some things to consider for now — Brendan
Thanks, You mean I should include the OrdNbr and Revision number fields in child table also, although I have OrderID already defined for Parent Child relationShip? Yes I have [PXDBDefault] with OrderID on child table I used [PXParent] on child dac with OrderID field — Shaj
I think he means you should add more details (code) to your question. My answer is very generic because there's not much information in your question. — Hugues Beauséjour
i was referring to the need for a multi part key when you are using an identity field since each insert to the table will already be unique there is no need for more than one key. ideally would just be the Identity field or if needing a pair you could use a numbering sequence field with another field such as revisionno. But PXDBDefault and PXParent should be all you need on the children. Also multi part keys with the default navigation sometimes are not the easiest to get working in an Acumatica page. We have a custom PXGraph to work with these dual key (2 strings) pages. — Brendan

Hugues Beauséjour Hugues Beauséjour · Accepted Answer · 2019-12-27T21:15:34

The multi-part query error is usually related to a master-detail (parent-child) query that resolves to more than one parent when executed. The child DAC should have a PXParent attribute that includes where clause on all the key fields.

[PXParent(typeof(Select<Master,
                 Where<Master.key1, Equal<Current<Child.key1>>,
                 And<Master.key2, Equal<Current<Child.key2>>>>>>))]

You should make sure all BQL queries in the DAC and the Graph that have such a relationship include a where filter or on join clause on all keys. Also make sure all key fields in DAC have IsKey=true and that the matching database fields where created as key fields in the database.

Acumatica - Composite primary key

2 Answers