Skip to main content
Solved

Can you use string functions in a PXSelector?


Joe Schmucker
Captain II
Forum|alt.badge.img+3

I have a UDF UsrCustomerID.

I originally used this for my DAC

        #region UsrCustomerID
        [Customer]
        [PXUIField(DisplayName = "Customer")]
        [PXRestrictor(typeof(Where<BAccount.parentBAccountID, IsNotNull>), ICSMessages.ParentAccount)]
        public virtual int? UsrCustomerID { get; set; }
        public abstract class usrCustomerID : PX.Data.BQL.BqlInt.Field<usrCustomerID> { }
        #endregion

I wanted to have access to the DescriptionField of the selector, so I tried this (which works)

        #region UsrCustomerID
        [PXDBInt]
        [PXUIField(DisplayName = "Customer")]
        [PXSelector(typeof(SearchFor<BAccount2.bAccountID>.
            Where<BAccount2.type.IsEqual<GetICSCustomerType>.
            And<BAccount2.parentBAccountID.IsNotNull>>),
                typeof(BAccount2.acctCD),
                typeof(BAccountCRM.acctName),
                SubstituteKey = typeof(BAccount2.acctCD),
                DescriptionField = typeof(BAccount2.acctName))]
        [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
 

This is what is being displayed after selecting a customer.  NOTE this is NOT a segmented field.  My client is creating customers and manually putting a suffix in the account CD.  It looks like it is segmented, but the AcctCD in the BAccount table is just a string “PT00019-PLN06969”.

I want to override the display on the screen to show a “substring” of the Customer number.  I just want to show “PT00019” in that field.

I’m thinking there might be a way to use a SubString function on the DescriptionField like this:

DescriptionField = Substring(typeof(BAccount.description), 1, 7)) but that doesn’t compile. 

Is this even possible?  I don’t want to tell my client is cannot be done if it can.

 

Best answer by aaghaei

@joe21 

I am so curious myself to see what happens here. Can you please direct me to the screen you are trying to customize and share the piece of the code related to this issue (DAC and Graph if there is any)?

View original
Did this topic help you find an answer to your question?

35 replies

Shawn Burt
Jr Varsity I
Forum|alt.badge.img+1
  • Jr Varsity I
  • 109 replies
  • December 23, 2022

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.


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • December 23, 2022

Hi @Shawn Burt 

I got it to compile!  

Unfortunately, I got an error on the page.  Unless there is a simple fix, I’m just going to tell the client that they will have to live with it.  It’s a very picky request for sure.


Shawn Burt
Jr Varsity I
Forum|alt.badge.img+1
  • Jr Varsity I
  • 109 replies
  • December 23, 2022

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


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • December 23, 2022

@Shawn Burt , I changed the code a bit.  I actually want the first 7 chars of the acctCD.  It still give the error but just fyi.

DescriptionField = typeof(Substring<BAccount2.acctCD, int1, ICSInt7>))]


Shawn Burt
Jr Varsity I
Forum|alt.badge.img+1
  • Jr Varsity I
  • 109 replies
  • December 23, 2022

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.

 


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • December 23, 2022

Didn’t help.  Nice try!  :-)  

 


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1177 replies
  • December 23, 2022

@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") { } }


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1177 replies
  • December 23, 2022

@joe21 @Shawn Burt 

sorry I guess I missed a part and seems you had tried this.

I have another suggestion. In your DAC add an unbounded string field and set it’s formula as you wish to show the substring and then show this unbound field in you selector as a normal field. Hopefully it will work


Forum|alt.badge.img+5
  • Captain II
  • 486 replies
  • December 23, 2022

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.

 


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • December 23, 2022

@aaghaei  I created the USRInt7 and tried it.  Same error.

Just for kicks, I tried to get the first 5 using int5 which is a native constant. 

DescriptionField = typeof(Substring<BAccount2.acctCD, int0, int5>))]

I also tried getting 1 - 5.

DescriptionField = typeof(Substring<BAccount2.acctCD, int1, int5>))]

No joy.  

It seems like as long as there are at least 5 chars in the AcctCD, it should be pulling some value from the substring.  Maybe the DescriptionField type just won’t allow it.

Not a big deal here.  It was just a picky request from the client.  Please don’t spend a lot of effort on this.

Thanks guys for your advice.

 

 


Shawn Burt
Jr Varsity I
Forum|alt.badge.img+1
  • Jr Varsity I
  • 109 replies
  • December 23, 2022

@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.


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1177 replies
  • December 23, 2022

@ddunn 😂 we we’re writing same sentence in the same time @joe21 @Shawn Burt 


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • December 23, 2022

@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. 

 


Leonardo Justiniano
Jr Varsity II
Forum|alt.badge.img+5

Hi @joe21 

 

You can resolve it through a DAC extension on BAccount2 as follow:

namespace PX.Objects.CR
{
  public class BAccountExt : PXCacheExtension<PX.Objects.CR.BAccount>
  {
    #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)]
    [PXFieldDescription]
	[PXDBScalar(typeof(BAccount.acctCD))]
    public virtual String UsrDescriptionPrefix
    {
      get
      {        
		try
		{
			return this._UsrDescriptionPrefix.Substring(0, _UsrDescriptionPrefix.IndexOf('-'));
		}
		catch 
		{
			return "";
		}
      }
      set
      {
        this._UsrDescriptionPrefix = value;
      }
    }
    #endregion
  }
}

So you can do 

DescriptionField = typeof(BAccountExt.usrDescriptionPrefix) 

Note: I haven’t tested it 😋

 

Happy New Year!


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • January 3, 2023

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
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1177 replies
  • January 4, 2023

@joe21 try the below 

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

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


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • January 4, 2023

@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
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1177 replies
  • January 4, 2023

@joe21 Can you remove the

            set
            {
                this._UsrDescriptionPrefix = value;
            }

from the code I provided and try. 


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • January 4, 2023

@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. :-)

 


Leonardo Justiniano
Jr Varsity II
Forum|alt.badge.img+5

Hi @joe21 

My Bad. As always Copy / Paste is dangerous. Please replace

[PXDBLocalizableString(250, IsUnicode = true)]

by

[PXString(250)]

 

and remove 

[PXDefault]

 


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • January 5, 2023

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!


Leonardo Justiniano
Jr Varsity II
Forum|alt.badge.img+5

Hi @joe21 

Make sure you did not remove

set
{
      this._UsrDescriptionPrefix = value;
}

 

[PXDBScalar] requires it to be able to assign the value to the protected attribute.


Joe Schmucker
Captain II
Forum|alt.badge.img+3
  • Author
  • Captain II
  • 443 replies
  • January 5, 2023

 

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
    }
 


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1177 replies
  • Answer
  • January 6, 2023

@joe21 

I am so curious myself to see what happens here. Can you please direct me to the screen you are trying to customize and share the piece of the code related to this issue (DAC and Graph if there is any)?


Leonardo Justiniano
Jr Varsity II
Forum|alt.badge.img+5
aaghaei wrote:

@joe21

I am so curious myself to see what happens here. Can you please direct me to the screen you are trying to customize and share the piece of the code related to this issue (DAC and Graph if there is any)?

@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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings