Skip to main content
Answer

Incorrect syntax near the keyword 'WHERE'.

  • October 26, 2023
  • 6 replies
  • 284 views
B
Jr Varsity II
Forum|alt.badge.img

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

Best answer by jinin

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.

 

6 replies

StevenRatner
Varsity I
Forum|alt.badge.img
  • StevenRatner
  • Varsity I
  • October 26, 2023

 

@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
Chris Hackett
RohitRattan88
RohitRattan88
Acumatica Moderator
Forum|alt.badge.img+4
  • RohitRattan88
  • Acumatica Moderator
  • October 26, 2023

@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

 

Chris Hackett
B
Jr Varsity II
Forum|alt.badge.img
  • bhagyat25
  • Author
  • Jr Varsity II
  • October 26, 2023

@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
Acumatica Moderator
Forum|alt.badge.img+4
  • RohitRattan88
  • Acumatica Moderator
  • October 26, 2023

@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)

jinin
rimamanaseryan16
RohitRattan88
Acumatica Moderator
Forum|alt.badge.img+4
  • RohitRattan88
  • Acumatica Moderator
  • October 26, 2023

@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)

Chris Hackett
jinin
Pro I
Forum|alt.badge.img+11
  • jinin
  • Pro I
  • Answer
  • October 26, 2023

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
Vignesh Ponnusamy
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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