Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sourav PSourav P 

Creating a validation rule on record saving , only by specific user groups , based on a criteria

Dear All,
I need to create a validation rule as per below criteria,
User-added image
The Premium adjtment % , is depends upon two above fields, the caalculation is " Adjustment Tech Premium - Technical premium / Technical Premium". The formula has not been created in SF and its coming from a different system. What i need is, that
If , Premium adjustment % is > 5 % ( only UW 2 group of users can able to modify the record and save it)
Premium adjustment % is > 10 % ( only UW 5 group of users can able to modify the record and save it)
Premium adjustment % is > 20% ( only UW 6 group of users can able to modify the record and save it)
Premium adjustment % is > 30 % ( only UW 7 group of users can able to modify the record and save it)

Can anybody plz suggest, how i can able to make this validation formula, with creating the specific group and adding it to the formula. Each group may have 2 4 spcific users. Thanks
 
 
Naveen DhanarajNaveen Dhanaraj
Try This, 
OR(
AND(
ISCHANGED( Premium adjustment __c >5 ),
NOT(OR( $User.LastName   = "Hartman" , $User.LastName   = "Cassidy" , $User.LastName   = "Hill" , $User.LastName   = "Beck" , $User.LastName   = "Bradley" , $User.LastName   = "Butler" , $User.LastName   = "Team"))),


AND(
ISCHANGED( Premium adjustment __c >10 ),
NOT(OR( $User.LastName   = "Hartman" , $User.LastName   = "Cassidy" , $User.LastName   = "Hill" , $User.LastName   = "Beck" , $User.LastName   = "Bradley" , $User.LastName   = "Butler" , $User.LastName   = "Team"))),

AND(
ISCHANGED( Premium adjustment __c >20 ),
NOT(OR( $User.LastName   = "Hartman" , $User.LastName   = "Cassidy" , $User.LastName   = "Hill" , $User.LastName   = "Beck" , $User.LastName   = "Bradley" , $User.LastName   = "Butler" , $User.LastName   = "Team"))),

AND(
ISCHANGED( Premium adjustment __c >30 ),
NOT(OR( $User.LastName   = "Hartman" , $User.LastName   = "Cassidy" , $User.LastName   = "Hill" , $User.LastName   = "Beck" , $User.LastName   = "Bradley" , $User.LastName   = "Butler" , $User.LastName   = "Team")))

)
Based on your wish you can use $User.Profile.Name or $User.LastName
 
Sourav PSourav P
Hii Naveen , Thanks. Based on your above suggestion i have taken like below, The requirement has slightly changed. The user group should not able to change the field " Adjusted Technical premium", i.e if the " premium adjustment % " field is > 10, only the UW 5 and above group ( 6,7) can change the Adjusted Technical Premium field. But they can change other fields in the record.

I preferred to make the group as roles, ( as profiles already added to some users as system admin or so). But my below code showing, " Syntax error missing ')'". Can you plz check where i went wrong. Thnx
  
OR(
AND(
Premium_adjustment_pct__c>5,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>'UW 2'
),
AND(
Premium_adjustment_pct__c>10,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>'UW 5'
$User.UserRoleId.Name<>'UW 6'
$User.UserRoleId.Name<>'UW 7'
),
AND(
Premium_adjustment_pct__c>20,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>'UW 6'
$User.UserRoleId.Name<>'UW 7'
),
AND(
Premium_adjustment_pct__c>30,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>'UW 7'
)
)

 
Sourav PSourav P
I changed as per below as well , but its till showing same syntax error as " missing )". Should i use UserRole.Name or UserRoleID.Name ?
  
OR(
AND(
Premium_adjustment_pct__c>5,
ISCHANGED(Adjusted_technical_premium__c),
NOT($User.UserRole.Name='UW 2')
),
AND(
Premium_adjustment_pct__c>10,
ISCHANGED(Adjusted_technical_premium__c),
NOT(OR($User.UserRole.Name='UW 5',
$User.UserRole.Name='UW 6',
$User.UserRole.Name='UW 7'))
),
AND(
Premium_adjustment_pct__c>20,
ISCHANGED(Adjusted_technical_premium__c),
NOT(OR($User.UserRole.Name='UW 6'
$User.UserRole.Name='UW 7'))
),
AND(
Premium_adjustment_pct__c>30,
ISCHANGED(Adjusted_technical_premium__c),
NOT($User.UserRole.Name='UW 7')
)
)

 
Naveen DhanarajNaveen Dhanaraj
Try This, 
OR(
AND(
Premium_adjustment_pct__c> 5,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>"UW 2"
),
AND(
Premium_adjustment_pct__c>10,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>"UW 5",
$User.UserRoleId.Name<>"UW 6",
$User.UserRoleId.Name<>"UW 7"
),
AND(
Premium_adjustment_pct__c>20,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>"UW 6",
$User.UserRoleId.Name<>"UW 7"
),
AND(
Premium_adjustment_pct__c>30,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId.Name<>"UW 7"
)
)

 
Naveen DhanarajNaveen Dhanaraj
You can use Both
Sourav PSourav P
Hi Naveen,

I tried as per below, Taking " UserRoleID.Name " showed error as field diest exists, so i have taken as " UserRoleID" only and it get saved. But it seems to me the validation rule not working as per needed, what i have taken is,
  
OR(
AND(
Premium_adjustment_pct__c> 5,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 2",
$User.UserRoleId<>"UW 5",
$User.UserRoleId<>"UW 6",
$User.UserRoleId<>"UW 7"
),
AND(
Premium_adjustment_pct__c>10,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 5",
$User.UserRoleId<>"UW 6",
$User.UserRoleId<>"UW 7"
),
AND(
Premium_adjustment_pct__c>20,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 6",
$User.UserRoleId<>"UW 7"
),
AND(
Premium_adjustment_pct__c>30,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 7"
)
)

Now, i have assigned myself as UW 6, i.e i can edit the Ad tech.premium field upto > 20. But on below case ( premium adjustment is calculated as 9000- 1000/1000 = 8, and till its showing me validation error, i think it shd show me error if its above 20. Isnt it.
Also, something going as minus sign, can you plz let me know rather than > 5 , how can i input its between 0 to 5 ? Thanks 
User-added image
 
Sourav PSourav P
IF(Adjusted_technical_premium__c>0,((Adjusted_technical_premium__c/ Technical_Premium__c)-1),0.00)

This is teh rule defined for the adjusted premium percentage. so as per this the above is coming 7, so i should able to edit, isnt it as UW 6 user
Sourav PSourav P
Another query plz, If i take the adjusted premium field as percentage ( not number), How do i change in teh formule, If i put >5%, its showing wrong syntax. what be teh best way that it can check it as percentage ?
Naveen DhanarajNaveen Dhanaraj
Give me some time sourav
Sourav PSourav P
Sure Naveen, Thanks
Sourav PSourav P
Hi Naveen, As of now i have created like this, plz evaluate if its correct once you free , thanks for your time.
  
OR(
AND(
ABS(Premium_adjustment_pct__c)>5/100 && ABS(Premium_adjustment_pct__c)<10/100,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 2",
$User.UserRoleId<>"UW 5",
$User.UserRoleId<>"UW 6",
$User.UserRoleId<>"UW 7"
),
AND(
ABS(Premium_adjustment_pct__c)>10/100 && ABS(Premium_adjustment_pct__c)<20/100,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 5",
$User.UserRoleId<>"UW 6",
$User.UserRoleId<>"UW 7"
),
AND(
ABS(Premium_adjustment_pct__c)>20/100 && ABS(Premium_adjustment_pct__c)<30/100,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 6",
$User.UserRoleId<>"UW 7"
),
AND(
ABS(Premium_adjustment_pct__c)>30/100,
ISCHANGED(Adjusted_technical_premium__c),
$User.UserRoleId<>"UW 7"
)
)

the formula field for " Premium_adjustment_pct__c" is as per below, 
IF(Adjusted_technical_premium__c>0,((Adjusted_technical_premium__c/ Technical_Premium__c)-1),0.00)
So, i have taken as 5/100 etc. if thats correct.
Thanks