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.
@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.
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