Try ERPNext Buy Support Partners Foundation

Query Report - Fetch Address and Contact linked to Lead/Customer

Hi,

I’m trying to make a simple report where I can get information from Leads, including the addresses and contact information linked. Unfortunately, the built-in Lead Details report only links the addresses and 1 email. Not even a contact name or anything…

I’ve been able to fetch the data that I would like from the Lead Doctype, apart from data from the linked Addresses and Contacts.
To start with, I wanted a link to the name of the Address. I’ll take care of getting each specific item once I know how this works. I tried checking the parent of the address, hoping this was the trick to do the link between both, but since I’ve done this, nothing shows up on my page.

Any idea on how this could be done?

Address and Contact doctypes are linked as Table with the option Dynamic Link, which I guess means that you don’t address them in the same way as a standard Child Table, but I’m not quite sure how to address them in this case…

Thanks for your help!

SELECT 
`tabLead`.name as "Lead:Link/Lead:80",
`tabAddress`.name as "Address:Link/Address:80",
`tabLead`.company_name as "Name:Data/Lead:250",
`tabLead`.source as "Source:Data/Lead:100",
`tabLead`.notes as "Notes:Data/Lead:300",
`tabLead`.type as "Type:Data/Lead:100",
`tabLead`.market_segment as "Market Segment:Data/Lead:250",
`tabLead`.industry as "Industry:Data/Lead:250",
`tabLead`.technology as "Technology:Data/Lead:100",
`tabLead`.interest_1 as "Interest 1:Data/Lead:100", 
`tabLead`.interest_2 as "Interest 2:Data/Lead:100"

FROM 
`tabLead`,
`tabAddress`

WHERE 
`tabLead`.docstatus=0
AND `tabAddress`.parent = `tabLead`.name

Probably lie this (and the same for contact):

SELECT l.*, a.*
from 
`tabLead` l,
`tabAddress` a,
`tabDynamic Link` dl
where l.name = dl.link_name
and dl.link_doctype = "Lead"
and dl.parenttype = "Address"
and dl.parent = a.name

Yes! This works, thank you so much!

I still have a little issue when trying to have both the address and contact though… (separately, they both work fine)

I’ve tried various combinations:

WHERE 
l.name = dl.link_name
AND dl.link_doctype = "Lead"
AND dl.parenttype = "Address"
AND dl.parent = a.name
AND dl.parenttype = "Contact"
AND dl.parent = c.name

WHERE 
l.name = dl.link_name
AND dl.link_doctype = "Lead"
AND (dl.parenttype = "Address" OR dl.parenttype = "Contact")
AND (dl.parent = a.name OR dl.parent = c.name)

WHERE 
l.name = dl.link_name
AND dl.link_doctype = "Lead"
AND ((dl.parenttype = "Address" AND dl.parent = a.name) OR (dl.parenttype = "Contact" AND dl.parent = c.name))

WHERE 
l.docstatus=0
AND (l.name = dl.link_name AND dl.link_doctype = "Lead" AND dl.parenttype = "Address" AND dl.parent = a.name)
OR (l.name = dl.link_name AND dl.link_doctype = "Lead" AND dl.parenttype = "Contact" AND dl.parent = c.name)

Should I be using some kind of Join instead? When looking at how join works it doesn’t seem to be the right structure for this, but maybe I looked at it wrong

Try thisone

SELECT distinct l.*, a.*, c.*
from 
`tabLead` l
left join
`tabDynamic Link` dl
on l.name = dl.link_name and dl.link_doctype = "Lead" and dl.parenttype = "Address"
left join 
`tabAddress` a
on dl.parent = a.name
left join
`tabDynamic Link` dl2
on dl2.parenttype = "Contact" and l.name = dl2.link_name and dl2.link_doctype = "Lead"
left join
`tabContact` c
on dl2.parent = c.name

My saviour!

In case it can be helpful to anyone, please find a working code below:

 SELECT distinct
l.name as "Lead:Link/Lead:80",
l.company_name as "Name:Data/Lead:250",
a.name as "Address:Link/Address:80",
a.address_line1 as "Address-line1:Data/Address:150",
a.address_line2 as "Address-line2:Data/Address:150",
a.city as "City:Data/Address:100",
a.county as "County:Data/Address:80",
a.state as "State:Data/Address:80",
a.country as "Country:Data/Address:80",
a.pincode as "Pincode:Data/Address:50",
c.name as "Contact:Link/Contact:80",
c.first_name as "First Name:Data/Contact:80",
c.last_name as "Last Name:Data/Contact:80",
c.position as "Position:Data/Contact:80",
ce.email_id as "Email:Data/Contact Email:100",
cp.phone as "Phone:Data/Contact Phone:100",
l.source as "Source:Data/Lead:100",
l.notes as "Notes:Data/Lead:300",
l.type as "Type:Data/Lead:100",
l.market_segment as "Market Segment:Data/Lead:250",
l.industry as "Industry:Data/Lead:250",
l.technology as "Technology:Data/Lead:100",
l.interest_1 as "Interest 1:Data/Lead:100", 
l.interest_2 as "Interest 2:Data/Lead:100"

FROM 
`tabLead` l
LEFT JOIN
`tabDynamic Link` dl
ON l.name = dl.link_name AND dl.link_doctype = "Lead" AND dl.parenttype = "Address"
LEFT JOIN
`tabAddress` a
ON dl.parent = a.name
LEFT JOIN
`tabDynamic Link` dl2
ON dl2.parenttype = "Contact" AND l.name = dl2.link_name AND dl2.link_doctype = "Lead"
LEFT JOIN
`tabContact` c
ON dl2.parent = c.name
LEFT JOIN
`tabContact Email` ce
ON ce.parent = c.name
LEFT JOIN
`tabContact Phone` cp
ON cp.parent = c.name
1 Like

please advise where to put this SQL ?

Use script report.