Custom query report

Frappe Framework Customization
1

How can I add fields from child table.
Ex: I have created query report from BOM, how can I add items from “Items table”?

1 Like
2

Hi,

Can anyone help me for above query.

Regards
Santosh

3

Can you please elaborate what exactly you are trying to do?

You can take a look at other Query Reports in ERPNext as example.

4

Thanks for ur reply.

I have created query report from Purchase Receipt. I need to add columns from child table as shown in picture.

Purchase receipt

Child Table.JPG1190×753 63.3 KB

Purchase Receipt report

PR report.JPG1140×604 122 KB

5

Pick the columns you need and Update, you should see data in the report. Screenshot below.

Screen Shot 2017-03-16 at 2.26.53 PM.png1324×976 64.3 KB

Screen Shot 2017-03-16 at 2.27.04 PM.png1926×1164 108 KB

6

Thank you KanchanChauhan,

We cannot pick and drag columns in query reports.
We appreciate for further suggestions.

Regards
Santosh

7

Sorry missed that it is a query report, Can you share the query please.

8

Kindly refer below images

Query Report.JPG1121×636 91.6 KB

Query.JPG1188×763 64.7 KB

9

KanchanChauhan,
I can add the query for fields which are directly on document, but can’t fetch the fields(data) from child table like in above picture.

10

You can fetch the data from Purchase Reciept Item Table

11

Can you please modify the above query.

12

Thank you all, it works with below query.

SELECT
tabPurchase Receipt.posting_date as “Date:Date/Date:120”,
tabPurchase Receipt.name as “ID:Link/Purchase Receipt:100”,
tabPurchase Receipt Item.item_code as “Item Code:Data/Data:120”,
tabPurchase Receipt Item.qty as “Qty”,
tabPurchase Receipt Item.rate as “Rate:Currency/Currency:70”,
tabPurchase Receipt Item.amount as “Amount:Currency/Currency:110”,
tabPurchase Receipt.total as “Total Amount:Currency/total:110”,
tabPurchase Receipt.per_billed as “Billed:Percent/Purchase Receipt:50”,
tabPurchase Receipt.amount_billed as “Amount Billed:Currency/Purchase Receipt:100”,
tabPurchase Receipt.amount_to_be_billed as “Amt To Be Billed:Currency/Purchase Receipt:100”,
tabPurchase Receipt.discount_amount as “Discount:Currency/currency:90”,
tabPurchase Receipt.grand_total as “Grand Total:Currency/currency:100”,
tabPurchase Receipt.is_return as “Return:Check/Purchase Receipt:50”,
tabPurchase Receipt.status as “Status:Select/Purchase Receipt:100”,
tabPurchase Receipt.supplier_invoice_no as “Supplier Inv No:Data/Purchase receipt:100”

FROM
tabPurchase Receipt Item,
tabPurchase Receipt
WHERE
tabPurchase Receipt.docstatus=1 && tabPurchase Receipt Item.parent=tabPurchase Receipt.name

1 Like