Acumatica: Selector Filter Duplicates

Question

I have a Rate type Grid which have a selector field that displays all rate types. The thing that I wanted to do is to filter the rate ID and Utility type to avoid duplicates in the grid.

To put it simply, There should only be 1 rate type of each type to be reflected in the grid, If I already selected a "Power" rate type then all power rate type should not be selected in the selector field, unless i removed the current "Power" rate type in the grid then I can then again select another rate type.

I tried using PXRestrictor to solve my problem

    [PXDBInt]
    [PXDefault()]
    [PXUIField(DisplayName = "Rate ID")]
    [PXSelector(typeof(Search<RERateTable.rateID>), 
        typeof(RERateTable.rateID), 
        typeof(RERateTable.refNbr), 
        typeof(RERateTable.rateName),
        typeof(RERateTable.utilityType),
        SubstituteKey = typeof(RERateTable.refNbr), 
        DescriptionField = typeof(RERateTable.rateName))]
    [PXRestrictor(typeof(Where<RERateTable.utilityType,
                        NotIn2<Search<RERateTable.utilityType,
                            Where<RERateTable.rateID,
                                Equal<Current<REPropertyRateDetail.rateID>>>>>>), REMessages.UtilityTypeDuplicateException)]
    public virtual int? RateID { get; set; }
    public abstract class rateID : IBqlField { }

The problem I encountered with this approach is that there are no more records that are being rendered in the selector field.

I undertake another method of using NotExists method

    [PXDBInt]
    [PXDefault()]
    [PXUIField(DisplayName = "Rate ID")]
    [PXSelector(typeof(Search<RERateTable.rateID,
                            Where<NotExists<Select<REPropertyRateDetail, 
                            Where<REPropertyRateDetail.rateID,                                             
                         Equal<Current<REPropertyRateDetail.rateID>>>>>>>), 
        typeof(RERateTable.rateID), 
        typeof(RERateTable.refNbr), 
        typeof(RERateTable.rateName),
        typeof(RERateTable.utilityType),
        SubstituteKey = typeof(RERateTable.refNbr), 
        DescriptionField = typeof(RERateTable.rateName))]
    public virtual int? RateID { get; set; }
    public abstract class rateID : IBqlField { }

In this approach, I also experienced the same problem in using the PXRestrictor approach, there are no records being rendered on my selector field.

Hoping for your answers/suggestions. Thank you so much.

Hugues Beauséjour Hugues Beauséjour · Accepted Answer · 2019-04-10T15:20:52

I have a hard time understanding exact requirements so these are general guidelines.

Duplicates can be reduced by grouping them in the selector in the type parameter:

typeof(Search4<RERateTable.rateID,
       Aggregate<GroupBy<RERateTable.utilityType>>>)

I'm guessing the reason why the selector grid is empty is that you are filtering against a null value. A common pattern is to filter against a value if it's not null or show everything if it's null:

typeof(Search<RERateTable.rateID,
       Where<RERateTable.rateID, Equal<Current<REPropertyRateDetail.rateID>>,
       Or<Current<REPropertyRateDetail.rateID>, IsNull>>>)

For your scenario a restrictor attribute seems unnecessary because you can put the condition in the selector type parameter (first param). If the logic is to difficult to express as a SQL query (some arithmetic and grouping logic can be) then a custom selector can be used. With a custom selector you can select, manipulate and return the records in any way you want with the GetRecords method, it works like a dataview delegate.

Acumatica: Selector Filter Duplicates

1 Answers