Solved

Error "Invalid column name" with PXSelector.SubstituteKey and PXDBScalar

  • 17 March 2021
  • 4 replies
  • 406 views
M
Rank
Userlevel 2
Badge

Hello,

I have a DAC MyCustomer that has Customer reference BAccountID. In a graph for another DAC MyPartnership I want to use a Selector to pick a MyCustomer and also shows the AcctCD by using an unbound column. 

The selector itself works fine, but I get errors for persisting MyPartnership or loading existing ones.

The error in Trace is this.

Invalid column name '[MyCustomerExt].[acctCD]'

 

Please note, that MyCustomer is not an Extension and I have no idea why it is looking for MyCustomerExt.

 

Here is my code:

    public class MyCustomer : IBqlTable
    {
        #region BAccountID
        [Customer(IsKey = true)]
        [PXDefault]
        public virtual int? BAccountID { get; set; }
        public abstract class bAccountID : PX.Data.BQL.BqlInt.Field<bAccountID> { }
        #endregion

        #region AcctCD
        [PXDBScalar(typeof(Search<BAccount.acctCD, Where<BAccount.bAccountID, Equal<MyCustomer.bAccountID>>>))]
        public virtual string AcctCD { get; set; }
        public abstract class acctCD : PX.Data.BQL.BqlString.Field<acctCD> { }
        #endregion

    }

 

    public class MyPartnership: IBqlTable
    {
        #region BAccountID
        [PXDBInt(IsKey = true)]
        [PXSelector(typeof(Search<MyCustomer.acctCD>),
            SubstituteKey = typeof(MyCustomer.acctCD))]
        [PXUIField(DisplayName = "Customer Account")]
        public virtual int? BAccountID { get; set; }
        public abstract class bAccountID : PX.Data.BQL.BqlString.Field<bAccountID> { }
        #endregion

 

How can solve this? 

icon

Best answer by mhaps 17 March 2021, 12:03

View original

4 replies

Naveen Boga
Rank
Userlevel 7
Badge +17

Yeah..it is an issue with the Substitute key because the substitute key always looks for the bound field but in above it was an unbound field, hence you got that issue.

Naveen B
M
Rank
Userlevel 2
Badge

I have also tried to use a join in the PXSelector to get the AcctCD directy from BAccount. That leeds to the same error. The error is gone when I omit the SubstituteKey.

M
Rank
Userlevel 2
Badge

​

Thanks @Naveen B for the quick response.

And sorry for mixing Int and String in the Selector. Should be

Search<MyCustomer.bAccountID>

I added the [PXString] to MyCustomer.AcctCD then changed the names as suggested.

Unfortunately this does not help. I get same error with new name still looking in MyCustomerExt.

Any more ideas?

Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @mhaps 

I see some issues in DAC field declarations. Please find the screenshot for reference.

 

 

 

As above DACs are custom DAC file, I recommend you to change field names (like instead of BAccountID,AcctCD, you can have CustomerID/CustomerName).

DBScaler attibute will be used for only Unbound fields. I have bit modified above DAC fields, please find DACs and comments below.

 

 public class MyCustomer : IBqlTable
    {
        #region BAccountID
        [Customer(IsKey = true)]
        [PXDefault]
        public virtual int? BAccountID { get; set; }
        public abstract class bAccountID : PX.Data.BQL.BqlInt.Field<bAccountID> { }
        #endregion

        #region AcctCD

        [PXString(255, IsUnicode = true)] // Added this line
        [PXDBScalar(typeof(Search<BAccount.acctCD, Where<BAccount.bAccountID, Equal<MyCustomer.bAccountID>>>))]
        public virtual string AcctCD { get; set; }
        public abstract class acctCD : PX.Data.BQL.BqlString.Field<acctCD> { }
        #endregion

    }



    public class MyPartnership : IBqlTable
    {
        #region BAccountID
        [PXDBInt(IsKey = true)]
        // Also, I see the issue with this selector, because you are searching for AcctCD(strig field in MyCustomer) for the BAccountID(which is integer in MyPartnership DAC)
        [PXSelector(typeof(Search<MyCustomer.acctCD>), SubstituteKey = typeof(MyCustomer.acctCD))]
        [PXUIField(DisplayName = "Customer Account")]
        public virtual int? BAccountID { get; set; }
        public abstract class bAccountID : PX.Data.BQL.BqlInt.Field<bAccountID> { }
        #endregion
    }

 

Hope this helps!!

Naveen B

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved