Solved

Can you use string functions in a PXSelector?

  • 23 December 2022
  • 35 replies
  • 459 views

Userlevel 6
Badge +2

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 +2

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 +8

😅 Within last 2 years that I have been active on community, actually this the first time I see communications expand to the second page.

Userlevel 6
Badge +4

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 +8

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

Userlevel 6
Badge +2

@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 7
Badge +8

@joe21 considering you already have built the selector

as alternative 2 , in your grid for this column property, switch the DisplayMode option (I guess it has Value, Text, Hint) to text and it will show the DescriptionField of the selector in grid which is the Prefix.

or as alternative 3 change the substitute field of the selector to the custom prefix field and don’t touch the grid

Userlevel 7
Badge +8

@joe21

Please see the below modified code of yours and screenshot from change you need to make to the page control. It works as you expected. Please note the Comment to the CustomerExt

Considering you can not create ext on your ext, decorate the classes as “sealed” and remove “virtual” from fields properties to prevent Acumunator error.

using ICSTimeCards;
using PX.Data;
using PX.Objects.AR;
using ICSTimeCards.Helper;
using PX.Data.BQL.Fluent;
using PX.Objects.CS;
using PX.Data.EP;

namespace PX.Objects.CR
{
public sealed class PMTimeActivityExt : PXCacheExtension<PX.Objects.CR.PMTimeActivity>
{
public static bool IsActive() => true;

#region UsrCustomerID
public abstract class usrCustomerID : PX.Data.BQL.BqlInt.Field<usrCustomerID> { }
protected int? _UsrCustomerID;
[PXDBInt]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "Customer")]
[PXSelector(typeof(Search2<Customer.bAccountID,
LeftJoin<Location, On<Location.locationID, Equal<Customer.defLocationID>>,
LeftJoin<Address, On<Address.addressID, Equal<Location.defAddressID>>>>,
Where<Customer.type, Equal<ICSCustomerType>>>),
typeof(Customer.bAccountID),
typeof(Customer.acctCD),
typeof(Customer.acctName),
typeof(USRCustomerExt.usrDescriptionPrefix),
SubstituteKey = typeof(Customer.acctCD),
DescriptionField = typeof(USRCustomerExt.usrDescriptionPrefix))]
[PXRestrictor(typeof(Where<Customer.parentBAccountID, IsNotNull>), ICSMessages.ParentAccount)]
public int? UsrCustomerID { get; set; }
#endregion


public sealed class USRCustomerExt : PXCacheExtension<PX.Objects.AR.Customer>
{
public static bool IsActive() => true;

public const string AccountSegment = "-";

#region UsrDescriptionPrefix
public abstract class usrDescriptionPrefix : PX.Data.BQL.BqlString.Field<usrDescriptionPrefix> { }
protected string _UsrDescriptionPrefix;
[PXString(250)]
[PXUnboundDefault("", PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "Account Prefix", Visibility = PXUIVisibility.SelectorVisible, Visible = true)]
public string UsrDescriptionPrefix
{
get
{
try
{
return Base.AcctCD.Substring(0, Base.AcctCD.IndexOf(AccountSegment));
}
catch
{
return ""; // or Base.AcctCD; if you want to see the full customer code for THOSE who do not have "-" in their Account Code.
}
}
set
{
this._UsrDescriptionPrefix = value;
}
}
#endregion
}
}

 

Userlevel 6
Badge +2

@aaghaei   IT WORKS!

Super sorry I didn’t explain the need properly.  I honestly thought that the DescriptionField = typeof(USRCustomerExt.usrDescriptionPrefix) was what returned the description to the screen.  

Thank you everyone for your help.  I am so grateful to you all.

Now, I will go through your code and try to understand how it works.  I’m new to both C# and I’m a novice at Acumatica.  I don’t know where you would go to learn how to accomplish what you did in the code you wrote for me.  I’d vote your last response as the solution, but someone already marked a previous reply as the answer.

THANKS!

Userlevel 7
Badge +8

@joe21 If you are planning to continue on customizations, then I strongly recommend to take T series course on Acumatica University which is free and is close to 25 hours of training and lots of materials.

Userlevel 6
Badge +2

@aaghaei   I did the online courses (3 times :-)  However, there are many things that are not covered such as the extension you did for that field.  I think a chunk of my difficulty is actually learning C# better.  Sealed classes, abstract classes, those types of things.

I have the Acumatica training code open all the time as I use that to help me figure out solutions to what I am trying to do.  That online university course is fantastic.  I’m at the point where the things I cannot do are beyond the scope of the training.  Thank GOD for you and the others on this forum.  Each time I get assistance, it adds to my breadth of knowledge.  Eventually, I MAY be able to help someone else on this forum, not holding my breath.  

Did we make it to 3 pages yet?  HA!

Userlevel 5
Badge +2

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

Userlevel 5
Badge +2

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 +8

@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 +8

@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 6
Badge +2

@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 +2

@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 6
Badge +2

@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 7
Badge +8

@joe21 Can you remove the

            set
            {
                this._UsrDescriptionPrefix = value;
            }

from the code I provided and try. 

Userlevel 6
Badge +4

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 +8

@joe21 @Leonardo Justiniano 

I assumed you wanted to add the segmented part to your customer. If you want it on other master record you can adjust it accordingly. I have added a few joins if you need to see the address as well. I do not have “-” in my Accounts Code so I used “P” for testing purpose.

using PX.Data;
using PX.Objects.AR;
using PX.Objects.CR;

namespace PX.Objects.AR
{
[PXNonInstantiatedExtension]
public sealed class USRCustomerExt : PXCacheExtension<PX.Objects.AR.Customer>
{
public static bool IsActive() => true;

public const string AccountSegment = "P"; //"-"

#region UsrDescriptionPrefix
public abstract class usrDescriptionPrefix : PX.Data.BQL.BqlString.Field<usrDescriptionPrefix> { }
protected string _UsrDescriptionPrefix;
[PXString(250)]
[PXUnboundDefault("", PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "Account Prefix", Visibility = PXUIVisibility.SelectorVisible, Visible = true)]
public string UsrDescriptionPrefix
{
get
{
try
{
return Base.AcctCD.Substring(0, Base.AcctCD.IndexOf(AccountSegment));
}
catch
{
return ""; // or Base.AcctCD; if you want to see the full customer code for those who do not have "-" in their Account Code.
}
}
set
{
this._UsrDescriptionPrefix = value;
}
}
#endregion
}
}

 

I picked the Sales Order as the screen you want to have the UsrCustomerID. You can adjust the DAC Extension based on the screen you are customizing.

using PX.Data;
using PX.Objects.AR;
using PX.Objects.CR;

namespace PX.Objects.SO
{
[PXNonInstantiatedExtension]
public sealed class USRSOOrderExt : PXCacheExtension<PX.Objects.SO.SOOrder>
{
public static bool IsActive() => true;

public class GetICSCustomerType : PX.Data.BQL.BqlString.Constant<GetICSCustomerType>
{
public GetICSCustomerType() : base("VC") { }
}

public static class ICSMessages
{
public const string ParentAccount = "Not Found"; //"?" I just wanted to be consistent with your code.
}

#region UsrCustomerID
public abstract class usrCustomerID : PX.Data.BQL.BqlInt.Field<usrCustomerID> { }
protected int? _UsrCustomerID;
[PXDBInt]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "Customer")]
[PXSelector(typeof(Search2<Customer.bAccountID,
LeftJoin<Location, On<Location.locationID, Equal<Customer.defLocationID>>,
LeftJoin<Address, On<Address.addressID, Equal<Location.defAddressID>>>>,
Where<Customer.type, Equal<GetICSCustomerType>>>),
typeof(Customer.bAccountID),
typeof(Customer.acctCD),
typeof(Customer.acctName),
typeof(USRCustomerExt.usrDescriptionPrefix),
typeof(Location.descr),
typeof(Address.addressLine1),
typeof(Address.city),
typeof(Address.state),
typeof(Address.postalCode),
SubstituteKey = typeof(Customer.acctCD),
DescriptionField = typeof(Customer.acctName))]
[PXRestrictor(typeof(Where<Customer.status, IsNull, Or<Customer.status, Equal<CustomerStatus.active>, Or<Customer.status, Equal<CustomerStatus.oneTime>>>>), "The customer status is '{0}'.", new[] { typeof(Customer.status) })]
[PXRestrictor(typeof(Where<Customer.parentBAccountID, IsNotNull>), ICSMessages.ParentAccount)]
public int? UsrCustomerID { get; set; }
#endregion
}
}

 

The UsrCustomerID Selector looks like below. I have shrunk the columns for confidentiality.

 

 

Everything works like a charm an no error  is raised for selector or saving data or retrieval. Hope this helps

Userlevel 7
Badge

I don't usually make many comments but this has been a fun post to watch 😁. It's this kind of activity that makes this community and I love to see. Thank you!

Userlevel 6
Badge +2

I am modifying EP305000.  I added 4 UDF’s to the Details tab. There is a fifth field that is not displayed.  It is used to store the “prefix” of the customer account CD for reporting purposes.

The customer setup their system using a parent child relationship for their customers.  The parent is a seven character value.  They create child accounts using the parent account and add a hyphen and a “plan ID” which is 8 characters.  All transactions are done on the child accounts.

FYI, I did not implement this client, so if you find it “unusual”, do not blame me.

My end game is to have the Customer field only show the prefix.  I don’t really care if the Lookup shows that value.

 

There is a fifth UDF called UsrPrimaryCustomer which is a string field to store the AcctCD prefix for reporting purposes.  This entire exercise was to try to show just the prefix in the Customer column. so that it did not make the PlanID look duplicated.

To reproduce my scenario for this client, you setup a Parent account with just the prefix.  Then you setup children accounts using the prefix plus a suffix.  When the customer ID lookup is fired, it will only pull CHILD customers from the BAccount table.  The Plan ID is nothing more than the suffix of the acctCD.

You can see why returning the PREFIX only for the Customer field is cosmetic only.  

I will upload the project for this as well as the VS source code.  This is running in 2021R2.

Any critical critiques of my code are WELCOME.  I am lone wolf with only you guys to ask questions of, so anything you want to teach me is great.

Userlevel 7
Badge +8

@joe21 if you had provided your last explanation I possibly would suggest something different from beginning 😂

Considering this is a display only field whether or not you want to DB type field for it is up to you and won’t make any change in the below logic I’m going to suggest.
You have already created your DAC ext for EP305000, on the graph of this screen add an extension and an FieldSelecting event handler  to populate and display the data as per logic you want. Below is an example I coded for a new community member that is similar to yours only you want to show substring that you have the code for it already.

See the code from link above and if you couldn’t make it work let me know.

Userlevel 6
Badge +2

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.

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