Spotlight redux: Building a FileMaker Search Engine
This technique isn’t a lifesaver, but it could be. People have been asking and I will be showing have already shown it to the NY FileMaker Pro User Group, so here it comes in a nutshell for all the other users and developers:
Wow!
Huh?
What This Is
A solution to the problem of finding data in FileMaker. A couple of months ago, Jeff Cohen of Blue Engine Solutions enlightened me to the fact that my users were getting in to Find Mode but not realizing it. Meaning, they couldn’t easily differentiate the different modes in FileMaker and often ended up nearly logging out and logging back in just to return to their starting point. Ultimately, this would usually result in a support phone call or some kind of panic attack on the part of the client and me having to re-acquaint the user with how to go back to Browse Mode or to click the button that would help the system return to data entry mode. As the deployment of my solution grew, the same kind of panic was being shared by many users. Thankfully, several other developers concurred that this is a problem they deal with often and was not exclusive to my design and database functionality. This is my solution, and it is one that I think once users wrap their head around in FileMaker, they will elaborate upon with all sorts of new whiz bang features and I can’t wait to see what people come up with.
What This Is Not
First off, I think alot of users might be looking for a way to include a search in FileMaker in the Spotlight search currently available in Mac OS X “Tiger”. For example, say you have a document management system, built in FileMaker 7, that stores JPEGS, Word, PowerPoint, Excel spreadsheets, etc. in a container field. I think many users are looking for a way to type in a Spotlight Query and also have Spotlight search the documents stored in the FileMaker database. This technique does not do that. Sorry, but it just doesn’t. I may have a quasi/almost-good/half-baked idea/workaround if that’s what you really need. I’m also working on this and I’ll keep you posted on the results.
The Solution
As that greyed box said earlier, this technique is simply a field placed in the header part of a FileMaker 7 layout that executes a script when data is entered into the field. (It’s important to note that this is only available to version 7 users. The basis for some of this functionality is not available in version 6, but there is a slight workaround - see the end of this article.) Simply put, this lets FileMaker do even more of the technical thinking and working for database users when they are querying for data, much like how a search engine like Google can determine if you are looking up an address, phone number, website, document, image, or a product to buy. We start, by creating a calculation field, which returns text, that compiles all the data we want to be easily searchable into one field. So, if we were designing this in an inventory system and wanted users to be able to search for items by a brand, descriptive text, price, and discount percentage we would create a calculation field called Search Index. The calculation would be:
Search Index =
Brand & ” ” & Description & ” ” & Price & ” ” & Discount
Next we create a global text field in a new table. I created a special Globals table which should have NO RECORDS. The table will still technically hold data but it should only have Global fields. The reason for this is that if there is a record in the said table, the script execution will occur with the wrong search query i.e. it will always be the previous search query and not the current one (this has something to do with the act of ‘committing data’ and even if the Commit Records script step is used, the script will not execute properly… go figure). See the thumbnail for the proper basic set up of this technique.
From there, the path to completing the setup for this technique is simple and straightforward. On any of the layouts you wish to use the Spotlight search technique, place the search entry field from the Globals table. In the case of my diagram, it’s the field entitled “Search Entry”. Put that global field on the top of whatever layout you plan on searching from and then change the Auto-Enter options to process a script (with an auto-enter calculation that says something like “Case(not IsEmpty(Search Entry); XMpl_StartScript(Get(FileName); “SearchScript”))” assuming your search script is named “SearchScript”). Specifically the search script should do the following:
- Commit records
- Enter Find Mode
- Set the Search Index field to the value of the Spotlight search field (called Text in the case of the diagram)
- Perform Find
… and go to whatever results layout you want or pop open a new window or do whatever. else you like.
The end result is you remove the necessity of having your user to break down a search query into its component parts. You could even build some natural language syntax processing and simplify FileMaker’s search prowess for new or less technically inclined users. For example, you could have users search a clothing inventory system by entering the query “sold items from 9/1/2005 to 9/10/2005 without jeans” and then break the query down to:
- Find Request #1
- Sold Checkbox: [x]
- Date sold: “9/1/2005…9/10/2005″
- Duplication Find Request #1
- Find Request #2
- Sold Checkbox: [x]
- Date sold: “9/1/2005…9/10/2005″
- Omit Records: [x]
- Item Description: “jeans”
Conclusion
I haven’t really finished the full presentation of this technique and I’ll be posting the example files shortly. I know many people emailed about when this technique would finally be brought to light and here is my first iteration at discussing it. I hope you find it useful and please pass along ideas and enhancements. In the meantime, watch out for the example files I’ll be providing.
Thanks Denis. Hey are you in the process of developing a spotlight plugin for entourage too?
James, I wish I could develop some kind of plugin to allow Spotlight to index Entourage email, but alas I can’t. That being said, it doesn’t even seem like Microsoft themselves have been able to yet surmout such a task. However, given their ingenuity, I’d imagine that they have accomplished it, it’s just that they’re looking for a way to repackage it and brand it as part of a new version of Entourage.
That reason of not being Spotlight compatible alone was enough for me to jump back to Apple Mail. I made the switch and have not looked back since, except for browsing and posting to newsgroups.
Cheers!
Hi Denis
I read that you have made the switch back from Entourage to Apple Mail in order to benefit from Spotlight search. I also want to do this, but I got stuck halfway. While I managed to get all of my 4 Gigabytes of mail messages over, I haven’t found a way to export my 200 plus mail sorting rules and my 500 plus random quotes inserted as signatures…
How did you proceed ? I used the Export-Import Entourage Apple Script, but it didn’t export mail filtering rules and signatures.
Cheers
Greg Wah
http://www.libertes-internets.net
Greg,
I think you’d maybe want to try using Automator or AppleScript UI events to write some kind of Automator script or AppleScript to automate the import of all the rules. I don’t know off hand, if there are any tools to actually do what you’re looking for. Maybe check versiontracker.com
Denis,
I found your article very interesting and I enjoyed your search field.
This gave me an idea for constructing a search layout based on three related tables with additional buttons for exporting records to Word or Excel. Your example is based on one table and I assume it would work with related tables as well?
I wonder if you could just reassure me that I am on the right track:
I have three tables Contacts, Category and Category Type related by ContactID, CategoryID and Category Type ID. I created additional three table occurrences based on each of these tables related by their IDs (Contacts2, Category2 and CategoryType2). Then I related Contacts 2 Table to Table Contacts by ContactID. Then I created a layout based on the additional Table Occurrences with your search field searching Category and Category Type. It works - it finds the records and it does not affect the layout based on the original table Contacts.
I am happy with that - it is just that I would like to make sure that I will not run
into any problems later. I would be grateful for your reply.
Many thanks,
Margit
Margit,
Thanks for the response. I’ve deployed the technique in a couple of solutions and it works great. The only minor issue is making sure that every FileMaker client has the free events plugin installed (assuming you are using the plugin). I imagine with large FileMaker databases, that the index might be a concern with keeping the database size. Other than that, no major concerns.
Please keep me abreast if you notice anything unusual with the technique!
Emile,
Many thanks for your response - I was very happy to receive it.
Could you, please, tell me what would constitute a large FileMaker database?
I have modified your script a little. I was wondering if I could email it to
you for
you to have a look to make sure it would be safe?
Many thanks and regards,
Margit
Denis,
You very kindky asked me to let you know if I have any additional questions.
I think I’ll be able to sort out the issues I mentioned before.
However, I would be most grateful if you could tell me if it is possible to do AND OR searches with your technique? Let’s say I would want to find both slacks and jeans in your demo file or both using the same Search field. I tried to work it out, but perhaps it is not possible?
I would really be happy to hear from you.
Margit
wow. this is so amazing. LOVE IT. i’ll definitely be using this alot. thank you, thank you, thank you.
your kung-fu is strong.
My kung-fu is both wicked and strong. It’s because Ninjas killed my family.
just a heads up, I’ve made one little insignificant mod by adding the little X graphic found in all OS X search fields (iTunes, Spotlight, etc) which is used to clear the search field. I’ve added that graphic to the FileMaker search field and made it a button attached to a 2 step script to clear the search field and show all records. This way the field is even easier to reset and more closely resembles the standard behavior of other OS X search fields. Just a wee little detail.
OOoooooo, that is a good little added touch. I’ll hunt down the source graphic in my Library folder and put that into an updated version of the technique! Thanks!
Man, you made my life so much easier with this solution, u can’t imagine…
Me love U for a long time!!!
I just discovered a bug(?) in this technique. Let me demonstrate it on this downloadable Spotlight.fp7 database (I did’nt modify it in any way):
search for term: “orth” or “ort” or “aid”
instead of finding “North Face” it finds nothing.
I’m on FileMaker Advanced 8.0v1 OS X 10.3.9
by the way… the Notes in the Instructions are unclear to me, most probably because my english is not perfect, so can you explain it with other words:
“NOTES:
If you set the Auto Enter calc to be the following instead:
Case(not IsEmpty(Search Entry); XMpl_StartScript(Get(FileName); “SearchScript”))
You will not NEED to put the Search Entry ”
if I change the Auto Enter to above nothing works…
Thanks for reply
Well I would like to answer my previous post - or at least the first question: the BUG…
1) What’s was missing from the script to function as it was Spotlight…:
Set Field [Spotlight::Search Index; “*” & Globals::Search Entry & “*”]
This way FileMaker will find even parts of the word and not only whole words, or parts of the word IF THE BEGINNING WAS GIVEN only.
I meen if you search for Wrangler and you type “wra” only, FM will find it,
but will give no result if the search phrase was “ran” only - without the first “W” letter of the word.
2) I also set the beaviour of Search Entry field in the header to “Go to next object using”
not only by “Tab key” but also with “Return key” and “Enter key” - to be more Spotligtish.
But I still don’t understand what you meen by:
NOTES:
If you set the Auto Enter calc to be the following instead:
Case(not IsEmpty(Search Entry); XMpl_StartScript(Get(FileName); “SearchScript”))
You will not NEED to put the Search Entry
Kakalaka,
Thanks for your spirited and detailed comments. I’m not sure what I meant by the note I put in the AutoCalc area frankly. Obviously, there is something that is grammatically incorrect. I think what I meant to say was a pseudo-code breakdown of that function. Basically:
Case(not IsEmpty(Search Entry); XMpl_StartScript(Get(FileName); “SearchScript”))
/* Test if we NEED to run the script because the Search Entry field is empty */
I was about to suggest the modification you mentioned using the asterisk character before and after search terms. It obviously worked for you as it did in my own tests. I’m going to modify the sample file and re-upload it to the site as soon as I can. Thanks for your help!
Emile,
I’ve come across a little mystery using your technique. I tested it on a couple of databases which seem to be working fine. Recently I implemented your technique in another database and I discovered an anomaly.
The scenario is: three tables Category, Subcategory and Organisation.
Organisation is related to Category by CategoryID and to Subcategory by SubcategoryID.
This scenario is no different from the other databases I mentioned above and which work fine with your technique.
I constructed a calculation field comprising Organisation, Category and Subcategory.
When I do a search on looking for name of Organisation or Category, it’s fine.
Now Subcategory shows a problem. Example data is: Clubs and Centres, Community Care, Affiliates etc. If I search for Clubs and Centres or just Clubs, the search finds nothing, but if I search for Centres, it finds Clubs and Centres. If I search for Community or Affiliates, it finds nothing, but if I search for Care, it finds all Community Care.
I tried everything to solve this - I have to say that adding the concatenated asterisk
did not work.
It seems to find the last, additional words - not the first one or data consisting of
a single word. The only thing I could do to remedy this, was to delete the whole Subcategory table with its records and recreate a new one.
Has anyone else come across this or is there a simpler solution to this rather than deleting the table?
Thanks.
[…] I just recently stumbled upon this technique and it caught my attention, most especially because it can prove to be a very interesting framework for using my own Spotlight technique. I have yet to fully build it and use it, to give full disclosure, but it’s one of the many kinds of innovation I like to see in FileMaker. The site, called builder.au, had been flakey for me and wouldn’t respond so I quote the article in its entirety below, but I would hope that you visit the site and see what other goodies are there. So without further adieu, enjoy: function lbLoad() { var ads_agt=navigator.userAgent.toLowerCase(); var ads_is_ie = ((ads_agt.indexOf(”msie”) != -1) && (ads_agt.indexOf(”opera”) == -1) && (ads_agt.indexOf(”omniweb”) == -1)); if(ads_is_ie) document.getElementById(’lbiframe’).height = document.getElementById(’lbiframe’).contentWindow.document.body.scrollHeight; else document.getElementById(’lbiframe’).height = document.getElementById(’lbiframe’).contentWindow.document.body.offsetHeight; } […]
The download link you provide in the instructions of your database to get the “Free Events” FileMaker Plug-In is only a download of another filemaker database and does not include the plug-in. Where can I find this plug-in?
Adrian,
This plugin is on your CD when you purchase FileMaker advanced. You can also download it from databasepros.com by going to the Resource section and searching the tips for the plugin
I didn’t get a CD, I received a download link from FileMaker. I even went through the program files folder without success. I checked databasepros.com and found the page in the resource section, which says it doesn’t include the plug-in. What’s the plug-ins file name?
Nevermind… It’s been a long day.
Yeah, it’s been a long day here too.
Regardless, you can view the page here:
http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND= &resource_id=DBPros000590
Click on either the Mac or Windows icon to download the plugin.
[…] my attention, most especially because it can prove to be a very interesting framework for using my own Spotlight technique. I have yet to fully build it and use it, to give full disclosure, but it’s one of the many kinds […]
Help. I have been trying to load the plugin so I can use this technique works and have encountered a number of problems. Is there is a secret to setting up the plugin? I am on a Intel based Mac and had to download the PC version of the plugin because the .sit ending doesn’t do anything. I added the .fp7 and the .sit version to the extension folder of FMP and still nothing. Then, which I think the heart of the problem, within the example file the needed script-running script has turned into this:
Case(not IsEmpty(Text); (Get(FileName); “Show Message”) & Text)
It wasn’t even in the plugin.fp7 I downloaded, or so it seems.
My FMP still doesn’t recognize the XMpl_StartScript command. Also what is the web compatibility of the technique?
I am on FMP8, in case it matters.
Any help or alternatives would be greatly appreciated.
Thanks,
Nicole,
There are a number of things to note here:
- First, did you install the plugin?
- Does FileMaker recognize it (see menu FileMaker Pro > Preferences > Plugins tab and you should “Example”)
- Are you on an Intel-based Mac?
Any one of these three may be the issue. I’m not sure on the web compatibility of the technique - it should work but YMMV.
Emile,
Thanks for the reply,
Yes, I am on an Intel-based Mac. How is that a problem?
Also, I have a remote database and may be able to install the plugin on the remote server instead of the local hard drive, right? I’ll let you know how that works out.
Hi Nicole,
The plugin doesn’t function properly on Intel Macs. The Shell plugin has to be replaced by the free ZippScript plugin from ZippTools.com. This changes the code as well. I’ll post that here too at some point but it effectively changes the call to the MacExample plugin to the ZippScript plugin and it should work again for you.