Using query report to connect a two tables in purchase invoice and purchase invoice item tables?

ERPNext
1

Hi,
Which columns name s same in two tables…How to identify a two column name s same as in two tables?

2

You can build condition as `Purchase Invoice Item`.parent = `Purchase Invoice`.name

3

Sorry i m understand…what s common name from two tables???That is only i m easy to connect a two tables…

4

Parent table name is comman in two tables.

On parent its termed as name - PINV-00001
on child its termed as parent - PINV-00001

5

Thanks…how to give the Invoice No in my report from purchase invoice table…??how to i give the feild name for purchase invoice???

6

In my Query Report,That Draft Invoice Also Shown But I dont want that draft data…How to remove the draft data from My query report??

7

Each table have parameter named as docstatus.

Docstatus | Meaning
___
0 | Draft
___
1 | Submitted
___
2 | Cancelled
___

In query select only submited documents.

2 Likes
8

How to give the invoice number from doctype for a query report…

9

Which name feild used for a Invoice no…

10

use `Purchase Invoice`.name in query .

12

Check how to implement filters.

These are just js side data filters.

13

@Mohamed_Sajid share your query to get help.

14 
select
`tabPurchase Invoice`.`name` as "Inv",
`tabPurchase Invoice`.`supplier` as "Supplier",
`bill_no` as `Supplier Invoice No`, 
`posting_date` as "Date", 
`item_name` as "Item Name", 
`qty` as "Qty", `rate` as "Rate", `amount` as "Amount", 
`net_price` as "Net Price",`qty` * `net_price` as "Net Amount" 
from 
  `tabPurchase Invoice Item` 
INNER JOIN 
  `tabPurchase Invoice` 
ON isnull(`tabPurchase Invoice Item`.`parent`)=isnull(`tabPurchase Invoice`.`name`) 
where `tabPurchase Invoice`.`docstatus` = "1"

UNION ALL

SELECT "",NULL,"",NULL,NULL,SUM(`Qty`),SUM(`Rate`),SUM(`Amount`), 
  SUM(`Net Price`),SUM(`Net Amount`) 
FROM (select `tabPurchase Invoice`.`name` as "Inv",
  `tabPurchase Invoice`.`supplier` as "Supplier",
  `bill_no` as `Supplier Invoice No`,`posting_date` as "Date", 
  `item_name` as "Item Name",`qty` as "Qty",`rate` as "Rate",
  `amount` as "Amount", `net_price` as "Net Price", 
  `qty` * `net_price` as "Net Amount" 
from `tabPurchase Invoice Item` 
INNER JOIN 
  `tabPurchase Invoice` 
ON isnull(`tabPurchase Invoice Item`.`parent`)=isnull(`tabPurchase Invoice`.`name`)
where `tabPurchase Invoice`.`docstatus` = "1"
)TT

Java Script

frappe.query_reports[`Rebate List`] = {
	"filters": [
		{
			"fieldname":"from_date",
			"label": __("From Date"),
			"fieldtype": "Posting Date",
			"width": "80",
			"default": sys_defaults.year_start_date,
		},
		{
			"fieldname":"to_date",
			"label": __("To Date"),
			"fieldtype": "Posting Date",
			"width": "80",
			"default": frappe.datetime.get_today()
		}
	]
}

But Date Filter s not working properly…It show all the data…How to See the perfect data of within PArticullar date???I want To show the data based on that from date and to date…

15

Please format this.

16

Sorry …I not understood…

Thanks & Regards,
Mohamed Sajid Waqir.

17

Sorry …I not understood…

18

@Mohamed_Sajid I have formated your query please check.

Please describe what you want to show in report.

19

Yeah Super that formatted is wright …I want To filter a data by date by using from and to date Filter…

20

You have to mention those filters in WHERE clause. Refer “Advanced (adding filters)” section in https://frappe.github.io/frappe/user/guides/reports-and-printing/how-to-make-query-report.html

21

In My Query How to use Advanced filter???