Solved

Need Help on Projection DAC BQL not right

  • 31 January 2024
  • 2 replies
  • 38 views

Userlevel 4
Badge

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
}
}

 

icon

Best answer by mjgrice32 31 January 2024, 21:06

View original

2 replies

Userlevel 4
Badge

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

 

Userlevel 7
Badge

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

Reply


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