Improve performance of grouping operations in Acumatica

Hello everybody,

today I want to write a note about interesting case I faced recently. 

I was asked to load from Acumatica database Sales orders joined with PO Receipts by inventory, but I had one issue. It was needed to join only by latest PO Receipt, not by allo PO Receipts. In other words it was needed to take only one element of each group. 

As usually such kind of implementation is done with help of overloading delegate, which loads all data from SQL Server into memory and then does grouping operaions in memory. Such approach is workable on relatively small cases, but if database of customer 

is around 130 Gb, then we faced timeout issue. 

How to deal with it. 

After some trial and error I made interesting discovery. If to create view in terms of MS SQL view, and create corresponding DAC Class Acumatica will be able to work with View as it was MS SQL table. 

Then I've decided to create SQL view, with CTE. It looked something like this:

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
create view [dbo].[POReceiptLineView] as
 
	WITH cte AS
	(
	   SELECT *,
			 ROW_NUMBER() OVER (PARTITION BY inventoryid ORDER BY ReceiptDate DESC) AS rn
	   FROM POReceiptLine
	)
	SELECT [CompanyID]
      ,[ReceiptNbr]
      ,[LineNbr]
      ,[SortOrder]
      ,[BranchID]
      ,[InventoryID]
      ,[VendorID]
      ,[ReceiptDate]
      ,[SubItemID]
      ,[SiteID]
      ,[LotSerialNbr]
      ,[POType]
      ,[PONbr]
      ,[POLineNbr]
      ,[POLineComplete]
      ,[UOM]
      ,[ReceiptQty]
      ,[BaseReceiptQty]
      ,[CuryUnitCost]
      ,[UnitCost]
      ,[CuryMultExtCost]
      ,[MultExtCost]
      ,[ManualPrice]
      ,[ManualDisc]
      ,[DiscPct]
      ,[CuryDiscAmt]
      ,[DiscAmt]
      ,[CuryLineAmt]
      ,[LineAmt]
      ,[GroupDiscountRate]
      ,[DocumentDiscountRate]
      ,[DiscountID]
      ,[DiscountSequenceID]
      ,[ReceiptType]
     FROM cte WHERE rn = 1

As you can see nothing fance. Ordinary SQL view with CTE.

And then I've created very simple DAC class:

       [Serializable]
       public class POReceiptLineView : IBqlTable
       {
           public abstract class inventoryID : IBqlField
           {
           }
 
           [POLineInventoryItem(Filterable = true)]
           [PXDefault()]
           [PXForeignReference(typeof(Field<inventoryID>.IsRelatedTo<InventoryItem.inventoryID>))]
           public virtual int? InventoryID
           {
               get;set;
           }
 
           public abstract class receiptType : IBqlField
           {
           }
 
           [PXDBString(IsKey = true)]
           public virtual string ReceiptType
           {
               getset;
           }
 
           public abstract class receiptNbr : IBqlField
           {
           }
 
           [PXDBString(IsKey = true)]
           public virtual string ReceiptNbr
           {
               getset;
           }
 
           public abstract class siteID : IBqlField
           {
           }
 
           [PXDBInt()]
           public virtual int? SiteID
           {
               getset;
           }
 
           public abstract class receiptDate : IBqlField
           {
           }
 
           [PXDBDate()]
           public virtual DateTime? ReceiptDate
           {
               getset;
           }
 
           [PXDBDate()]
           public virtual DateTime? CreatedDateTime
           {
               getset;
           }
 
           #region CuryUnitCost
           public abstract class curyUnitCost : PX.Data.IBqlField
           {
           }
           
 
           [PXDBCurrency(typeof(Search<CommonSetup.decPlPrcCst>), typeof(POReceiptLine.curyInfoID), typeof(POReceiptLine.unitCost))]
           [PXUIField(DisplayName = "Unit Cost", Visibility = PXUIVisibility.SelectorVisible)]
           [PXDefault(TypeCode.Decimal, "0.0")]
           public virtual Decimal? CuryUnitCost
           {
               getset;
           }
           #endregion
 
           public abstract class receiptQty : PX.Data.IBqlField
           {
           }
 
           [PXDBQuantity(typeof(POReceiptLine.uOM), typeof(POReceiptLine.baseReceiptQty), HandleEmptyKey = true, MinValue = 0)]
           [PXDefault(TypeCode.Decimal, "0.0")]
           [PXFormula(nulltypeof(SumCalc<POReceipt.orderQty>))]
           [PXUIField(DisplayName = "Receipt Qty.", Visibility = PXUIVisibility.Visible)]
           public virtual Decimal? ReceiptQty
           {
               get;set;
           }
       }

And finally I've used following BQL query:

var query = new PXSelectJoinGroupBy<SOOrder,
    InnerJoin<SOLineOn<SOOrder.orderNbrEqual<SOLine.orderNbr>, And<SOOrder.orderTypeEqual<SOLine.orderType>>>>,
    Aggregate<GroupBy<SOLine.inventoryIDSum<SOLine.curyLineAmt>>>>(this);
 
query.Join<InnerJoin<POReceiptLineViewOn<POReceiptLineView.inventoryIDEqual<SOLine.inventoryID>>>>();
query.Join<InnerJoin<POReceiptOn<POReceipt.receiptTypeEqual<POReceiptLineView.receiptType>,
                            And<POReceipt.receiptNbrEqual<POReceiptLineView.receiptNbr>>>>>();
 
query.WhereAnd<Where<POReceipt.receiptTypeEqual<POReceiptType.poreceipt>>>();
query.WhereAnd<Where<POReceiptLineView.siteIDEqual<Current<Filter.wareHouse>>>>();

And then executed testing. Try to guess what was performance boost of SQL view with CTE usage in comparison with code which took everything from database? 

Version with CTE view become 20 times faster!

Summary

If you need to make some complicated data massaging with some kind of tricky grouping analysis of data, then instead of loading plenty of staff from Acumatica database use SQL Views. It will help you to speed up your Acumatica drastically. 

One more point worth mentioning is that if you will make complicated DAC class for your view, you'll get errors. Don't try to add plenty of attributes to your DAC Class view. Otherwise Acumatica will refuse to work with your view.

How to notify about errors on Processing screens that are made through Automation schedules

ello everybody,

today I want to leave a short notice on how to send a message from your graph for cases, when your graph is used in processing screen which is implemented through Automation steps. For this purpose you can use new property of PXAdapter class. It has property 

MassProcess. In case if your graph will be executed from processing screen that property will be set to true. Then you'll get a chance to notify processing screen about error with help of SetError message. In that case in your graph you can write something like this:

var currentSoOrder = currentSoOrderBoxed.GetItem<SOOrder>();
string errorMessage = string.Format(WorkFlowMessages.creditLimitExceededTemplate, currentSoOrder.OrderNbr);
try
 {
  ShipmentsTermsValidator.ValidateShipment(Base, currentSoOrder.CustomerID,
                    currentSoOrder.CuryUnpaidBalance, errorMessage, ShipmentsTermsValidator.ShipmentValidation.ShipmentConfirmation);
  }
  catch (Exception e)
  {
     if (adapter.MassProcess)
     {
          PXProcessing<SOShipment>.SetError(e);
     }
     throw e;
  }

In this way you can notify your processing screen.

NullIf and IsNull in Acumatica

Hello everybody,

recently I debugged code of Acumatica, and found following expression:

[PXFormula(typeof (Switch<Case<Where<Add<SOOrder.releasedCntrSOOrder.billedCntr>, 
Equal<PX.Objects.CS.int0>>, SOOrder.curyOrderTotal
Case<Where<Current<SOSetup.freightAllocation>, Equal<FreightAllocationList.prorate>>, 
Add<SOOrder.curyUnbilledOrderTotal
IsNull<Mult<Div<SOOrder.curyUnbilledOrderTotal, NullIf<SOOrder.curyLineTotal, decimal0>>, 
SOOrder.curyFreightTot>, decimal0>>>>, SOOrder.curyUnbilledOrderTotal>))]

I always got scared when I see such kind of expressions. I have a feeling that it was written by somebody who can easily pass zebra puzzle. Unfortunately I'm not such a men. 

While debugging it I needed to have reference for IsNull and NullIf. Below goes description of each one of them:

NullIf<Operand1, Operand2> 

Returns null if Operand1 equals Operand2 and returns Operand1 if the two expression are not equal. Equivalent to SQL function NULLIF.

IsNull<Operand1, Operand2>

Returns Operand1 if it is not null, or Operand2 otherwise.

 

How to allow Deleting of row in RowPersisting method of Acumatica

Hello everybody,

today I want to tell interesting behavior of method RowPeristing in Acumatica graphs. Try to guess, after which actions of user that event is getting called? Save ? Yes. Delete? Also yes! Insert? Also yes. 

If you ask me why? I'll not provide you an anser. But I want to leave a piece of code which describes how to understand which action was called. I'll demonstrate it on sample of SOShipLine, and you'll be able to use it for other DAC classes, with slight modifications.

Here you can see two cases:

protected void SOShipLine_RowPersisting(PXCache sender, PXRowPersistingEventArgs e)
{
    var soShipLine = e.Row as SOShipLine;
    if (sender.GetStatus(soShipLine) == PXEntryStatus.Deleted) // Delete was pressed
        return;
    if (sender.GetStatus(soShipLine) == PXEntryStatus.Inserted) // Insert was pressed
        return;

Why such staff is important? In my case I throwed some exceptions which ought to happen before Shipline can be persisted to database. And not affect other actions. For that purpose I've used method GetStatus

How to use PXUIVisible in Acumatica

Hello everybody,

today I want to leave a short notice on how to use PXUIVisible attribute. I already mentioned that attribute in this article but today just want to give one more example of it's usage.

Take a look on the following code:

[PXDBBool()]
[PXUIField(DisplayName = "Shipment display")]
[PXUIVisible(typeof(Where<Terms.visibleToEqual<TermsVisibleTo.all>, Or<Terms.visibleToEqual<TermsVisibleTo.customer>>>))]
protected void Terms_UsrShipmentDisplay_CacheAttached(PXCache sender)
{
}

As you can see from the example, PXUIvisible expectes from you to get some kind of where condition. Another important detail is that control, from which visibility depends should have CommitChanges set to true.

How to override ExecuteSelect in Acumatica in your graph

Hello everybody,

today I want to leave a short notice on how to override ExecuteSelect method in your own graph for debugging purposes. I found following code useful:

public override IEnumerable ExecuteSelect(string viewName, object[] parameters, object[] searches, 
string[] sortcolumns, bool[] descendings, PXFilterRow[] filters, 
ref int startRow, int maximumRows, ref int totalRows) {     //Here you can analyze what is coming into your select     var result = base.ExecuteSelect(viewName, parameters, searches, sortcolumns, descendings, 
filters, ref startRow, maximumRows, ref totalRows);     //And here you can see what is going out      return result; }

With this approach you can better understand what's going on with views, which view is called first, second. Also you'll be surprised ( maybe ) that selectors will use the method ExecuteSelect as well.

Anyway I want to remind you advice from T200 manual, use method ExecuteSelect only for debugging purposes

What is Min. Markup, Markup, MSRP in Acumatica

Hello everybody,

today I want to leave a note about few abbreviations in Acumatica related to Price/Cost info tab.

Min. Markup % - percentage. In other words that is minimal addition to price which your company adds to cost of buying of something

MSRP - manufacturer suggested retail price

Markup - extra cost added to price.

How to make managers not as sheep

Hi everybody,

today I want to leave this piece of advice that I agree.

As our business grows, it becomes increasingly necessary to delegate responsibility and to encourage men and women to exercise their initiative. This requires considerable tolerance. Those men and women, to whom we delegate authority and responsibility, if they are good people, are going to want to do their jobs in their own way. Mistakes will be made. But if a person is essentially right, the mistakes he or she makes are not as serious in the long run as the mistakes management will make if it undertakes to tell those in authority exactly how they must do their jobs. Management that is destructively critical when mistakes are made kills initiative. And it’s essential that we have many people with initiative if we are to continue to grow. "If you put fences around people, you get sheep. Give the people the room they need."

— William L. McKnight

Default values for inquiry for date in Acumatica

Hello everybody,

today I want to leave a short note on Default value for the inquiry screens for Data type. For date you can use following predefined values:

 

  • @WeekStart and @WeekEnd - as name suggests start and the end of Current week
  • @MonthStart and @MonthEnd - start and the end of Current Month
  • @PeriodStart and @PeriodEnd - start and the end of Current period
  • @QuarterStart and @QuarterEnd - start and the end of Current quarter
  • @YearStart and @YearEnd - start and the end of Current year
  • @Today - today is always today

I hope this short notice will help you better feed dates into your inquiries. 

How to execute action Email Purchase Order in Acumatica and how create PXAdapter

Hello everybody,

today I want to post code sample on how to execute menu item "Email Purchase Order" in Acumatica.

I mean this fragment of Purchase order screen:

If you'll try to find the source code of that Action or menu item, you'll be disappointed because you'll not find it. As some other actions, Email Purchase Order is implemented in Acumatica as combination of Automation steps with C# functions.

Below goes code sample, that allows you to call that method with help of static method:

public static void SendNotificationForGraph(POOrderEntry graph, POOrder order)
{
    graph.Clear(PXClearOption.ClearAll);
    graph.Document.Current = graph.Document.Search<POOrder.orderNbr>(order.OrderNbr, order.OrderType);
    var adapter = new PXAdapter(graph.Document);
    adapter.SortColumns = new string[] { typeof(POOrder.orderNbr).Name };
    adapter.Searches = new object[] { order.OrderNbr };
    adapter.Menu = "Email Purchase Order";
    adapter.Arguments = new Dictionary<stringobject>();
    adapter.MaximumRows = 1;
    List<object> list = new List<object>();
    foreach (var r in graph.action.Press(adapter))
    {
        list.Add(r);
    }
}

Another very interesting part of that code is that it shows how to create instance of PXAdapter class in Acumatica.