Solved

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

  • 23 October 2023
  • 9 replies
  • 83 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

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

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

@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

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

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

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

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

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

@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!

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