Printable
Version
 

Finding people on the rapid flex in FileMaker Pro

Using Fusion Reactor for ultimate in smoovness

NOTE 17.11.08: Please check latest article and new improved methodology. The method below has known and significant volume related performance issues (with data sets above 5-10k).

why u want this: flexibility is desirable

    I wanted to make Fusion Reactor's LiveField be put to use in the following manner for finding people in my database...



    ...and look!!
    it still gives same results if I switch the order...


  • more like FileMaker finds - so more intuitive

      In FileMaker, I can type in partially completed names in FirstName and Surname fields. Using the methodolgy detailed in this article I can also do so in my single LiveField and it instantly performs them - by cunningly searching across two fields! So if he says, "Bond, James" or "James Bond" and you don't know how to spell James or Bond, you could just type in "Bo Ja" or "ja bo" and still find him!

  • works quicker - less keystrokes & less accuracy required

      Spelling problems, when I've got lots and lots of people with weird and wonderful names, it reduces the found set more easily and more quickly to write first few letters of both names. "Do you spell Claire, C-l-a-i-r-e, C-l-a-r-e, C-l-a-i-r?" Alistair, Alaistair, Alaister, Alister, Alasdair... So you just write Cla, or Al and move on to second name... Less need for the sometimes embarassing and always slow, "How do you spell that?" We pin down our target without having to know exactly how their names are spelt - less keystrokes required, and less interaction with user - time is saved.

      Of course, young Kathryn, Katherine, Catherine, or is it Catheryn(?) still presents a problem... I haven't bothered create further syntax like: Ka|Ca to allow such variants!!

implementation steps

      This will work without the Fusion Reactor plug-in, but not as nicely. The relational structure is obviously still functional, but you can't instantly 'see' that you've narrowed results down sufficiently. If I was doing it w/o Fusion I'd have a surname field and tab into firstname field - a script would perform on field exit and we'd have narrowed it down to just those with the relevant surname.

  • Fusion Reactor plug-in

      Go get it and install it. This demo file won't work if you're not running Fusion Reactor.

      You may want to buy the Reactor after this demo (and looking at the DayCalendar and other marvelous stuff it can do etc - demo'ed in their file), they give free updates to whoever has bought it, and are planning on updating the item... I would suggest joining FileMaker Technical Network to get a discount on the developer license (as well as myriad other benefits).

  • 1 global text 'SearchField'

      In your global/utility table [Ray Cologon's excellent FileMaker Bible has influenced me], create a global text field. I called mine 'SearchField'.

  • 3 global text calculation fields based on 'SearchField'

      In your global/utility table create 3 calculation fields based on above 'SearchField'. Mine are named _kfz_LeftWord, _kfz_RightWord, _kfz_BothWords and look like this:


      k = key, f = filter [type of key], z = zzz [type of filter key!]

  • why the ZZZ's?

      If you are asking that question, now is definitely the time for some FileMaker relational theory.

      The relationship technique depends on a well known use of a calculated multi-key to 'filter' the portal. It is based on your global 'SearchField' and looks like this:

        SearchField & '¶' & SearchField & 'zzz'

      You then build the relationship like this:

        SearchField <= FieldToBeSearched
        AND SearchField >= FieldToBeSearched

      So if SearchField = 'ad', then the following match: Adrian, adelle, adrienne, Adam, etc. - they all lie [alphabetically] between 'ad' and 'adzzz' - 'Adzzzzzy' would not, however!

  • 3 indexed calculation fields in 'People' table

      Originally, I only had one. And I thought, "How neat." Then alas, alack, I realised that with double-barrelled names, or names that used apostrophes it simply would not work. So I had to make them. Bit of a shame really. Do you think it's worth the price? Probably is my thought. Here they are:

      I highlight 'Fn' cos it might be empty, it has to have a value to avoid it breaking the relationship - hence the IsEmpty test.

  • a mashup relationship built on 8 criteria

      This particular relationship uses eight such criteria [where two is the standard], because I desired the flexibility to work with two fields supplied in any order - first name and surname.


      The first 4 relational criteria will do in almost all situations. Then I discovered a little bug that could only really be fixed by adding a further four[!!!] relationships. The slight weakness was where Fn & Sn similar, e.g. the occasional 'Peter Peters', or 'S Seidler' - where the S for the forename would not narrow down the selection at all. This is now fixed, so it behaves very robustly. This is just in case you were thinking it unduly complex. Here are the last 4 criteria:

      For finding people with first or second names like 'Jean Paul', 'Anne Marie', 'de Beers' or 'van Dyk', you need to omit spaces in 'searching' - there is an error warning to tell you this, otherwise it could get hopelessly lost...

  • portal redraw/refresh requirement for LiveField

      The only slightly less than obvious feature about its installation, is that you will need to force portal redraw/refresh on 'livefield' edit. To do this you have to have the real field (which the 'livefield' is feeding) covering the portal which it is used to filter. This is just because FileMaker only redraws stuff it deems to have changed, and while it knows that the field has changed, it doesn't automatically 'clock' that the portal contents will have. So you fix this by entirely covering the portal with the 'SearchField' [make font size 1 and other trix to make it invisible], looking like this:

      Livefield, portal and, to the right, the field cover >>>

      Voilà, portal invisibly covered!

download demo file