Solved

PXSelectBase command with dynamic values in a delegate on a processing screen

  • 23 October 2023
  • 9 replies
  • 91 views

Userlevel 6
Badge +3

I know I just asked a similar question recently, but I am still having an issue.  I really need to be able to use a dynamic value in the PXSelectBase command.

In this code, I am overriding a PXSelectBase command. (public delegate PXSelectBase GetShipmentsSelectCommandDelegate(SOShipmentFilter filter);)  One of my overrides works, but the one with the GroupBy / Having clause fails.

This one works.  Note that the And is pulling from the ICSProcShipPref.QtyLessThan field in my custom table. If I change the value in the table, the result set comes back as I would expect.  This allows me to use a dynamic amount to be selected based on the value in the table at run time (not have to use a constant).  

cmd = new
SelectFrom<SOShipment>.
InnerJoin<INSite>.On<SOShipment.FK.Site>.
InnerJoin<Customer>.On<SOShipment.customerID.IsEqual<Customer.bAccountID>>.SingleTableOnly.
InnerJoin<SOOrderShipment>.On<SOOrderShipment.shipmentNbr.IsEqual<SOShipment.shipmentNbr>>.SingleTableOnly.
LeftJoin<Carrier>.On<SOShipment.FK.Carrier>.
//LeftJoin<ICSProcShipPref>.On<SOShipment.FK.Carrier>.
Where<
SOShipment.confirmed.IsEqual<True>.
And<Match<Customer, AccessInfo.userName.FromCurrent>>.
And<Match<INSite, AccessInfo.userName.FromCurrent>>.
And<Exists<
SelectFrom<SOOrderShipment>.
Where<
SOOrderShipment.shipmentNbr.IsEqual<SOShipment.shipmentNbr>.
And<SOOrderShipment.shipmentType.IsEqual<SOShipment.shipmentType>>.
And<SOOrderShipment.invoiceNbr.IsNull>.
And<SOOrderShipment.createARDoc.IsEqual<True>>>>>.
And<SOOrderShipment.shipmentQty.IsLess<ICSProcShipPref.qtyLessThan.FromCurrent>>>.
View(Base);
break;

In this statement, I am doing something very similar.  I am adding a join to the SOShipLine and SOLine tables to get a count of the lines in the SOLine table for the current shipment.

cmd = new
SelectFrom<SOShipment>.
InnerJoin<INSite>.On<SOShipment.FK.Site>.
InnerJoin<Customer>.On<SOShipment.customerID.IsEqual<Customer.bAccountID>>.SingleTableOnly.
InnerJoin<SOShipLine>.On<SOShipLine.shipmentNbr.IsEqual<SOShipment.shipmentNbr>>.
InnerJoin<SOLine>.On<SOLine.orderNbr.IsEqual<SOShipLine.origOrderNbr>.
And<SOLine.orderType.IsEqual<SOShipLine.origOrderType>>.
And<SOLine.lineNbr.IsEqual<SOShipLine.origLineNbr>>>.
LeftJoin<Carrier>.On<SOShipment.FK.Carrier>.
//LeftJoin<ICSProcShipPref>.On<SOShipment.FK.Carrier>.
Where<
SOShipment.confirmed.IsEqual<True>.
And<Match<Customer, AccessInfo.userName.FromCurrent>>.
And<Match<INSite, AccessInfo.userName.FromCurrent>>.
And<Exists<
SelectFrom<SOOrderShipment>.
Where<
SOOrderShipment.shipmentNbr.IsEqual<SOShipment.shipmentNbr>.
And<SOOrderShipment.shipmentType.IsEqual<SOShipment.shipmentType>>.
And<SOOrderShipment.invoiceNbr.IsNull>.
And<SOOrderShipment.createARDoc.IsEqual<True>>>>>>.
AggregateTo<GroupBy<SOLine.orderNbr, Count<SOLine.orderNbr>>>.
Having<Count.IsGreater<ICSProcShipPref.pickMedium.FromCurrent>>.
//Having<Count.IsGreater<Ten>>.
View(Base);

If I use Having<Count.IsGreater<Ten>>. it works.  This is hard coding 10 as a constant.

If I use Having<Count.IsGreater<ICSProcShipPref.pickMedium.FromCurrent>>. I get an error at run time.

I am able to use my custom table to pull the ICSProcShipPref.qtyLessThan.FromCurrent in the first example.  Why doesn’t it work in my Having clause when I try to do the same thing by pulling from ICSProcShipPref.pickMedium.FromCurrent?  

Just to be clear, if I use a constant Ten, it works fine.  

I’ve tried adding a Usr field (extension) to the filter and using that instead of my custom table, but I get the same error.

Here are the DAC fields.  I think they look perfect, but maybe I’ve been staring at this too long.

		#region QtyLessThan
[PXDBDecimal()]
[PXUIField(DisplayName = "Quantity Less Than")]
public virtual Decimal? QtyLessThan { get; set; }
public abstract class qtyLessThan : PX.Data.BQL.BqlDecimal.Field<qtyLessThan> { }
#endregion

#region PickMedium
[PXDBInt]
[PXUIField(DisplayName = "SO Line Count Greater Than")]
public virtual int? PickMedium { get; set; }
public abstract class pickMedium : PX.Data.BQL.BqlInt.Field<pickMedium> { }
#endregion

 

This is baffling me.

 

icon

Best answer by Mike Gifford 24 October 2023, 18:42

View original

9 replies

Userlevel 6
Badge +3

@Mike Gifford You are the hero of the day!

Your fix worked!  I had the Count<SOLine.OrderNbr> wrong.  It should have been as shown below.  I didn’t notice that because I was getting a run time error and I never got to see the results on the screen.  That was an easy fix.

After making these changes, it works as expected.

AggregateTo<GroupBy<SOLine.orderNbr, Count<SOLine.lineNbr>>>.
Having<SOLine.lineNbr.Counted.IsGreater<ICSProcShipPref.pickMedium.FromCurrent>>.

This was my first attempt at using Having in a select statement.  I will keep this for future reference for sure!

Good bye VIEWS!  Thank you again!

Userlevel 4
Badge

I dont have the data to test to see if this makes a difference at runtime, but I think the having/count syntax was a bit off:

Having<SOLine.orderNbr.Counted.IsGreater<ICSProcShipPref.pickMedium.FromCurrent>>.

Userlevel 6
Badge +3

If you really want to dig into it, I’ve included a zip file of the VS code.  Starting on Line 90 of SOInvoiceShipment.cs is where I am attempting this.  Just uncomment that section and comment the production section and you will see the error.

To make the file smaller, I did not include the Bin folder.  If you need the Bin folder to open the project, I can zip it up but it is pretty big.

Userlevel 4
Badge

Now I am really curious, lol  - can you post the current FBQL? I want to tinker with it, because I know at some point, I will run into this!

Userlevel 6
Badge +3

It doesn’t compile without FromCurrent.  I did try that.  I feel like I tried everything…  Ha!

I even tried joining the custom setup table to the command but there is no key in the setup table to join to.

Thanks for trying though.  I appreciate the time and effort.

Userlevel 4
Badge

I think in our case we did have BQL vs FBQL - might be the issue? Not sure.

 

Also have you tried to reference the field without any “current” or fromCurrent indicators?

Userlevel 6
Badge +3

@Mike Gifford 

I get a red squiggly.  I tried the following to see if I could get the syntax right.  Does that CurrentValue<> have to be used in BQL and not BQL Fluent?

The error is this:

Do I need to do some kind of “Cast”?

 

Thanks for the tip!  

 

Userlevel 4
Badge

We ran into an issue where .fromCurrent and Current<> would not supply the right value and we had to use CurrentValue<> instead - in our case it was when trying to Filter a PXProjection with a filter DAC - but the symptoms sound familiar - worth a try.

Userlevel 6
Badge +3

I found a way to do it.  I created a SQL view to handle this.

If anyone has a way to solve this without a View, that would be great.  

Here is my SQL view

CREATE VIEW ICSPickMediumView
AS
    Select 
        SOShipment.CompanyID,
        SOShipment.ShipmentNbr,
        SOLine.orderNbr,
        Count(SOLine.LineNbr) As LineCount,
        ICSProcShipPref.PickMedium
    From
        SOShipment
        Inner Join
        SOShipLine
            On SOShipLine.shipmentNbr = SOShipment.shipmentNbr
            AND SOShipLine.CompanyID = SOShipment.CompanyID
        Inner Join
        SOLine 
            On SOLine.orderNbr = SOShipLine.origOrderNbr
            And SOLine.orderType = SOShipLine.origOrderType
            And SOLine.lineNbr = SOShipLine.origLineNbr
            AND SOLine.CompanyID = SOShipment.CompanyID
        Inner Join
        ICSProcShipPref 
            On ICSProcShipPref.CompanyID = SOShipment.CompanyID
    --WHERE
    --    SOShipment.CompanyID = 2
    --    AND SOShipment.ShipmentNbr = 'SHP23003390'
    Group By
        SOShipment.ShipmentNbr,
        SOLine.orderNbr, 
        ICSProcShipPref.PickMedium, 
        SOShipment.CompanyID
    Having
        Count(SOLine.LineNbr) > ICSProcShipPref.PickMedium
GO

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