Skip to main content
Solved

Adding a Column to Sales Order SOLine from a custom SQL View


Forum|alt.badge.img

Ive developed a custom SQL view and associated DAC which generates a table of available quantities of stock kits.

Im trying to show the Qty in the Sales Order by adding PXDBScalar to the SOLine DAC detail screen however I keep getting null? 

 

namespace AutoKitAssembly
{
  public class SOLineExt : PXCacheExtension<PX.Objects.SO.SOLine>
  {
        #region UsrKitQtyAvail
        [PXDefault(TypeCode.Decimal, "0.0")]
        [PXDBScalar(typeof(Search<KitAssy.qtyAvail,
            Where<KitAssy.builtItemID, Equal<Current<SOLine.inventoryID>>,
                And<KitAssy.siteId, Equal<Current<SOLine.siteID>>>>>))]
        [PXUIField(DisplayName = "Kit Qty Available")]

        public virtual int? UsrKitQtyAvail{ get; set; }
        public abstract class usrKitQtyAvail: PX.Data.BQL.BqlInt.Field<usrKitQtyAvail> { }
        #endregion
  }
}

 

 

Best answer by Naveen Boga

Hi, @Dantheman88988  I don’t see any issues with the above code.

But still, I made minor changes to your code. Can you please check with the below code once and confirm?

 protected void SOLine_SiteID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e, PXFieldUpdated InvokeBaseHandler)
        {
            InvokeBaseHandler?.Invoke(cache, e); 
            var row = (SOLine)e.Row;
            if (row != null)
            {
                SOLineExt soLineExt = row.GetExtension<SOLineExt>();

                //Get the KitAssy record
                AutoKitAssembly.KitAssy kitAssy = PXSelectReadonly<AutoKitAssembly.KitAssy,
                                                  Where<AutoKitAssembly.KitAssy.builtItemID, Equal<Required<AutoKitAssembly.KitAssy.builtItemID>>,
                                                  And<AutoKitAssembly.KitAssy.siteId, Equal<Required<AutoKitAssembly.KitAssy.siteId>>>>>
                                                  .Select(Base, row.InventoryID, row.SiteID);
                if (kitAssy != null)
                {
                    soLineExt.UsrKitQtyAvail = (kitAssy.QtyAvail ?? 0m);
                }
                else
                {
                    soLineExt.UsrKitQtyAvail = null;
                }
            }
        }

 

View original
Did this topic help you find an answer to your question?

14 replies

Forum|alt.badge.img

Update: Have fixed this by removing the current. However now field does not update when i change the warehouse location?


Forum|alt.badge.img+5
  • Captain II
  • 483 replies
  • April 20, 2023

I could be wrong in how I’m approaching these fields but I find that I have to force the calculation on PXDBScalar when fields that the calculation depends on change. My this I mean that in addition to the PXDBScalar attribute that I would, in your case, add an override to the existing SOLine_SiteID_FieldUpdated event to lookup the value and populate your UsrKitQtyAvail field.

Generally, once the record is saved then the calculation does what I want it to do, but before that point, I have to duplicate the lookup when fields that are part of the calculation changes.

I would love to be proven wrong. :)


Forum|alt.badge.img

Thanks though I have no idea how to do that?

namespace PX.Objects.SO
{
  public class SOOrderEntry_Extension : PXGraphExtension<PX.Objects.SO.SOOrderEntry>
  {
    protected virtual void InventoryID_FieldUpdated(PXCache sender, PXFieldUpdatedEventArgs e)
    {
        SOLine row = e.Row;
        if ( row!= null && row.InventoryID != null)
        {
            ???
        }


    }
  }
}

 


Forum|alt.badge.img+5
  • Captain II
  • 483 replies
  • April 21, 2023

You’re very close.  You’re going to add something like (untested):


SOLineExt soLineExt = row.GetExtension<SOLineExt>();

//Get the KitAssy record
KitAssy kitAssy = PXSelect<KitAssy,
  Where<KitAssy.builtItemID, Equal<Required<KitAssy.builtItemID>>,
  And<KitAssy.siteId, Equal<Required<KitAssy.siteId>>>>>
  .Select(aGraphObject, SOLine.InventoryID, SOLine.SiteID);

if (kitAssy != null) 
{
  soLineExt.UsrKitQtyAvail = kitAssy.QtyAvail;
}

 


Forum|alt.badge.img

Thanks a lot I think Im closer? Heres my code with the compile error i cant figure out

namespace PX.Objects.SO
{
  public class SOOrderEntry_Extension : PXGraphExtension<PX.Objects.SO.SOOrderEntry>
  {
    protected virtual void InventoryID_FieldUpdated(PXCache sender, PXFieldUpdatedEventArgs e)
    {

      SOLine row = (SOLine)e.Row;
      SOLineExt soLineExt = row.GetExtension<SOLineExt>();

//Get the KitAssy record
      AutoKitAssembly.KitAssy kitAssy = PXSelect<AutoKitAssembly.KitAssy,
        Where<AutoKitAssembly.KitAssy.builtItemID, Equal<Required<AutoKitAssembly.KitAssy.builtItemID>>,
        And<AutoKitAssembly.KitAssy.siteId, Equal<Required<AutoKitAssembly.KitAssy.siteId>>>>>
        .Select(InventoryID_FieldUpdated, SOLine.InventoryID, SOLine.SiteID);

      if (kitAssy != null)
      {
        soLineExt.UsrKitQtyAvail = kitAssy.QtyAvail;
      }

    }
  }
}

 


Forum|alt.badge.img

Update. I can get this code to assemble however it doesnt seem to update the row correctly. each time i change the site or inventory it just goes to zero? Loading a item however still works..

   protected void SOLine_InventoryID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e)
    {

      var row = (SOLine)e.Row;
            SOLineExt soLineExt = row.GetExtension<SOLineExt>();

//Get the KitAssy record
      AutoKitAssembly.KitAssy kitAssy = PXSelect<AutoKitAssembly.KitAssy,
        Where<AutoKitAssembly.KitAssy.builtItemID, Equal<Required<AutoKitAssembly.KitAssy.builtItemID>>,
        And<AutoKitAssembly.KitAssy.siteId, Equal<Required<AutoKitAssembly.KitAssy.siteId>>>>>
        .Select(this.Base, row.InventoryID, row.SiteID);

      if (kitAssy != null)
      {
        soLineExt.UsrKitQtyAvail = kitAssy.QtyAvail;
       }
      else if (kitAssy == null)
        {
          soLineExt.UsrKitQtyAvail = 0;
        }
    }

 


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1176 replies
  • April 21, 2023

Change 

protected virtual void InventoryID_FieldUpdated(PXCache sender, PXFieldUpdatedEventArgs e) 

to 

protected virtual void SOLine_InventoryID_FieldUpdated(PXCache sender, PXFieldUpdatedEventArgs e) 

or

protected virtual void _(Events.FieldUpdated<SOLine, SOLine.inventoryID> e)
 


Forum|alt.badge.img

Hi aaghaei, yep I did this already could you check my post just above?

 


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1176 replies
  • April 21, 2023

Yeah I noticed they are posted at the same time


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1176 replies
  • April 21, 2023

Is CommitChange for the field is set to True in your page?


Forum|alt.badge.img

Yes CommitChange for both fields (InventoryID and SiteID are true). I almost have it working. When I change the inventoryID the scalar field recalculates each time all good! However when I change the Warehouse location it changes only once and then never again? If I scroll to the next record then back it shows the correct quantity?

 protected void SOLine_SiteID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e)
    {

      var row = (SOLine)e.Row;
                        SOLineExt soLineExt = row.GetExtension<SOLineExt>();

//Get the KitAssy record
      AutoKitAssembly.KitAssy kitAssy = PXSelect<AutoKitAssembly.KitAssy,
        Where<AutoKitAssembly.KitAssy.builtItemID, Equal<Required<AutoKitAssembly.KitAssy.builtItemID>>,
        And<AutoKitAssembly.KitAssy.siteId, Equal<Required<AutoKitAssembly.KitAssy.siteId>>>>>
        .Select(Base, row.InventoryID, row.SiteID);


      if (kitAssy != null)
      {
        soLineExt.UsrKitQtyAvail = kitAssy.QtyAvail;


      }

      else if (kitAssy == null)
        {
          soLineExt.UsrKitQtyAvail = null;
        }

    }

When record first loads (below is correct):

change warehouse to wholesale (below is correct)

change warehouse back to retail (below is incorrect, should be 100 per first image)

 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • Answer
  • April 21, 2023

Hi, @Dantheman88988  I don’t see any issues with the above code.

But still, I made minor changes to your code. Can you please check with the below code once and confirm?

 protected void SOLine_SiteID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e, PXFieldUpdated InvokeBaseHandler)
        {
            InvokeBaseHandler?.Invoke(cache, e); 
            var row = (SOLine)e.Row;
            if (row != null)
            {
                SOLineExt soLineExt = row.GetExtension<SOLineExt>();

                //Get the KitAssy record
                AutoKitAssembly.KitAssy kitAssy = PXSelectReadonly<AutoKitAssembly.KitAssy,
                                                  Where<AutoKitAssembly.KitAssy.builtItemID, Equal<Required<AutoKitAssembly.KitAssy.builtItemID>>,
                                                  And<AutoKitAssembly.KitAssy.siteId, Equal<Required<AutoKitAssembly.KitAssy.siteId>>>>>
                                                  .Select(Base, row.InventoryID, row.SiteID);
                if (kitAssy != null)
                {
                    soLineExt.UsrKitQtyAvail = (kitAssy.QtyAvail ?? 0m);
                }
                else
                {
                    soLineExt.UsrKitQtyAvail = null;
                }
            }
        }

 


Forum|alt.badge.img

Hi Naveen,

 

That worked perfectly thanks so very much Ive been working on this all day!!!!

 

I have 2 other tasks to do which could you point me in the right direction:

  1. I want to add a new grid tab called “Kit Detail” to show the kit components of the row selected in the detail tab.
  2. I need a dropbox box/selector user input field (Auto Build Amount) which allows the user to select a integer (0,1,2,3...) but only up to the Qty on the sales order line. It should update the combobox/selector if the quantity is changed too?

 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • April 21, 2023

Hi @Dantheman88988  Please find the details below hope that helps!

 

  1. I want to add a new grid tab called “Kit Detail” to show the kit components of the row selected in the detail tab.

[Naveen] Create a new grid and add a Master-Detail relation with SOLine grid (Master) and your grid (Detail grid) so that based on the selected KIT item, it will show its components in the new grid.

Here is link the for more info - https://asiablog.acumatica.com/2019/04/grids-master-detail-relationship.html

  1. I need a dropbox box/selector user input field (Auto Build Amount) which allows the user to select a integer (0,1,2,3...) but only up to the Qty on the sales order line. It should update the combobox/selector if the quantity is changed too?

[Naveen]  Since you wanted to build the Amount automatically, you can write a field updated events for Qty and UnitPrice fields, so that when you change these fields, accordingly will update the new field with the amount value.

 

Hope this info helps!!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings