Solved

Can you use string functions in a PXSelector?

  • 23 December 2022
  • 35 replies
  • 672 views

Userlevel 7
Badge +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.

 

icon

Best answer by aaghaei 6 January 2023, 03:47

View original

35 replies

Userlevel 5
Badge +1

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.

Userlevel 7
Badge +3

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.

Userlevel 5
Badge +1

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

Userlevel 7
Badge +3

@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>))]

Userlevel 5
Badge +1

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.

 

Userlevel 7
Badge +3

Didn’t help.  Nice try!  :-)  

 

Userlevel 7
Badge +9

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

Userlevel 7
Badge +9

@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

Userlevel 7
Badge +5

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.

 

Userlevel 7
Badge +3

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

 

 

Userlevel 5
Badge +1

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

Userlevel 7
Badge +9

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

Userlevel 7
Badge +3

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

 

Userlevel 6
Badge +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!

Userlevel 7
Badge +3

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

 

 

Userlevel 7
Badge +9

@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

Userlevel 7
Badge +3

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

 

 

 

Userlevel 7
Badge +9

@joe21 Can you remove the

            set
            {
                this._UsrDescriptionPrefix = value;
            }

from the code I provided and try. 

Userlevel 7
Badge +3

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

 

Userlevel 6
Badge +5

Hi @joe21 

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

[PXDBLocalizableString(250, IsUnicode = true)]

by

[PXString(250)]

 

and remove 

[PXDefault]

 

Userlevel 7
Badge +3

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!

Userlevel 6
Badge +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.

Userlevel 7
Badge +3

 

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
    }
 

Userlevel 7
Badge +9

@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)?

Userlevel 6
Badge +5

@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


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