Home Forums Support Full Text Search

This topic contains 10 replies, has 7 voices, and was last updated by  Randy 6 days, 12 hours ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #17813

    Nasir Javed
    Participant

    `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,

    #17820

    Hillel Coren
    Keymaster

    Sorry, it isn’t supported.

    #21499

    Motor
    Participant

    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?

    #23367

    bbrendon
    Participant

    Anyone know some mysql hackery to do this?

    #23368

    Titanfail
    Participant

    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.

    #23396

    Hillel Coren
    Keymaster

    That’s awesome!!

    #23400

    Titanfail
    Participant

    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.

    #23418

    CadmiumTelluride
    Participant

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

    #23422

    Titanfail
    Participant

    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;
    #23424

    Titanfail
    Participant

    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;
    #23484

    Randy
    Participant

    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;

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.

Posted in: