In our organization a sale is considered as complete only when a custom picklist called Account status is changed to 'Registered' on an Account. (We deal with registering new companies)

Business would like a report which shows how many Leads that got converted eventually became a 'Registered' Account.

For this I need to create a formula field on Lead with logic 1) If Lead is not converted, VALUE = Status of Lead 2) If Lead is converted and account is registered, VALUE = ConvertedAccount.Account_Status__c

But I am unable to reference convertedAccount fields on the New Formula Field page, Error

However, I am able to query this info from Developer Console by referencing convertedAccount.Account_Status__c

Is there any way can I make a formula to combine data from Lead and converted Account to display on a Lead Report?

1 Answer 1


I'm not sure if you can create this formula field (I tried it but didn't suceed). What I did instead was create such formula directly on a report.

  1. Create a Custom Report of Leads or edit an existing one.
  2. Click on Edit in the Fields Available for Reports section
  3. Click on Add fields related via lookup » on the right.
  4. Find Converted Account and add your desired field.
  5. Create the report with this type. Your field should be available in the report.
  6. Create a new Row Level Formula
  7. Make sure to use TEXT() with the picklist values or else you will get errors.

Your Row Level Formula should look something like this: TEXT(Lead.ConvertedAccount.Account_Status__c)

  • Hi Ruiter, Can this work where I run a report on Leads, because I want to display leads that are not yet converted along with already converted Leads in my report. Commented Mar 17, 2020 at 17:48
  • Hi @AsifMuhammad. I updated my answer, as you don't need to add the relationship between Lead and Account. Adding the related field to the custom Lead report layout should be enough. Commented Mar 17, 2020 at 22:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .