hi @ppowell,

We can override base field selector using cacheattached.

Here is the sample.

[PXMergeAttributes(Method = MergeMethod.Append)]
[PXSelector<Write Your Query>]
protected virtual void InventoryItem_PriceManagerID_CacheAttached(PXCache cache)
{
}

Thanks. I have been trying to construct a query to go inside <Write your query> but I can’t seem to get a working one.  I’m not entirely sure how to specify that UsersInRoles.RoleName should equal purchasing.

 

I came up with the following but not sure how to implement it in a selector and I know I need to somehow get ‘Purchasing’ into the bit that tells Required<UsersInRoles.Rolename> to use that. I also am not sure how to get from this to the selector returning Contact.ContactID_description which seems to be what this field uses normally or does this somehow get added to the existing definition of the selector and that would be returned automatically?. I’ve been looking at the documentation to get this far but not been able to find anything that specifically applies to this case.

If anyone has pointers for good sources to read I’d be grateful for that too as I’m still trying to get my head around how this stuff works.

 

kPXSelector<Users, InnerJoin<Contact,
                On<Contact.userID, Equal<Users.pKID>>,
                InnerJoin<UsersInRoles,
                On<UsersInRoles.username, Equal<Users.username>,
                And<UsersInRoles.Rolename, Equal<Required<UsersInRoles.Rolename>>>>>>>))]

Thanks,

Phil

After looking at it I realised I’m working about this the wrong way and should probably be doing it from Contact in the first place. 

I feel like I'm getting closer but still not there yet:

So SQL equivalent would be as below:

SELECT Contact.ContactID_description
FROM Contact
INNER JOIN Users
    ON Contact.UserID = Users.pKID
INNER JOIN UsersInRoles
    ON UsersInRoles.username = Users.username
WHERE
  UsersInRoles.Rolename = 'Purchasing'

so the equivalent BQL would be:

<Select<Contact.ContactID_description, InnerJoin<Users, On<Contact.UserID, Equal<Users.pKID>>,
InnerJoin<UsersInRoles, On<UsersInRoles.username, Equal<Users.username>>, Where<UsersInRoles.Rolename, Equal<'Purchasing'>>>>>>>>>

but I don't think it's the right syntax and I still don't know how to specify 'Purchasing'.

Thanks,

Phil

Hi @ppowell 

You can write the BQL like below. Add a constant value for the user role and assign it to the field.

 >PXSelector(typeof(Search2<Contact.contactID,
            InnerJoin<Users, On<Contact.userID, Equal<Users.pKID>>,
                InnerJoin<UsersInRoles, On<UsersInRoles.username, Equal<Users.username>>>>,
            Where<UsersInRoles.rolename,
            Equal<purchasing>>>), DescriptionField = typeof(Contact.fullName), DirtyRead = true)]


 public class purchasing : BqlString.Constant<purchasing> { public purchasing() : base("Purchasing") { } }

Thanks so much.  I had got to the following last night after reading various sources and trial and error, but I had a problem where the Contact ID was being displayed alongside the displayName, I guess because it was being merged in. I was missing the DescriptionField part of PXSelector.

You have been a great help.

 

private class PurchRole : Constant<string>
    {
      public PurchRole() : base("Purchasing")
      {
      }
    }

}PXSelector(typeof(Search2<Contact.contactID, InnerJoin<Users, On<Users.pKID, Equal<Contact.userID>>, InnerJoin<UsersInRoles, On<UsersInRoles.username, Equal<Users.username>>>>, Where<UsersInRoles.rolename, Equal<PurchRole>>>))]

  

 

Now I’m confused.  I thought the DescriptionField part was supposed to determine what is shown in the field but the field still displays the contactID and displayname like: (12345 - Phil Powell) after I select a contact from the Selector.

Is there a way to get it to just show the name?

Thanks,

Phil 

Hi @ppowell 

To show the name instead of ID, you can add the subtitutekey as ID and search with the full name. I did a small change on the BQL, please try with the below one. 

    PXSelector(typeof(Search2<Contact.fullName,
            InnerJoin<Users, On<Contact.userID, Equal<Users.pKID>>,
                InnerJoin<UsersInRoles, On<UsersInRoles.username, Equal<Users.username>>>>,
            Where<UsersInRoles.rolename,
            Equal<purchasing>>>),  SubstituteKey = typeof(Contact.contactID), DirtyRead = true)]
 

I discovered the problem.  The field wasn’t set to DisplayMode=Text.  Once I set that the first DescriptionField method worked perfectly.

Thanks so much for all your help.

Phil