Solved

Incorrect syntax near the keyword 'WHERE'.

  • 26 October 2023
  • 6 replies
  • 87 views
B
Rank
Userlevel 3
Badge

Hi All,

I created a virtual DAC and linked that to my graph but when opening the screen it gives this error. 

can someone help me out?

Incorrect syntax near the keyword 'WHERE'. 

My DAC and Graph

    [PXCacheName("HMRCVendorRegisterDetailDummy")]
    [PXVirtual]
    public class HMRCVendorRegisterDetailDummy : IBqlTable
    {
        #region Hmrcregid       
        public virtual int? Hmrcregid { get; set; }
        public abstract class hmrcregid : PX.Data.BQL.BqlInt.Field<hmrcregid> { }
        #endregion

        #region VendorID       
        [PXUIField(DisplayName = "Vendor", Required = true)]
        [PXInt]     
        [PXCheckUnique(typeof(bAccountID))]
        [PXUnboundDefault(PersistingCheck = PXPersistingCheck.NullOrBlank)]
        public virtual int? BAccountID { get; set; }
        public abstract class bAccountID : PX.Data.BQL.BqlInt.Field<bAccountID> { }
        #endregion

        #region Utrno
        [PXString(256, IsUnicode = true, InputMask = "")]
        [PXUnboundDefault(PersistingCheck = PXPersistingCheck.NullOrBlank)]
        [PXUIField(DisplayName = "UTR No", Required = true)]
        public virtual string Utrno { get; set; }
        public abstract class utrno : PX.Data.BQL.BqlString.Field<utrno> { }
        #endregion

        #region VerificationNo
        [PXString(256, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Verification No")]
        [PXUnboundDefault(PersistingCheck = PXPersistingCheck.NullOrBlank)]
        public virtual string VerificationNo { get; set; }
        public abstract class verificationNo : PX.Data.BQL.BqlString.Field<verificationNo> { }
        #endregion

        #region VerifiedDate
        [PXDate()]
        [PXUIField(DisplayName = "Verified Date")]
        [PXUnboundDefault(typeof(AccessInfo.businessDate))]

        public virtual DateTime? VerifiedDate { get; set; }
        public abstract class verifiedDate : PX.Data.BQL.BqlDateTime.Field<verifiedDate> { }
        #endregion

        #region VerifiedTypeID
        [PXString(1, IsFixed = true)]
        [PXUnboundDefault(RegisterTypes.System, PersistingCheck = PXPersistingCheck.NullOrBlank)]
        #endregion
    }
    
    //Graph
    public class HMRCVendorBulkRegisterNewMaint : PXGraph<HMRCVendorBulkRegisterNewMaint>
    {

        public PXSave<HMRCVendorRegisterDetailDummy> Save;
        public PXCancel<HMRCVendorRegisterDetailDummy> Cancel;

        public SelectFrom<HMRCVendorRegisterDetailDummy>.View VendorRegisterDetail;


    }

 

Thanks

icon

Best answer by jinin 26 October 2023, 17:56

View original

6 replies

StevenRatner
Rank
Userlevel 5
Badge

 

@bhagyat25 

1. In the `VerifiedTypeID` region, you start defining a property but it abruptly ends after the attribute. It seems like you're missing the actual property definition there. Make sure you add it in like so:

    #region VerifiedTypeID
    [PXString(1, IsFixed = true)]
    [PXUnboundDefault(RegisterTypes.System, PersistingCheck = PXPersistingCheck.NullOrBlank)]
    public virtual string VerifiedTypeID { get; set; }
    public abstract class verifiedTypeID : PX.Data.BQL.BqlString.Field<verifiedTypeID> { }
    #endregion
   
2. For the `[PXCheckUnique(typeof(bAccountID))]` attribute, are you sure `PXCheckUnique` exists and allows a type parameter? You might want to confirm that.

3. Lastly, make sure the `RegisterTypes.System` value you're using in `[PXUnboundDefault(RegisterTypes.System, PersistingCheck = PXPersistingCheck.NullOrBlank)]` actually exists. It's not defined in the provided code.

 

Steve - Klear Systems
RohitRattan88
Rank
Userlevel 7
Badge +4

@bhagyat25 , as @StevenRatner  mentioned, I see issues with the declaration of 

        #region Hmrcregid       
        public virtual int? Hmrcregid { get; set; }
        public abstract class hmrcregid : PX.Data.BQL.BqlInt.Field<hmrcregid> { }
        #endregion

and 

        #region VerifiedTypeID
        [PXString(1, IsFixed = true)]
        [PXUnboundDefault(RegisterTypes.System, PersistingCheck = PXPersistingCheck.NullOrBlank)]
        #endregion

 

B
Rank
Userlevel 3
Badge

@bhagyat25 , as @StevenRatner  mentioned, I see issues with the declaration of 

        #region Hmrcregid       
        public virtual int? Hmrcregid { get; set; }
        public abstract class hmrcregid : PX.Data.BQL.BqlInt.Field<hmrcregid> { }
        #endregion

and 

        #region VerifiedTypeID
        [PXString(1, IsFixed = true)]
        [PXUnboundDefault(RegisterTypes.System, PersistingCheck = PXPersistingCheck.NullOrBlank)]
        #endregion

 

Hi @RohitRattan88,

I changed both as following. But still error comming.

[PXInt(IsKey = true)]
        [PXUIField(DisplayName = "UID")]
        [PXUnboundDefault()]
        public virtual int? Hmrcregid { get; set; }
        public abstract class hmrcregid : PX.Data.BQL.BqlInt.Field<hmrcregid> { }

[PXString(1, IsFixed = true)]
        [PXUnboundDefault(RegisterTypes.System, PersistingCheck = PXPersistingCheck.NullOrBlank)]
        public virtual string VerifiedTypeID { get; set; }
        public abstract class verifiedTypeID : PX.Data.BQL.BqlString.Field<verifiedTypeID> { }

RohitRattan88
Rank
Userlevel 7
Badge +4

@bhagyat25 

try adding PXVirtualDAC attribute to your view as

[PXVirtualDAC]public SelectFrom<HMRCVendorRegisterDetailDummy>.View VendorRegisterDetail;

https://help.acumatica.com/(W(214))/Help?ScreenId=ShowWiki&pageid=db057e6d-a28e-7ed6-4bfc-232344776875

How To Work With Unbounded Dac In Acumatica (zaletskyy.com)

RohitRattan88
Rank
Userlevel 7
Badge +4

@bhagyat25 you could also review following related discussion:

Adding a smart panel to SO301000 to show what other orders a customer has placed, only shows one order in grid. | Community (acumatica.com)

J
Rank
Userlevel 7
Badge +11

Hi @bhagyat25 ,

Virtual DAC does not support adding WHERE conditions in the view. If you wish to use a view with a WHERE condition, create a dummy table(for the Same virtual DAC) in the database and use the DAC for cache insert/update. There's no need to insert/update data in the table. Please try this alternative; it should work.

 

Jini N

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