Filter or Restrict customers in lookup (Selector) that are parent customers

Why couldn’t you do this?

[PXRestrictor(typeof(Where<BAccount.parentBAccountID.IsNull>), "Has parent")]

Edit:

Ah,

[PXRestrictor(typeof(Where<BAccount.parentBAccountID.IsNull>), "Has parent")]

Right, it’s easy to know if I’m a child, but not if I’m a parent :)

How about:

[PXRestrictor(typeof(Where<NotExists<
    SelectFrom<BAccount>.
    Where<BAccount.parentBAccountID.IsEqual<BAccount.bAccountID.FromCurrent>>>>), 
    "Is parent")]

[PXRestrictor(typeof(Where<NotExists<
    SelectFrom<BAccount>.
    Where<BAccount.parentBAccountID.IsEqual<BAccount.bAccountID.FromCurrent>>>>), 
    "Is parent")]

This would be amazing if it works.  I tried using the CacheAttached event on the ARInvoice DAC from the ARInvoiceEntry graph extension:

[PXMergeAttributes(Method = MergeMethod.Append)]
[PXRestrictor(typeof(Where<NotExists<SelectFrom<BAccount>.
Where<BAccount.parentBAccountID.IsEqual<BAccount.bAccountID.FromCurrent>>>>), "Is parent")]
protected virtual void ARInvoice_CustomerID_CacheAttached(PXCache sender)
{

}

but then I get no records in the popup search/selector.  Not sure what the problem is.

@rjean09 what @darylbowman wrote is valid but I am not sure why it is not working. Try this alternative to see how it pays out:

        [PXMergeAttributes(Method = MergeMethod.Merge)]
        [PXRestrictor(typeof(Where<Customer.parentBAccountID.IsNull // When is null then is parent
            .And<Customer.bAccountID.IsInSubselect<SelectFrom<BAccount> // Now search if the parent account has a tie to the current customer that you are trying to get it's parent.
                .Where<BAccount.bAccountID.IsEqual<Customer.bAccountID.FromCurrent>>
                .SearchFor<Customer.parentBAccountID>>>>), "Is parent")]
        protected virtual void ARInvoice_CustomerID_CacheAttached(PXCache sender)
        {

        }

Sorry my post is submited for moderaor review so I can’t edit my previous post. I updated the SearchFor to read from BAccount:

[PXMergeAttributes(Method = MergeMethod.Merge)]
[PXRestrictor(typeof(Where<Customer.parentBAccountID.IsNull
    .And<Customer.bAccountID.IsInSubselect<SelectFrom<BAccount>
        .Where<BAccount.bAccountID.IsEqual<Customer.bAccountID.FromCurrent>>
        .SearchFor<BAccount.parentBAccountID>>>>), "Is parent")]
protected virtual void ARInvoice_CustomerID_CacheAttached(PXCache sender)
{

}

[PXMergeAttributes(Method = MergeMethod.Merge)]
[PXRestrictor(typeof(Where<Customer.parentBAccountID.IsNull
    .And<Customer.bAccountID.IsInSubselect<SelectFrom<BAccount>
        .Where<BAccount.bAccountID.IsEqual<Customer.bAccountID.FromCurrent>>
        .SearchFor<BAccount.parentBAccountID>>>>), "Is parent")]
protected virtual void ARInvoice_CustomerID_CacheAttached(PXCache sender)
{

}

Hi aaghaei,

Unfortunately, none of the CacheAttached/Graph extension solutions by themselves seem to work.  

I think I’m very close, however, creating a PXDBCalced field and using that within the PXRestrictor:

    public class CustomerExt : PXCacheExtension<Customer>
    {
        public abstract class isParent : PX.Data.BQL.BqlBool.Field<isParent> { }

        [PXBool]
        [PXUIField(DisplayName = "Is Parent")]
        [PXDBCalced(typeof(IIf<
        Exists<Select<BAccount,
               Where<BAccount.parentBAccountID, Equal<Customer.bAccountID>>>>,
        True, False>), typeof(bool))]
        public virtual bool? IsParent { get; set; }
    }

Then in the graph extension:

    public class SOInvoiceEntry_Extension : PXGraphExtension<SOInvoiceEntry>
  {

        [PXMergeAttributes(Method = MergeMethod.Append)]
        [PXRestrictor(
            typeof(Where<CustomerExt.isParent, Equal<False>>),
            "Account cannot be selected because it is a parent account.",
            typeof(Customer.acctCD))]
        protected void _(Events.CacheAttached<ARInvoice.customerID> e)
        {
        }
    }

This works in the SO Invoice screen as expected and prevents selecting a parent customer.  I can also view this property on the customer generic inquiry list.  

However, when trying to add or view a customer in AR303000 I get this error:

Insufficient stack to continue executing the program safely. This can happen from having too many functions on the call stack or function on the stack using too much stack space.

But I’m not sure why.  Any ideas?

[PXDBCalced(
    typeof(IIf<
        Exists<
            SelectFrom<BAccountR>.
            Where<BAccountR.parentBAccountID.IsEqual<Customer.bAccountID>>>,
    True, False>), typeof(bool))]

Thanks!  This appears to be working perfect now.  Thanks to all who responded.  I also need to give some credit to ChatGPT-4o and StackOverflow for helping me get close.

I have a follow-up question.  I need to do the same thing on some screens/graphs that use BAccount vs. Customer as the referenced foreign key.  I attempted to do the same thing that worked for customer but am getting another stack overflow when I try it.  I still try using BAccountR but that doesn’t seem to help. 

namespace PX.Objects.CR
{
    // Acuminator disable once PX1016 ExtensionDoesNotDeclareIsActiveMethod extension should be constantly active
    // Acuminator disable once PX1011 InheritanceFromPXCacheExtension [Justification]
    public class BAccountExt : PXCacheExtension<BAccount>
    {
        public abstract class isParentAcct : PX.Data.BQL.BqlBool.Field<isParentAcct> { }

        [PXBool]
        [PXUIField(DisplayName = "Is Parent Acct")]
        [PXDBCalced(typeof(IIf<
        Exists<Select<BAccountR,
               Where<BAccountR.parentBAccountID, Equal<BAccount.bAccountID>>>>,True, False>), typeof(bool))]
        public virtual bool? IsParentAcct { get; set; }
    }
}

Any thoughts?

BAccountR inherits from BAccount, meaning that when BAccountR is searched, it is using all the fields from BAccount, including calculating the ‘IsParentAcct’ field.

Try defining a cache extension on BAccountR and put in the exact same field definition for ‘IsParentAcct’, except without the PXDBCalced attribute. In theory, this will replace the field inherited from BAccount with a version that will not attempt to calculate the ‘IsParentAcct’ field.

Then I think you should be able to use your other field exactly as you have it.

Hi Daryl,

That totally makes sense why this was creating a stack overflow.

You’re idea didn’t work quite exact.  It got rid of the stack overflow but then told me every customer selection was a parent. 

But this gave me another idea.

I just used BAccountRExt and then query BAccountParent in my PXDBCalced expression.  Completely removing BAccount DAC from any involvement.

    // Acuminator disable once PX1016 ExtensionDoesNotDeclareIsActiveMethod extension should be constantly active
    // Acuminator disable once PX1011 InheritanceFromPXCacheExtension [Justification]
    public class BAccountRExt : PXCacheExtension<BAccountR>
    {
        public abstract class isParentAcct : PX.Data.BQL.BqlBool.Field<isParentAcct> { }

        [PXBool]
        [PXUIField(DisplayName = "Is Parent Acct")]
        [PXDBCalced(typeof(IIf<
        Exists<Select<BAccountParent,
               Where<BAccountParent.parentBAccountID, Equal<BAccount.bAccountID>>>>, True, False>), typeof(bool))]
        public virtual bool? IsParentAcct { get; set; }
    }

Then, in my graphs I just use this ext and field:

    // Acuminator disable once PX1016 ExtensionDoesNotDeclareIsActiveMethod extension should be constantly active
    public class SOInvoiceEntry_Extension : PXGraphExtension<SOInvoiceEntry>
    {
        [PXMergeAttributes(Method = MergeMethod.Append)]
        [PXRestrictor(
            typeof(Where<BAccountRExt.isParentAcct, Equal<False>>),
            "Customer cannot be selected because it is a parent customer.",
            typeof(Customer.acctCD))]
        protected void _(Events.CacheAttached<ARInvoice.customerID> e)
        {
        }
    }

Now I should be able to use this extension between Customer/BAccount reference regardless.

Nice, way to go

This all worked great until I got to the graphs in CR namespace.  Apparently, BAccountR does not participate in the selector queries for these (specifically, Leads, Opportunities and Quotes)

Only with these 3 graphs and trying to use the existing BAccountRExt with the PXDBCalced I have results in “The multi-part identifier BAccountR.BAccountID could not be bound” message.

So I’m trying to do it all withing the graph CacheAttached with the following:

public class LeadMaint_Extension : PXGraphExtension<LeadMaint>
{

	[PXMergeAttributes(Method = MergeMethod.Append)]
	[PXRemoveBaseAttribute(typeof(PXSelectorAttribute))]
	[PXSelector(
	typeof(Search2<BAccount.bAccountID,
		LeftJoin<BAccountParent,
			On<BAccountParent.parentBAccountID, Equal<BAccount.bAccountID>>>>),
	typeof(BAccount.acctCD),
	typeof(BAccount.acctName),
	typeof(BAccount.type),
	SubstituteKey = typeof(BAccount.acctCD),
	DescriptionField = typeof(BAccount.acctName))]
	[PXRestrictor(
	typeof(Where<BAccountParent.bAccountID, IsNull>),
	"Customer/Vendor cannot be selected because it is a parent account.",typeof(BAccount.acctCD))]
	protected void _(Events.CacheAttached<CRLead.bAccountID> e)
	{
	}

}

Which does not apply the PXRestrictor to the query (I looked in profiler) and also every account I select gives me the message: “Customer/Vendor cannot be selected because it is a parent”

Here is the profiler SQL:

SELECT /* CR.30.10.00, A23B3E5C */ TOP (19) [BAccount].[BAccountID]
	,[BAccount].[AcctCD]
	,[BAccount].[AcctName]
	,[BAccount].[ClassID]
	,[BAccount].[LegalName]
	,[BAccount].[Type]
	,(
		CASE 
			WHEN (
					[BAccount].[Type] = 'CU'
					OR [BAccount].[Type] = 'VC'
					)
				THEN CONVERT(BIT, 1)
			ELSE CONVERT(BIT, 0)
			END
		)
	,[BAccount].[IsBranch]
	,[BAccount].[AcctReferenceNbr]
	,[BAccount].[ParentBAccountID]
	,[BAccount].[ConsolidateToParent]
	,[BAccount].[ConsolidatingBAccountID]
	,[BAccount].[COrgBAccountID]
	,[BAccount].[VOrgBAccountID]
	,[BAccount].[BaseCuryID]
	,[BAccount].[CuryID]
	,[BAccount].[CuryRateTypeID]
	,[BAccount].[AllowOverrideCury]
	,[BAccount].[AllowOverrideRate]
	,[BAccount].[Status]
	,[BAccount].[VStatus]
	,[BAccount].[CampaignSourceID]
	,[BAccount].[DefAddressID]
	,[BAccount].[DefContactID]
	,[BAccount].[DefLocationID]
	,[BAccount].[TaxRegistrationID]
	,[BAccount].[WorkgroupID]
	,[BAccount].[PrimaryContactID]
	,[BAccount].[GroupMask]
	,[BAccount].[OwnerID]
	,[BAccount].[NoteID]
	,(
		SELECT TOP (1) [Note].[NoteText]
		FROM [Note] [Note]
		WHERE (
				[Note].[CompanyID] IN (
					1
					,2
					)
				AND 8 = SUBSTRING([Note].[CompanyMask], 1, 1) & 8
				)
			AND [Note].[NoteId] = [BAccount].[NoteID]
		)
	,NULL
	,NULL
	,NULL
	,[BAccount].[tstamp]
	,[BAccount].[CreatedByID]
	,[BAccount].[CreatedByScreenID]
	,[BAccount].[CreatedDateTime]
	,[BAccount].[LastModifiedByID]
	,[BAccount].[LastModifiedByScreenID]
	,[BAccount].[LastModifiedDateTime]
	,[BAccount].[OverrideSalesTerritory]
	,[BAccount].[SalesTerritoryID]
	,[BAccount].[PseudonymizationStatus]
	,[BAccountParent].[BAccountID]
	,[BAccountParent].[ClassID]
	,[BAccountParent].[LegalName]
	,[BAccountParent].[Type]
	,(
		CASE 
			WHEN (
					[BAccountParent].[Type] = 'CU'
					OR [BAccountParent].[Type] = 'VC'
					)
				THEN CONVERT(BIT, 1)
			ELSE CONVERT(BIT, 0)
			END
		)
	,[BAccountParent].[IsBranch]
	,[BAccountParent].[AcctReferenceNbr]
	,[BAccountParent].[ParentBAccountID]
	,[BAccountParent].[ConsolidateToParent]
	,[BAccountParent].[ConsolidatingBAccountID]
	,[BAccountParent].[COrgBAccountID]
	,[BAccountParent].[VOrgBAccountID]
	,[BAccountParent].[BaseCuryID]
	,[BAccountParent].[CuryID]
	,[BAccountParent].[CuryRateTypeID]
	,[BAccountParent].[AllowOverrideCury]
	,[BAccountParent].[AllowOverrideRate]
	,[BAccountParent].[Status]
	,[BAccountParent].[VStatus]
	,[BAccountParent].[CampaignSourceID]
	,[BAccountParent].[DefAddressID]
	,[BAccountParent].[DefContactID]
	,[BAccountParent].[DefLocationID]
	,[BAccountParent].[TaxRegistrationID]
	,[BAccountParent].[WorkgroupID]
	,[BAccountParent].[PrimaryContactID]
	,[BAccountParent].[GroupMask]
	,[BAccountParent].[OwnerID]
	,[BAccountParent].[NoteID]
	,NULL
	,NULL
	,NULL
	,NULL
	,[BAccountParent].[tstamp]
	,[BAccountParent].[CreatedByID]
	,[BAccountParent].[CreatedByScreenID]
	,[BAccountParent].[CreatedDateTime]
	,[BAccountParent].[LastModifiedByID]
	,[BAccountParent].[LastModifiedByScreenID]
	,[BAccountParent].[LastModifiedDateTime]
	,[BAccountParent].[OverrideSalesTerritory]
	,[BAccountParent].[SalesTerritoryID]
	,[BAccountParent].[AcctCD]
	,[BAccountParent].[AcctName]
	,[BAccountParent].[PseudonymizationStatus]
FROM [BAccount] [BAccount]
LEFT JOIN [BAccount] [BAccountParent] ON ([BAccountParent].[CompanyID] = 2)
	AND [BAccountParent].[DeletedDatabaseRecord] = 0
	AND [BAccountParent].[ParentBAccountID] = [BAccount].[BAccountID]
WHERE ([BAccount].[CompanyID] = 2)
	AND [BAccount].[DeletedDatabaseRecord] = 0
ORDER BY [BAccount].[AcctCD]
OPTION (OPTIMIZE FOR UNKNOWN)

If I paste this into SQL and add: 

“AND [BAccountParent].BAccountID IS NULL”

to the WHERE clause I get the correct results.