Solved

Need help with a PXSelector - Foreign Key reference cannot be created

  • 23 February 2024
  • 4 replies
  • 44 views

Userlevel 6
Badge +3

I have a DAC on my SO Shipping graph extension.  

My goal is to adapt this working selector to only show records that are related to a SOShipment that is of status Hold or Open and the SOShipLineSplit.inventoryID is equal to the inventory ID in the filter.

The following selector (the one not commented out) is throwing an error at runtime.

#region FromLotSerialNbr
[PXString(200, IsUnicode = true)]
[PXUIField(DisplayName = "To Serial Number")]
[PXSelector(typeof(SelectFrom<SOShipLineSplit>
.Where<SOShipLineSplit.lotSerialNbr.IsNotLike<Empty>
.And<SOShipLineSplit.inventoryID.IsEqual<SwapFilter.inventoryID.FromCurrent>>>),
typeof(SOShipLineSplit.shipmentNbr),
typeof(SOShipLineSplit.lotSerialNbr))]

//[PXSelector(typeof(SelectFrom<SOShipLineSplit>
// .InnerJoin<SOShipment>.On<SOShipment.shipmentNbr.IsEqual<SOShipLineSplit.shipmentNbr>>
// .Where<SOShipLineSplit.lotSerialNbr.IsNotLike<Empty>
// .And<SOShipLineSplit.inventoryID.IsEqual<SwapFilter.inventoryID.FromCurrent>>
// .And<Brackets<SOShipment.status.IsEqual<PX.Objects.SO.SOShipmentStatus.hold>>
// .Or<SOShipment.status.IsEqual<PX.Objects.SO.SOShipmentStatus.open>>>>),
// typeof(SOShipLineSplit.shipmentNbr),
// typeof(SOShipLineSplit.lotSerialNbr))]
public virtual string ToLotSerialNbr { get; set; }
public abstract class toLotSerialNbr : PX.Data.BQL.BqlString.Field<toLotSerialNbr> { }
#endregion

 The error is 

 

If I leave out the .And<SOShipLineSplit.inventoryID.IsEqual<SwapFilter.inventoryID.FromCurrent» it works but not the result I want.

I further want to link this selector to the SOShipments table and only get records where the Shipment is OPEN or HOLD.  That is the second selector that is commented out.  If I can at least get the non commented selector working, I will deal with that when I get there.

I might be able to do this with a SQL View, but I really try not to use them unless I have to.

 

icon

Best answer by darylbowman 23 February 2024, 20:20

View original

4 replies

Userlevel 6
Badge +3

As is usual, I spend hours trying to figure something out and within minutes of posting a cry for help, I found a way to get it to work.

I think I found that you cannot use “FromCurrent” in a selector.

I used PXRestrictor to restrict the result set.

This is the code that works.

[PXSelector(typeof(Search2<SOShipLineSplit.lotSerialNbr,
    InnerJoin<SOShipment, On<SOShipment.shipmentNbr.IsEqual<SOShipLineSplit.shipmentNbr>>>,
    Where<SOShipLineSplit.lotSerialNbr.IsNotLike<Empty>>>),
    typeof(SOShipment.shipmentNbr),
    typeof(SOShipLineSplit.lotSerialNbr))]
[PXRestrictor(typeof(Where<SOShipLineSplit.inventoryID.IsEqual<SwapFilter.inventoryID.FromCurrent>>), null)]
[PXRestrictor(typeof(Where<SOShipment.status.IsEqual<PX.Objects.SO.SOShipmentStatus.hold>
    .Or<SOShipment.status.IsEqual<PX.Objects.SO.SOShipmentStatus.open>>>), null)]
 

Badge +11

Try this:

[PXSelector(typeof(SearchFor<SOShipLineSplit.lotSerialNbr>.
In<SelectFrom<SOShipLineSplit>.
Where<SOShipLineSplit.lotSerialNbr.IsNotLike<Empty>.
And<SOShipLineSplit.inventoryID.IsEqual<SwapFilter.inventoryID.FromCurrent>>>>),
typeof(SOShipLineSplit.shipmentNbr),
typeof(SOShipLineSplit.lotSerialNbr))]

 

Userlevel 6
Badge +3

Thanks @darylbowman!!!  That works!  I’ve never seen or used “In<” before.  Is that basically like a “sub query”?

Appreciate the quick assist.  My selector i just posted does work, but I like yours better.  I’ll just tailor it to add a couple more where restrictions.

Now, if I can only get the selector on the form to update based on the current row in the grid!  I’m digging through the web for that one.  If I cannot get it to work, looks like I will be posting ANOTHER cry for help.

Badge +11

I’ve never seen or used “In<” before.  Is that basically like a “sub query”?

I believe it’s the F-BQL version of Search<>

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