+ Start a Discussion
Abhijit Shrikhande 10Abhijit Shrikhande 10 

How do I implement this validation rule on an opportunity?

I have three objects. Two standard and one custom.
The standard objects are: Account and Opportunity.
The custom object is an object called Billing_Profile__C.

AccountId is the common field in all three objects. I am able to write a SOQL query like this.

Select o.Name, o.Id, o.Competitor__c, o.Amount, o.AccountId From Opportunity o WHERE o.AccountId NOT IN (Select  b.Account__c From Billing_Profile__c b)


This gives me a list of all the opportunities that have been created on accounts that do not have a billing profile. I want to put this in a validation rule on the opportunity object so that my user is not able to create an opportunity for any account that does not have a billing profile.

Firstly, instead of my query that simply returns a list of opportunities, how do I get the count of billing profiles for an account on an opportunity.

Secondly, how can I use that information in a validation rule when the user is creating this opportunity.

I have attempted to write a validation using VLOOKUP function. This is the rule I wrote.

VLOOKUP(
$ObjectType.Billing_Profile__c.Fields.Account__c,
$ObjectType.Billing_Profile__c.Fields.Name,
AccountId) <> AccountId
I am not sure if this rule is working as expected. I am getting an error on every save, regardless of the data. How can I correct this situation? Is there some way to log the values for each of the parameters I am sending? Can I test my function in the execute anonymous window?

Patrick Maxwell AppfolioPatrick Maxwell Appfolio
Hi Abhijit,

Unfortunately, I can't really test this in my environment, but I think I might see where you're steered in the wrong direction  In your formula

VLOOKUP($ObjectType.Billing_Profile__c.Fields.Account__c, $ObjectType.Billing_Profile__c.Fields.Name, AccountId) <> AccountId

You are cross referencing the current related Account ID field with the name of the Billing Profile.  Now I don't know if you attach a Billing Profiie via lookup to the Opportunity, but if you do, then this should work.

VLOOKUP($ObjectType.Billing_Profile__c.Fields.Account__c, $ObjectType.Billing_Profile__c.Fields.Name, Billing_Profile__c.Name) <> AccountId

But I'm assuming that you don't put the Billing Profile on the Opp and that's the problem that you're trying to solve for.

So, that being said, you're probably going to have to make this a somewhat manual process and put the actual billing profile names in your Validation Rule.  This is easy if you don't have many different Biling Profiles, but obviously unmanagable if you have a lot.  See example below.

(VLOOKUP($ObjectType.Billing_Profile__c.Fields.Account__c, $ObjectType.Billing_Profile__c.Fields.Name, 'Credit Card') <> AccountId) && (VLOOKUP($ObjectType.Billing_Profile__c.Fields.Account__c, $ObjectType.Billing_Profile__c.Fields.Name, 'ACH') <> AccountId)

Good luck getting this figured out!
Patrick
Abhijit Shrikhande 10Abhijit Shrikhande 10
Hi Patrick,
You're correct in your assumption. The billing profile custom object is not related to the opp. This is the problem, I am trying to solve.