Acumatica Branch and BranchBAccount extension updating all branch extension records in the company

Question

I'm attempting to add a Default Branch Location field to Branch. This field will allow the user to select a Branch Location to be the default and have this default in several future customizations. My problem is that when I populate my new extension field, the value is saved on all branch records instead of the selected branch.

I extend the Branch DAC (as that is where the data will be stored):

//Branch Extension
[PXTable(typeof(Branch.branchID), IsOptional = false)]
public class BranchExtension : PXCacheExtension<Branch>
{
    #region DefaultBranchLocation
    public abstract class defaultBranchLocation : IBqlField { }
    [PXDBInt()]
    [PXUIField(DisplayName = "Default Branch Location")]
    [PXSelector(typeof(FSBranchLocation.branchLocationID),
        DescriptionField = typeof(FSBranchLocation.descr),
        SubstituteKey = typeof(FSBranchLocation.branchLocationCD))]
    public virtual int? DefaultBranchLocation { get; set; }
    #endregion
}

Here is the Branch extension table I created:

<Sql TableName="BranchExtension" TableSchemaXml="#CDATA">
  <CDATA name="TableSchemaXml"><![CDATA[<table name="BranchExtension">
    <col name="CompanyID" type="Int" default="Zero" />
    <col name="BranchID" type="Int" />
    <col name="DeletedDatabaseRecord" type="Bit" />
    <col name="DefaultBranchLocation" type="Int" nullable="true" />
    <index name="BranchExtension_PK" clustered="true" primary="true" unique="true">
      <col name="CompanyID" />
      <col name="BranchID" />
    </index>
  </table>]]></CDATA>
</Sql>

I created a backfill script (not included). To handle the projection that is used on the branches page I created the following BranchBAccount extension:

//BranchBAccount Projection Extension
public class BranchBAccountExtension : PXCacheExtension<BranchMaint.BranchBAccount>
{
    #region DefaultBranchLocation
    public abstract class defaultBranchLocation : IBqlField { }
    [PXDBInt(BqlField = typeof(BranchExtension.defaultBranchLocation))]
    [PXUIField(DisplayName = "Default Branch Location")]
    [PXSelector(typeof(
        Search2<FSBranchLocation.branchLocationID,
            InnerJoin<Branch, On<Branch.branchID, Equal<FSBranchLocation.branchID>>>,
            Where<Branch.branchCD, Equal<Current<BranchMaint.BranchBAccount.branchBranchCD>>>>),
        DescriptionField = typeof(FSBranchLocation.descr),
        SubstituteKey = typeof(FSBranchLocation.branchLocationCD))]
    public virtual int? DefaultBranchLocation { get; set; }
    #endregion
}

For the aspx I simply added my extension field to the Branches page (CS102000) as the first field under General Info, MISC SETTINGS (SHARED). You can see how it is placed:

...
<px:PXLayoutRule runat="server" StartGroup="True" GroupCaption="Misc Settings (Shared)" ></px:PXLayoutRule>
<px:PXSelector runat="server" ID="edDefaultBranchLocation" DataField="DefaultBranchLocation" CommitChanges="True" AutoRefresh="True" />
<px:PXFormView ID="CommonSettings" runat="server" DataMember="commonsetup" DataSourceID="ds" RenderStyle="Simple">
...

In my test company I have 3 branches (Branch IDs 1, 2, & 3). I navigate to CS102000 (Branches) and select one of the Branches (we'll say Branch ID 3). The Default Branch Location field I added is present and the selector seems to work as desired (whichever Branch I have selected, only its locations are shown). No error is shown upon selecting a Branch Location. I click the Save button and everything looks okay. That is, until I look at any of the other branch records, they now have the branch default that I selected. All the other data on the screen changes upon changing the Branch. When looking in the database sure enough my extension table has the same DefaultBranchLocation value for Branch 1, 2, and 3. Doing a SQL Trace shows that it is simply running the following:

UPDATE BranchExtension SET [BranchExtension].[DefaultBranchLocation] = @P0 WHERE CompanyID = 2

Note that it isn't filtering on the key fields.

I've never had this problem with an extension table before, so I'm unsure of what I'm doing wrong. My guess would be is that it is related to the extension of the projection, but my field declaration in my extension looks no different that the fields declared on that projection which save properly.

Any help is greatly appreciated. Thank you in advance.

elativus elativus · Accepted Answer · 2018-04-14T12:52:44

Really your issue is related to that you customize tail of a projection. I recommend to consider storing your data in a BAccount extension. Then if it is needed you can map a field from BAccount to a Branch extension using PXDBScalarAttribute. If you strongly need to avoid subselect or you have other requirements you can do the following hack: add BranchID field to branch extension and manually handle this field.

See both examples below

public class BranchMaintExt : PXGraphExtension<BranchMaint>
{
    protected void BranchBAccount_BranchID_CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
    {
        if ((e.Operation & PXDBOperation.Command) != PXDBOperation.Select && e.Table != typeof(BranchExtension))
        {
            e.Cancel = true;
        }
        else if ((e.Operation & PXDBOperation.Command) == PXDBOperation.Update && e.Value == null && e.Row != null)
        {
            decimal? ident = PXDatabase.SelectIdentity<PX.Objects.GL.Branch>(typeof(PX.Objects.GL.Branch.branchID).Name);
            if (ident != null && ident.Value > 0)
            {
                sender.SetValue<BranchBAccountExtension.branchID>(e.Row, Convert.ToInt32(ident));
                e.Value = ident;
            }
        }
    }

    protected void BranchBAccount_BAccountID_CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
    {
        if (((e.Operation & PXDBOperation.Command) == PXDBOperation.Update)
            && sender.GetValue<BranchBAccountExtension.branchID>(e.Row) == null && e.Row != null)
        {
            decimal? ident = PXDatabase.SelectIdentity<PX.Objects.GL.Branch>(typeof(PX.Objects.GL.Branch.branchID).Name);
            if (ident != null && ident.Value > 0)
            {
                sender.SetValue<BranchBAccountExtension.branchID>(e.Row, Convert.ToInt32(ident));
            }
        }
    }
}

[PXTable(typeof(Branch.branchID), IsOptional = false)]
public class BranchExtension : PXCacheExtension<Branch>
{
    #region DefaultBranchLocation
    public abstract class defaultBranchLocation : IBqlField { }
    [PXDBInt()]
    [PXUIField(DisplayName = "Default Branch Location")]
    public virtual int? DefaultBranchLocation { get; set; }
    #endregion

    #region BAccountDefaultBranchLocation
    public abstract class bAccountDefaultBranchLocation : IBqlField { }
    [PXDBScalar(typeof(Search<BAccountExtension.bAccountDefaultBranchLocation, Where<BAccount.bAccountID, Equal<Branch.bAccountID>>>))]
    [PXInt()]
    [PXUIField(DisplayName = "Default Branch Location")]
    public virtual int? BAccountDefaultBranchLocation { get; set; }
    #endregion
}

[PXTable(typeof(BAccount.bAccountID), IsOptional = false)]
public class BAccountExtension : PXCacheExtension<BAccount>
{
    #region BAccountDefaultBranchLocation
    public abstract class bAccountDefaultBranchLocation : IBqlField { }

    [PXDBInt()]
    [PXUIField(DisplayName = "Default Branch Location")]
    public virtual int? BAccountDefaultBranchLocation { get; set; }
    #endregion
}

public class BranchBAccountExtension : PXCacheExtension<BranchMaint.BranchBAccount>
{
    #region BranchID
    public abstract class branchID : PX.Data.IBqlField
    {
    }
    [PXDBInt(BqlField = typeof(PX.Objects.GL.Branch.branchID))]
    [PXUIField(DisplayName = "Branch ID", Visibility = PXUIVisibility.Invisible)]
    public virtual int? BranchID { get; set; }

    #endregion

    #region DefaultBranchLocation
    public abstract class defaultBranchLocation : IBqlField { }
    [PXDBInt(BqlField = typeof(BranchExtension.defaultBranchLocation))]
    [PXUIField(DisplayName = "Default Branch Location")]
    public virtual int? DefaultBranchLocation { get; set; }
    #endregion

    #region BAccountDefaultBranchLocation
    public abstract class bAccountDefaultBranchLocation : IBqlField { }

    [PXDBInt]
    [PXUIField(DisplayName = "Default Branch Location")]
    public virtual int? BAccountDefaultBranchLocation { get; set; }
    #endregion
}

Acumatica Branch and BranchBAccount extension updating all branch extension records in the company

1 Answers