How to include a DAC extension unbound field in a Search<> parameter of PXSelector?

0
votes

I have segmented INLocation.LocationCD and need to restrict the PXSelector to include or exclude a particular segment based on the screen where the location is being entered. I have been able to access the DAC extension field in the displayed columns of the PXSelector, but the field is evaluated as null during the Search<> parameter of PXSelector.

I have tried:

  • referencing INLocationExt.myField directly,
  • making an inherited DAC to define the custom field directly,
  • and creating a PXProjection in hopes that the additional layer would cause the unbound field to be retrieved to populate the PXSelector in case the field was not being loaded in time for the Search<>.

Key points:

  • This is an unbound field in the DAC extension
  • It returns an value based evaluating the last segment of the INLocation.LocationCD
  • Generic Inquiries properly display this value
  • I have been unable to make PXSelector return any values when I reference the unbound field unless I simply check And

The field defined in my DAC extension:

[PXString(1)]
[PXUIField(DisplayName = "Condition")]
[ConditionType.List]
public String UsrSSCondition
{ 
    get
    {
        if (LocationCD == null || LocationCD.Length == 0) return ConditionType.Undefined;

        switch (LocationCD.Substring(LocationCD.Length - 1, 1))
        {
            case "N":
                return ConditionType.New;
            case "R":
                return ConditionType.Repair;
            case "C":
                return ConditionType.Core;
            case "U":
                return ConditionType.Used;
            default:
                return ConditionType.Undefined;
        }
    }
}
public abstract class usrSSCondition : PX.Data.BQL.BqlString.Field<usrSSCondition> { }

The PXSelector:

[PXSelector(typeof(Search<INLocation.locationID, Where<INLocation.receiptsValid, Equal<True>,
    And<INLocationExt.usrSSCondition, NotEqual<ConditionType.core>>>>),
    typeof(INLocation.locationCD),
    typeof(INLocation.active),
    typeof(INLocation.primaryItemID),
    typeof(INLocation.primaryItemClassID),
    typeof(INLocationExt.usrSSCondition),
    typeof(INLocation.receiptsValid),
    SubstituteKey = typeof(INLocation.locationCD))]

The PXProjection:

[Serializable]
[PXCacheName("SSCS INLocation")]
[PXProjection(typeof(Select<INLocation>))]
public partial class SSINLocation : IBqlTable
{
    #region LocationID
    [PXDBInt(IsKey = true, BqlField = typeof(INLocation.locationID))]
    public int? LocationID { get; set; }
    public abstract class locationID : PX.Data.BQL.BqlInt.Field<locationID> { }
    #endregion

    #region LocationCD
    [PXDBString(BqlField = typeof(INLocation.locationCD))]
    public String LocationCD { get; set; }
    public abstract class locationCD : PX.Data.BQL.BqlString.Field<locationCD> { }
    #endregion

    #region UsrSSCondition
    [PXDBString(BqlField = typeof(INLocationExt.usrSSCondition))]
    public String UsrSSCondition { get; set; }
    public abstract class usrSSCondition : PX.Data.BQL.BqlString.Field<usrSSCondition> { }
    #endregion

    #region ReceiptsValid
    [PXDBBool(BqlField = typeof(INLocation.receiptsValid))]
    public bool? ReceiptsValid { get; set; }
    public abstract class receiptsValid : PX.Data.BQL.BqlBool.Field<receiptsValid> { }
    #endregion

    #region Active
    [PXDBBool(BqlField = typeof(INLocation.active))]
    public bool? Active { get; set; }
    public abstract class active : PX.Data.BQL.BqlBool.Field<active> { }
    #endregion

    #region PrimaryItemID
    [PXDBInt(BqlField = typeof(INLocation.primaryItemID))]
    public int? PrimaryItemID { get; set; }
    public abstract class primaryItemID : PX.Data.BQL.BqlInt.Field<primaryItemID> { }
    #endregion

    #region PrimaryItemClassID
    [PXDBInt(BqlField = typeof(INLocation.primaryItemClassID))]
    public int? PrimaryItemClassID { get; set; }
    public abstract class primaryItemClassID : PX.Data.BQL.BqlInt.Field<primaryItemClassID> { }
    #endregion

}

I have tried simple versions and various combinations of those to no avail. How do I leverage my "condition" in the Search<> clause of the PXSelector?

Edit 1: Picture of PXSelector returning values as a column - does not work as a PXRestrictor or as a where clause in Select<>.

Condition

Edit 2: More info

I have simplified the DAC extension to use PXFormula and pick out the last character of the LocationCD retrieved by PXFormula to set the value.

We need to use the last segment of the LocationCD to manage the condition of the part in a bin.  This would allow us to separate cost as well as manage MRO Spares for maintenance as New, Used, Repaired, and Requiring Repair while also allowing the ability to specify other conditions later (like NCM if received damaged, etc.) if needed.  While some materials can be used globally, materials of certain conditions need to be available under certain use cases.  My intended strategy is to apply the rules to the last segment of the Location CD to allow the PXSelector to control user entry, either as a DAC extension on INLocation or as a Cache_Attached in the relevant graphs, if necessary.

I created a DAC extension on INLocation for usrSSCondition as a PXString.  My latest attempt was to use a PXFormula to pull the LocationCD value and then custom code on the set{} to pick out the last segment and set the code for the relevant condition.  (This technique was actually new to me, and a response in the stackoverflow post guided me to the idea.)

When using in a PXSelector as a displayed column, I can see the value.  However, the Select<> does not allow me to tap into that segment or custom PXString field used to display that condition.  I was hoping that some "behind the scenes magic" would evaluate my PXString field to limit the results, but it seems that the field is returned as null during the Select and then processed in a later step of DAC processing.  When I think about what a Select is doing, it would make sense that data not stored in the database cannot be used to filter the results.  PXRestrictor does not impact it either.

1) Is there a way to get my DAC to process the PXString value before the PXSelector applies the where clause? 2) Is this something I need to take to an attribute for post-processing?  (If so, any suggestions on where to look for a simple example?)

Updated DAC:

#region usrSSCondition
private String _condition;
[PXString]
[PXUIField(DisplayName = "Condition")]
[PXFormula(typeof(INLocation.locationCD))]
[ConditionType.List]
public String UsrSSCondition
{
    get { return _condition; }
    set
    {
        string Loc = value;
        if (Loc == null || Loc.Length == 0)
        {
            _condition = ConditionType.Undefined;
        }
        else
        {
            _condition = (Loc.Substring(Loc.Length - 1, 1)) switch
            {
                "N" => ConditionType.New,
                "R" => ConditionType.Repair,
                "C" => ConditionType.Core,
                "U" => ConditionType.Used,
                _ => ConditionType.Undefined,
            };
        }
    }
}
2
acumatica
I am not sure whether it will help, but perhaps you could try using PXFormula in your Unbound field, rather than logic in the getter. Perhaps Acumatica might then be able to include the Formula in your search - Joseph Caruana
Thanks for the response. I thought about that, but the point of the field is to isolate the segment. Do you know a way to do that in a PXFormula? As you can see from the explicit get block, I strip the last character off the location to return the condition identified by the segment. If there is a way to do that in the switch, I can use the example from asiablog.acumatica.com/2016/08/cool-ways-to-use-pxformula.html to do what you suggest. I just don't know how to get that last position off the LocationCD in that syntax... hence the way I'm stripping it out above. Any ideas? - Brian Stevens
It might not be possible to do all that in a PXFormula. Since we are not sure whether PXFormula will work ... could you perhaps do a simple test where you add the PXFormula and you just return one of the values for now, example ConditionType.Repair. If that works, maybe we can try to find a way to get the PXFormula working with the substring logic - Joseph Caruana
I'm doing something fundamentally wrong, but I'm not seeing it. I can return the condition in the selector columns (adding image to question) but I can't seem to use it in the Search<> or PXRestrictor. Trying PXFormula didn't work, and I even tried including PXDependsOnFields to try to nudge it. Will sleep on it and try again in the morning. (and update if I get anywhere with it) - Brian Stevens
Some offline discussion with Huges Beauséjour confirms my speculation... "filtering occurs at the DB level and in the DB the unbound field doesn't exists". His advice was to use a means of evaluating the segment from the locationCD field directly in the PXSelector or in a PXRestrictor and use switch/case. A bit more complex and more to maintain, but the only means suggested so far to accomplish my issue. - Brian Stevens

2 Answers

2
votes

Do not ever use code inside the getter, it won't be work properly in BQL expressions!

If you wanna check Loc.Substring(Loc.Length - 1, 1) somewhere in BQL just write your own BQL function

public class ConditionTypeBySegment<Source> : BqlFunction, IBqlOperand, IBqlCreator
    where Source : IBqlOperand
{
    private IBqlCreator _source;

    public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
    {
        if (!getValue<Source>(ref _source, cache, item, pars, ref result, out value) || value == null)
            return;

        if (value is string strValue)
        {
            switch (strValue.Substring(strValue.Length - 1, 1))
            {
                case "N":
                    value = ConditionType.New;
                    break;
                case "R":
                    value = ConditionType.Repair;
                    break;
                case "C":
                    value = ConditionType.Core;
                    break;
                case "U":
                    value = ConditionType.Used;
                    break;
                default:
                    value = ConditionType.Undefined;
                    break;
            }

            return;
        }

        value = ConditionType.Undefined;
    }

    public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
    {
        ...
        return true;
    }
}

or use a combination of existing functions. For example:

[PXSelector(typeof(Search<INLocation.locationID,
    Where<INLocation.receiptsValid, Equal<True>,
        And<Substring<FABookBalance.deprToPeriod, Sub<StrLen<FABookBalance.deprToPeriod>, int1>, int1>, NotEqual<ConditionTypes.tCore>>>>),
    typeof(INLocation.locationCD),
    typeof(INLocation.active),
    typeof(INLocation.primaryItemID),
    typeof(INLocation.primaryItemClassID),
    typeof(INLocationExt.usrSSCondition),
    typeof(INLocation.receiptsValid),
    SubstituteKey = typeof(INLocation.locationCD))]

public static class ConditionTypes
{
    public class tNew : PX.Data.BQL.BqlString.Constant<tNew> { public tNew() : base("N") { } }
    public class tRepair : PX.Data.BQL.BqlString.Constant<tRepair> { public tRepair() : base("R") { } }
    public class tCore : PX.Data.BQL.BqlString.Constant<tCore> { public tCore() : base("C") { } }
    public class tUsed : PX.Data.BQL.BqlString.Constant<tUsed> { public tUsed() : base("U") { } }
}
0
votes

Simple solution - simplify. Changed the field to simply hold the value and then let the BLC set the value when the locaitonCD value is set. On creation of the record, the locationCD field is empty, so defining the FieldDefaulting logic causes the condition to be undefined initially. By monitoring FieldUpdated of the LocationCD, we can then reapply the FieldDefaulting rules to the "real" value.

DAC field definition:

#region usrSSCondition
[PXDBString]
[PXUIField(DisplayName = "Condition")]
[ConditionType.List]
public String UsrSSCondition { get; set; }
public abstract class usrSSCondition : PX.Data.BQL.BqlString.Field<usrSSCondition> { }
#endregion

Event Handlers in the BLC:

#region INLocationExt_UsrSSCondition_FieldDefaulting
protected void INLocation_UsrSSCondition_FieldDefaulting(PXCache sender, PXFieldDefaultingEventArgs e)
{
    INLocation row = (INLocation)e.Row;

    string Loc = row?.LocationCD;
    if (Loc == null || Loc.Length == 0)
    {
        e.NewValue = ConditionType.Undefined;
    }
    else
    {
        e.NewValue = (Loc.Substring(Loc.Length - 1, 1)) switch
        {
            ConditionType.New => ConditionType.New,
            ConditionType.Repair => ConditionType.Repair,
            ConditionType.Core => ConditionType.Core,
            ConditionType.Used => ConditionType.Used,
            _ => ConditionType.Undefined,
        };
    }

}
#endregion

#region INLocation_LocationCD_FieldUpdated
protected void _(Events.FieldUpdated<INLocation.locationCD> e)
{
    INLocation row = (INLocation)e.Row;
    e.Cache.SetDefaultExt<INLocationExt.usrSSCondition>(row);
}
#endregion

Since locations are defined in INSiteMaint, the event handlers in that graph allow setting the field value to store in the database without any translation. That enables use of PXRestrictorAttribute to limit available locations accordingly or write rules to set the location flags on the INSiteMaint screen.

Below is one example of CacheAttached to add a PXRestrictor to prevent receipt into a Core location type unless it is done from the NcmTag Screen. (A heavy hand controlling what locations a user may select is not needed universally, so this was not applied globally to the DAC field.)

#region INTran_LocationID_CachedAttached
[PXMergeAttributes(Method = MergeMethod.Append)]
[PXRestrictor(typeof(Where<INLocationExt.usrSSCondition, NotEqual<ConditionType.core>,
     Or<Current<AccessInfo.screenID>, Equal<SSCS.Constants.NcmTagScreenID>>>), "")]
#endregion

Also worth noting, since my purpose is to use a character of the LocationCD for the end user to recognize the location type, I have to prevent the user from changing the LocationCD value through the RowSelected event for INLocation.

#region INLocation_RowSelected
protected void _(Events.RowSelected<INLocation> e)
{
    INLocation row = e.Row;
    if(row?.SiteID != null)
    {
        INLocationExt rowExt = row.GetExtension<INLocationExt>();
        PXUIFieldAttribute.SetEnabled<INLocation.locationCD>(e.Cache, row, 
            !DisableLocationRename(row?.LocationID));
    }
}
#endregion

#region DisableLocationRename
protected virtual bool DisableLocationRename(int? locationID)
{
    int counter = PXSelect<SSINNcmTag,
        Where<SSINNcmTag.locationID, Equal<Required<SSINNcmTag.locationID>>,
          And<SSINNcmTag.tranRefNbr, IsNull>>>
        .SelectSingleBound(Base, null, locationID).Count;
    if (counter > 0) return true;
    counter = PXSelect<INLocationStatus,
        Where <INLocationStatus.locationID, Equal<Required<INLocationStatus.locationID>>,
          And<INLocationStatus.qtyOnHand, Greater<DecimalZero>>>>
        .SelectSingleBound(Base, null, locationID).Count;
    if (counter > 0) return true;
    return false;
}
#endregion

While we have the ability to write some very interesting code, it is important to stop once in a while to ask, "Why am I making this complicated?" Whenever possible, simplify.