Search invoices or quotes for a product

It would be great if we could find quotes and invoices, that contain a certain product.
For me this is important to check, which quotes were sent for a certain product or which invoices. So that customers can be contacted in regards to this very product.

Variant A

Filtering the invoice/quote list and also allow to be search for the product.
I am not sure if it makes sense to run a full text search over all invoices…

Variant B

In the product view there could be something like a list a button like “this product was used in the following invoices…”

Last Update: 09.05.2017

Yes that is something I would also be interested in as a standard feature. In the meantime is there a way to obtain this information (which customers bought that product) either through a SQL query or through some php file which we (I?) could run when I need it?
Thank you.

Hey

Here is what I do for your SQL query
It can be fully adaptable : https://pastebin.com/bKXRVcJj (take the one from pastebin as the forum converts some characters)

SELECT ip_clients.client_name
FROM ip_products, ip_invoice_items, ip_invoices, ip_clients
WHERE ip_products.product_id = ip_invoice_items.item_product_id
AND ip_invoice_items.invoice_id = ip_invoices.invoice_id
AND ip_invoices.client_id = ip_clients.client_id
AND ip_products.product_name = "test"
AND ip_invoices.invoice_status_id > 1

The last line means invoice which are not draft (so send, viewed or paid)
If the line

AND ip_products.product_name = “test”

is not working properly replace it with the product ID (you can found it in the ip_products table

AND ip_products.product_id = 1

And it has to be approved by @Kovah to add it in the dev todo list for the initial requests

If you need some changes for the SQL query feel free to ask I will see what I can do :slight_smile:

1 Like

Super cool! That worked wonders. Thank you very much!

Now is it possible to get the invoice number rather than the client names?

ps: actually I did it all by myself, it is just a matter of replacing the first line with SELECT ip_invoices.invoice_number.

So thank you very much for the prompt and super useful response.

1 Like

Yup you’re an SQL expert now haha :slight_smile: