How to filter sales orders with mulitiple invoices?

ERPNext
,
1

We found a bug in our app and it created multiple invoices to the same sales order. I do not know what we did wrong. We are looking into it.

We can see number of invoices in the connections tab but we have to go into every single sales order. And this problem does not happen for all sales orders.

Now we would like to find out how many and which are sales orders that has multiple paid invoices.

Tried using the erpnext filters but could not do it?

Is there another way?

If really no web browser way, can share some rough sql statements to find sales orders with multiple paid invoices.

2

The ERPNext filters can’t do this unfortunately, they don’t support grouping or counting like that.

Easiest way is to create a Query Report inside ERPNext (Report > New > Query type) and run your SQL from there. You can save it and export to Excel too.

If you have server access, bench --site yoursite.com mariadb works just as well.

Just keep in mind that in Frappe the Sales Order link sits on the invoice line level (tabSales Invoice Item), not the invoice header, so your query needs to join through there.

1 Like
3

Thank you very much for the detailed advice. A very good starting point for experimentation.

Thank you again for replying. I do appreciate it.

4

You can use the below query. Either create a ‘Query Report’ or fire the query from ‘System Console’. docstatus = 1 means Submitted.

SELECT
    so.name AS sales_order,
    COUNT(DISTINCT sii.parent) AS sales_invoice_count
FROM
    `tabSales Order` so
LEFT JOIN
    `tabSales Invoice Item` sii
    ON sii.sales_order = so.name
LEFT JOIN
    `tabSales Invoice` si
    ON si.name = sii.parent
    AND si.docstatus = 1
WHERE
    so.docstatus = 1
GROUP BY
    so.name;
1 Like
5

Thank you so much for the sql code. This has saved me so much of time.

Looking at the sql code, I would not have figured it out!

Thank you again!!!

I added the HAVING to filter out sales orders that have more than one invoice.

SELECT
    so.name AS sales_order,
    COUNT(DISTINCT sii.parent) AS sales_invoice_count
FROM
    `tabSales Order` so
LEFT JOIN
    `tabSales Invoice Item` sii
    ON sii.sales_order = so.name
LEFT JOIN
    `tabSales Invoice` si
    ON si.name = sii.parent
    AND si.docstatus = 1
WHERE
    so.docstatus = 1
GROUP BY
    so.name
HAVING
    sales_invoice_count > 1;
2 Likes
6

good.