+ Start a Discussion
Gary AllenGary Allen 

How to link opportunity to a custom object and thereby automatically update two custom fields on the opportunity.

The project criteria is to have two fields on the opportunity, electric utility and gas utility that are automatically 
filled out without user input. Utilities are based on two critera, city and state.

First I created a custome object called Utility with the fields city, state, elecric utiliy and gas utility. I took the city 
and state field and put them together to create "Utility_Service_Area__c" and did the same on the opportunity. 
On the opportunity I created a lookup field linking the Opportunity utility service area to that on the Utility custom 
object.

I then created two more fields on the opportunity (electric and gas utilities). These two fields are the reason for the whole project. The following is  an example of the electric utility field (Utility_Service_Area__r.Electric_Utility__c). I felt that this should work but it doesn't. 

Any help on solving this would be greatly appreciated!
Waqar Hussain SFWaqar Hussain SF
You can create formula field on opportunity to accomplish this task. 
cczccz
Hi Garry,

I just did a test and it worked.
I created the Utility object that has the following fields: City, State, Electric utility, Gas utility and an Utility Service Area formula field that concatenates the City & state
On the opportunity object I added the Utility Service Area as a lookup field to the Utility object.
After that I created two formula fields on the opportunity object and it worked.
Utility_Service_Area__r.Electric_Utility__c and ​Utility_Service_Area__r.Gas_Utility__c

If this helps you please mark the answer as correct.

Thank you
​Good luck
Gary AllenGary Allen
Can you elaborate? Do you mean that this formula field on the opportunity is incorrect?
(Utility_Service_Area__r.Electric_Utility__c)
cczccz
No, the formula is correct.
Try to review what you did up to the formula.
Did you entered the formula manually or with Insert field?
Gary AllenGary Allen
Insert field. The service area filed on the opportunity is also a formula field that concatenates city & state 
and therefore I can't use this as a lookup. Am I missing something obvious?
cczccz
And how did you created the lookup between the two objects?
Gary AllenGary Allen
Ok my bad. First I created a formula field that concatenates city & state on the opportunity called service area 
and then created a lookup field to the custom utility object called utility service area. The problem must be in the 
way I created this field. 

I am used to working with traditional relational databases where I can see everything on a server. I am still trying 
to get used to a lookup field as a traditional link. When I look at the two objects in the schema they appear to be 
linked correctly therefore I must be missing something. Is there anything I should have put in the filter settings?
Gary AllenGary Allen
Does it matter that the service area field I created on the opportunity is using the city and state fields from the related parent account 
on the account object?
cczccz
So, as a recap: You have the Utility object with City, State, Formula field that concatenates the two You have the Opportunity object with Formula fields that concatenates city/state from parent account, Lookup field to the Utility object. The cross formula fields (electric & gas utility) in the opportunity object should be populated when you populate and save the lookup field in the opportunity object Related to the databases, I completely understand, I worked for 10 years on relational databases before :) Cheers
Gary AllenGary Allen
I think you pretty much summed things up. It's interesting that you got it to work and mine doesn't. 
Whenever this happens logic tells me that there has to be a difference between our two processes.
Gary AllenGary Allen
Tell me. When you open up an opportunity the electric utility and gas utility fields are filled out?
cczccz
If you have the lookup field to the Utility object you don't even have to have the city / state as a formula in the opportunity. When you select the value in the list of values from the lookup field the formula for electricity/gas should be populated. I don't really understand how you are populating the lookup field ( not the formula field that takes the city/state from account)
Gary AllenGary Allen
Two questions:
1. After I have created the formula fields on both the opportunity and utility objects (concatenating city and state) what is the next 
step in creating a lookup.
2. As I have said I am not used to the lookup as a link. How do I know what field I am linking to. If there was some criteria that told 
me you are linking the service area field on the opportunity with the service area field on the utiliy I would understand.

Again, am I missing something?
cczccz
Lookup is a type of field which shows a list of values with data from the field that it looks up to, not a relation. When you populate the lookup field, the two records are "linked". You have the object ( city, state, electricity, gas) and you want to populate electricity and gas in opportunity based on join ( city state from parent account to city state in utility object) Is that right?
Gary AllenGary Allen
Yes. That is exactly correct.
Join, boy do I miss that word.