I need some help with a formula.

I have a custom object called Relationships.

Within this custom object I have multiple lookups (see lookups below). I would like to create one field called Associated With which is a text formula field and returns the names of

  • Lookup(Agreement)
  • Lookup(Legal Entity)
  • Lookup(Opportunity)
  • Lookup(Portfolio)

Example 1)

Lookup(Agreement) Populated A

Lookup(Legal Entity) Blank

Lookup(Opportunity) Blank


outcome "Associated with" A

Example 2) Lookup(Agreement) Blank

Lookup(Legal Entity) Blank

Lookup(Opportunity) Populated C


outcome "Associated with" C

closed as off-topic by Adrian Larson, Mohith Shrivastava, Himanshu, Boris Bachovski, Eric Oct 27 '16 at 3:09

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – Adrian Larson, Mohith Shrivastava, Himanshu, Boris Bachovski, Eric
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Can you post whatever you have tried and where you are facing issues? – Santanu Boral Oct 26 '16 at 16:59
  • BLANKVALUE(Opportunity__r.Name, Agreement__r.Name) This only returns Opp Name or Agr name, i am unsure how to build it out to include others – Masond3 Oct 26 '16 at 17:16

You can indeed use BLANKVALUE here. Note that its syntax is BLANKVALUE(expression, substitute_expression). You can simply use another BLANKVALUE as the substitute_expression.

        BLANKVALUE(Portfolio__r.Name, Legal_Entity__r.Name)

You could incorporate this into a HYPERLINK by using similar logic to create the url and the exact logic as the "friendly name":

    "/" & BLANKVALUE(Opportunity__c,
            BLANKVALUE(Portfolio__c, Legal_Entity__c)
            BLANKVALUE(Portfolio__r.Name, Legal_Entity__r.Name)
  • Hi Adrian, thanks for providing the example above. Is there a way that I can make that field a hyperlink ? So using the scenarios above and example one the outcome in *associated with" would be "a". Anyway that I can click on this and it takes me to that related record ? – Masond3 Oct 26 '16 at 19:12
  • Yeah I can add that detail. – Adrian Larson Oct 26 '16 at 19:24
  • Hi Adrian, unfortunately i cant get into my org to test :(. I assume by creating the field a hyperlink which takes you to the associated record, this functionality would also work if I added this field to a related list ? – Masond3 Oct 26 '16 at 19:51
  • It should work if you include it in a related list, yes. – Adrian Larson Oct 26 '16 at 19:54
  • Thanks Adrian: wish I could test this . I have looked at this solution all day and couldn't get my head around it. Ask the community and sitting mins get a potential/working solution :) – Masond3 Oct 26 '16 at 19:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.