Solved

Changing default sort order of Data View has odd results

10 months ago
27 May 2023
11 replies
202 views

+11

darylbowman
951 replies

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

aaghaei
Captain II
1005 replies
10 months ago
27 May 2023

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

+11

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

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

Fernando Amadoz
Jr Varsity I
74 replies
10 months ago
7 June 2023

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

+11

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

Hi guys,

The view “APDocumentList” doesn’t use Vendor table:
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;

    }

Result on my local Sales Demo DB:

+11

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.

+11

Bump

+11

This behavior is also present in 23.102

Userlevel 4

Keith Richardson
Varsity II
36 replies
8 months ago
8 August 2023
Answer

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

+11

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

Userlevel 7

aaghaei
Captain II
1005 replies
8 months ago
8 August 2023

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.

Reply

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded