Skip to main content
Solved

Add Address Column to PXSeelctor for Contacts, Leads & opportunities

  • 24 July 2024
  • 8 replies
  • 67 views

I want to add the address field to the selectors commonly used on the Contacts, Leads and Opportunity tables. I have to join the address table to make this happen

I looked at this post, but don’t think it will allow me to join the additional table.

Is there a way to add Contact.displayName to the columns in the Selector for the Customer Lookup in an SO? | Community (acumatica.com)

 

It's a large effort to modify every field using a Contact, Lead or Opportunities to add this join and additional column, is this the only way?

 

This is the code I am using for the Contact table ContactId field. it looks to work in most cases however when navigating to the contact screen from an opportunity when clicking on the contact field edit button (the pencil button) I get the following error.

“Conversion failed when converting the nvarchar value 'Leif' to data type int.” Leif is my name, so that is a clue.

    

        #region Contact_ContactID_CacheAttached
        dPXMergeAttributes(Method = MergeMethod.Merge)]
        ]PXCustomizeSelectorColumns(
                typeof(Contact.displayName),
                //typeof(Contact.classID),      
                typeof(Contact.eMail),
                typeof(Address.addressLine1),
                //typeof(Address.city),
                typeof(Address.postalCode),
                typeof(Contact.phone1),
                typeof(Contact.ownerID),
                typeof(BAccount.acctCD),
                typeof(BAccount.acctName),
                typeof(BAccount.parentBAccountID),
                typeof(Contact.contactType)

            )]

        >PXSelector(typeof(Search2<Contact.contactID,
            LeftJoin<BAccount, On<Contact.FK.BusinessAccount>,
            LeftJoin<Address, On<Contact.FK.Address>>>,
            Where<
                BAccount.bAccountID, Equal<Null>,
                Or<BAccount.bAccountID, Equal<Current<AccessInfo.userName>>,
                And<
                    Contact.contactType, Equal<ContactTypesAttribute.person>,
                    Or<Contact.contactType, Equal<ContactTypesAttribute.employee>>>
                    >
                >,
            OrderBy<Desc<Contact.createdDateTime>>

            >),
                 //,typeof(Address.addressLine1), typeof(Contact.eMail),
                 DescriptionField = typeof(Contact.displayName), Filterable = true,
            SelectorMode = PXSelectorMode.Undefined)]

        protected void Contact_ContactID_CacheAttached(PXCache cache)
        {

        }
        #endregion

I tired to modify the BQL in ContactSelectorAttribute.

            Type type = BqlCommand.Compose(typeof(Where<,,>), typeof(BAccount.bAccountID), typeof(IsNull), typeof(Or<>), typeof(Match<,>), typeof(BAccount), typeof(Current<>), typeof(AccessInfo.userName));


 

8 replies

darylbowman
Rank
Badge +12

I’m guessing ‘Leif’ is also your username:

Or<BAccount.bAccountID, Equal<Current<AccessInfo.userName>>,

BAccountID is an int. UserName is a string.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
NicholasBova52
Rank
Userlevel 4
Badge +1

Hello @Leif 

 

On top of the fix from @darylbowman, if you want to modify the selector columns for every Contact, Lead or Opportunities selector on the site, you can do so in a CacheExtension for each of these DACs. Doing it this way will modify the selector columns for every selector of the DAC on the site, so you don’t have to add CacheAttached to each GraphExtension you want the modification to apply to. Here’s an example just modifying the ContactID selector to add AddressLine1 from Address using a new unbound field on the Contact CacheExtension.

 

using PX.Data;
using PX.Objects.CR;
using System;

namespace CD
{
    public sealed class ContactExt : PXCacheExtension<Contact>
    {
        public static bool IsActive() => true;

        public abstract class addressLine1 : PX.Data.BQL.BqlString.Field<addressLine1> { }
        [PXString(50, IsUnicode = true)]
        [PXUIField(DisplayName = "Address Line 1", Visibility = PXUIVisibility.Invisible)]
        [PXFormula(typeof(Selector<Contact.defAddressID, Address.addressLine1>))]
        public String AddressLine1 { get; set; }

        [PXMergeAttributes(Method = MergeMethod.Merge)]
        [PXCustomizeSelectorColumns(
                typeof(Contact.displayName),
                typeof(Contact.eMail),
                typeof(ContactExt.addressLine1),
                typeof(Contact.phone1),
                typeof(Contact.ownerID),
                typeof(Contact.contactType)

            )]
        public Int32? ContactID { get; set; }
    }
}

Note we do not add a new abstract class for the ContactID field since it’s modifying a field from the base DAC, while we do add an abstract class for addressLine1 since it’s a new unbound field added in this CacheExtension. Another thing to note, having this functionality in a CacheExtension that is always active can negatively affect performance, specifically if you have a lot of unbound fields and you’re relying on querying Contact records for larger GI’s or other long running operations. In most cases you won’t notice the performance hit, it is just something to keep in mind. 

L
Rank
Userlevel 3
Badge +1

@darylbowman Yes I think you are correct. I tried to translate what I found in the original PXSeelctor Attribute.

From ContactSelectorAttribute function GetQuery, can you help me on translating this to FluentBQL?

Type type = BqlCommand.Compose(typeof(Where<,,>), typeof(BAccount.bAccountID), typeof(IsNull), typeof(Or<>), typeof(Match<,>), typeof(BAccount), typeof(Current<>), typeof(AccessInfo.userName));

 

darylbowman
Rank
Badge +12

Sorry, I got nothing there. Maybe give Nick’s suggestion a shot.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
L
Rank
Userlevel 3
Badge +1

@NicholasBova52 

I like the ideal of adding the unbound field and using the PXFormula attribute. 

I worry about the performance hit, but it sounds like I shouldn’t be.

 

NicholasBova52
Rank
Userlevel 4
Badge +1

@NicholasBova52

I like the ideal of adding the unbound field and using the PXFormula attribute. 

I worry about the performance hit, but it sounds like I shouldn’t be.

 

Yeah I only bring up the potential performance hit as something to be aware of, especially since it is emphasized within our developer documentation. If you implement your solution and performance is fine in testing then I otherwise wouldn’t worry about it!

andriitkachenko
Rank
Userlevel 6
Badge +2

Hi @Leif 

Type type = BqlCommand.Compose(typeof(Where<,,>), typeof(BAccount.bAccountID), typeof(IsNull), typeof(Or<>), typeof(Match<,>), typeof(BAccount), typeof(Current<>), typeof(AccessInfo.userName));

translates to

.Where<BAccount.bAccountID.IsNull
    .Or<Match<BAccount, Current<AccessInfo.userName>>>>

 

Andrii Tkachenko | Sprinterra | https://www.sprinterra.com/ | If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
L
Rank
Userlevel 3
Badge +1

@andriitkachenko I see my mistake now, thanks for the fix

 

Reply


Terms & ConditionsCookie settings