Creating a custom search function

[Angelssword]

Hoping someone can help point me in the right direction as I have never created my of custom function, but I think it is what I need for this...

 

I have the following fields

 

c_search ( a calculated field that adds brand, product, barcode, part number into the same field)

zt_SearchString ( a global field where the user enters there search criteria )

c_SearchMatch (a field with a calculation that ranks the search result on on several criteria,

                             whether it is a customer favourite - Raises Priority greatly

                             How many times the string appears - 1 point for every occurance

                           as well as controlling rank if the customer wants to view by price.)

 

to do this so far I have been using PatternCount() and from a technical point of view, this works.  EXCEPT if the customer tries to use it like a web search, eg looking for a product and they enter "Green Jersey" does not give them a "Green Long Sleeved Jersey" as the string does not exist.  what I want to do is use a custom function with the MiddleWords function to search for each individual word and then rank the results for each word it finds, I know my syntax is terible, but hopefully someone can make sense of this and tell me how to create it as a function?

 

Let ( $wordcount = WordCount ( zt_SearchString ) ; $ActiveWord = 1 ; $CurrentCount = 0)

Loop

Let ( $Score = PatternCount ( C_Search ; MiddleWords ( zt_SearchString ; ActiveWord ; 1 ) )

Let ( $CurrentCount = $CurrentCount + $Score )

Exit Loop If $ActiveWord = $WordCount

Let ( $ActiveWord = ActiveWord + 1 )

End Loop

If ( $Score > 0 ; Score ; 0 )

[comment]

EXCEPT if the customer tries to use it like a web search, eg looking for a product and they enter "Green Jersey" does not give them a "Green Long Sleeved Jersey" as the string does not exist.

 

Do your users actually enter the search phrase in quotes? In Filemaker, if you enter a phrase in quotes, then the search engine will look for the exact, full text between the quotes. I believe the same thing holds true for a "web search" (Google?). So if you enter "Green Jersey" (quoted) as your search phrase, you should not expect to find a record containing "Green Long Sleeved Jersey". IOW, this ain't broken and needs no fixin'.

 

---

Edit:

Or did you just mean that the PatternCount() doesn't return anything after the search? That would be something else.

[Rick Whitelaw]

Absolutely. No need for any further complications especially a CF. You could also trap for, and disallow, quotes.

[comment]

what I want to do is use a custom function with the MiddleWords function to search for each individual word and then rank the results for each word it finds, 

 

This could be done as follows:

 

MultiPatternCount ( text ; searchPhrase ) =

Let (
countWords = WordCount ( searchPhrase )
;
PatternCount ( text ; LeftWords ( searchPhrase ; 1 ) )
+
Case ( countWords > 1 ; MultiPatternCount ( text ; RightWords ( searchPhrase ; countWords - 1 ) ) )
)

This goes over the searchPhrase word-by-word and adds the PatternCount of that word in text to the total result.

 

Important:

The PatternCount() function counts the occurrences of the current word in text as a string - not necessarily as a word. For example, given the  text "Fred has a red car." and the searchPhrase "red car", the function will return 3 (2 x "red"  + 1 x "car"). Counting the occurrences of a given string in text as a word is more complicated - see: http://www.briandunning.com/cf/1390

[Angelssword]

 

Do your users actually enter the search phrase in quotes? In Filemaker, if you enter a phrase in quotes, then the search engine will look for the exact, full text between the quotes. I believe the same thing holds true for a "web search" (Google?). So if you enter "Green Jersey" (quoted) as your search phrase, you should not expect to find a record containing "Green Long Sleeved Jersey". IOW, this ain't broken and needs no fixin'.

 

---

Edit:

Or did you just mean that the PatternCount() doesn't return anything after the search? That would be something else.

 

They don't do the quotes, but as it is calculated in a field to allow me to set up a sort priority I created it using PatternCount() which is a search limiting factor in this case.  It is both the filter and sort field on a portal, my original code is as follows 

If ( Suppress = 1 ; -10000000 ;

Let ( $score =

If ( zt_SortBy = "Favourites" or IsEmpty ( zt_SortBy ) ;

Case (

/* if search is empty and show favourites = 0 */
IsEmpty ( zt_search_string ) and ( zn_Favourites_only ≠ 1 ) ;
If ( Favourites_Control_WS::favourite = 1 ; 20 ; 2 ) ;

/* if search is empty and show favourites = 1 */
IsEmpty ( zt_search_string ) and ( zn_Favourites_only = 1 ) ;
If ( Favourites_Control_WS::favourite = 1 ; 20 ; 0 ) ;

/* if search is present and show favourites = 0 */
not IsEmpty ( zt_search_string ) and ( zn_Favourites_only ≠ 1 ) ;
Case (
PatternCount ( c_search ; zt_search_string & " ") > 0 ; ( PatternCount ( c_search ; zt_search_string) * 2 ) +
If ( Favourites_Control_WS::favourite = 1 ; 20 ; 2 );
PatternCount ( c_search ; zt_search_string) > 0 ; PatternCount ( c_search ; zt_search_string) +
If ( Favourites_Control_WS::favourite = 1 ; 20 ; 2 )
) ;

/* if search is present and show favourites = 1 */
not IsEmpty ( zt_search_string ) and ( zn_Favourites_only = 1 ) ;
Case (
PatternCount ( c_search ; zt_search_string & " " ) > 0 and Favourites_Control_WS::favourite = 1 ; ( PatternCount ( c_search ; zt_search_string) * 2 ) + 20 ;
PatternCount ( c_search ; zt_search_string) > 0 and Favourites_Control_WS::favourite = 1 ; PatternCount ( c_search ; zt_search_string) + 20 ; 0
)
) ;
/* If the search is ordered by a price criteria uses the following */
If ( IsEmpty ( zt_search_string ) ; 1 +
( Case (
zt_SortBy = "Price ( Low - High )" ;
100000 - Price_Lists_WSProductSearch::Price ;
zt_SortBy = "Price ( High - Low )" ;
Price_Lists_WSProductSearch::Price
) / 100000 ) +
    Case ( PatternCount ( c_search ; zt_search_string) > 0 ; 1 ; 0 );
( Case (
zt_SortBy = "Price ( Low - High )" ;
100000 - Price_Lists_WSProductSearch::Price ;
zt_SortBy = "Price ( High - Low )" ;
Price_Lists_WSProductSearch::Price
) / 100000 ) +
    Case ( PatternCount ( c_search ; zt_search_string) > 0 ; 1 ; 0 )
)
);

/* Result, less penalty if discontinued */
If (
IsActive = "Discontinued" ; Case ( $Score > 20 ; $Score - 15 ; $Score - 1 ) ;
$Score )
)
)

functional if you want to find the whole string, but not so much if you want to search on each word.

 

 

 

This could be done as follows:

 

MultiPatternCount ( text ; searchPhrase ) =

Let (
countWords = WordCount ( searchPhrase )
;
PatternCount ( text ; LeftWords ( searchPhrase ; 1 ) )
+
Case ( countWords > 1 ; MultiPatternCount ( text ; RightWords ( searchPhrase ; countWords - 1 ) ) )
)

This goes over the searchPhrase word-by-word and adds the PatternCount of that word in text to the total result.

 

Important:

The PatternCount() function counts the occurrences of the current word in text as a string - not necessarily as a word. For example, given the  text "Fred has a red car." and the searchPhrase "red car", the function will return 3 (2 x "red"  + 1 x "car"). Counting the occurrences of a given string in text as a word is more complicated - see: http://www.briandunning.com/cf/1390

 

will this automatically work for 3, 4 or even 5 word entries?

 

thanks

[comment]

They don't do the quotes, but as it is calculated in a field to allow me to set up a sort priority I created it using PatternCount() which is a search limiting factor in this case.  It is both the filter and sort field on a portal, 

 

I am afraid I am not entirely with you on that point. Let me suggest we concentrate here on what happens after performing the find - i.e. ranking the found records according to their relevance to the search criteria. If there's another issue with how the actual find is performed, it might be better to start a new thread, as these issues seem to be unrelated?

 

 

 

will this automatically work for 3, 4 or even 5 word entries?

 

It will work for any searchPhrase of up to 10,000 words. Why don't you try it yourself?

[Angelssword]

Thanks, this has worked well, I have limited the count it provides to a maximum of 1 per word and it does what I needed.