function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
spiegsspiegs 

How to create a simple cross-object formula from a picklist

I want to pull in a value from a picklist froma custom Account field into a related custom Opportunity field.

Account = DOD,  Business Unit =  Federal.  Opportunity = "Army Procurement Project",  Account = DOD,
Business Unit = Federal.
 
I need the Opportunity Business Unit value to be automatically pulled from the Account Business Unit field where Account = DOD.  The Account Business Unit API name is Business_Unit_c.  The value "Federal" was selected on Account object from a picklist.

Help with the syntax and an example would be appreciated!  Thanks!
Best Answer chosen by Admin (Salesforce Developers) 
Steve :-/Steve :-/

Here you go

 

Datatype: Formula(Text)

Formula:TEXT(Account.PicklistField)

 

 So for your example it would be something like 

 

TEXT(Account.Business_Unit__c)

 

 

 

 

Message Edited by Stevemo on 01-22-2010 03:33 PM
Message Edited by Stevemo on 01-22-2010 03:49 PM

All Answers

Steve :-/Steve :-/

Here you go

 

Datatype: Formula(Text)

Formula:TEXT(Account.PicklistField)

 

 So for your example it would be something like 

 

TEXT(Account.Business_Unit__c)

 

 

 

 

Message Edited by Stevemo on 01-22-2010 03:33 PM
Message Edited by Stevemo on 01-22-2010 03:49 PM
This was selected as the best answer
spiegsspiegs

Thanks for the suggestion.  I tried it and received the following error message.

 

function toggleInlineDisplay(name) { var disp = document.getElementById(name+'_selector').style.display!='none' ? 'none' : ''; if(disp!='none') document.getElementById(name).focus(); var suffixes = ['_selector','_header', '_title','_footer','_functions','_validate']; for (var i=0; i
Select Field TypeInsert Field
-- Select field type --$Organization $System $User $UserRole -- Insert Merge Field --
categoryToFieldMap = {'$UserRole' : [new Option('Case Access Level for Account Owner', '$UserRole.CaseAccessForAccountOwner'), new Option('Contact Access Level for Account Owner', '$UserRole.ContactAccessForAccountOwner'), new Option('Description', '$UserRole.RollupDescription'), new Option('Last Modified By ID', '$UserRole.LastModifiedById'), new Option('Last Modified Date', '$UserRole.LastModifiedDate'), new Option('May Forecast Manager Share', '$UserRole.MayForecastManagerShare'), new Option('Name', '$UserRole.Name'), new Option('Opportunity Access Level for Account Owner', '$UserRole.OpportunityAccessForAccountOwner'), new Option('Portal Type', '$UserRole.PortalType'), new Option('Role ID', '$UserRole.Id')],'$Organization' : [new Option('City', '$Organization.City'), new Option('Country', '$Organization.Country'), new Option('Division', '$Organization.Division'), new Option('Fax', '$Organization.Fax'), new Option('Google Apps Domain', '$Organization.GoogleAppsDomain'), new Option('Name', '$Organization.Name'), new Option('Organization ID', '$Organization.Id'), new Option('Phone', '$Organization.Phone'), new Option('State\/Province', '$Organization.State'), new Option('Street', '$Organization.Street'), new Option('UI Skin', '$Organization.UiSkin'), new Option('Zip\/Postal Code', '$Organization.PostalCode')],'$System' : [new Option('OriginDateTime', '$System.OriginDateTime')],'$User' : [new Option('Active', '$User.IsActive'), new Option('Alias', '$User.Alias'), new Option('Cell', '$User.MobilePhone'), new Option('City', '$User.City'), new Option('Community Nickname', '$User.CommunityNickname'), new Option('Company Name', '$User.CompanyName'), new Option('Contact ID', '$User.ContactId'), new Option('Country', '$User.Country'), new Option('Department', '$User.Department'), new Option('Division', '$User.Division'), new Option('E-mail', '$User.Email'), new Option('Employee Number', '$User.EmployeeNumber'), new Option('End of Day', '$User.EndDay'), new Option('Extension', '$User.Extension'), new Option('Fax', '$User.Fax'), new Option('First Name', '$User.FirstName'), new Option('Last Name', '$User.LastName'), new Option('Manager ID', '$User.ManagerId'), new Option('Phone', '$User.Phone'), new Option('Profile ID', '$User.ProfileId'), new Option('Role ID', '$User.UserRoleId'), new Option('Start of Day', '$User.StartDay'), new Option('State\/Province', '$User.State'), new Option('Street', '$User.Street'), new Option('Title', '$User.Title'), new Option('User ID', '$User.Id'), new Option('Username', '$User.Username'), new Option('User Type', '$User.UserType'), new Option('Zip\/Postal Code', '$User.PostalCode')]}document.getElementById('fieldCategorySelector').onchange()
new MenuButton('insertOperator', false);
TEXT(Account.Business_Unit__c)
Error: Field Account does not exist. Check spelling.

 

It is looking at "Account" as a field instead of an object.  Any idea why?

Steve :-/Steve :-/
What exactly are you trying to do?  Are you writing this in Salesforce Formula Language, or JavaScript?
spiegsspiegs
Trying to just use a Fomula for a Text field to pull in data from a field in Account into a related Opportunity.  When I entered TEXT(Account.Business_Unit__c) into the Formula Editor, I received the error message that the field Account wasn't found.
Steve :-/Steve :-/

That should work fine, we do almost the exact same thing on our SFDC Org.  I have a custom Opportunity Formula(Text) field that displays the value in the Account Region(Picklist) field.

 

 

Object: Opportunity

Field Name: Account Region

Datatype: Formula(Text)

Formula:

TEXT(Account.Region__c)

 What are the objects, field names, datatypes and values that you are trying to use in your formula?


 

spiegsspiegs

Object: Account

Field Name: Business Unit

Datatype: PickList "Commercial","Federal","Defense"

 

Object: Opportunity

Field Name: Business Unit

Datatype: Formula(Text) - I put in the formula you suggested TEXT(Account.Business_Unit_c)

 

Not sure why the formula editor interprets "Account" as a field and not interpreted as an object.  Any more ideas?   I appreciate your help!  you'd think this would be simple!

 

 

 

Steve :-/Steve :-/

Are you entering the the Formula manually?  or are you using the Formula Wizard?  

 

Custom Fields always end in double-underscore lowercase c "__c" 

 

So your formula should look like:

 

 

TEXT(Account.Business_Unit__c)

 

 

Message Edited by Stevemo on 01-26-2010 06:27 PM
spiegsspiegs

I was using one underscore, but even adding a second one doesn't work. Same error message
I attempted to paste it below...

 

Default Value

TEXT(Account.Business_Unit__c)
Error: Field Account does not exist. Check spelling.

 

 



 

Steve :-/Steve :-/
But are you entering the the Formula manually?  or are you selecting the Objects and Fields using the Formula Wizard?  
Steve :-/Steve :-/
spiegsspiegs
Manually.  I never used the Wizard before.  If I use the wizard and select Function TEXT, then it just inserts Text(value).  I don't know how to select a field.
Steve :-/Steve :-/

did you look at the screen shot image in the link in my last post?  

 

When you use the Wizard it inserts the function, in this case Text(Value) all that you need to do is replace Value with the fields that you want to use in your formula?

 

Have you ever written a Formula Field before? 

Steve :-/Steve :-/
Steve :-/Steve :-/

Not sure if you're still working on this.  

 

If you're referencing a Lookup Field in a formula the API Field Name changes from "__c" to "__r"

spiegsspiegs

When I try to use the field selector I am only prompted with the following choices.  

 

$Organization

$System

$User

$UserRole

 

I don't see the options that were in your screen shot to select Opportunity->Account and then an Account field.  Am I doing something that isn't correct where I wouldn't see the same field selection options as you did?

 

 

Steve :-/Steve :-/
Can you post a screenshot of where you are?  I think you're trying to build your formula from the wrong place
spiegsspiegs

See link below.  I am creating a test field and am using formula editor in Default Value.

 

http://picasaweb.google.com/lh/photo/kc6tLqemvOsChWrwxODWTQ?feat=directlink

spiegsspiegs
Nevermind, I figured it out.  I was trying to use a Formula in a Text field instead of creating a read only Formula Field. TEXT(Account.Business_Unit__c) works just fine. Thanks for all your help!
Steve :-/Steve :-/

No problem, you just owe me one of these> http://www.russianriverbrewing.com/web/brews/plinytheelder.html

 

spiegsspiegs
Cheers!
RaulrocksRaulrocks

Arrrrrgggghhhhhh! (though that's more applicable to Stevemo's avatar). I saw this thread and got so excited because I'm having the same problem, I'm trying to do almost the exact same thing. Thought there would be an answer at the end, but you've seemed to figure it out.

 

I'm trying to create a new Contact field that pulls data from a field from Accounts. I'm on the Contact Fields screen. I create a new field, name it (i've tried naming it the same as my original custom field and something different), then select Text as return output.

 

Object: Account
Field Name: Company ID (my custom field)
Datatype: PickList

 

Object: Contact
Field Name: Company ID
Datatype: Formula (Text) - I've tried Account.Company_ID__c - Account_r.Company_ID__c -Account.r.Company_ID__c - Contact.Company_ID__c - contact.r.Company_ID__c and things I can't even remember anymore

 

I don't know if I'm in the wrong place with the wrong formula or what, but it's making me crazy because I know this should be a simple one. Please help? :robotsad:

spiegsspiegs
My mistake was not creating a new Formula field that is read only instead of trying to add a Formula to the Default entry of an existing field.  In your case, select New Field for Contact and then select the field type as Formula.  Then you can enter in your TEXT(Contact.Company_ID__C)
Steve :-/Steve :-/

Are you using the Formula Wizard?  as I said a few times in my earlier posts, I STRONGLY recommend doing this when trying to build any kind of Formula Field, but especially when dealing with Cross-Object Formulas.

 

Now with that being said, you should end up with a formula that looks something like this  

 

TEXT(Account.Company_ID__c)

 

* When you're working with Formuals you need to use the API Field Name, NOT the Field Label 

** Custom fields end in double underscore lowercase c  "__c" 

The syntax is:

 

TEXT(SourceObject.SourceField)

 

* in this case you need the TEXT function because your Source Field is a PickList, so you need to convert the Value to Text.

 


Message Edited by Stevemo on 02-05-2010 04:14 PM
Message Edited by Stevemo on 02-05-2010 04:14 PM
RaulrocksRaulrocks

Yay!!!!!!! Got it, thank you both for your assistance!! Pliny all around! :smileyvery-happy:

 

Yes, I was using the Formula Wizard, I'm all about wizards!

jgriff191jgriff191

End Goal: Mass email contacts with Opportunities in Stage=Pledged.

 

To mass email them, there need to be the opportunity stage field on the Contact object.  I have been trying for a long time and I cannot figure this out.  When I go to create a custom field formula (text) inside my Contact object, I type

 

TEXT(Opportunity.StageName)

 

It says field name doesn't exist.

 

I know you should use the wizard, but the wizard doesn't even give me the Opportunity object to display the stage field.

 

I also tried creating a custom opportunity field Test that displays the stage and then using this formula in my Contact object field creation:

 

TEXT(Opportunity.Test__c)

 

Still nothing.  Any Help would be GREATLY appreciated.

 

Thank you.

KatherineCKatherineC
I had the same problem, just figured it out. Since it's cross object, should add an "R" with 2 underscores before that, 

TEXT(Opportunity__r.Test__c)
@DBohrisch@DBohrisch
Thanx @Steve Molis, I just utilized this while integrating JIRA n SalesForce :)
Shakeria Walker 10Shakeria Walker 10
I have a picklist field on the User Object for Territory.  I want the territory to pull through to the Account and Opportunity Objects based on the Account Owner.  I tried Text(User.Territory__c); however, I receive the error "Error: Field User does not exist. Check spelling." Am I using the wrong formula?
Glenn Nyhan 54Glenn Nyhan 54
Not sure why I'm having trouble with this but here goes. I am trying to create a Cross Object formula which is a simple one, but don't seem to be getting it right.

I have two related objects the Contact object with a related object called Board Member Tracking. On the Board Member Tracking object there is a section called Member Position and Service Duration which has 10 individual picklists, one for each type of Board Member. I've been asked to make the contents of these fields visible on the Contact object, so they can be used in creating List Views that show those results. I can't seem to get this to work. Can anyone help with the solution and an example of how this can be executed as a Cross Object formula. Thanks in advance. 
Christine PrestonChristine Preston
I am having a really hard time with something simimlar.  I have the parent object that is STG_Online_C.  I need a field on this object for each individual states Sales Tax rate, plus 3 other fields (these fields are on a child object STG_Individual_State_Tax_Report)so that these fields for all 50 states are all in a single spot.  When I attempt the cross-object formula on the parent object, the child object is not an option.  I have never done a cross onject formula, so I am getting quite lost.  
Kumar Iyer 6Kumar Iyer 6
Is this a valid Cross-object formula to check a picklist value and return a text?
IF( ISPICKVAL ( Account.Rating  = Hot, "HIGH" ))
Kumar Iyer 6Kumar Iyer 6
This is the Error Iam getting:  Error: Field Rating is a picklist field. Picklist fields are only supported in certain functions. Tell me more