Solved

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

D
Rank
Userlevel 1
Badge

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
  }
}

 

 

icon

Best answer by Naveen Boga 21 April 2023, 13:12

View original

14 replies

D
Rank
Userlevel 1
Badge

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

D
Rank
Userlevel 7
Badge +5

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. :)

D
Rank
Userlevel 1
Badge

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)
        {
            ???
        }


    }
  }
}

 

D
Rank
Userlevel 7
Badge +5

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;
}

 

D
Rank
Userlevel 1
Badge

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;
      }

    }
  }
}

 

D
Rank
Userlevel 1
Badge

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
Rank
Userlevel 7
Badge +9

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)
 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
D
Rank
Userlevel 1
Badge

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

 

aaghaei
Rank
Userlevel 7
Badge +9

Yeah I noticed they are posted at the same time

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +9

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

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
D
Rank
Userlevel 1
Badge

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
Rank
Userlevel 7
Badge +17

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;
                }
            }
        }

 

Naveen B
D
Rank
Userlevel 1
Badge

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
Rank
Userlevel 7
Badge +17

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

Naveen B

Reply


Terms & ConditionsCookie settings
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