Announcement

Collapse
No announcement yet.

I need the Grid app's QuickSearch to work like Google (multiple search terms)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • I need the Grid app's QuickSearch to work like Google (multiple search terms)

    When ever I type a single value into the QuickSearch, it will find 7 records. But when I want to narrow my result set, I add another search term separated by a space. Using SQL in another software tool, I know that the result set should reduce from 7 records to 3 records, once I search for both the first and second term. However, SC8.1 shows me no records in the result set.

    The single value QuckSearch works ok now as it is. However...

    What should happen when two or more QuickSearch values are entered, is this:

    - Each term has it's own set of "WHERE" clause conditions.
    - The WHERE clause conditions will be generated from all the fields listed in the "Advanced Search" fields view
    - The condition for all the fields will revert to CONTAINING (regardless of what is set for operators in the "Advanced Search" fields view
    - The logic operator between all the fields will be OR

    Then...

    - EACH search term's WHERE clause conditions set will be wrapped in ( and )
    - All the WHERE clause condition sets will have the AND operator put between them
    - Now all the WHERE clause condition sets are ready to go. I put an extra ( and ) around the whole thing just because it feels good

    Add a single 'WHERE" at the top, and you're done. Execute.

    ---------------------------------------------------------------------------------------------
    Example:

    Search terms entered: Mic son ing


    WHERE
    (
    (
    FST_NM CONTAINING 'Mic' OR
    LST_NM CONTAINING 'Mic' OR
    OCCUPATION CONTAINING 'Mic'
    )
    AND
    (
    FST_NM CONTAINING 'son' OR
    LST_NM CONTAINING 'son' OR
    OCCUPATION CONTAINING 'son'
    )
    AND
    (
    FST_NM CONTAINING 'ing' OR
    LST_NM CONTAINING 'ing' OR
    OCCUPATION CONTAINING 'ing'
    )
    );


    Result set: Count: 1 record:

    FirstName LastName Occupation
    Michael JACKSON Singer



    This will enable the SC Quick Search to behave more like Google and my users will be happy, that they don't have to go into the Advanced Search every time they want to narrow their search down from 3,000 records to 5 records or even 1 record.

  • #2
    Hello,

    is there any solution for this?

    Comment


    • #3
      Hi JCM-ML, I have not received any info from NetMake about this.

      Comment


      • #4
        Hi,
        ...either it is much complicated or ....?
        In my opinion and exerience with cushy users it could be very usefull, because of lot of other filter funktions that are achieve faster than advanced search!

        Comment


        • #5
          I already built the Delphi Object Pascal code that generates the SQL for this kind of search. It's not overly complicated. If I can do it, then NetMake can certainly do it in PHP. (I'm not a PHP expert)

          Comment


          • #6
            I've done this on quick search, very much similar, but may not be efficient (using mySQL and fulltext). However on small sets of data, you can implement something like below code on the grid's OnScriptInit

            See my screenshot, I can have any number of words on the search bar.

            During the grid's Script Init, since there are more than 1 words on the search bar, grid will not be able to find any result..... however, I used sc_select_where(add) and put my multiple column searches there using "OR".

            You can always change the behavior of the where clause as you like. The first 3 PHP lines only extracted what was entered on the "Quick Search" bar. there may be better ways to extract the words but I don't know them yet.

            You can implement +keywords, - keywords however you like. Check MySQL implementation.

            Again, this is just a workaround until netmake can support this.


            if (! empty({sc_where_current})){
            {test} = {sc_where_atual};
            $pos1 = strpos({sc_where_atual}, "'%");
            $pos2 = strpos({sc_where_atual}, "%'");
            $StrtoSearch = substr({sc_where_atual}, $pos1 + 2, $pos2 - $pos1 - 2); // string to search
            sc_select_where(add) = " OR MATCH (GuideFileName, CreatedBy, GuideStatus) AGAINST ('$StrtoSearch' in boolean mode)";
            }

            Comment

            Working...
            X