Solved

Filter ReplenishmentItem by new Multiple Selector

  • 8 February 2023
  • 14 replies
  • 107 views

Userlevel 2
Badge

Hello experts,

I created a Multi Selector on Prepare Replenishment and I want to filter item in Grid ReplenishmentItem by this field

Here is the field schema:

    public class INReplenishmentFilterExt : PXCacheExtension<PX.Objects.IN.INReplenishmentFilter>
{
#region UsrWarehouse
[PXSelector(typeof(INSite.siteCD), typeof(INSite.siteCD), typeof(INSite.descr), ValidateValue = false, DescriptionField = typeof(INSite.siteCD))]
[PXUIField(DisplayName = "Warehouse")]

public virtual string UsrWarehouse { get; set; }
public abstract class usrWarehouse : PX.Data.BQL.BqlString.Field<usrWarehouse> { }
#endregion
}

I update Aspx add following code:

  <px:PXMultiSelector runat="server" ID="CstPXSelector2" DataField="UsrWarehouse" CommitChanges="True" />

Then I modified graph INReplenishmentCreate, change the filter condition as following:

[PXFilterable]
public Processing<INReplenishmentItem> Records;
//If update Acmatica to later version need to check this function to update it to latest code from Acumatica (keep use INReplenishmentItem.siteID in INReplenishmentFilterExt.usrWarehouse
public class Processing<Type> :
PXFilteredProcessingJoin<INReplenishmentItem, INReplenishmentFilter,
LeftJoin<INItemClass, On<INReplenishmentItem.FK.ItemClass>>,
Where<Current<INReplenishmentFilterExt.usrWarehouse> , Contains<INReplenishmentItem.siteCD>,//The original is: Where<INReplenishmentItem.siteID, Equal<Current<INReplenishmentFilter.replenishmentSiteID>>
And2<Where<
Current<INReplenishmentFilter.itemClassCDWildcard>, IsNull,
Or<INItemClass.itemClassCD, Like<Current<INReplenishmentFilter.itemClassCDWildcard>>>>,
And2<Where<
INReplenishmentItem.launchDate, IsNull,
Or<INReplenishmentItem.launchDate, LessEqual<Current<INReplenishmentFilter.purchaseDate>>>>,
And<Where<
INReplenishmentItem.terminationDate, IsNull,
Or<INReplenishmentItem.terminationDate, GreaterEqual<Current<INReplenishmentFilter.purchaseDate>>>>>
>>>>
where Type : INReplenishmentItem
{

public Processing(PXGraph graph)
: base(graph)
{
this._OuterView = new PXView(graph, false, BqlCommand.CreateInstance(
typeof(
Select2<INReplenishmentItem,
LeftJoin<INItemClass, On<INReplenishmentItem.FK.ItemClass>>,
Where<Current<INReplenishmentFilterExt.usrWarehouse>, Contains<INReplenishmentItem.siteCD>,//The original is: Where<INReplenishmentItem.siteID, Equal<Current<INReplenishmentFilter.replenishmentSiteID>>
And2<Where<
Current<INReplenishmentFilter.itemClassCDWildcard>, IsNull,
Or<INItemClass.itemClassCD, Like<Current<INReplenishmentFilter.itemClassCDWildcard>>>>,
And2<Where<
INReplenishmentItem.launchDate, IsNull,
Or<INReplenishmentItem.launchDate, LessEqual<Current<INReplenishmentFilter.purchaseDate>>>>,
And<Where<
INReplenishmentItem.terminationDate, IsNull,
Or<INReplenishmentItem.terminationDate, GreaterEqual<Current<INReplenishmentFilter.purchaseDate>>>>>
>>>>)));

this._OuterView.WhereAndCurrent<INReplenishmentFilter>(nameof(INReplenishmentFilter.itemClassCDWildcard), nameof(INReplenishmentFilter.itemClassCD));
this._OuterView.WhereAnd<Where<Current<INReplenishmentFilter.onlySuggested>, Equal<False>, Or<INReplenishmentItem.qtyProcessInt, Greater<decimal0>>>>();
}
}

The value of multi selector should be: “BD 2; BD; BDADJ; BRVT” and it should return warehouse with id “BD”

But somehow it doesn’t work.

Have you ever face similar issue? or Do you have any idea?

 

Thank you in advance.

Khoi

icon

Best answer by Yuriy Zaletskyy 12 February 2023, 22:32

View original

14 replies

Userlevel 5
Badge +3

Can you check, if adding commitchanges = true will make any difference:

<px:PXMultiSelector runat="server" ID="CstPXSelector2" DataField="UsrWarehouse" CommitChanges="True" />

 

Userlevel 2
Badge

Hi @Yuriy Zaletskyy

thank you for your response. 

I have added commitchanges=true already but it doesn’t work :(

 

Userlevel 5
Badge +3

Can you attach here customization package, which I can play with on Sales demo?

Userlevel 2
Badge

Hi @Yuriy Zaletskyy,

please find the package in attachment.

You could find the source code in the description of topic. (data field DAC extension and Graph extensions)

 

Userlevel 5
Badge +3

Hi one more time. I wanted to see C# code, but I see only dll. Can you make package with C# code inside, or provide a class library?

Userlevel 2
Badge

Hi @Yuriy Zaletskyy , 

I attached class library. could you please check it?

 

Thanks

Userlevel 5
Badge +3

Before I’d suggest you workaround, want to explain, what I found.

  1. Searching by SiteCD is not the best idea, because SiteCD is bound via PXProjection mechanism, and it seems, like it has some hard to catch bug
  2. Seems like nice example of impedance mismatch, or Contains function doesn’t have nice reflection in SQL ( either MS SQL or MySQL )
  3. In Request profiler I have found, that different versions of Contains, IsIn, In, etc.,,, generated = ‘warehouse 1; warehouse2; warehouse3’

As alternative, I’d suggest you to use code below, but with addiional tuning, as I’ve did my best to provide you with MVP only:

    public class INReplenishmentFilterExt : PXCacheExtension<PX.Objects.IN.INReplenishmentFilter>
{
#region UsrWarehouse
[PXSelector(typeof(INSite.siteCD), typeof(INSite.siteCD), typeof(INSite.descr), ValidateValue = false, DescriptionField = typeof(INSite.siteCD))]
[PXUIField(DisplayName = "Warehouse")]

public virtual string UsrWarehouse { get; set; }
public abstract class usrWarehouse : PX.Data.BQL.BqlString.Field<usrWarehouse> { }
#endregion
}

public class INReplenishmentCreate_Extension : PXGraphExtension<PX.Objects.IN.INReplenishmentCreate>
{
public override void Initialize()
{
base.Initialize();
BqlCommand cmd =
new SelectFrom<INReplenishmentItem>();
var f1 = new PXSelectDelegate(
() =>
{
return records1(Base);
});
Base.Views["Records"] = new PXView(Base, false, cmd, f1);
}

public virtual IEnumerable records1(PXGraph graph)
{
var cr = Base.Filter.Current;
if (cr != null)
{
var ext = cr.GetExtension<INReplenishmentFilterExt>();

var objs = ext.UsrWarehouse.Split(';').ToList().Select(a => a.Trim()).ToArray<String>();
var listResults = new List<INReplenishmentItem>();

var warehouses = SelectFrom<INSite>.Where<INSite.siteCD.IsIn<@P.AsString>>.View.Select(graph, new[]{ objs}).ToList(100);

var wsIds = warehouses.Select(a => a.GetItem<INSite>().SiteID).ToList();

return SelectFrom<INReplenishmentItem>.Where<INReplenishmentItem.siteID.IsIn<@P.AsInt>>.View.Select(graph, wsIds.ToArray());
}
else
{
return Base.Records.Select(Base);
}
}

overall want to say, that provided code is kind of ugly, but it worked on my machine. 

One more point, you can try to play with .In, IsIn instead of contains, but instead of passing SiteCD, try to pass SiteID. It increases chanced of success. 

Second point, provided code is kind of “hack” which I don’t like, but still allow myself to use, if other ideas doesn’t work.

 

Userlevel 2
Badge

Hello @Yuriy Zaletskyy, thank you very much for your answer.

It helps me a lot

Userlevel 2
Badge

Hello @Yuriy Zaletskyy,

Sorry for this inconvenient, I have a further question: Is it possible to add groupby InventoryID then sum QtyOnHand?

I spent a day on the internet but can’t research for a result. Could you please give me suggestion?

 

Thank you in advanced.

 

Userlevel 5
Badge +3

I’d prefer to have separate question for suchlike situations. Potentially someone else may use it for improving their reputation.

 

But this code seems working:

 


return SelectFrom<INReplenishmentItem>
.Where<INReplenishmentItem.siteID.IsIn<@P.AsInt>>
.AggregateTo<GroupBy<INReplenishmentItem.inventoryID>, Sum<INReplenishmentItem.qtyOnHand>>
.View.Select(graph, wsIds.ToArray());

 

Userlevel 2
Badge

@Yuriy Zaletskyy thank you in advanced. I will create another topic next time with this kind of situation.

Userlevel 7
Badge +8

@Yuriy Zaletskyy can you please elaborate why did you introduce a view delegate for “Records” in  the graph initialization and added a new Method “records1” instead of directly overriding the “Records” view using a View Delegate?

Userlevel 5
Badge +3

@aaghaei because direct overriding of Records didn't work in scope of "Prepare Replenishment" page. Acumatica team implemented view Records in a quite different way 

Userlevel 7
Badge +8

Thanks for the clarification @Yuriy Zaletskyy 

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 — 2022  Acumatica, Inc. All rights reserved