Finding people on the rapid flex in FileMaker Pro

why u want this: flexibility is desirable

My original goals remained unchanged: I wanted to make Fusion Reactor's LiveField be put to use in the following google-like one field manner for finding people in my database...

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

The method below almost entirely resolves the performance problems I encountered, and no 'crippling' errors have occurred during testing on 3 machines, only occasional and minor disfunction¹. This has been tested on an old G4 with next to nothing in available RAM, accessing a networked copy of FileMaker and runs just fine.

The key new aspect is an initial "limiter" relationship. In practice, it means you need to type in 4 chars of one word or 3 chars of two before it will really start 'searching', however, this is quite acceptable in most environments. Only with tiny data sets would it be of much use to have "a" returning all people with first name or last name beginning with A. For such an eventuality the first method will still work very well and with tighter indexes.

• 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.

It will make life much simpler if you can include the custom functions I use also — so FileMaker Advanced is well worth having, but then you probably won't be here unless you have it.

• 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).

• custom functions

  Both: FilterC ( text ; AZaz09etc ): this is used to clean the text uniformly

  az ( t1 ; t2 ): a rather silly little function that I only made as such cos it keeps code clean and tidy - it shuffles a pair of values by alphabetical order and puts a separator [determined in function only to keep it tight] in between them. I use this to keep indexes small.

  If(t1<t2; t1 & "_" & t2; t2 & "_" & t1)

  Custom Function: az ( t1 ; t2 )

• 2 text fields in 'People' table: minimally indexed [with auto-index off] and with auto-enter calculations

  Both of these text fields need to have particular settings. The Storage tab must be set index minimal, with auto-index off; and the Auto-enter tab must be set to replace existing values:

      

  _FnLN_chars [first name & last name char keys]

  this is absolutely key to forming the relevant "limiter" keys and especially for providing exception handling — where there is no firstname, or only an initial, or no last name, or a really short last name. [Copy and paste of these functions doesn't work well cos of line endings or some such, here is plain text doc.]

  Let(
  [
  
  wbl = 4; // word bit length
  il = 3; // initials length
  w1 = Left(FilterC(Lower(FirstName); "az");wbl);
  w2 = Left(FilterC(Lower(LastName); "az");wbl);
  w1l = Length(w1); // word 1 length
  w2l = Length(w2);
  w1c = Left(w1; 1); // word 1 character
  w2c = Left(w2; 1);
  w1b = Left(w1; il-1); // word 1 bit
  w2b = Left(w2; il-1);
  result=
  Case(w1l=1; "_" & az(w1c; w2b) & "¶"; // initial only [exception]
  
  not w1l; "__" & Left(w2; 2) & "¶"; w1 & "¶") // [exception - no first name]
  &
  If(not w2l; "__" & Left(w1; 2) & "¶"; w2 & "¶") // [exception - no last name]
  
  &
  If(w1l and w2l; az(w2c; w1b) & "¶" & az(w1c; w2b) & If(w1l>=2 and w2l>=2; "¶" & az(Left(w1;2); Left(w2;2)) )
  )]; // initials if both
  
  If(Right(result;1)="¶"; Left(result; Length(result)-1); result)
  
  
  ) // e outer let

  Custom Function: Portal_Filter_Name_Chars ( fn ; _ln )

  _FnLn [first name & last name]

  Let([
  text = FilterC(Lower(FirstName & " " & LastName); "az ");
  wc = WordCount(text);
  _fn = LeftWords(text; 1);
  _ln = MiddleWords(text; 2; 1)
  ];
  
  If(wc=1; _fn & _ln; // only one will have value thanks to middlewords
  
  Left(_fn; 1) & "_" & _ln & "¶" & // initial & name key²
  Left(_ln; 1) & "_" & _fn & "¶" & // initial & name key²
  _ln & "¶" & _fn
  )
  
  )

• 1 global text 'LiveField'

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

• 3 global text calculation fields based on the 'LiveField'

  In your global/utility table create 3 calculation fields based on above 'LF'. Mine are named _kfz_Both_Chars ["limiter"], _kfz_W1, _kfz_W2 (k = key, f = filter [type of key], z = zzz [type of filter key!]) and look like this:

  _kfz_Both_Chars

  Let(
  [
  wbl = 4; //SYNC with data keys or it will die (only applies if u are thinking of changing these for performance related issues)
  il = 3; //SYNC
  text = FilterC(Lower(LF); "az ");
  wc = WordCount(text);
  w1 = LeftWords(text; 1);
  w2 = RightWords(text; 1);
  w1c = Left(w1; 1);
  w2c = Left(w2; 1);
  w1b = Left(w1; il-1);
  w2b = Left(w2; il-1);
  w1l = Length(w1);
  w2l = Length(w2);
  //initial or name keys
  initials = If(wc=2 and w1l>=2 and w2l>=2; az( Left(w1;2);Left(w2;2) ) & "¶"; az(w1c; w2b) & "¶" & If(w1l>1; az(w2c; w1b) ));
  names = Left(w1; wbl) & "¶" & Left(w2; wbl)
  ];
  
  Case(
  
  wc=1; Left(w1; wbl); // just put the word in there...
  wc=2;
  
  "_" & az(w1c; w2b) & If(w1l>1; "¶_" & az(w2c; w1b)) // initial only [exception]
  
  & "¶__" & Left(w2; 2) & "¶__" & Left(w1; 2) // [no first name exception]
  
  & "¶" & If(wc=2; initials; names) // initials if possible or names
  
  ) // end of case
  
  )

  _kfz_W1

  Let (
  [
  max_lnb=4;
  min_lnb=3;
  text=FilterC(Lower(LF); "az ");
  wc=WordCount(text);
  w1=LeftWords(text; 1);
  w2=RightWords(text;1);
  w1c = Left(w1;1);
  w2c = Left(w2;1);
  w1l = Length(w1);
  key1 = w1c & "_" & w2;
  stk= w1 & "¶" & w1 & "zzz" //std key
  ];
  
  Case(wc = 1 and w1l<max_lnb; w1;
  wc = 1; stk;
  wc=2; key1 & "¶" & key1 & "zzz";
  wc = 3; "Remove whitespace¶in two word names.") //this shows error text for names like 'Leonardo Da Vinci', which should be written, Leonardo DaVinci, or 'Jean Paul' => 'Jeanpaul'
  
  )

  _kfz_W2

  Let (
  [
  text=FilterC(Lower(LF); "az ");
  wc=WordCount(text);
  w1=LeftWords(text; 1);
  w2=RightWords(text;1);
  w1c = Left(w1;1);
  w2c = Left(w2;1);
  key1 = w1c & "_" & w2;
  key2 = w2c & "_" & w1
  ];
  
  Case(wc = 1; _kfz_W1;
  
  wc=2; key2 & "¶" & key2 & "zzz")
  
  )

• 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 'LF' and looks like this:

  LF & '¶' & LF & 'zzz'

  You then build the relationship like this:

  LF <= FieldToBeSearched
  AND LF >= FieldToBeSearched

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

• a mashup relationship built on 5 criteria

  This particular relationship uses five criteria. One "limiter" =/matches relationship [highlighted below] to dramatically reduce the potential related set early on, and two more ranges (built from two relationships each as above) to aid in pinning down the exact 'target' person.

  
  

  It works well for the occasional 'Peter Peters', or 'S Seidler' too. I also sort the relationship on the basis of firstname then lastname.

  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 if you enter more than 2 words...

  If this makes no sense to you, there is a little diagram to show you how the relationship works in the file itself [click on 'A Brief Explanation'].

• 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 'LF' [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!

• LiveField onload script

  I need to be able to switch between layouts to hunt for people or organisations, but want them to look more or less identical and have barely discernable change of layout. A problem I found was I couldn't too simply 'go to object' to enter the livefield. A fractional pause might work, but more reliable still is to use the additional LiveField parameter for 'onload script' to enter itself or do what you need. Kindly documented by Craig@Fusion. I use this to select the LiveField in the demo on load.

  This isn't working on my G5 10.4 system atm. So if you find this doesn't do the job add this to it — a perform applescript in the same onload triggered script:

  tell application "System Events"
      key code 48 --tab
      --key code 124 --right arrow key
  end tell

download demo file

Right click to download a file demo'ing this method with 40,000 test records.

Thanks for your time. Please let me know if there are some bugs in it still.

footnotes