Can you use string functions in a PXSelector?

you have to use substring within the BQL so it should be inside the typeof function.

DescriptionField =typeof(Substring<BAccount.description,Int1,Int7>)

I didn’t test this within the system yet. Int1 is a constant with PX.Objects.CS for the value 1. I assume Int7 is too but you may have to define that.

let me play a bit. perhaps  I can figure out a solution. I will post back shortly

haven’t tried yet but you might change it to Int0,Int7 as substring is a zero based index. hopefully the error is because of that.

@joe21 @Shawn Burt 

the out of box 0.1 and 100 exist I believe but you can create your own easily. In the name space of your extension add a class as follow and then use it’s name in your formula
public class USRInt7 : PX.Data.BQL.BqlInt.Constant<USRInt7> { public USRInt7() : base(“7") { } }

The other thing that I was thinking is that you could create another field to hold that value and then you don’t have to worry about the formula. Plus, I would anticipate that you might need this for future reporting needs.

@ddunn thats a good solution. Do an unbound dac extension. using a PXFormula or a field selecting event handler to get the value you need into the dac field.

@ddunn

I was actually thinking the same thing.  I was thinking of creating another UDF type string to store the first 7 characters so they could use it for reporting.  I don’t even need to display that field, just store it for them.

That is what I am doing with the “Plan ID” field.  I use the FieldUpdating event to grab the last section of the acctCD and store it as a string.

If I cannot change the display of the Customer field on the screen, so be it. 

Hi @Leonardo Justiniano 

Thanks for taking the time to put that code together for me!

The code compiles nicely.

Even if I don’t use the DescriptionField = typeof(BAccountExt.usrDescriptionPrefix) in the selector, the page errors out.

This is the error I am getting

Error: The first type has to be convertible to the Bqlcommand type.
Parameter name: types

This is the BAccount extension.  Maybe it is a simple fix.  I don’t see anything wrong with this.

    public class BAccountExt : PXCacheExtension<PX.Objects.CR.BAccount2>
    {
        #region Description
        public abstract class usrDescriptionPrefix : PX.Data.BQL.BqlString.Field<usrDescriptionPrefix> { }
        protected string _UsrDescriptionPrefix;

        [PXDBLocalizableString(250, IsUnicode = true)]
        [PXDefault()]
        [PXUIField(DisplayName = "Description", Visibility = PXUIVisibility.SelectorVisible, IsReadOnly = true)]
        [PXDBScalar(typeof(BAccount2.acctCD))]
        public virtual string UsrDescriptionPrefix
        {
            get
            {
                try
                {
                    return this._UsrDescriptionPrefix.Substring(0, _UsrDescriptionPrefix.IndexOf('-'));
                }
                catch
                {
                    return "";
                }
            }
            set
            {
                this._UsrDescriptionPrefix = value;
            }
        }
        #endregion
    }

Maybe someone has seen that error and knows exactly what to look for.  My noobishness says it looks fine.  :-)

@aaghaei 

That compiles and does not cause any errors when I open the screen.

When I try to create a timecard entry, I get this error:

Did I do something wrong in my implementation of this DAC field?

        #region UsrCustomerID
        [PXDBInt]
        [PXUIField(DisplayName = "Customer")]
        [PXSelector(typeof(SearchFor<BAccount2.bAccountID>.
            Where<BAccount2.type.IsEqual<ICSCustomerType>.
            And<BAccount2.parentBAccountID.IsNotNull>>),
                typeof(BAccount2.acctCD),
                typeof(BAccountCRM.acctName),
                SubstituteKey = typeof(BAccount2.acctCD),
                DescriptionField = typeof(BAccountExt.usrDescriptionPrefix))]
        [PXRestrictor(typeof(Where<BAccount2.parentBAccountID, IsNotNull>), ICSMessages.ParentAccount)]
        public virtual int? UsrCustomerID { get; set; }
        public abstract class usrCustomerID : PX.Data.BQL.BqlInt.Field<usrCustomerID> { }
        #endregion

I was thinking maybe the entire DAC field needs to use the BAccountExt but that does not compile.

@aaghaei  I commented out that code.  Same error occurs.

You guys have spent a bunch of time on this and I hate to have you spend any more.  If you are like me, I don’t like to lose a battle with Acumatica coding, but this request to just show the first part of the AcctCD field in the screen is cosmetic.  It doesn’t affect the actual functionality.

Please don’t spend more time on this.  I feel guilty enough already. :-)

Hi @Leonardo Justiniano 

The code is firing and not causing any errors, which is great!

However, this line always returns null

return this._UsrDescriptionPrefix.Substring(0, _UsrDescriptionPrefix.IndexOf('-'));

so the Description field in the Lookup is empty as it hits the catch block.

Please don’t spend more time on this.  The customer can live with the field showing the full customer CD.

I thank you all for your efforts!

Hi @Leonardo Justiniano 

When I put in PXDBScalar, I get an error on the page:

Error: The first type has to be convertible to the Bqlcommand type.
Parameter name: types

If I remove that line, the code runs, but always returns an empty string.

This is what my code looks like at this point:

    public class BAccountExt : PXCacheExtension<PX.Objects.CR.BAccount2>
    {
        #region Description
        public abstract class usrDescriptionPrefix : PX.Data.BQL.BqlString.Field<usrDescriptionPrefix> { }
        protected string _UsrDescriptionPrefix;
        [PXString(250)]
        [PXUIField(DisplayName = "Description", Visibility = PXUIVisibility.SelectorVisible)]
        [PXDBScalar(typeof(BAccount2.acctCD))]
        public virtual string UsrDescriptionPrefix
        {
            get
            {
                try
                {
                    return this._UsrDescriptionPrefix.Substring(0, _UsrDescriptionPrefix.IndexOf('-'));
                }
                catch
                {
                    return "";
                }
            }
            set
            {
                this._UsrDescriptionPrefix = value;
            }
        }
        #endregion
    }
 

@joe21 I put that request on you. Is your screen a custom one or an extension of an existing one? I want to test the code accordingly. My draft is based on the Customer screen.