Solved

Can you use string functions in a PXSelector?

  • 23 December 2022
  • 35 replies
  • 553 views

Userlevel 6
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 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 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 +3

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

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

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

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