I have a custom field on the Equipment screen to show the Production Order number related to the equipment. To get that value, it is a fairly complicated select statement. Where I am now, I have the field displaying on the screen, but when you click the Production Number value it does not open the Production Order Management screen.Â
I tried many ways to make the field pull using a selector so that it would simply default the value in the field. I failed. So, as a last ditch effort, I created a SQL View that returns my value and I set the field value in the row selected handler. Â
If anyone sees the flaws in my selector statements that will make it so I don’t have to do it in the row selected, or by using a SQL View, that’s great. Â
Otherwise, if someone can tell me how to get the custom field to open the Production Order Management screen from what I have working now, that would be fine too.
Here’s my Code. You can see the various things I’ve tried. I’ve left all my commented out code so you can see my attempts.
using Motivair;
using PX.Data;
using PX.Data.BQL.Fluent;
using PX.Objects.AM;
using PX.Objects.AM.Attributes;
namespace PX.Objects.FS
{
public sealed class FSEquipmentExt : PXCacheExtension<PX.Objects.FS.FSEquipment>
{
public static bool IsActive() => true;
#region UsrAMProdOrdID
//APXString(15, IsUnicode = true, InputMask = "", BqlField = typeof(AMProdItem.prodOrdID))]
//rPXString(15, IsUnicode = true, InputMask = "", typeof(AMProdItem.prodOrdID))]
.PXUIField(DisplayName = "Production Nbr.", Enabled = false)]
lProductionOrderSelector(typeof(AMProdItem.prodOrdID))]
//this works and allows me to drill into the production order
//cPXSelector(typeof(SearchFor<AMProdItem.prodOrdID>.Where<AMProdItem.prodOrdID.IsEqual<FSEquipmentExt.usrAMProdOrdID.FromCurrent>>))]
//This compiles but I get errors on the screen when it loads
//ePXSelector(typeof(SelectFrom<AMProdItem>
// .InnerJoin<SOLineMfgOnly>.On<SOLineMfgOnly.aMProdOrdID.IsEqual<AMProdItem.prodOrdID>>
// .InnerJoin<ARTran>.On<ARTran.sOOrderNbr.IsEqual<SOLineMfgOnly.orderNbr>.And<ARTran.sOOrderLineNbr.IsEqual<SOLineMfgOnly.lineNbr>>>
// .InnerJoin<FSEquipment>.On<FSEquipment.sourceRefNbr.IsEqual<ARTran.refNbr>.And<FSEquipment.arTranLineNbr.IsEqual<ARTran.lineNbr>>>
// .Where<FSEquipment.SMequipmentID.IsEqual<FSEquipment.SMequipmentID.FromCurrent>.And<FSEquipment.sourceRefNbr.IsEqual<FSEquipment.sourceRefNbr.FromCurrent>>>
// .AggregateTo<GroupBy<AMProdItem.prodOrdID>>))]
//cant get this to compile
// PXSelector(typeof(Search5<AMProdItem.prodOrdID,
// InnerJoin<SOLineMfgOnly, On<SOLineMfgOnly.aMProdOrdID, Equal<AMProdItem.prodOrdID>>>,
// InnerJoin<ARTran, On<ARTran.sOOrderNbr, Equal<SOLineMfgOnly.orderNbr>, And<ARTran.sOOrderLineNbr, Equal<SOLineMfgOnly.lineNbr>>>>,
// InnerJoin<FSEquipment, On<FSEquipment.sourceRefNbr, Equal<ARTran.refNbr>, And<FSEquipment.arTranLineNbr, Equal<ARTran.lineNbr>>>>,
// Aggregate<GroupBy<AMProdItem.prodOrdID>>>))]
//cant get this to compile
// PXSelector(typeof(Search6<AMProdItem.prodOrdID,
// InnerJoin<SOLineMfgOnly, On<SOLineMfgOnly.aMProdOrdID, Equal<AMProdItem.prodOrdID>>>,
// InnerJoin<ARTran, On<ARTran.sOOrderNbr, Equal<SOLineMfgOnly.orderNbr>, And<ARTran.sOOrderLineNbr, Equal<SOLineMfgOnly.lineNbr>>>>,
// InnerJoin<FSEquipment, On<FSEquipment.sourceRefNbr, Equal<ARTran.refNbr>, And<FSEquipment.arTranLineNbr, Equal<ARTran.lineNbr>>>>,
// Aggregate<GroupBy<AMProdItem.prodOrdID>>,
// Where<FSEquipment.SMequipmentID, Equal<FSEquipment.SMequipmentID.FromCurrent>, And<FSEquipment.orderNbr, Equal<FSEquipment.sourceRefNbr.FromCurrent>>>>]
//This compiles but I get errors on the screen when it loads
//ePXSelector(typeof(SelectFrom<ICSFSEquipAMProdOrderView>
// .Where<ICSFSEquipAMProdOrderView.sMEquipmentID.IsEqual<FSEquipment.SMequipmentID.FromCurrent>.And<ICSFSEquipAMProdOrderView.orderNbr.IsEqual<FSEquipment.salesOrderNbr.FromCurrent>>>))]
public string UsrAMProdOrdID { get; set; }
public abstract class usrAMProdOrdID : PX.Data.BQL.BqlString.Field<usrAMProdOrdID> { }
#endregion
}
public class SMEquipmentMaint_Extension : PXGraphExtension<SMEquipmentMaint>
{
#region Event Handlers
protected void FSEquipment_RowSelected(PXCache cache, PXRowSelectedEventArgs e)
{
var row = (FSEquipment)e.Row;
ICSFSEquipAMProdOrderView item = SelectFrom<ICSFSEquipAMProdOrderView>
.Where<ICSFSEquipAMProdOrderView.sMEquipmentID.IsEqual<FSEquipment.SMequipmentID.FromCurrent>
.And<ICSFSEquipAMProdOrderView.orderNbr.IsEqual<FSEquipment.salesOrderNbr.FromCurrent>>>.View.Select(Base);
if (item != null)
{
FSEquipmentExt itemExt = PXCache<FSEquipment>.GetExtension<FSEquipmentExt>(row);
itemExt.UsrAMProdOrdID = item.ProdOrdID;
}
}
#endregion
}
}
This is the SQL View that works to bring me the value I am displaying:
CREATE VIEW gdbo].
SELECT Â Â
   SOL.CompanyID,
   AMPI.ProdOrdID,
   SOL.OrderNbr,
   FSE.SMEquipmentID
FROM
   dbo.FSEquipment FSE
   INNER JOIN
   dbo.ARTran ART
      ON ART.RefNbr = FSE.SourceRefNbr
      AND ART.LineNbr = FSE.ARTranLineNbr
   INNER JOIN
   dbo.SOLine SOL
      ON SOL.OrderNbr = ART.SOOrderNbr
      AND SOL.LineNbr = ART.SOOrderLineNbr
   INNER JOIN
   dbo.AMProdItem AMPI
      ON AMPI.ProdOrdID = SOL.AMProdOrdID
GO
Executing this command returns the value I want
select * from dICSFSEquipAMProdOrderView] whereÂ
   OrderNbr = 'SO003984'
   AND SMEquipmentID = 160
Â
![](https://uploads-us-west-2.insided.com/acumatica-en/attachment/d6976082-4618-42a2-b611-40c51c311105.png)
This is the DAC for the SQL View
using PX.Data;
using PX.Objects.AM;
using System;
namespace Motivair
{
   #region ICSFSEquipAMProdOrderView
   gSerializable]
   ePXCacheName("ICSFSEquipAMProdOrderView")]
   public partial class ICSFSEquipAMProdOrderView : IBqlTable
   {
      #region ProdOrdID
      aPXDBString(15, IsUnicode = true, BqlField = typeof(AMProdItem.prodOrdID))]
      public virtual string ProdOrdID { get; set; }
      public abstract class prodOrdID : PX.Data.BQL.BqlString.Field<prodOrdID> { }
      #endregion
      #region OrderNbr
      aPXDBString(15, IsUnicode = true)]
      public virtual string OrderNbr { get; set; }
      public abstract class orderNbr : PX.Data.BQL.BqlString.Field<orderNbr> { }
      #endregion
      #region SMEquipmentID
      ÂPXDBInt]
      public virtual Int32? SMEquipmentID { get; set; }
      public abstract class sMEquipmentID : PX.Data.BQL.BqlInt.Field<sMEquipmentID> { }
      #endregion
   }
   #endregion
}
These are the current settings on the field in the Project Editor
![](https://uploads-us-west-2.insided.com/acumatica-en/attachment/855edcc1-76b4-4250-ba61-955c3dc04e40.png)
Â
This is what the screen looks like. Â
![](https://uploads-us-west-2.insided.com/acumatica-en/attachment/827e5d1d-c8b9-477b-b7a1-b43e1bcd8147.png)
Â
Â
My current solution gets me where I want to be, I just want to get the linking from my field to go to the Production Order Maintenance screen.
Â