Skip to main content
Solved

Need Help on Projection DAC BQL not right


Forum|alt.badge.img

I am trying to create a projection of the Service Order Detail joined to the Appointment detail.

This is the SQL statement I am trying to emulate:

SELECT FD.[SrvOrdType] ,FD.[RefNbr]
      ,FD.[OrigSrvOrdNbr],FD.[OrigLineNbr]
      ,FD.[UnitCost],FD.[UnitPrice]
	  ,FS.[RefNbr] ,FS.[LineNbr]
      ,FS.[LineRef],FS.[UnitCost]
      ,FS.[UnitPrice],FS.[TranDesc]
      ,FS.[SODetID],FS.[SOID]
From [FSAppointmentDet] FD
   JOIN [FSSODet] FS on FD.OrigSrvOrdNbr = FS.RefNbr 
   AND FS.LineNbr = FD.OrigLineNbr
WHERE fs.UnitPrice <> FD.UnitPrice 

And this is the Projection I am trying to get together, but there are syntax errors here I can’t seem to trace down:


    [PXProjection(typeof(Select2<FSAppointmentDet,
    InnerJoin<FSSODet,
        On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
        And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>,
    Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>>), Persistent = true)]

I seem to always get lost somehow in the brackets… what am I doing wrong here?

 

This is the full DAC in case it matters:

using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.AR;
using PX.Objects.CS;
using PX.Objects.FS;
using PX.Objects.GL.FinPeriods.TableDefinition;
using PX.Objects.IN;
using PX.Objects.IN.S;
using PX.Objects.SO;



namespace MyOwn.DAC
{
    /***************************************************************************
	 * This is a projection set up to allow the Process Screen to be run to
	 * fix the error we are getting in the FSSODET Unit Price.
	 ****************************************************************************/

    [PXProjection(typeof(Select2<FSAppointmentDet,
    InnerJoin<FSSODet,
        On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
        And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>,
    Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>>), Persistent = true)]

    [PXCacheName("FixPriceProjection")]
    public class PlyFixPriceProjDAC : IBqlTable
    {

        #region From the FSAppointmentDet DAC
        #region FDRefNbr
        [PXDBString(20, IsUnicode = true, BqlField = typeof(FSAppointmentDet.refNbr))]
        public virtual string FDRefNbr { get; set; }
        public abstract class fDRefNbr : PX.Data.BQL.BqlString.Field<fDRefNbr> { }
        #endregion

        #region OrigSrvOrdNbr
        [PXDBString(20, IsUnicode = true, BqlField = typeof(FSAppointmentDet.origSrvOrdNbr))]
        public virtual string OrigSrvOrdNbr { get; set; }
        public abstract class origSrvOrdNbr : PX.Data.BQL.BqlString.Field<origSrvOrdNbr> { }
        #endregion

        #region OrigLineNbr
        [PXDBInt(BqlField = typeof(FSAppointmentDet.origLineNbr))]
        public virtual int? OrigLineNbr { get; set; }
        public abstract class origLineNbr : PX.Data.BQL.BqlInt.Field<origLineNbr> { }
        #endregion

        #region FDUnitCost
        [PXDBDecimal(4, BqlField = typeof(FSAppointmentDet.unitCost))]
        public virtual decimal? FDUnitCost { get; set; }
        public abstract class fDUnitCost : PX.Data.BQL.BqlDecimal.Field<fDUnitCost> { }
        #endregion

        #region FDUnitPrice
        [PXDBDecimal(4, BqlField = typeof(FSAppointmentDet.unitPrice))]
        public virtual decimal? FDUnitPrice { get; set; }
        public abstract class fDUnitPrice : PX.Data.BQL.BqlDecimal.Field<fDUnitPrice> { }
        #endregion
        #endregion

        #region From the FSSODet DAC
        #region FSRefNbr
        [PXDBString(20, IsUnicode = true, BqlField = typeof(FSSODet.refNbr))]
        public virtual string FSRefNbr { get; set; }
        public abstract class fSRefNbr : PX.Data.BQL.BqlString.Field<fSRefNbr> { }
        #endregion

        #region FSLineNbr
        [PXDBInt(BqlField = typeof(FSSODet.lineNbr))]
        public virtual int? FSLineNbr { get; set; }
        public abstract class fSLineNbr : PX.Data.BQL.BqlInt.Field<fSLineNbr> { }
        #endregion

        #region FSLineRef
        [PXDBString(30, IsUnicode = true, BqlField = typeof(FSSODet.lineRef))]
        public virtual string FSLineRef { get; set; }
        public abstract class fSLineRef : PX.Data.BQL.BqlString.Field<fSLineRef> { }
        #endregion

        #region FSUnitCost
        [PXDBDecimal(4, BqlField = typeof(FSSODet.unitCost))]
        public virtual decimal? FSUnitCost { get; set; }
        public abstract class fSUnitCost : PX.Data.BQL.BqlDecimal.Field<fSUnitCost> { }
        #endregion

        #region FSUnitPrice
        [PXDBDecimal(4, BqlField = typeof(FSSODet.unitPrice))]
        public virtual decimal? FSUnitPrice { get; set; }
        public abstract class fSUnitPrice : PX.Data.BQL.BqlDecimal.Field<fSUnitPrice> { }
        #endregion


        #region TranDesc
        [PXDBString(255, IsUnicode = true, BqlField = typeof(FSSODet.tranDesc))]
        public virtual string TranDesc { get; set; }
        public abstract class tranDesc : PX.Data.BQL.BqlString.Field<tranDesc> { }
        #endregion
        #endregion
    }
}

 

Best answer by mjgrice32

Aaargh! 

I think I found it:

 

    [PXProjection(typeof(Select2<FSAppointmentDet,
        InnerJoin<FSSODet,
        On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
           And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>>,
        Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>>), Persistent = true)]

 

View original
Did this topic help you find an answer to your question?

2 replies

Forum|alt.badge.img
  • Author
  • Varsity I
  • 76 replies
  • Answer
  • January 31, 2024

Aaargh! 

I think I found it:

 

    [PXProjection(typeof(Select2<FSAppointmentDet,
        InnerJoin<FSSODet,
        On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
           And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>>,
        Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>>), Persistent = true)]

 


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2754 replies
  • January 31, 2024

Thank you for sharing your solution with the community @mjgrice32!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings