Try ERPNext Buy Support Partners Foundation

How to fetch parent doctype name

I want to fetch parent doctype field such as (Doctype = Address).How do i fetch this using sql query.I dont want to fetch doctype name.Please help I am new to erpnext.

By using frappe call you can fetch parent doctype field.

@ROHAN_JAIN1 thank you for your reply. Can you give an example.I want to write a query report for the same.

You want to fetch in query report? or in js file?

In query report

For Query Report you can refer this.

I have written the following query report.Please tell me what changes need to be done to fetch doctype such as address,tax rule and item.

SELECT
AD.name AS "Doc#:Link/Doctype:120",
AD.owner AS "Created By:Link/User:120",
AD.modified AS "Modified On:Date:120"
FROM `tabAddress` AS AD
WHERE
DATEDIFF(now(),AD.modified) = 1
UNION ALL
SELECT
TR.name AS "Doc#:Link/Doctype:120",
TR.owner AS "Created By:Link/User:120",
TR.modified AS "Modified On:Date:120"
FROM `tabTax Rule` AS TR
WHERE
DATEDIFF(now(),TR.modified) = 1
UNION ALL
SELECT
IT.name AS "Doc#:Link/Doctype:120",
IT.owner AS "Created By:Link/User:120",
IT.modified AS "Modified On:Date:120"
FROM `tabItem` AS IT
WHERE
DATEDIFF(now(),IT.modified) = 1

Columns are created like this.

For Writing query

I want this in query report and not in .py file.

Okay wait let me see.

Yes @ROHAN_JAIN1 please help me. Thank you for your replies.

I think that this is what you want:

	SELECT
		AD.name AS "Doc#:Link/Address:120",
		AD.owner AS "Created By:Link/User:120",
		AD.modified AS "Modified On:Date:120",
	FROM tabAddress AS AD
	WHERE
		DATEDIFF(now(),AD.modified) = 1
UNION ALL
	SELECT
		TR.name AS "Doc#:Link/Tax Rule:120",
		TR.owner AS "Created By:Link/User:120",
		TR.modified AS "Modified On:Date:120"
	FROM `tabTax Rule` AS TR, 
	WHERE
		DATEDIFF(now(),TR.modified) = 1
UNION ALL
	SELECT
		IT.name AS "Doc#:Link/Item:120",
		IT.owner AS "Created By:Link/User:120",
		IT.modified AS "Modified On:Date:120"
	FROM tabItem AS IT
	WHERE
		DATEDIFF(now(),IT.modified) = 1

@Nahuel_Nso the code you suggested is giving me the same output.Below image shows “Doctype = Address” I want to fetch that name using query report.I want one more column as “Doctype” under which it should mention Address,Tax Rule or Item.How do I add this using query report??

I have made the following modifications in the query report but not getting the expected output.

SELECT
D.name AS “Doctype:Link/Doctype:120”,
AD.name AS “Doc#:Link/Doctype:120”,
AD.owner AS “Created By:Link/User:120”,
AD.modified AS “Modified On:Date:120”
FROM tabAddress AS AD , tabDocType AS D
WHERE
DATEDIFF(now(),AD.modified) BETWEEN 1 AND 30
UNION ALL
SELECT
D.name AS “Doctype:Link/Doctype:120”,
TR.name AS “Doc#:Link/Doctype:120”,
TR.owner AS “Created By:Link/User:120”,
TR.modified AS “Modified On:Date:120”
FROM tabTax Rule AS TR , tabDocType AS D
WHERE
DATEDIFF(now(),TR.modified) = 1
UNION ALL
SELECT
D.name AS “Doctype:Link/Doctype:120”,
IT.name AS “Doc#:Link/Doctype:120”,
IT.owner AS “Created By:Link/User:120”,
IT.modified AS “Modified On:Date:120”
FROM tabItem AS IT , tabDocType AS D
WHERE
DATEDIFF(now(),IT.modified) = 1

Why fetch it when you can hardcode it? (?)

	SELECT
		"Address" AS "Doctype:Link/Doctype:120",
		AD.name AS "Doc#:Link/Address:120",
		AD.owner AS "Created By:Link/User:120",
		AD.modified AS "Modified On:Date:120"
	FROM tabAddress AS AD
	WHERE
		DATEDIFF(now(),AD.modified) = 1
UNION ALL
	SELECT
		"Tax Rule" AS "Doctype:Link/Doctype:120",
		TR.name AS "Doc#:Link/Tax Rule:120",
		TR.owner AS "Created By:Link/User:120",
		TR.modified AS "Modified On:Date:120"
	FROM `tabTax Rule` AS TR, 
	WHERE
		DATEDIFF(now(),TR.modified) = 1
UNION ALL
	SELECT
		"Item" AS "Doctype:Link/Doctype:120",
		IT.name AS "Doc#:Link/Item:120",
		IT.owner AS "Created By:Link/User:120",
		IT.modified AS "Modified On:Date:120"
	FROM tabItem AS IT
	WHERE
		DATEDIFF(now(),IT.modified) = 1

Thank you @Nahuel_Nso it worked.