Hello -

I have recently created a custom object that will help our organization manage and report on a referral program we have developed. The object is called Marketing Programs and it helps us track the progress between existing clients and our associates in the referral program and the leads generated by the specific client. To track the leads generated, I have created a related list custom object that appears on both the Lead View and the Contact View. From the Lead view we are able to see what contacts have referred us to the lead and date, and on the contact view we are able to see a list of people our contact/client has referred us too.

So getting down to my question....

As a result of this information, I am wanting to create a custom report that is based on Lead Activity but also includes the "referred" by field from the related list called - "Referral Program - Lead Tracking" (which is the related lists that shows who referred what)

Basically what I want is a report that shows: Referred By, Lead Name, Lead Phone, Lead Mobile, and activities related.

A lead may show up more than once on the report if they have been referred in by multiple people.

I have worked in the back end trying to create custom report types using various scenarios. I am able to create an activity report based off of lead alone, but the only thing that is missing is the "referred by" field from the custom related list.

Have spent maybe 4/5 hours on different scenarios and am hoping the professionals can give me additional direction.

I spoke to SFDC support and they said that i currently can not link the 2 objects to create the report that I want from the reporting tools, so I have 2 options. 

1. Create a custom formula that will auto populate a look up referred by field that would be placed on the Lead record - with the person who most recently provided the referral to the lead.

Or

2. Combine the info in Excel which would be very inefficient.

Does any one have any informaiton on how to get option 1 to work.  The issue is that we recievie mutliple referrals into one prospect and I want to track that information - so  the Look up field would just auto populate with the most recent person & date for the report.

Thanks,