December 23, 2018 at 12:20 pm #17813
Sometimes we need to search a certain keyword in invoices/quotations area, is there any feature where we can do full text search so that we can find invoices based on the keyword?
I tried to search the forum if this feature is available but couldn’t get any results so any help will be appreciated in this regards.
Thanks,December 24, 2018 at 6:21 am #17820
Sorry, it isn’t supported.August 7, 2019 at 2:07 am #21499
When can this be supported? we are desperately trying to find an invoice. Any way at all we can search them without downloading them all manually and then indexing them with a 3rd party app? or operating system?January 29, 2020 at 11:53 pm #23367
Anyone know some mysql hackery to do this?January 30, 2020 at 4:30 am #23368
The hard part comes in the fact that the invoice items (which contains the item descriptions) are in a separate table from the invoice numbers themselves. With that in mind, you could try the following (horribly ugly, slow, and completely unoptimized) bit of SQL. Note that the ; on each line is intentional, as it’s two separate statements.
SET @InvoiceIDNumber = (SELECT invoice_id FROM YourDatabase.invoice_items WHERE notes LIKE '%SearchString%'); SELECT invoice_number FROM YourDatabase.invoices WHERE id = @InvoiceIDNumber;
This will search the
invoice_itemsfor the text you want, then return the
invoice_idvalue from the same row, then lookup the matching id number in the
invoicestable. The result should be the invoice number of the particular invoice(s) you’re looking for, and you can then look them up from there in the UI.
The usual caveats apply, especially the part where I’m garbage-tier at MySQL. Fortunately it’s just SELECT and not altering anything.February 1, 2020 at 4:50 pm #23396
That’s awesome!!February 1, 2020 at 9:16 pm #23400
Major pothole in the road I just noticed: If your search string is found in more than one invoice, you WILL get an error. Specifically
ERROR 1242 (21000): Subquery returns more than 1 row.
But if you know that your search string only applies to a single invoice, that code should still be useful.February 3, 2020 at 4:47 pm #23418
That is easily solved by the use of IN and changing the variable into a subquery:
SELECT invoice_number FROM YourDatabase.invoices WHERE id IN (SELECT invoice_id FROM YourDatabase.invoice_items WHERE notes LIKE ‘%SearchString%’);February 3, 2020 at 7:40 pm #23422
Add in an ORDER BY and I think you nailed it.
SELECT invoice_number FROM YourDatabase.invoices WHERE id IN (SELECT invoice_id FROM YourDatabase.invoice_items WHERE notes LIKE '%Search String%') ORDER BY invoice_number;February 3, 2020 at 8:58 pm #23424
And just to make it a bit easier to read
SELECT invoice_number AS 'Matching Results' FROM YourDatabase.invoices WHERE id IN (SELECT invoice_id FROM YourDatabase.invoice_items WHERE notes LIKE '%Search String%') ORDER BY invoice_number;February 10, 2020 at 2:32 pm #23484
I’d like to play :))
Out the top of my head:
SELECT DISTINCT inv.invoice_number
FROM dba.invoices as inv
INNER JOIN dba.invoice_items as items on items.invoice_id = inv.id
WHERE items.notes like ‘%String_to_search%’
ORDER BY inv.invoice_number;
You must be logged in to reply to this topic.