Using formula fields to add additional information related to lookup fields
Sometimes, you may have put a lookup field from another object into a page layout -- AND you might want some related information about the looked-up field, to also appear.
(Note: Reports are usually the best way to bring data from different objects together into an easy to see view however occasionally you may want a few items to be visible in the object record itself.)
Example: What if you want to add the mobile phone number for the Opportunity Coordinator to the Occurrence Record?
There is already a lookup for the Opportunity Coordinator name, as well as one for email.
As long as we have a lookup already in the object -- we can use formulas to bring in additional information about that 'looked-up' field.
Create a new field of type "Formula"
Follow the instructions in the general post about creating a new field in an object.
If you haven't already -- add the lookup field for the contact.
Then you will create another field -- of type Formula
Select Text as the data type
The mobile phone field in the contact record is of type 'phone' - but all we are concerned with is capturing the 'text' equivalent of the number in that field. So we select Text as the type.
Click on the Advanced Formula tab, and then Insert Field button
We want the field to be related to the Occurrence object. Notice that all items in the hierarchal list that have more options have a > next to them.
We follow the hierarchy from Occurrence to Opportunity Coordinator (the lookup field already in the object), to all the fields related to the Opportunity Coordinator.
We select Mobile .
As we continue to scroll to the right, we see that we have the mobile phone for the correct contact.
Note: if there are more than one contacts on the Occurrence object -- we'd need to make sure we selected the right 'lookup' to get to the phone of the correct person.
This formula requires nothing else. But you can build complex formulas by concatenating different bits of information (i.e. First Name and Last Name).
For more info on using formulas -- click on the "Quick Tips" links in the upper right of this page.
Select the profiles you wish to make this object accessible to. (Note, since its a lookup formula, the field will be read only access)
In the final step you'll select which page layouts to add it to.
Then, modify the page layout to place the field exactly where you'd like it.
Our new page layout has the Mobile phone now appearing!
(Note: You won't see the formula fields populated UNTIL you have saved the record. At that time, the formulas are calculated and the fields will populate themselves.)
You can then add this new field to email templates used by alerts that are triggered by occurrence sign-up
Note: The three merge fields shown here for the Opportunity Coordinator are the correct ones to use.
Older users may have a different merge field for the NAME of the Opportunity Coordinator, and this should be updated to the merge field shown above.
These threee fields will deliver the Name, Email Address, and Mobile Phone for the Opportunity Coordinator for this occurrence. (Assuming you named the custom Mobile Phone field as shown above).
0 Comments
Add your comment