Full Text Search

Self-Hosted | v4/Legacy
1

`Hi,

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,

2

Sorry, it isn’t supported.

3

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?

4

Anyone know some mysql hackery to do this?

5

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 notes column invoice_items for the text you want, then return the invoice_id value from the same row, then lookup the matching id number in the invoices table. 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.

6

That’s awesome!!

7

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.

8

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%’);

9

Brilliant!

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;
10

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;
11

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;