+ Start a Discussion
Christophe LucchiniChristophe Lucchini 

validation rule field

Hi Everyone !

I want to make a validation rule on the object "Opportunity", the rule is basically :

We want to check the Stage of the opportunity , if the opp is in stage = Negotiation / Verbal / Contract / Launched / Invoiced / Closed , and the field "agent" (Sales_Agent__c) is different from "None (Sales Agent)" and the field "Agent_Commission__c" is blank and the field "Agent_Commission_Amount_Manual__c" is blank.

So if all this is gathered, an error message is displayed:"Please check the agent commissions. ..."

So in order to do that , i have created a validation rule , like this:

AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",
OR(
ISBLANK( Agent_Commission__c )
,
Sales_Agent__c<>"None (Sales Agent)"
,
ISBLANK(Agent_Commission_Amount_Manual__c)))

But i have an issue , when we apply the validation rule , even if the field "Agent" is equal to "None (Sales Agent)" the rule still applies.

Thanks for your help !
Best Answer chosen by Christophe Lucchini
HARSHIL U PARIKHHARSHIL U PARIKH
I think I see where the problem is.. :)

Here we go, try this:
 
AND(

OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed"
),

ISBLANK( Agent_Commission__c ),
Sale_agent_TEXT__c != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
)

Hope this helps and if it solves the issue then mark it as Best Answer!

All Answers

GauravendraGauravendra
Hi Christophe,

As you mentioned you want both "Agent_Commission__c" should be blank and the field "agent" (Sales_Agent__c) is different from "None (Sales Agent)".
So, you need to provide AND condition between these two.

AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",
),
ISBLANK( Agent_Commission__c ),
Sales_Agent__c<>"None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c))

If my understanding is correct, then it should work. Check this and let me know.
Christophe LucchiniChristophe Lucchini
Hi Gauravendra,

I tried the code but when i change the Subsegment (New) , i still have the error message.

User-added image
HARSHIL U PARIKHHARSHIL U PARIKH
Try this: I have just tried to take TEXT out of Sales_Agent__c field.
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",
),
ISBLANK( Agent_Commission__c ),
TEXT(Sales_Agent__c) != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
)

If it solves the issue, mark it as best answer!
Christophe LucchiniChristophe Lucchini
Hi Harshil,
Sorry its not working with this code :
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",

ISBLANK( Agent_Commission__c ),
TEXT(Sales_Agent__c) != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
))
This is giving me the following error with "Sales_Agent__c":

Error: Incorrect parameter type for function 'TEXT()'. Expected Number, Date, DateTime, Picklist, received Lookup(Account)
 
HARSHIL U PARIKHHARSHIL U PARIKH
I would say create a seperate formula field named Sales_Agent__TEXT__c and take a copy of text from Sales_Agent__c field. So the formula would look like this:
Sales_Agent__TEXT__c = TEXT( Sales_Agent__c.Name ) - anything that fetching that full name.

Once done, try to reference this formula into our original formula. The end result would look somehting like below,
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",

ISBLANK( Agent_Commission__c ),
Sales_Agent__TEXT__c != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
))
Hope this helps!
 
Christophe LucchiniChristophe Lucchini
Hi i have created the field "Sales_Agent__TEXT__c" with the formula:

Sales_Agent__TEXT__c = Sales_Agent__c

Because you can't put TEXT() with a lookup field.

So i get the ID of the agent from "Sales_Agent__c", wich is for "None" = "0011000000mV2Cc".

So i put this formula:
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",

ISBLANK( Agent_Commission__c ),
Sales_Agent__TEXT__c != "0011000000mV2Cc",
ISBLANK(Agent_Commission_Amount_Manual__c)
))

But it's not working, its like the part :

"text(StageName) = "Negotiation", text(StageName) = "Verbal", text(StageName) = "Contract", text(StageName) = "Launched", text(StageName) = "Invoiced", text(StageName) = "Closed", "
is Passing in front of the rest of the formula ?
HARSHIL U PARIKHHARSHIL U PARIKH
I would say YES, we can take the text from a lookup field value.
Here we go,

User-added image

User-added image

Try to set it up and see if it helps.
Christophe LucchiniChristophe Lucchini
Yes sorry i was not doing this good so now the Sale agent TEXT is like :

User-added image
And its ok on the opportunity :

User-added image

But when i put the formula :
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",

ISBLANK( Agent_Commission__c ),
Sale_agent_TEXT__c != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
))
it's not working , in this example the field "Agent" is "None (Sales Agent)" / the field "Agent_Commission__c" is blank / the field "Agent_Commission_Amount_Manual__c" is blank and the "Stage" is "Contract" but i have th error message when i try to change the Segment:

User-added image
But with the Field ""Agent" is "None (Sales Agent)" , logically i don't have error message.

Thanks for your help !
 
HARSHIL U PARIKHHARSHIL U PARIKH
let's check the following,

Agent_Commission__c is blank OR zero (0)?

Agent_Commission_Amount_Manual__c is blank OR zero (0)?

Try this if that is the case,
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",

gent_Commission__c  = 0,
Sale_agent_TEXT__c != "None (Sales Agent)",
Agent_Commission_Amount_Manual__c = 0
))

also make sure there is no double space or any obvious issue in TEXT fields..
Christophe LucchiniChristophe Lucchini
Unfortunatly both fields are Blank:

User-added image

I don't understand why its not working, no space or anything else in TEXT fields.
 
HARSHIL U PARIKHHARSHIL U PARIKH
Wait..... In the screen shot you haave posted the one before (the one that shows the error message), it supposed to show the error message though Since

1) that agent TEXT field criteria matches which is "None (Sales Agent)"
2) Both of the comission field is null
3) I am not sure about the STAGE but it maybe either one of these: Negotiation, Verbal, Contract, Launched, Invoiced, Closed

I am not sure what is not working?!
Christophe LucchiniChristophe Lucchini
In fact in order to have the error message , this three conditions have to be reunited:

1)The agent text field have to be different from "None (Sales Agent)".
2)Both of the comission field are Blank/null
3)The stage have to be one of the following : Negotiation, Verbal, Contract, Launched, Invoiced, Closed

In the exemple condition 2 and 3 are ok but the 1 not because the field is "None (Sales Agent)".
HARSHIL U PARIKHHARSHIL U PARIKH
Hi Chris, Can you please send me your formula again?
Christophe LucchiniChristophe Lucchini
Ok no problem :
 
AND(
OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed",

ISBLANK( Agent_Commission__c ),
Sale_agent_TEXT__c != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
))
Thanks !
 
HARSHIL U PARIKHHARSHIL U PARIKH
I think I see where the problem is.. :)

Here we go, try this:
 
AND(

OR(
text(StageName) = "Negotiation",
text(StageName) = "Verbal",
text(StageName) = "Contract",
text(StageName) = "Launched",
text(StageName) = "Invoiced",
text(StageName) = "Closed"
),

ISBLANK( Agent_Commission__c ),
Sale_agent_TEXT__c != "None (Sales Agent)",
ISBLANK(Agent_Commission_Amount_Manual__c)
)

Hope this helps and if it solves the issue then mark it as Best Answer!
This was selected as the best answer
Christophe LucchiniChristophe Lucchini
Hi Thank you very much for your help ! with this code its working good! !!!