Join Leads with a Custom Object based on email address

Question

I have been tasked to create a report that ties leads to a Custom Object(Product_Ordered). Each object may or may not have an email on the record. For example: There 3 lead records with an email address of [email protected] There is a Product_Ordered record with the email address [email protected] I need to be able to show that the 3 Leads are associated to the Product_Ordered

The Lead and Product_Ordered object are currently not related. So I tried to create a junction object but you cannot create a Master-Detailed relationship for a Lead object so I did not pursue that route. I then tried creating a Report Type to ties the and that did not return any results. Any suggestions would be greatly appreciated. M

Answer 1

You can create an object and make a look up to both Lead and Product Ordered objects and make those lookup fields as mandatory.

That way, if 1 Product Ordered records have 3 leads then 3 records will be created in that object.

Product_Order_1 | Lead1

Product_Order_1 | Lead2

Product_Order_1 | Lead3

Create a summary report to display the report data on this object group by Product Ordered.

Answer 2

You can just create a lookup relationship from Lead to Product Ordered. Then all you need to do is to relate the Product Order to the Lead. It is a one to many relationships in which the Product Ordered is the One and the Lead the Many.

If you want to automate the process you can use a trigger to related the Leads to the Product Ordered based on the email field.