Question

Sort Labor Item from Timecard By Department subaccount

  • 29 September 2023
  • 6 replies
  • 82 views

Hi Experts,

I'm facing a challenge with this customization to sort Labor Items on the Time Card Entry screen. The goal is to default an employee's department-specific labor items to the top of the list, with the remaining items following.

I've written the following code for this purpose, but it doesn't seem to be sorting the items as expected. Interestingly, when I tried using an SQL query, it worked as intended.

Code Snippets.

using PX.Data;
using PX.Objects.IN;
using PX.Objects.PM;
using static PX.Objects.AP.APGenerateIntercompanyBillsFilter;
using static PX.Objects.CR.CRCaseClassLaborMatrix;

namespace PX.Objects.EP
{
[PXNonInstantiatedExtension]
public class EP_TimeCardMaint_EPTimeCardSummaryWithInfo_ExistingColumn : PXCacheExtension<PX.Objects.EP.TimeCardMaint.EPTimeCardSummaryWithInfo>
{
#region LabourItemID
[PMLaborItem(typeof(projectID), typeof(earningType), typeof(Select<EPEmployee, Where<EPEmployee.bAccountID, Equal<Current<EPTimeCard.employeeID>>>>))]
[PXSelector(typeof(Search2<InventoryItem.inventoryID,
LeftJoin<EPDepartment,
On<InventoryItem.cOGSSubID, Equal<EPDepartment.expenseSubID>,
Or<EPDepartment.expenseSubID, NotEqual<InventoryItem.cOGSSubID>>>,
LeftJoin<EPEmployee,
On<EPEmployee.departmentID, Equal<EPDepartment.departmentID>>>>,
Where<InventoryItem.itemType, Equal<INItemTypes.laborItem>,
And<EPEmployee.bAccountID, Equal<Current<EPTimeCard.employeeID>>>>,
OrderBy<Desc<Case<Where<EPDepartment.expenseSubID, Equal<InventoryItem.cOGSSubID>>, EPDepartment.expenseSubID>>>>
),
SubstituteKey = typeof(InventoryItem.inventoryCD),
DescriptionField = typeof(InventoryItem.descr)
)]
[PXCustomizeSelectorColumns(
typeof(PX.Objects.IN.InventoryItem.inventoryCD),
typeof(PX.Objects.IN.InventoryItem.descr),
typeof(PX.Objects.IN.InventoryItem.itemClassID),
typeof(PX.Objects.IN.InventoryItem.itemStatus),
typeof(PX.Objects.IN.InventoryItem.itemType),
typeof(PX.Objects.IN.InventoryItem.baseUnit),
typeof(PX.Objects.IN.InventoryItem.salesUnit),
typeof(PX.Objects.IN.InventoryItem.purchaseUnit),
typeof(PX.Objects.IN.InventoryItem.basePrice))]
public int? LabourItemID { get; set; }
#endregion
}
}

SQL Query I used which Work expected.

SELECT
ii.inventoryID,
ii.inventoryCD AS SubstituteKey,
ii.descr AS DescriptionField,
ii.itemClassID,
ii.itemStatus,
ii.itemType,
ii.baseUnit,
ii.salesUnit,
ii.purchaseUnit,
ii.basePrice
FROM
InventoryItem AS ii
LEFT JOIN
EPDepartment AS dep
ON
(ii.cOGSSubID = dep.expenseSubID OR dep.expenseSubID <> ii.cOGSSubID)
LEFT JOIN
EPEmployee AS emp
ON
emp.departmentID = dep.departmentID
WHERE
ii.itemType = 'L'
AND emp.baccountid =2892
ORDER BY
CASE
WHEN dep.expenseSubID = ii.cOGSSubID THEN dep.expenseSubID
END DESC;


Any help on this will greatly appriciated.

Thank you!!!


6 replies

Userlevel 6
Badge +3

I tried to use a PXProjection with a redefined InventoryCD, I tried to remove the key from InventoryCD, but they didn’t help at all, the Grid on the Screen always had the default sort order on it.

Userlevel 4
Badge +1

I took a look at this and was also unable to get the above code to perform the sorting correctly.

According to this page : https://stackoverflow.com/questions/47205078/modify-selector-default-sorting

And also this one : https://stackoverflow.com/questions/47205078/modify-selector-default-sorting

 I believe the sorting is done after the GetRecords values are returned, so it doesn't matter what order we return the records in.

I tried some hacky workarounds with Custom Selectors and adding ‘AA-’ to the start of inventory items CD so that the natural sorting would give you the desired result. That did not work. Another possibility might be using a SQL view to get the records in the order you want, but I don’t think you can pass the current employee ID to a view so I’d say that is a dead end also. Overall It does not look like what you are trying to do is supported. That said I wouldn't be surprised if there was a way to do this with the new UI features they are rolling out.

Best of luck!

Hi @Zoltan Febert,

Thanks, I've checked the code provided with the custom attribute, but unfortunately, it hasn't made any changes to the result. It still sorts by Inventory CD.

 

Userlevel 6
Badge +3

Hi @KumarDighe,

You can try to use a Custom Selector Attribute and override the view delegate. I hope it works.

using System;
using System.Collections;
using System.Collections.Generic;
using PX.Data;
using PX.Data.BQL.Fluent;
using PX.Objects.Common;
using PX.Objects.CS;
using PX.Objects.CT;
using PX.Objects.IN;
using PX.Objects.PM;
using static PX.Objects.AP.APGenerateIntercompanyBillsFilter;
using static PX.Objects.CR.CRCaseClassLaborMatrix;

namespace PX.Objects.EP
{
// Acuminator disable once PX1016 ExtensionDoesNotDeclareIsActiveMethod extension should be constantly active
[PXNonInstantiatedExtension]
public class EP_TimeCardMaint_EPTimeCardSummaryWithInfo_ExistingColumn : PXCacheExtension<TimeCardMaint.EPTimeCardSummaryWithInfo>
{
#region LabourItemID
[PMLaborItem(typeof(projectID), typeof(earningType), typeof(Select<EPEmployee, Where<EPEmployee.bAccountID, Equal<Current<EPTimeCard.employeeID>>>>))]
// !!!!!!
[MyCustomAttribute(typeof(Search2<InventoryItem.inventoryID,
LeftJoin<EPDepartment,
On<InventoryItem.cOGSSubID, Equal<EPDepartment.expenseSubID>,
Or<EPDepartment.expenseSubID, NotEqual<InventoryItem.cOGSSubID>>>,
LeftJoin<EPEmployee,
On<EPEmployee.departmentID, Equal<EPDepartment.departmentID>>>>,
Where<InventoryItem.itemType, Equal<INItemTypes.laborItem>,
And<EPEmployee.bAccountID, Equal<Current<EPTimeCard.employeeID>>>>,
OrderBy<Desc<Case<Where<EPDepartment.expenseSubID, Equal<InventoryItem.cOGSSubID>>, EPDepartment.expenseSubID>>>>
),
SubstituteKey = typeof(InventoryItem.inventoryCD),
DescriptionField = typeof(InventoryItem.descr)
)]
[PXCustomizeSelectorColumns(
typeof(InventoryItem.inventoryCD),
typeof(InventoryItem.descr),
typeof(InventoryItem.itemClassID),
typeof(InventoryItem.itemStatus),
typeof(InventoryItem.itemType),
typeof(InventoryItem.baseUnit),
typeof(InventoryItem.salesUnit),
typeof(InventoryItem.purchaseUnit),
typeof(InventoryItem.basePrice))]
public int? LabourItemID { get; set; }
#endregion
}

public class MyCustomAttribute : PXCustomSelectorAttribute
{
public MyCustomAttribute(Type type, params Type[] fieldList) : base(type, fieldList) { }

protected virtual IEnumerable GetRecords()
{
var select = new PXView(_Graph, true, _Select);

var totalRow = 0;
var startRow = PXView.StartRow;
var result = select.Select(PXView.Currents, PXView.Parameters,
PXView.Searches, PXView.SortColumns, PXView.Descendings,
PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRow);
PXView.StartRow = 0;

return result;
}
}
}

 

Hi @Zoltan Febert,

Thank you for your response. Is there any way to sort it by the Inventory CD and Subaccount?
 

Userlevel 6
Badge +3

Hi @KumarDighe, I checked it with SQL Server Profiler, and it looks like the selector popup is always sorted by SubstituteKey, in this case by InventoryCD

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