Perform a Search using multiple words separated by a comma

[Christoph]

I've developed a Search field that operates smoothly with single words, retrieving all relevant records associated with that word. For instance, when you search for "guitarist," it scans all fields across records for occurrences of "guitarist." Now, I'm aiming to enhance its functionality. I'd like to incorporate the ability to search for multiple terms, such as "guitarist, vocalist," separated by a comma. This expanded search should cover all fields and records. Thank you for your assistance.

[Søren Dyhr]

Quickfinds aren't stackable as fm-requests are, but you could then loop you through each found set and gather recordIDs in a global, and then finally GTRR(FS) based on the collection af matching IDs.

I have made tabbing out as well as typing <return> in the search field, triaging the search:

 

stackedQuickseach.fmp12

Edited May 20 by Søren Dyhr

[comment]

2 hours ago, Christoph said:

search for multiple terms, such as "guitarist, vocalist,"

You need to clarify whether you want to find records that contain ALL of your search terms (an AND find) or records that contain ANY of them (an OR find).

 

2 hours ago, Christoph said:

This expanded search should cover all fields

Maybe the built-in QuickFind feature fits your requirements? 
https://help.claris.com/en/pro-help/content/performing-a-quick-find.html

Otherwise it could get quite complicated.

 

[Christoph]

12 hours ago, Søren Dyhr said:

Quickfinds aren't stackable as fm-requests are, but you could then loop you through each found set and gather recordIDs in a global, and then finally GTRR(FS) based on the collection af matching IDs.

I have made tabbing out as well as typing <return> in the search field, triaging the search:

 

stackedQuickseach.fmp12 724 kB · 1 download

Brilliant! Thank you, Søren, for the info and the file! Makes it so much easier knowing where I went wrong. I see what you've done there and where I got confused is the connection between the Table and Table 2 occurrence. I had it pointed elsewhere. It all seems so easy in the end. There's one thing I can't seem to get going. I already had a file with several records and when I do a Search, I don't get a result. I do if I create new records after the latest script was added. Anyway around that and not have me re-enter all of the info from the previous records? Thanks again!

12 hours ago, comment said:

You need to clarify whether you want to find records that contain ALL of your search terms (an AND find) or records that contain ANY of them (an OR find).

 

Maybe the built-in QuickFind feature fits your requirements? 
https://help.claris.com/en/pro-help/content/performing-a-quick-find.html

Otherwise it could get quite complicated.

 

Good question, comment. At first, I wasn't sure how I needed the Search to work but yes, the end result was to find records that contain all of the search terms. I did try the built-in Quick Find and although it is great, I needed something a little more customized. Thanks for the info!

[Søren Dyhr]

1 hour ago, Christoph said:

Anyway around that and not have me re-enter all of the info from the previous records? Thanks again!

It must be the relational key, eventhoug it's an autoenter, could a replace be done in it, maybe you will hit an upper limit of 32k if the relational key is made out of Get(UUID) ... then turn it into a serial number instead.

--sd

[Christoph]

Thank you for the extra info, Søren. Where exactly would I turn it into a serial number? I'm still learning

[Søren Dyhr]

In the dialog where the autoenter i set up, here choose serial numbers. When leaving make sure new values are given to each ID, here enter the field on the layout, a from there turn to the replace found in the menu above. Here is an oppertunity to change the serialisation (for the found set) make sure to show all records before! - then is there some extra to take care off, the auto enter should continue from the highest number at present. (This might be where you encountered the error?)

—sd

[Christoph]

I believe I understand what and where you're referring to. Once again, my beginner status makes me look in all the wrong places, which throws me off. Thank you, sd! The added info is appreciated.

[Søren Dyhr]

I've just made you a movie, showing what I'm doing:

https://www.dropbox.com/scl/fi/xbu6wrwia5lk0fw7e8bee/Sk-rmoptagelse-2024-05-21-kl.-14.24.42.mov?rlkey=1bl493fmgjn0xc226udykdn8q&st=pizpb1qt&dl=0

--sd

[Christoph]

Hi Søren,

Thank you so much for creating the video on Replacing Field Contents. I really appreciate the extra effort you put in. It’s a huge help. I was on the right track as you described, but I won’t be able to confirm until I’m back in my studio this evening. It’s best to follow your steps exactly while working on the file. Thanks again!

[Christoph]

I just wanted to thank you again, Søren. I followed your video and now, all of my records are showing up without any issues. Cheers!

[Søren Dyhr]

You didn't in you initial description clearly distinguish between and'ed and or'ed searches ... and it bothered me at bit as how to accomplish it with Quickfind? I recalled an old template of Jon Rosen's, from previous century - with a method to pull it off. 

It utilises a special feature with repeating fields, and in order to fill in record wide values into each a repeaters cells, I found rescue in this CF:

https://www.briandunning.com/cf/895

But in order to reflect any change in the record, must trigging happen, because he (Bruce Robertson) uses Evaluate( inside his CF... I do it as well, actually!

But take a look at the template I here attach, and please question - what you don't get!!!!

--sd

 

stackedAndOrSearch.fmp12

[Christoph]

Søren, I appreciate you raising this topic. While comment had already touched upon it, my understanding is limited, making it difficult to ask the right questions. Reopening the discussion and sharing the template file and the link is greatly appreciated. Thank you so much.

Due to the extended weekend in the US, I am away with the family until next week and will revisit the file. Yes, I may have questions

[Søren Dyhr]

On 5/26/2024 at 12:38 AM, Christoph said:

Reopening the discussion and sharing the template file and the link is greatly appreciated. Thank you so much.

While playing along with the latest template, have i discovered a need to highligt the phrases on which the search is made, there are still som flaws in the way the highlighting works here, data is only supposed to reflect what a single users search have of consequences, each user will distract every other user!!!!!!! Other contributors to this thread are welcome to chip in with suggestions to solve this particular problem.

 

 

stackedAndOrSearchHiLi.fmp12

[Christoph]

What an excellent enhancement to the template, Søren! It greatly simplifies the process of finding the highlight. I believe I understand the issues you've mentioned and I also look forward to others chiming in with their suggestions. A big thank you for your efforts thus far!

[comment]

On 5/27/2024 at 5:25 PM, Søren Dyhr said:

data is only supposed to reflect what a single users search have of consequences, each user will distract every other user!!!!!!!

Indeed that is a serious flaw. It's not just a matter of distraction; there is also record locking and modification logs to consider.  I don't know of a solution other than stacking calculation fields on top of the "real" fields to display the highlight. But with so many fields participating in the search... Perhaps a single repeating calculation field could serve them all? Or maybe just build the entire UI in a web viewer.

It should also be pointed out that the requirement to "search all fields" is not entirely legitimate from the standpoint of database design. If you are searching for "Washington", it makes no sense that you would really need to find people whose first name is Washington, along with people whose last name is Washington, combined with people who live on Washington Avenue, or in Washington D.C, or in the state of Washington. It's true that Quick Find provides this functionality, but I would need great amounts of persuasion to get into it myself.

 

 

[Søren Dyhr]

10 hours ago, comment said:

Perhaps a single repeating calculation field could serve them all? Or maybe just build the entire UI in a web viewer.

Yes I've how ever been thinking along those lines, by including the gist of this:

https://filemakerhacks.com/2011/06/09/lookups-and-repeaters/

Then having two panes, one for editing the record and the other one performing the lookup into a browse "search results" in which each users field contents could be high lighted accordingly. But I havn't had the opportunity to think it through - which in my case unfortunately is done on a subconcious level. 

But importing, between tables - could split each from the repeater into a portal, of related records keyed by Get ( AccountName ) and imports could be auto entered ...on their way in!  

--sd

Edited May 30 by Søren Dyhr

[Søren Dyhr]

12 hours ago, Søren Dyhr said:

But importing, between tables - could split each from the repeater into a portal, of related records keyed by Get ( AccountName ) and imports could be auto entered ...on their way in!  

It should be pretty simple to prevent both record locking as well as personalize the highlighting with just these scriptsteps trigged from an OnRecordLoad trigger because we already have the repeater to import from:

...the portal can be cut into pieces, and be scattered out over the layout, as if it was the original field....

--sd

Edited May 30 by Søren Dyhr

[comment]

How much effort and resources are you willing to invest in this? A repeating calculation field that highlights the search terms separately for each field should not be that difficult to implement. Anything beyond that seems to me as exceeding a reasonable cost/benefit ratio.

Do note that your file highlights the search phrases anywhere in the field, but the default find only looks for words that begin with the search phrase. Therefore, if you searched for "pa" (without the quotes) and found Lenna Paprocki, the highlight in "lpa[email protected]" is incorrect and misleading: that is not why this record was found.

 

[Søren Dyhr]

9 hours ago, comment said:

if you searched for "pa" (without the quotes) and found Lenna Paprocki

I've just ignored it for a while, by using Ray Cologons CF which are ment to highligt larger blocks of text ... where a dedicated CF properly would be possible - borrowing some ideas from his.

9 hours ago, comment said:

How much effort and resources are you willing to invest in this?

Isn't it the stereotype saying about Rock'n'roll being 3 chords and audiences in 1000, while Jazz is 1000 chords and audience sizes around 3 people? ... or rather why are some people solving suduko or similar puzzles, instead of getting some real work done? Why byzantine stuff when somebody has mislaid Occam's razor somewhere?

Allthough there is a UX matter in this, too many things going on in a nearly "headless" layout: 

Quote

The pilot of a small plane is hopelessly lost in a cloud bank. His passenger worriedly asks, ‘What can we do?’The pilot responds, ‘I’ll fly a little lower and see if I can get my bearings.’ Just then a building comes into view below. The pilot yells to a man on the roof, ‘Where am I?’‘ About 150 feet above the ground, in an airplane!’ the man yells back. Upon hearing the answer, the pilot immediately locates the airfield and lands.The bewildered passenger can’t restrain his curiosity. ‘How did you figure out where we were?’‘Easy,’ answered the pilot. ‘That guy’s answers were completely accurate, but totally useless, so I knew he had to be a Microsoft software engineer. I quickly recognized the airfield at Redmond, Washington, and landed the plane.’The Art of Not Saying Too Much with an Interface.Who knows most, speaks least.

Presented by Albert Harum-Alvarez at the FileMaker Developer Conference,August 2000

But for a purely jazzed take on this matter have yet another template arrived: 

 

--sd

HiliteWithIndentionError.fmp12

Edited May 31 by Søren Dyhr