+ Start a Discussion
JudyJudy 

Formulas to assign user role

Hi
 
I'm trying to create a formula for below, but can't seem to get it working..
Any help is appreciated!
 
Owner role = Asia Sales; assign Region field to Asia
 
Owner role = ANZ Sales; assign Region field to ANZ
Owner role = ANZ Managing Director, assign Region field to ANZ
 
Owner role = China Sales; assign Region field to China
Owner role = China Sales Management; assign Region field to China
Owner role = China Managing Director, assign Region field to China
 
Owner role = Japan Sales; assign Region field to Japan
Owner role = Japan Sales Management; assign Region field to Japan
Owner role = Japan Managing Director, assign Region field to Japan
 
Thanks, Judy
NPMNPM
Judy - could you paste in your code and describe the Object (User, other(s)?) and fields types you are working with (Picklist, text, etc.), and any error messages youare getting?  At first glance it looks like maybe a CASE formula field for Region could work.  May need to use the role id instead of the role name.  Use the SRC button to past in your code.
JudyJudy
Hi !
 
In the opportunity report, there's a Owner Role field.
I'm trying to use the owner role field to identify the region for the opportunity record i.e. if the owner role equal to ANZ MD, ANZ Sales, ANZ Consulting, then the region field should automatically reflect as ANZ ...
 
Using below formula but there's a syntax error. Alternatively, is there another shorter / easier formula for below? thanks!
 

CASE(

($UserRole.Name),

"ANZ MD","ANZ",

"ANZ Consulting Manager","ANZ",

"ANZ Marketing","ANZ",

"ANZ Presales", "ANZ"

"ANZ Sales Management","ANZ",

"ANZ Sales", "ANZ"

"Asia MD", "Asia"

"Asia Consulting Manager", "Asia"

"Asia Marketing", "Asia"

"Asia Presales", "Asia"

"Asia Sales", "Asia"

"China MD", "China"

"China Consulting Manager", "China"

"China Sales Management", "China"

"China Sales", "China"

"India MD", "India"

"India Presales", "India"

"India Sales", "India"

"Japan MD", "Japan"

"Japan Consulting Manager", "Japan"

"Japan Sales Management", "Japan"

"Japan Sales", "Japan", "")

NPMNPM

Judy it looks like a number of commas (,) are missing:

Code:
CASE(

($UserRole.Name),
"ANZ MD","ANZ",
"ANZ Consulting Manager","ANZ",
"ANZ Marketing","ANZ",
"ANZ Presales", "ANZ"
"ANZ Sales Management","ANZ",
"ANZ Sales", "ANZ",
"Asia MD", "Asia",
"Asia Consulting Manager", "Asia",
"Asia Marketing", "Asia",
"Asia Presales", "Asia",
"Asia Sales", "Asia",
"China MD", "China",
"China Consulting Manager", "China",
"China Sales Management", "China",
"China Sales", "China",
"India MD", "India",
"India Presales", "India",
"India Sales", "India",

"Japan MD", "Japan",

"Japan Consulting Manager", "Japan",

"Japan Sales Management", "Japan",

"Japan Sales", "Japan", "")


 

NPMNPM
Also you may need to remove the quotation marks from the values - for example "ANZ MD", would be ANZ MD, 
KC-CRM AnalystKC-CRM Analyst

Is this being done in a formula field or in a workflow field update?

The $UserRole.Name actually evaluates the current user not the role of another user attached to the record.

NPMNPM
Good point - I only focused on the syntax issue.
nikkonikko
this is done thru formula field; syntax error occurred (see syntax error 1 & 2)
 
i was told that i can use another formula; LEFT( $UserRole.Name , FIND(" ", $UserRole.Name) )
but the result in the field is incorrect (see user-role-ANz sales & region-SVP)
 
please let me know if there's another alternative way to group the opportunity records into region by the opportunity owner field.
thanks!
 

 

 

 

nikkonikko
here's the 2nd screenshot...
nikkonikko
3rd screenshot
nikkonikko
final screenshot
nikkonikko

below formula will help me; but its exceeding the character limits in SF ..:smileymad:

any idea how this can be further shortened.. thanks!

IF(OwnerId = "005200000010QOE", "ANZ",

IF (OwnerId = "005200000010QOT", "ANZ",
IF (OwnerId = "00520000000vX94", "ANZ",
IF (OwnerId = "00520000000sMWD", "ANZ",
IF (OwnerId = "005200000010Kjf", "ANZ",
IF (OwnerId = "00520000000tZbM", "ANZ",
IF (OwnerId = "00520000000sUB8", "ANZ", 
IF (OwnerId = "00520000000t5Aq", "ANZ",
IF (OwnerId = "00520000000uLc8", "ANZ",
IF (OwnerId = "005200000010IcA", "ANZ",
IF (OwnerId = "00520000000zyOu", "ANZ",
IF (OwnerId = "00520000000sOQT", "ANZ",
IF (OwnerId = "00520000000sUB3", "ANZ",
IF (OwnerId = "00520000000syWB", "ANZ",
IF (OwnerId = "00520000000sOQ3", "Asia",
IF (OwnerId = "00520000000sOQQ", "Asia",
IF (OwnerId = "00520000000sMWE", "Asia",
IF (OwnerId = "00520000000sOQP", "Asia",
IF (OwnerId = "00520000000sOQR", "Asia",
IF (OwnerId = "00520000000v6Ce", "Asia",
IF (OwnerId = "00520000000uvBB", "Asia",
IF (OwnerId = "00520000000u0J3", "Asia",
IF (OwnerId = "00520000000uCdI", "Asia",
IF (OwnerId = "00520000000vCQr", "Asia",
IF (OwnerId = "00520000000uBuU", "Asia",
IF (OwnerId = "00520000000vEoA", "Asia",
IF (OwnerId = "00520000000sOQ2", "China",
IF (OwnerId = "00520000000sPEM", "China",
IF (OwnerId = "00520000000sOQ5", "China",
IF (OwnerId = "00520000000sPDd", "China",
IF (OwnerId = "00520000000sOQM", "China",
IF (OwnerId = "00520000000sOQ7", "China",
IF (OwnerId = "00520000000sOQ8", "China",
IF (OwnerId = "00520000000sOQ9", "China",
IF (OwnerId = "00520000000sOQB", "China",
IF (OwnerId = "00520000000ucCa", "China",
IF (OwnerId = "00520000000tMz4", "China",
IF (OwnerId = "00520000000u9VA", "China",
IF (OwnerId = "005200000010Sgz", "China",
IF (OwnerId = "00520000000sMWF", "China",
IF (OwnerId = "00520000000tZan", "India",
IF (OwnerId = "0052000000109kT", "India",
IF (OwnerId = "00520000000sOQm", "Japan",
IF (OwnerId = "00520000000sOPp", "Japan",
IF (OwnerId = "00520000000sOQc", "Japan",
IF (OwnerId = "005200000010foZ", "Japan",
IF (OwnerId = "00520000000sOQe", "Japan",
IF (OwnerId = "00520000000sOQf", "Japan",
IF (OwnerId = "00520000000sOQg", "Japan",
IF (OwnerId = "00520000000sOPo", "Japan",
IF (OwnerId = "00520000000sOPr", "Japan",
IF (OwnerId = "00520000000sOQK", "Japan",
IF (OwnerId = "00520000000sOQL", "Japan",
IF (OwnerId = "00520000000tn1i", "Japan",
IF (OwnerId = "00520000000tluv", "Japan",
IF (OwnerId = "005200000010LpU", "Japan",
IF (OwnerId = "00520000000sMWG", "Japan",
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))
KC-CRM AnalystKC-CRM Analyst
Try this instead:
 

CASE(OwnerId,

"005200000010QOE", "ANZ",

"005200000010QOT", "ANZ",
"00520000000vX94", "ANZ",
"00520000000sMWD", "ANZ",
"005200000010Kjf", "ANZ",
"00520000000tZbM", "ANZ",
"00520000000sUB8", "ANZ", 
"00520000000t5Aq", "ANZ",
"00520000000uLc8", "ANZ",
"005200000010IcA", "ANZ",
"00520000000zyOu", "ANZ",
"00520000000sOQT", "ANZ",
"00520000000sUB3", "ANZ",
"00520000000syWB", "ANZ",
"00520000000sOQ3", "Asia",
"00520000000sOQQ", "Asia",
"00520000000sMWE", "Asia",
"00520000000sOQP", "Asia",
"00520000000sOQR", "Asia",
"00520000000v6Ce", "Asia",
"00520000000uvBB", "Asia",
"00520000000u0J3", "Asia",
"00520000000uCdI", "Asia",
"00520000000vCQr", "Asia",
"00520000000uBuU", "Asia",
"00520000000vEoA", "Asia",
"00520000000sOQ2", "China",
"00520000000sPEM", "China",
"00520000000sOQ5", "China",
"00520000000sPDd", "China",
"00520000000sOQM", "China",
"00520000000sOQ7", "China",
"00520000000sOQ8", "China",
"00520000000sOQ9", "China",
"00520000000sOQB", "China",
"00520000000ucCa", "China",
"00520000000tMz4", "China",
"00520000000u9VA", "China",
"005200000010Sgz", "China",
"00520000000sMWF", "China",
"00520000000tZan", "India",
"0052000000109kT", "India",
"00520000000sOQm", "Japan",
"00520000000sOPp", "Japan",
"00520000000sOQc", "Japan",
"005200000010foZ", "Japan",
"00520000000sOQe", "Japan",
"00520000000sOQf", "Japan",
"00520000000sOQg", "Japan",
"00520000000sOPo", "Japan",
"00520000000sOPr", "Japan",
"00520000000sOQK", "Japan",
"00520000000sOQL", "Japan",
"00520000000tn1i", "Japan",
"00520000000tluv", "Japan",
"005200000010LpU", "Japan",
"00520000000sMWG", "Japan",null)


Message Edited by KC-CRM Analyst on 09-17-2008 11:52 PM
nikkonikko

no luck ... still have the Error: Compiled formula is too big to execute (8,187 characters). Maximum size is 5,000 characters

NPMNPM

Something you might try to get around the compile size issue.  It is certainly not less complicated though.  You will need a text field in opportunity for each region (Region ANZ, Region Asia, etc.)  These fields would not be placed on the page layout they are place holders that would be used in a Region formula field.

The placeholder fields would be updated by workflow, and a number of field updates (one for each region) that would trigger each time an opportunity record is edited/saved. You would use criteria like Opportunity Name Not Equal to (blank). 

The formula field would evaluate the vales in these "intermediate" fields to determine region and would be on the page layout. 


Caveat - I have not tested this but you may want to try it depending on your need.


This is an example of 2 field update formulas:
for Region ANZ:

Code:
CASE(OwnerId,
"005200000010QOE", "ANZ",
"005200000010QOT", "ANZ", 
"00520000000vX94", "ANZ", 
"00520000000sMWD", "ANZ", 
"005200000010Kjf", "ANZ", 
"00520000000tZbM", "ANZ", 
"00520000000sUB8", "ANZ",  
"00520000000t5Aq", "ANZ", 
"00520000000uLc8", "ANZ", 
"005200000010IcA", "ANZ", 
"00520000000zyOu", "ANZ", 
"00520000000sOQT", "ANZ", 
"00520000000sUB3", "ANZ", 
"00520000000syWB", "ANZ", 
"")

for Region Asia:

Code:
CASE(OwnerId,
"00520000000sOQ3", "Asia", 
"00520000000sOQQ", "Asia", 
"00520000000sMWE", "Asia", 
"00520000000sOQP", "Asia", 
"00520000000sOQR", "Asia", 
"00520000000v6Ce", "Asia", 
"00520000000uvBB", "Asia", 
"00520000000u0J3", "Asia", 
"00520000000uCdI", "Asia", 
"00520000000vCQr", "Asia", 
"00520000000uBuU", "Asia", 
"00520000000vEoA", "Asia", 
"")


 
This is an example Region Formula field:

Code:
IF( LEN( Region_ANZ__c )<>0 , "ANZ", 
      IF(LEN( Region_Asia__c )<>0,"Asia",

etc., etc.. etc.,

""))

The above example with 2 regions you would need to extend to the number you have.  What is does is check if there is a value in the intermediate fields and if so that is the value of Region.  It does assume that once the region is set based on the ownerid/workflow filed update it will not change.
 

 

NPMNPM

I withdraw this part of my last post:

"It does assume that once the region is set based on the ownerid/workflow field update it will not change."
 
because the workflow field update should handle that situation.

nikkonikko
On professional edition ..workflow rule is not applicable..
 
same syntax error encounter when the region formula is done....
KC-CRM AnalystKC-CRM Analyst

OK, lets rethink this from the start.  I don't think that basing the formula on the Owner ID is a good idea.

Lets just start with the ANZ group.  Do they create their own records?

nikkonikko
yes; the users will create their own opportunity records...
and these can be created by users in asia, anz ... etc
 
 
 
KC-CRM AnalystKC-CRM Analyst

OK, I think this way way over thought.

You know which users are going to create in which Regions and you are trying to set it by a formula.  Why not control it from the user record.  Make it an editable field because it won't be controlled by owner instead it will be a default value.

You place a UserUser.User Region on the user record.  If you create Region__c with a Default value of $User.User_Region__c and you have an editable default filled by the creator's Region.

nikkonikko
in this way, how can i make the UserUser.User Region custom field available in Opportunity and Opportunity Products report?
i need this field for reporting and dashboard purpose..
trying the group the total sales by Region ...
KC-CRM AnalystKC-CRM Analyst

It won't actually be the User.User_Region__c field it will be a text copy of the User.User_Region__c field.

You will be able to pull it on the opportunity report and if you use a formula field on the Opportunity Product to pull it down from the Opportunity you can report on it there as well.  This is like Work-flow Rules Low Carb style.

nikkonikko
Not sure if i hav understood completely what you mention earlier...
 
i created a Region field (picklist or text) in the user record
after which i created a formula field (Region 1) in Opportunity :-
 
IF( OwnerId = $User.Id, "$User.Region__c", "")
 
but then the Region 1 field is blank for most records...
 
is there some error with my formula?