Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Zohaib Ahmed 4Zohaib Ahmed 4 

Formula Field Alignment Logic

Hello,

I am looking to create a formula field simlar to 2 we already have in our instance under the Account & Opportunity object for Leads. Basically what the field does in a nutshell is, narrow results in reports and dashboards to the region or geo the user's alignment is set to here is the syntax for the  formula fields under Accounts and Objects :

Account Object:
Field 1:
IF (OwnerId = $User.Id, "Y", "N")

Field 2:
IF(OR(Account_Owner_Region__c="",Account_Owner_Geo__c =""),"N",
if(OR
(Is_Mine__c = 'Y',$User.Align__c="Global",
CONTAINS($User.Align__c ,Acct_OwnerName__c),
CONTAINS($User.Align__c ,Account_Owner_Region__c),
CONTAINS($User.Align__c, Account_Owner_Geo__c)
),"Y","N"))

Opportunity Object: 
Field 1: 
IF (OwnerId = $User.Id, "Y", "N")

Field 2:
IF(OR(Owner_Region__c="",Owner_GEO__c =""),"N", 
if(OR 
(Is_Mine__c = 'Y',$User.Align__c="Global", 
CONTAINS($User.Align__c ,OwnerName__c), 
CONTAINS($User.Align__c ,Owner_Region__c), 
CONTAINS($User.Align__c, Owner_GEO__c) 
),"Y","N"))

What I want to do is use a similar logic for leads, EXCEPT instead of the lead owner use another field which is manually maintained called, "Generalist_Sales_Rep__c" :

What is the syntax I need to put in when I create the new Formula field on the Lead object? Thanks for the help in advance! 

Field 1?

Field 2?


 
SwarnaSankhaSinghSwarnaSankhaSingh

Hi Zohaib,

Did you find the solution to your question? If not then can you please confirm if the field named Generalist_Sales_Rep__c on the Lead object is a custom Lookup field to the user object?

Kind Regards,
Swarna.
Zohaib Ahmed 4Zohaib Ahmed 4
HI Swarna, Thanks for your response! This has not yet been resolved and I confirm Generalist_Sales_Rep__c is a look up to the user object. I got a bit further, and updated the formula for the is mine field on the leads object with the following: IF(OwnerId = $User.Id,"Y", IF(Generalist_Sales_Rep__c = $User.Id,"Y", IF($User.Align__c= Generalist_Sales_Rep__r.Align__c,"Y", IF($User.Align__c = Owner:User.Align__c, "Y", IF($User.Align__c = Generalist_Sales_Rep__r.Align__c , "Y", "N"))))) So I would want the lead owner/Generalist_Sales_Rep__c to have 'Y’ for there alignment, and additionally anyone who has the lead owner/generalist in their alignment as well. Let me know if any additional info is needed. Thanks! Regards, Zohaib Ahmed Sales Systems Analyst Riverbed Technology Ext: 3241 Email: Zohaib.Ahmed@riverbed.com www.riverbed.com [signature_working_file-tagline.jpg]
SwarnaSankhaSinghSwarnaSankhaSingh
Hi Zohaib,

Correct me if I am wrong but from what I see, I think that what you were referring to as Field 1 is basically the field with the API name as Is_Mine__c for the Lead object in your SFDC instance. Now the logic you shared in your previous comment is for the Is_Mine__c field and the syntax is as follows: 
IF
(
	OwnerId = $User.Id,
	"Y",
	IF
	(
		Generalist_Sales_Rep__c = $User.Id,
		"Y",
		IF
		(
			$User.Align__c = Generalist_Sales_Rep__r.Align__c,
			"Y",
			IF
			(
				$User.Align__c = Owner:User.Align__c,
				"Y",
				IF
				(
					$User.Align__c = Generalist_Sales_Rep__r.Align__c ,
					"Y",
					"N"
				)
			)
		)
	)
)
The Syntax for the Is_Mine__c field can be shortened, updated to ensure that the Align__c field vaules are only compared when they have values in it and will need to be as follows: 
IF
(
    OR
    (
        OwnerId = $User.Id,
        AND
        (
            NOT(ISBLANK($User.Align__c)),
            NOT(ISBLANK(Owner:User.Align__c)),
            $User.Align__c = Owner:User.Align__c
        ),
        Generalist_Sales_Rep__c = $User.Id,
        AND
        (
            NOT(ISBLANK($User.Align__c)),
            NOT(ISBLANK(Generalist_Sales_Rep__r.Align__c)),
            $User.Align__c = Generalist_Sales_Rep__r.Align__c
        )
    ),
    "Y",
    "N"
)
Now before I jump into the Field 2 logic, I need the details for the following fields on the Opportunity object:
  • OwnerName__c
  • Owner_Region__c
  • Owner_GEO__c
  1. Are these formula fields? If yes then please provide the logic constructed for each one of these.
  2. Are these non-formula fields in which case I need to know their respective data types like if there are TEXT fields or PICKLIST Fields etc.
Kind Regards,
Swarna
Zohaib Ahmed 4Zohaib Ahmed 4
HI Shawrna, Thanks for your response. For your questions, please see the responses below: 1. Are these formula fields? If yes then please provide the logic constructed for each one of these. Y * Yes these are formula fields referencing a pick list on the owner user record called “geo”, “region” and the owner’s full name 2. Are these non-formula fields in which case I need to know their respective data types like if there are TEXT fields or PICKLIST Fields etc. I entered in the formula you gave below. We want the "is mine” field to essentially show yes if the geo,region, or alignment are aligned with the view who is viewing the report if the the filter is mine = Y. Hope this helps. Let me know if any additional info is needed. Thanks ! Regards, Zohaib Ahmed Sales Systems Analyst Riverbed Technology Ext: 3241 Email: Zohaib.Ahmed@riverbed.com www.riverbed.com [signature_working_file-tagline.jpg]
SwarnaSankhaSinghSwarnaSankhaSingh

Hi Zohaib,

Where did you enter the corrected formula I shared with you in the earlier post? Did you enter it as Field 1 or Field 2? And what are the field labels for Field 1 and Field 2 on the Opportunity and Account objects? This information will help clear out my head because I honestly dont have a complete pciture of your process.

Kind Regards,
Swarna.
Zohaib Ahmed 4Zohaib Ahmed 4
Hi Swarna, Here is our current logic by object: User object: * Field name: Geo * Picklist (americas, emea, apj) * Field name: Region * Picklist (many regions within the geos above) * Field name: Alignment * Text field (Can enter geo,region,or specific user name) Account and Opportunity Object: * Field name: "Is Mine” * Formula: IF (OwnerId = $User.Id, "Y", "N") * Field name: “Is My Alignment" * Formula: IF(OR(Owner_Region__c="",Owner_GEO__c =""),"N", * if(OR (Is_Mine__c = 'Y',$User.Align__c="Global", CONTAINS($User.Align__c ,OwnerName__c), CONTAINS($User.Align__c ,Owner_Region__c), CONTAINS($User.Align__c, Owner_GEO__c) ),"Y","N")) The above basically makes is easier to report and filter out to opportunity & accounts that you want only based on your “alignment” which is on your user record. The goal is to implement something similar with the Lead object, except we are not just looking at the lead owner but the “generalist sales rep” as well. Let me know if this helps, or if you need any further details. Thank you. Regards, Zohaib Ahmed Sales Systems Analyst Riverbed Technology Ext: 3241 Email: Zohaib.Ahmed@riverbed.com www.riverbed.com [signature_working_file-tagline.jpg]