Solved

Changing default sort order of Data View has odd results


Badge +10

I am trying to change the default sort order of the details on Approve Bills for Payment. I’ve read these articles:

The APApproveBills graph has a data view and a data view delegate, but according to the above articles, it seems like the following code should achieve what I need:

namespace PX.Objects.AP
{
public class APApproveBills_Extension : PXGraphExtension<PX.Objects.AP.APApproveBills>
{
public override void Initialize()
{
Base.APDocumentList.OrderByNew<OrderBy<Vendor.acctName.Asc>>();
}
}
}

This does change the sort, but the result is not what I am expecting.

 

Can someone explain why this is happening and/or how to fix it?

icon

Best answer by Keith Richardson 8 August 2023, 16:13

View original

11 replies

Userlevel 7
Badge +8

@darylbowman when you have Delegate, it overrides all predecessors which includes initialization.

you will need to override the delegate. If you didn’t  have Delegate then initialization override would do the work. 

Badge +10

I suspected this may be the case. So I overrode the data view and delegate like this, including copying a private method (not shown) which actually returns the data, like this (summary below): 

namespace PX.Objects.AP
{
public class APApproveBills_Extension : PXGraphExtension<APApproveBills>
{
[PXFilterable]
public PXSelect<APInvoice> APDocumentList;

protected virtual IEnumerable apdocumentlist()
{
ApproveBillsFilter filter = Base.Filter.Current;

if (filter != null && filter.SelectionDate != null)
{
DateTime PayInLessThan = ((DateTime)filter.SelectionDate).AddDays(filter.PayInLessThan.GetValueOrDefault());
DateTime DueInLessThan = ((DateTime)filter.SelectionDate).AddDays(filter.DueInLessThan.GetValueOrDefault());
DateTime DiscountExpiresInLessThan = ((DateTime)filter.SelectionDate).AddDays(filter.DiscountExpiresInLessThan.GetValueOrDefault());

//PXView view = new PXView(Base, false, BqlCommand.CreateInstance(getAPDocumentSelect(false)));
BqlCommand command = BqlCommand.CreateInstance(getAPDocumentSelect(false));
command.OrderByNew<OrderBy<Vendor.acctCD.Asc>>();
PXView view = new PXView(Base, false, command);

foreach (PXResult<APInvoice> res in
view.Graph.QuickSelect(view.BqlSelect, new object[] { PayInLessThan, DueInLessThan, DiscountExpiresInLessThan }, null, false))
{
APInvoice apdoc = res;

if (string.IsNullOrEmpty(apdoc.PayTypeID))
{
try
{
APDocumentList.Cache.SetDefaultExt<APInvoice.payTypeID>(apdoc);
}
catch (PXSetPropertyException e)
{
APDocumentList.Cache.RaiseExceptionHandling<APInvoice.payTypeID>(apdoc, apdoc.PayTypeID, e);
}
}

if (apdoc.PayAccountID == null)
{
try
{
APDocumentList.Cache.SetDefaultExt<APInvoice.payAccountID>(apdoc);
}
catch (PXSetPropertyException e)
{
APDocumentList.Cache.RaiseExceptionHandling<APInvoice.payAccountID>(apdoc, apdoc.PayAccountID, e);
}
}

yield return apdoc;
}
}
}
}
}

 

I changed

PXView view = new PXView(Base, false, BqlCommand.CreateInstance(getAPDocumentSelect(false)));

to

BqlCommand command = BqlCommand.CreateInstance(getAPDocumentSelect(false));
command.OrderByNew<OrderBy<Vendor.acctCD.Asc>>();
PXView view = new PXView(Base, false, command);

which I believe should completely change the sort. Yet, the result is the same as simply changing the sort in the Initialize method, which is fantastically easier, but the result is still incorrect.

Have I done this wrong in some way?

@Nayan Mansinha @slesin 

Userlevel 5
Badge +2

@darylbowman AFAIK, you will need to override the delegate if it’s originally defined in the main logic (as you are doing it), but it will be the actual Data view’s orderby the one that will define the order.

 

So try this:

  • Remove the logic from the Initialize() method.
  • Define your overridden data view + delegate like this:
		[PXFilterable]
public PXSelectOrderBy<APInvoice, OrderBy<Asc<APInvoice.[yourSortField]>>> APDocumentList;

protected virtual IEnumerable apdocumentlist()
{
(...) //your delegate definition
}

 

Badge +10

Thank you for your reply. 

Unfortunately, the result is the same. The sort is applied in some way, but not like I would expect:

 

Userlevel 5
Badge +1

Hi guys,

  1. The view “APDocumentList” doesn’t use Vendor table:
  2. fix in graph extension:
   public class APApproveBillsExt : PXGraphExtension<PX.Objects.AP.APApproveBills>
{
public static bool IsActive() => true;

[PXFilterable]
public SelectFrom<APInvoice>.InnerJoin<BAccount>.On<APInvoice.vendorID.IsEqual<BAccount.bAccountID>>.OrderBy<BAccount.acctName.Asc>.View APDocumentList;

}
  1. Result on my local Sales Demo DB:

 

Badge +10

I guess this makes sense. Vendor is joined in the data view delegate, which I guess is why I thought I would be able to do that.

I tried it, with the data view delegate also defined, and the result was the same:

 

I realized that you hadn’t mentioned the data view delegate, so I removed that and tried again. It sorted the list fantastically. However, without the data view delegate, the whole APInvoice table is returned, which is not correct. The data view delegate is necessary to return the correct results.

So yet one more way to get a very close (and obviously working), but not correct result.

 

Also, since the result is exactly the same as my original attempt, it stands to reason that you can actually sort by tables that are not explicitly joined in the data view.

Badge +10

Bump

Badge +10

This behavior is also present in 23.102

Userlevel 4
Badge +2

I was able to get this accomplished through a DAC extension adding the Vendor.AcctCD to the APInvoice, and then using your initial idea to sort by that new field. It works with the filters!

 


public class APInvoiceApproveBillsExt : PXCacheExtension<APInvoice>
{

#region UsrVendorCD
[PXString]
[PXFormula(typeof(Selector<APInvoice.vendorID, Vendor.acctCD>))]
[PXUIField(DisplayName = "Vendor")]
public virtual String UsrVendorCD { get; set; }
public abstract class usrVendorCD : PX.Data.BQL.BqlString.Field<usrVendorCD> { }
#endregion
}
public class APApproveBillsExt : PXGraphExtension<APApproveBills>
{
public override void Initialize()
{
Base.APDocumentList.OrderByNew<OrderBy<APInvoiceApproveBillsExt.usrVendorCD.Asc>>();
}
}

 

Badge +10

Fantastically simple solution that I would not have thought of otherwise. Thank you!

Userlevel 7
Badge +8

Thank you for sharing your solution with the community @Keith Richardson 

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