Skip to main content
Question

How can i add in customer lookup add Primary Contact Name field.

  • February 20, 2026
  • 1 reply
  • 64 views
mos11
Freshman I
Forum|alt.badge.img
public class BZCustomerAttribute : CustomerAttribute
{
    public BZCustomerAttribute(Type search)
            : this(search, typeof(Customer.acctCD), typeof(Customer.acctName), typeof(Contact.phone1), typeof(Customer.primaryContactID), typeof(Customer.customerClassID), typeof(Address.addressLine1), typeof(Address.addressLine2), 
                  typeof(Address.postalCode),  typeof(Address.city), typeof(Address.countryID), typeof(Location.taxRegistrationID), 
                  typeof(Customer.curyID), typeof(Contact.attention), typeof(Customer.customerClassID), typeof(Customer.status))
    {
    }

    private readonly Type[] _fields;

    [PXHidden]
    [PXProjection(typeof(Select<Contact>))]
    public class Contact2 : Contact
    {
    }

    public BZCustomerAttribute(Type search, params Type[] fields)
    {
        Type genericTypeDefinition = search.GetGenericTypeDefinition();
        Type[] genericArguments = search.GetGenericArguments();
        Type type;
        if (genericTypeDefinition == typeof(Search<>))
        {
            type = BqlCommand.Compose(typeof(Search2<,,>), typeof(BAccountR.bAccountID), typeof(LeftJoin<,,>), typeof(Customer), 
                typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
                typeof(LeftJoin<,,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
                typeof(LeftJoin<,,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>),
                typeof(LeftJoin<,,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>),
                typeof(LeftJoin<,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
                typeof(Where<Customer.bAccountID, IsNotNull>));
        }
        else if (genericTypeDefinition == typeof(Search<,>))
        {
            type = BqlCommand.Compose(typeof(Search2<,,>), typeof(BAccountR.bAccountID), 
                typeof(LeftJoin<,,>), typeof(Customer), typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
                typeof(LeftJoin<,,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
                typeof(LeftJoin<,,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>),
                typeof(LeftJoin<,,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>),
                typeof(LeftJoin<,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
                genericArguments[1]);
        }
        else if (genericTypeDefinition == typeof(Search<,,>))
        {
            type = BqlCommand.Compose(typeof(Search2<,,>), typeof(BAccountR.bAccountID), 
                typeof(LeftJoin<,,>), typeof(Customer), typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
                typeof(LeftJoin<,,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
                typeof(LeftJoin<,,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>),
                typeof(LeftJoin<,,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>),
                typeof(LeftJoin<,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
                genericArguments[1], genericArguments[2]);
        }
        else if (genericTypeDefinition == typeof(Search2<,>))
        {
            type = BqlCommand.Compose(typeof(Search2<,,>), typeof(BAccountR.bAccountID), typeof(LeftJoin<,,>), typeof(Customer), 
                    typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
                    typeof(LeftJoin<,,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
                    typeof(LeftJoin<,,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>), 
                    typeof(LeftJoin<,,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
                    typeof(LeftJoin<,,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>),
                    genericArguments[1], typeof(Where<Customer.bAccountID, IsNotNull>));
        }
        else if (genericTypeDefinition == typeof(Search2<,,>))
        {
            type = BqlCommand.Compose(typeof(Search2<,,>), typeof(BAccountR.bAccountID), typeof(LeftJoin<,,>), typeof(Customer), 
                typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
                typeof(LeftJoin<,,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
                typeof(LeftJoin<,,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>), 
                typeof(LeftJoin<,,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
                typeof(LeftJoin<,,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>), 
                genericArguments[1], genericArguments[2]);
        }
        else
        {
            if (!(genericTypeDefinition == typeof(Search2<,,,>)))
            {
                throw new PXArgumentException("search", "An invalid argument has been specified.");
            }

            type = BqlCommand.Compose(typeof(Search2<,,>), typeof(BAccountR.bAccountID), typeof(LeftJoin<,,>), typeof(Customer), 
                typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
                typeof(LeftJoin<,,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
                typeof(LeftJoin<,,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>), 
                typeof(LeftJoin<,,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
                typeof(LeftJoin<,,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>),
                genericArguments[1], genericArguments[2], genericArguments[3]);
        }

        PXDimensionSelectorAttribute pXDimensionSelectorAttribute;
        _Attributes.Add(pXDimensionSelectorAttribute = new PXDimensionSelectorAttribute("BIZACCT", type, typeof(BAccountR.acctCD),
                                    typeof(BAccountR.acctCD), typeof(Customer.acctName), typeof(Contact.phone1), typeof(Contact2.firstName),
                                    typeof(Customer.customerClassID), typeof(Customer.status), typeof(Address.city), typeof(Address.countryID),
                                    typeof(Address.addressLine1), typeof(Address.addressLine2), typeof(Address.postalCode)));


        //_Attributes.Add(pXDimensionSelectorAttribute = new PXDimensionSelectorAttribute("BIZACCT", type, typeof(BAccountR.acctCD),
        //                            typeof(BAccountR.acctCD), typeof(Customer.acctName), typeof(Contact.phone1), typeof(Contact2.firstName),
        //                            typeof(Customer.customerClassID), typeof(Customer.status), typeof(Address.city), typeof(Address.countryID),
        //                            typeof(Address.addressLine1), typeof(Address.addressLine2), typeof(Address.postalCode))); 
        pXDimensionSelectorAttribute.DescriptionField = typeof(Customer.acctName);
        pXDimensionSelectorAttribute.CacheGlobal = true;
        pXDimensionSelectorAttribute.FilterEntity = typeof(Customer);
        _SelAttrIndex = _Attributes.Count - 1;
        Filterable = true;
        _fields = fields;


        //Type genericTypeDefinition = search.GetGenericTypeDefinition();
        //Type[] genericArguments = search.GetGenericArguments();
        //Type type;
        //type = BqlCommand.Compose(typeof(Search2<,>), typeof(BAccountR.bAccountID), 
        //    typeof(LeftJoin<,>), typeof(Customer), typeof(On<Customer.bAccountID, Equal<BAccountR.bAccountID>, And<Match<Customer, Current<AccessInfo.userName>>>>), 
        //    typeof(LeftJoin<,>), typeof(Contact), typeof(On<Contact.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.defContactID>>>), 
        //    typeof(LeftJoin<,>), typeof(Address), typeof(On<Address.bAccountID, Equal<BAccountR.bAccountID>, And<Address.addressID, Equal<BAccountR.defAddressID>>>), 
        //    typeof(LeftJoin<,>), typeof(Location), typeof(On<Location.bAccountID, Equal<BAccountR.bAccountID>, And<Location.locationID, Equal<BAccountR.defLocationID>>>),
        //    typeof(LeftJoin<,>), typeof(Contact2), typeof(On<Contact2.bAccountID, Equal<BAccountR.bAccountID>, And<Contact.contactID, Equal<BAccountR.primaryContactID>>>));


        //PXDimensionSelectorAttribute pXDimensionSelectorAttribute;
        //_Attributes.Add(pXDimensionSelectorAttribute = new PXDimensionSelectorAttribute("BIZACCT", type, typeof(BAccountR.acctCD), 
        //                            typeof(BAccountR.acctCD), typeof(Customer.acctName), typeof(Contact.phone1), typeof(Contact2.firstName), 
        //                            typeof(Customer.customerClassID), typeof(Customer.status), typeof(Address.city), typeof(Address.countryID), 
        //                            typeof(Address.addressLine1), typeof(Address.addressLine2), typeof(Address.postalCode)));
        //pXDimensionSelectorAttribute.DescriptionField = typeof(Customer.acctName);
        //pXDimensionSelectorAttribute.CacheGlobal = true;
        //pXDimensionSelectorAttribute.FilterEntity = typeof(Customer);
        //_SelAttrIndex = _Attributes.Count - 1;
        //Filterable = true;
        //_fields = fields;
    }
}

When I want to add a join to the same DAC in one BQL command using another key, I cannot do it. How can I get the telephone from Contact by DefaultContactID and the primary contact name by PrimaryContactID?

1 reply

arpine08
Jr Varsity I
Forum|alt.badge.img+1
  • arpine08
  • Jr Varsity I
  • February 22, 2026

Hi @mos11,

To join the same DAC to itself in a single BQL command, you can create a derived class (alias) for the secondary instances of the table. This prevents mapping conflicts.

For example:

// Define the alias class
public class ContactAlias : Contact
{
    public new abstract class contactID : BqlInt.Field<contactID> { }
    // ...
}

 

Here are my suggestions.

Approach 1: Using the Customer Attribute with Aliases

DAC→ FIeld


        #region UsrCustomerID
        [Customer(
            typeof(Search2<Customer.bAccountID,
                  LeftJoin<BZPrimaryContact,
                        On<BZPrimaryContact.contactID, Equal<Customer.primaryContactID>>,
                  LeftJoin<BZDefaultContact,
                        On<BZDefaultContact.contactID, Equal<Customer.defContactID>>>>>),
            // Add Join with other tables if needed
            typeof(Customer.acctCD),
            typeof(Customer.acctName),
            typeof(Customer.customerClassID),
            typeof(Customer.status),
            typeof(BZPrimaryContact.displayName),
            typeof(BZDefaultContact.phone1),
            Visibility = PXUIVisibility.SelectorVisible,
            DescriptionField = typeof(Customer.acctName),
            DisplayName = "Customer ID",
            Filterable = true
        )]
        [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
        public int? UsrCustomerID { get; set; }
        public abstract class usrCustomerID : BqlInt.Field<usrCustomerID> { }
        #endregion

Aliases:

public class BZPrimaryContact : Contact
{
    public new abstract class contactID : BqlInt.Field<contactID> { }

    [PXDBString(255, IsUnicode = true)]
    [PXUIField(DisplayName = "Contact Name")]
    public new virtual string DisplayName { get; set; }
    public new abstract class displayName : BqlString.Field<displayName> { }
}

public class BZDefaultContact : Contact
{
    public new abstract class contactID : BqlInt.Field<contactID> { }

    [PXDBString(50, IsUnicode = true)]
    [PXUIField(DisplayName = "Contact Phone")]
    public new virtual string Phone1 { get; set; }
    public new abstract class phone1 : BqlString.Field<phone1> { }
}

Result:

 

Approach 2: Using the PXDimensionSelector Attribute with Aliases

DAC→ FIeld

#region UsrCustomerID
[PXDBInt]
[PXDimensionSelector(CustomerAttribute.DimensionName,
    typeof(Search2<Customer.bAccountID,
          LeftJoin<BZPrimaryContact,
                On<BZPrimaryContact.contactID, Equal<Customer.primaryContactID>>,
          LeftJoin<BZDefaultContact,
                On<BZDefaultContact.contactID, Equal<Customer.defContactID>>>>,
             // Add Join with other tables if needed
             Where<Customer.status, Equal<CustomerStatus.active>>>),
    typeof(Customer.acctCD),
        new Type[]
        {
            typeof(Customer.acctCD),
            typeof(Customer.acctName),
            typeof(Customer.customerClassID),
            typeof(Customer.status),
            typeof(BZPrimaryContact.displayName),
            typeof(BZDefaultContact.phone1),
        },
    DescriptionField = typeof(Customer.acctName),
    Filterable = true
)]
[PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
[PXUIField(DisplayName = "Customer ID", Visibility = PXUIVisibility.SelectorVisible)]
public int? UsrCustomerID { get; set; }
public abstract class usrCustomerID : BqlInt.Field<usrCustomerID> { }
#endregion

Aliases:

  public class BZPrimaryContact : Contact
  {
      public new abstract class contactID : BqlInt.Field<contactID> { }

      [PXDBString(255, IsUnicode = true)]
      [PXUIField(DisplayName = "Contact Name")]
      public new virtual string DisplayName { get; set; }
      public new abstract class displayName : BqlString.Field<displayName> { }
  }

  public class BZDefaultContact : Contact
  {
      public new abstract class contactID : BqlInt.Field<contactID> { }

      [PXDBString(50, IsUnicode = true)]
      [PXUIField(DisplayName = "Contact Phone")]
      public new virtual string Phone1 { get; set; }
      public new abstract class phone1 : BqlString.Field<phone1> { }
  }

Result:

 

 Approach 3: Using PXCustomSelectorAttribute and GetRecords() — Build a dynamic Customer lookup with Aliases.

For this approach, define a separate DAC to specify the fields displayed in the lookup.

 public class BZCustomerAttribute : PXCustomSelectorAttribute
 {
     public BZCustomerAttribute() : base(
             typeof(BZCustomerLookup.bAccountID),
             typeof(BZCustomerLookup.acctCD),
             typeof(BZCustomerLookup.status),
             typeof(BZCustomerLookup.contactDisplayName),
             typeof(BZCustomerLookup.contactPhone)
             )
     {
         this.SubstituteKey = typeof(BZCustomerLookup.acctCD);
         this.Filterable = true;
     }

     protected IEnumerable GetRecords()
     {
         List<BZCustomerLookup> list = new List<BZCustomerLookup>();

         PXSelectBase<Customer> cmd = SelectCommandCustomer(_Graph);
         foreach (PXResult<Customer, BZPrimaryContact, BZDefaultContact> res in cmd.Select())
         {
             Customer customer = (Customer)res;
             BZPrimaryContact pContact = (BZPrimaryContact)res;
             BZDefaultContact dContact = (BZDefaultContact)res;

             BZCustomerLookup newLine = new BZCustomerLookup
             {
                 BAccountID = customer.BAccountID,
                 AcctCD = customer.AcctCD,
                 Status = customer.Status,
                 ContactDisplayName = pContact.DisplayName,
                 ContactPhone = dContact.Phone1,
             };
             list.Add(newLine);
         }
         return list;
     }
     public PXSelectBase<Customer> SelectCommandCustomer(PXGraph graph)
     {
         if (graph == null) throw new ArgumentNullException(nameof(graph));

         return new PXSelectReadonly3<Customer,
                             LeftJoin<BZPrimaryContact,
                                   On<BZPrimaryContact.contactID, Equal<Customer.primaryContactID>>,
                             LeftJoin<BZDefaultContact,
                                   On<BZDefaultContact.contactID, Equal<Customer.defContactID>>>>,
                              OrderBy<Asc<Customer.acctCD>>>(graph);
     }
 }

 #region Alias definitions for multiple joins on the same table
 public class BZPrimaryContact : Contact
 {
     public new abstract class contactID : BqlInt.Field<contactID> { }
 }

 public class BZDefaultContact : Contact
 {
     public new abstract class contactID : BqlInt.Field<contactID> { }
 }
 #endregion


  #region BZCustomerLookup
  // Acuminator disable once PX1069 MissingMandatoryDacFields [Justification]
  [Serializable]
  [PXHidden]
  [PXCacheName("Customer Lookup")]
  public class BZCustomerLookup : PXBqlTable, IBqlTable
  {
      #region BAccountID
      [PXDBInt(IsKey = true)]
      [PXUIField(DisplayName = "Customer ID", Visibility = PXUIVisibility.Invisible)]
      public virtual int? BAccountID { get; set; }
      public abstract class bAccountID : BqlInt.Field<bAccountID> { }
      #endregion

      #region AcctCD
      [PXDBString(30, IsUnicode = true)]
      [PXUIField(DisplayName = "Customer ID")]
      public virtual string AcctCD { get; set; }
      public abstract class acctCD : BqlString.Field<acctCD> { }
      #endregion

      #region Status
      [PXDBString(1, IsFixed = true)]
      [PXUIField(DisplayName = "Customer Status")]
      [CustomerStatus.List]
      public virtual string Status { get; set; }
      public abstract class status : BqlString.Field<status> { }
      #endregion

      #region ContactDisplayName
      [PXDBString(255, IsUnicode = true)]
      [PXUIField(DisplayName = "Contact Name")]
      public virtual string ContactDisplayName { get; set; }
      public abstract class contactDisplayName : BqlString.Field<contactDisplayName> { }
      #endregion

      #region ContactPhone
      [PXDBString(50, IsUnicode = true)]
      [PXUIField(DisplayName = "Contact Phone")]
      public virtual string ContactPhone { get; set; }
      public abstract class contactPhone : BqlString.Field<contactPhone> { }
      #endregion

      // add additional field(s) if needed 
  }
  #endregion

 

DAC → Field

 #region UsrCustomerID
 [PXDBInt]
 [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
 [PXUIField(DisplayName = "Customer ID", Visibility = PXUIVisibility.SelectorVisible)]
 [BZCustomerAttribute]
 public int? UsrCustomerID { get; set; }
 public abstract class usrCustomerID : BqlInt.Field<usrCustomerID> { }
 #endregion

Result:

 

Alternatively, you can use a PXProjection(as mentioned in your code). In this case, you can use the following structure, which does not derive from the Contact class directly:

   [Serializable]
   [PXHidden]
   [PXCacheName("Primary Contact")]
   [PXProjection(typeof(Select<Contact>))]
   public class BZProjPrimaryContact : PXBqlTable, IBqlTable
   {
       #region ContactID
       [PXDBInt(BqlField = typeof(Contact.contactID), IsKey = true)]
       public virtual int? ContactID { get; set; }
       public abstract class contactID : BqlInt.Field<contactID> { }
       #endregion

       #region DisplayName
       [PXDBString(255, IsUnicode = true, BqlField = typeof(Contact.displayName))]
       [PXUIField(DisplayName = "Contact Name")]
       public virtual string DisplayName { get; set; }
       public abstract class displayName : BqlString.Field<displayName> { }
       #endregion
   }

   [Serializable]
   [PXHidden]
   [PXCacheName("Default Contact")]
   [PXProjection(typeof(Select<Contact>))]
   public class BZProjDefaultContact : PXBqlTable, IBqlTable
   {
       #region ContactID
       [PXDBInt(BqlField = typeof(Contact.contactID), IsKey = true)]
       public virtual int? ContactID { get; set; }
       public abstract class contactID : BqlInt.Field<contactID> { }
       #endregion

       #region Phone1
       [PXDBString(50, IsUnicode = true, BqlField = typeof(Contact.phone1))]
       [PXUIField(DisplayName = "Contact Phone")]
       public virtual string Phone1 { get; set; }
       public abstract class phone1 : BqlString.Field<phone1> { }
       #endregion
   }

 

For filtering lookup fields, please make sure that the FilterByAllFields property is set to True in the Customization Project Browser:

 

 

Terms & ConditionsCookie settingsAccessibility statement
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