Why I'm getting Muti-part identifier error in view query(after added the newly created extension field into the query?)

Question

Multi-part identifier error

I newly created one extension field in Contacts screen (UsrLocationCD int field). After creatimg that field I added that field into the view query and I got the above error.

The concept is the "Customer and Location ID" (Customer Location screen) should match in Contacts screen "Business Account and Location ID" (Location ID, newly added). After this condition is satisfied that related Contact ID should display in Customer Location screen under the Contacts Tab.

Full concept

This the query what I wrote:

    [PXViewName(Messages.Contacts)]
    [PXFilterable]
    [PXViewSavedDetailsButton(typeof(Location))]
    public PXSelectJoin<Contact,
         LeftJoin<Address, On<Address.addressID, Equal<Contact.defAddressID>>>,
             Where<Contact.bAccountID, Equal<Current<Location.bAccountID>>,
                 And<Where<ContactExt.usrLocationCD, Equal<Location.locationID>,
                    And<Where<Contact.contactType, Equal<ContactTypesAttribute.person>,
                        Or<Contact.contactType, Equal<ContactTypesAttribute.lead>>>>>>>> Contacts;

here is the newly created extension field:

public class ContactExt : PXCacheExtension<PX.Objects.CR.Contact> /*, IBqlTable*/
{
    #region UsrLocationCD
    [PXDBInt()]
    [PXUIField(DisplayName = "Location ID")]

    [PXSelector(
      typeof(Search<Location.locationID, Where<Location.bAccountID,
           Equal<Current<Contact.bAccountID>>>>),
        SubstituteKey = typeof(Location.locationCD), ValidateValue = false)]

    public virtual int? UsrLocationCD { get; set; }
    public abstract class usrLocationCD : PX.Data.BQL.BqlInt.Field<usrLocationCD> { }
    #endregion
}

I'm sharing one point here that newly created extension field is not creating any problem in the Contacts screen, successfully I'm able to saving the record you can see the below imgs.

Before saving the record

After saving the record

In the contacts screen location id field is "Int".

Where is the mistake and how to overcome this issue?

Also, since this is the Location ID, and not the Location CD, you might consider UsrLocationID as your field name, just to be consistent with the usual field names. — Brian Stevens, Sep 25 '20 at 22:14
Thank you Stevens. It was helped me and I don't know how I forgot that miner mistake. — Ashok, Sep 28 '20 at 06:04
I have made the same mistake many times myself when I got started. Did that fix it? If so, please post as an answer and accept your answer so that it shows as resolved. — Brian Stevens, Sep 28 '20 at 13:17
Yes, it was fixed and I put the Flag to that answer(I don't know how to accept comment answer). — Ashok, Sep 29 '20 at 09:50
You can't accept an answer in a comment, but you can add your own answer and then accept that. Solution added as an *answer* with a little more detail. (answers should be detailed enough that someone else finding your question when searching for help might gain a better understanding rather simply a *quick fix*.) — Brian Stevens, Sep 29 '20 at 11:33

score 0 · Answer 1 · answered Sep 27 '20 at 04:10

In the above code, join is missed for location DAC. I hope this may help you.

  [PXViewName(PX.Objects.CR.Messages.Contacts)]
    [PXFilterable]
    [PXViewSavedDetailsButton(typeof(Location))]
    public PXSelectJoin<Contact,
    LeftJoin<Address, On<Address.addressID, Equal<Contact.defAddressID>>,
        LeftJoin<Location, On <Location.bAccountID,Equal<Contact.bAccountID>>>>,
        Where<Contact.bAccountID, Equal<Current<Location.bAccountID>>,
            And<Where<ContactExt.usrLocationCD, Equal<Location.locationID>,
               And<Where<Contact.contactType, Equal<ContactTypesAttribute.person>,
                   Or<Contact.contactType, Equal<ContactTypesAttribute.lead>>>>>>>> Contacts;

score 0 · Accepted Answer · answered Sep 29 '20 at 11:29

Your PXSelect is missing a Current<> on where you added in your usrLocationCD.

Original line with missing Current<>:

And<Where<ContactExt.usrLocationCD, Equal<Location.locationID>,

After adding the missing Current<> back in:

[PXViewName(Messages.Contacts)]
[PXFilterable]
[PXViewSavedDetailsButton(typeof(Location))]
public PXSelectJoin<Contact,
     LeftJoin<Address, On<Address.addressID, Equal<Contact.defAddressID>>>,
         Where<Contact.bAccountID, Equal<Current<Location.bAccountID>>,
             And<Where<ContactExt.usrLocationCD, Equal<Current<Location.locationID>>,
                And<Where<Contact.contactType, Equal<ContactTypesAttribute.person>,
                    Or<Contact.contactType, Equal<ContactTypesAttribute.lead>>>>>>>> Contacts;

When selecting data, you always must connect the referenced DAC's in some way... either by joining directly to another table selected in the join, by joining the field to a Current value (such as a field in a parent view), or by supplying a parameter that you pass in.

Also, for consistency, I'd recommend changing the name of your new field from usrLocationCD to usrLocationID. ID means "identifier" and CD means "code". LocationID (the identifier) is the integer field used to identify the Location record, in this case. LocationCD is the field of the Location record that contains the Location Code that we normally see in the display. When another Acumatica developer looks at the code above, the first impression is that you are trying to relate a string field to an integer field. Technically, as long as your field type matches on both sides of the equals then it will work, but consistency is important in coding standards.

Why I'm getting Muti-part identifier error in view query(after added the newly created extension field into the query?)

2 Answers2