How to get Child Table Value in Query Report

ERPNext Reports
1

Hi,

I want to have child table value in Query report but I can’t find a way to get it.

Here is my Query :

SELECT
  `tabDelivery Note`.`name`,`tabDelivery Note`.`docstatus`,`tabDelivery Note Item`.`incoming_rate` as 'Delivery Note Item:incoming_rate',`tabDelivery Note`.`posting_date`,`tabDelivery Note`.`status`,`tabDelivery Note Item`.`against_sales_order` as 'Delivery Note Item:against_sales_order',`tabDelivery Note Item`.`name` as 'Delivery Note Item:name'
FROM
  `tabDelivery Note`,
  `tabDelivery Note Item`
  WHERE
  `tabDelivery Note Item`.`parent` = `tabDelivery Note`.`name`

image
image1028×931 104 KB

Thank you for your help!

2

@FredericVerville

Try this

select
    dn.name as "Delivery Note:Link/Delivery Note:120",
    dn.docstatusas "Docstatus:Data:230", 
    dni.incoming_rate as "Incoming Rate:Currency:120",
    dn.posting_date as "Date:Date:120",
    dn.status as "Status:Data:120",
    dni.against_sales_order as "Against Sales Order:Link/Sales Order:120",
    dni.item_code as "Delivery Note Item:Link/Item:120"
	
From `tabDelivery Note` dn, `tabDelivery Note Item` dni

where dn.name = dni.parent;
3

Wow thank you so much it works!!

1 Like
4

Hi Hardik,

Can you help me setting filter for Against_sales_order and posting_date?

Right not it is not working

image
image1286×842 61.9 KB

image
image1139×928 113 KB

thank you!

5

First, i think your fieldname and label values are flipped.
Also you must include the use of your filter on the where clause, like

WHERE dn.posting_date = %(posting_date)s
AND dni.against_sales_order = %(against_sales_order)s

inside the parenthesis you must write the fieldname for your filter

6

I changed it for that :

image
image1056×566 30.2 KB

But I have this error :

image
image955×453 18.4 KB

7

you could set it as “mandatory” and reload your report. The error is because the value is not set and has nothing to compare

1 Like