Skip to main content
Solved

Create a Selector with one additional option


I want a selector that will let me pick a BAccountID from a list of BAccounts which I filter, but I also want it to have one additional option I can pick “All Trading Partners”. If all I want is to pick a BAccountID then I would just use a vanilla selector, however since I also want that additional option, I’m trying to use a simple Custom Selector here is an example.
 

Here is the selector

public class SimpleSelectorAttribute : PXCustomSelectorAttribute
{
Serializable]
public class SelectorFields : IBqlTable
{
#region id
public abstract class id : PX.Data.BQL.BqlString.Field<id> {}
PXString]
public virtual string Id {get; set;}
#endregion

#region displayField
public abstract class displayField : PX.Data.BQL.BqlString.Field<displayField>{}
PXString]
public virtual string DisplayField {get; set;}
#endregion
}


public SimpleSelectorAttribute()
: base(typeof(SelectorFields.id))
{
this.SubstituteKey = typeof(SelectorFields.displayField);
}

public virtual IEnumerable GetRecords()
{
yield return new SelectorFields()
{ Id = "*ALL*", DisplayField = "All Trading Partners"};

foreach (BAccount cust in PXSelect<BAccount, Where<ISPSCustomerExtension.iSPSEDICust, Equal<True>>>.Select(this._Graph))
{
yield return new SelectorFields()
{ Id = cust.BAccountID.ToString(), DisplayField = cust.AcctCD };
}
}
}

And this is a field using it

        #region testField
public abstract class testField : PX.Data.BQL.BqlString.Field<testField>{}
PXString]
PXUIField(DisplayName = "testField")]
SimpleSelectorAttribute()]
public virtual string TestField{get; set;}
#endregion

 

This works great, except I need the field to be bound to a DB column so I have to change TestField from PXString to PXDBString. Once I do that(And of course make sure there is a matching column in the table) the selector the column bound to the selector will no longer let me sort or filter it via the Acumatica UI, throwing an “Invalid object name ‘SelectorFields’” error whenever I try. I could both sort and filter before I set the TestField to being bound to the DB.

 

I’ve also discovered that if I override SubstituteKeyCommandPreparing method inside my custom selector and just leave it blank the error is no longer thrown(Yay) but now when I try to sort the column is being sorted by the Id field instead of the DisplayField field(Boo). Which makes sense if the SubstituteKeyCommandPreparing method is what was applying the substitute key logic in the first place. After more testing I’ve found that the reason the “Invalid object name ‘SelectorFields’” error is being thrown when I try to sort my column is that it is an unbound DAC. When I use a bound DAC the sort/filter “Invalid object” error is not thrown.

If anyone has any thoughts or suggestions I’d appreciate them, adding one additional static element to a selector seems like it shouldn't be that hard, but I’m struggling :)
Philip Engesser

5 replies

Badge +12

Would it make sense to move the SelectorFields table outside of the SimpleSelectorAttribute class? Is its location somehow interfering?

Userlevel 4
Badge +1

Hi @darylbowman thanks for the suggestion. Unfortunately moving the SelectorFields DAC outside of the SimpleSelectorAttribute class doesn’t change the behavior.

 

On a side note I just noticed I messed up the title of my question, does anyone know if I can edit that?

Userlevel 7
Badge

Hi @paengesser82  - You can’t edit after a certain amount of time which has passed. Let me know what it should say and I can rename. 😀

Userlevel 4
Badge +1

@Chris Hackett good to know! The title should be “Create a Selector with one additional option”. Thank you!

Userlevel 4
Badge +1

So the best solution I’ve come up with is to create a SQL view which does what I need it to do and then have my selector use a dac which binds to that view. Here is the code for my SQL view

CREATE VIEW ISPSTPsUnionAll AS
SELECT DISTINCT '*ALL*' AS id, 'All Trading Partners' AS cd, 'All Trading Partners' AS name, BAccount.CompanyID
FROM BAccount
WHERE BAccount.CompanyID > 0
UNION ALL
SELECT CAST(BAccount.BAccountID AS VARCHAR(10)), BAccount.AcctCD, BAccount.AcctName, BAccount.CompanyID
FROM ISPSCustomerExtension
INNER JOIN BAccount ON BAccount.BAccountID = ISPSCustomerExtension.BAccountID
AND BAccount.CompanyID = ISPSCustomerExtension.CompanyID
WHERE ISPSEDICust = 1 AND BAccount.CompanyID > 0

The first part of the query, before the UNION ALL, adds the static option I want, I have to add one option for each CompanyID though which is why I use the BAccount.CompanyID and the Distinct.
The second part of the query, after the UNION ALL, adds all the other options I want.

I’ve read that views are not really the preferred way to doing things in Acumatica so I’ve tried other approaches to perform this task with out any success. I was especially hoping to get it to work using PXProjection since that is Acumatica’s version of a view, but was unable to get it to work.

At this point I’m going to test out the above solution with MySQL(I’ve been using MS SQL Server) and assuming that works and no one else knows of a better way of achieving this I’ll probably use the above SQL View. 

Reply