Phoenix FMPug: Presenting FileMaker 10

Just a quick announcement for those of you in the Phoenix area. I’ll be presenting FileMaker Pro 10 with a focus on practical uses for Script Triggers at the next Phoenix FMPug meeting. We’d love to see you there.

Update 10-Jan-2009: I added a link to the RSVP form.

If you plan on attending, please rsvp here so they organizers can get a proper count.

The Phoenix branch of FMPug, the FileMaker Pro Users Group, is young and growing. This meeting promises to be a hit, with all the FileMaker 10 excitement. We’d especially like to thank Audrey Akhavan from FM Magic, Inc, for starting the group and inviting us to present. Thanks, Audrey!

We can’t wait to see you all there.

Now back to regularly scheduled programming.

January 9, 2009 | Filed Under Announcements, S.F.R, Syndicated | Leave a Comment 

Script Triggers: Using Layout Triggers for a Dynamic Tab Orders

If you’re a male, do you really need to be asked you if you’re pregnant when you are at a clinic? If your female, do you need a prostate exam? Probably not. There are little nuances to data entry that occur with just about every solution that make it just slightly more tedious to collect information with just a standard tab order. We’re going to remedy that with using a simple layout keystroke trigger.

Dynamic Tab Order for FileMaker 10

In FileMaker there is only one way to navigate the tab order. This can be annoying to an end user who has to tab through a bunch of useless fields to get to the good stuff. The gender question above is a perfect problem to solve using a dynamic tab order in FileMaker 10.

Note: As Mikhail Edoshin points out, this process could be accomplished by attaching a script trigger directly to the field in question. The reason I chose to attach the trigger to the layout is so all tab order changes are centralized in one script. For instance if you had 4 or 5 different modifications to the tab order, you would still only have one script when using the layout trigger. If you were to place a trigger on each individual field you would need 4 or 5 scripts. That was really the main idea of this whole post but I left it out for some stupid reason.

Thanks for the heads up Mikhail.

A better way for our tab order to progress in our example is to just skip fields depending on which gender is choosen. Here is the psuedocode for what I think the tab order should look like:

IF the active field is Gender THEN
  IF Gender = Male THEN go to the prostate question END
  IF Gender = Female THEN go to the pregnancy question END
END

Finding our Key

The TAB, ENTER and RETURN as they are the three keys that filemaker uses to move to the next field.

Note:  You can specify one, two, three of these keys to progress to the 
next field in the Format >> Field/Control >> Behavior...

So using our knowledge from the previous article about keystroke triggers and consulting our ascii tables we know the following will return true if any one of those keys is pressed:

Let(
  keyCode = Code( Get ( TriggerKeystroke ) );

  keyCode = 9 or //Tab Key
  keyCode = 10 or // Enter
  keyCode = 13 // Retun
)

Overriding the tab order

Assuming that my table name is TEST and my field names are GENDER, PROSTATE_EXAM? and PREGNANT, we can write our script.

If[ Let( keyCode = Code( Get ( TriggerKeystroke ) ); keyCode = 9 or keyCode = 10 or keyCode = 13) ]
  If[Get( ActiveFieldTableName ) & "::" & Get( ActiveFieldName ) = getFieldName( TEST::GENDER )]
    If[ TEST::GENDER = "F"]
      Go to Field [ TEST::PREGNANT? ]
      Exit Script [Result: False]
    Else If [ TEST::GENDER = "M" ]
      Go to Field [ TEST::PROSTATE_EXAM? ]
      Exit Script [Result: False]
    End
  End
End

Notice on the second line of the script we combined two get functions to determine the table and name of the field and then compared that to the result of getFieldName( TEST::GENDER ):

Get( ActiveFieldTableName ) & "::" & Get( ActiveFieldName ) = 
getFieldName( TEST::GENDER )

The reason we do this is to make sure that if for some reason you ever decide to change the name of either the table or field the script won’t break.

You’ll also noticed that I use the step Exit Script [Result: False] after I navigate to each field. The purpose of this step is to reject the TAB, ENTER or RETURN key press. If we were to leave the Exit Script step out, FileMaker would process the keystroke and therefore go the the next field in the tab order after the field I just told it to go to. This would drive everyone crazy!

Once I’ve played with this technique a little more I’m sure it will prove quite useful. It definitely makes data entry a little more user friendly.

January 7, 2009 | Filed Under FileMaker 10 Tips, S.F.R, Syndicated | Leave a Comment 

Isolate Any Record, Anywhere, Anytime and Any Context With One Script

There are some things that are easier to explain through examples.  Putting the Set Field By Name script step to work is definitely one of them.  It’s not a complicated process, but seeing the new Indirection capabilities that Filemaker 10 has to offer makes a much stronger impression than trying to talk through it.  Also, all my introductory quips for “isolating” and “isolation” were turning out quite depressing.  So, here is how we used to find a specific record, and how we can do it now.

The Old Way

For this article I’m going to assume you are using two methods that are a part of our development process.  The first is multiple parameter passing (feel free to check this out if you’ve never run into it before) and the second is Safe Find.  Safe Find is a script we use to safely run a find and capture any errors that occur and return them without throwing an error dialog.  Here is a copy of the actual script:

Performing a Safe Find

So, now on to the concept of Indirection.  Here is how we used to find customers using our standard naming conventions, parameter passing and error handling:

Finding a Customer

Now, here is how we find orders:

Finding an Order

And now for something completely different.  Here is how we find vendors:

Finding a Vendor

By now you’re probably seeing a trend in our scripting that has driven me nuts since day one.  In Filemaker 9, we had to maintain a separate set of scripts for each context that we wanted to operate in.  If we want to find a Vendor, that’s one script.  Find an Order?  That’s a second script.  Customer?  A Third script.

The New Way

Here is how we can find a Customer, Order or Vendor now:

One is the Lonliest Number

This little beauty does something that makes us here at Six Fried Rice absolutely giddy.  I can call it from any layout, pass it an ID and it will isolate the record with the corresponding ID without any further hassle!  It is completely context independent.  Essentially we now have a single script to replace every find by ID script we have ever created.  A huge part of development model revolves around using structures, systems and conventions to centralize and simplify the development process.  This particular script is a perfect example of a very simple application of a very useful feature to cut out a huge amount of redundancy in a systems.

Note: This script does require that a few things are in place before hand.  For one, you’ll notice that we use the Set Field By Name to set “ID” to the parameter that is passed in.  This only works if you strictly follow the naming convention that dictates having a primary key field called “ID” in every table.  I’ll be posting an article going much deeper into our own conventions, but for now remember that to use a script like this, you must have consistent naming conventions throughout your system or the script will not execute correctly.

January 6, 2009 | Filed Under FileMaker 10 Tips, FileMaker Basics, S.F.R, Syndicated | Leave a Comment 

Script Triggers: Using the Keystroke Trigger

Adding Script Triggers has opened up a world of possibilities for FileMaker developers. Each different type of script triggers has it’s own nuances and pitfalls to be aware of. In fact, with script triggers, FileMaker, in some ways, begins to expose the complexity of more advanced programming environments. Nothing illustrates this more than the OnLayoutKeystroke and OnObjectKeystroke triggers. In this article, we’ll look closely at keystroke triggers: the most powerful and complex of the triggers in FileMaker 10.

Download: We have developed a free (public domain) tool to help explore and discover keystroke modifiers and codes. You can download the Keystroke Companion and use it as you see fit.

Overview

In this article we’ll dig deep into the OnObjectKeystroke trigger. With this trigger, whenever someone presses any key while an object has keyboard focus, the script you specify will run. It’s pretty straight forward in concept.

Note: Keyboard Focus, to FileMaker, means different things to different objects. The most typical case is a field. When you click into a field, it has focus. If that field object has an OnObjectKeystroke trigger, it will fire with each key. You can also attach keystroke triggers to buttons and tab controls. These have focus when you add them to the tab order, then tab to them, so that the button or one of the tabs has the thick black border. Portals and Web Viewers never have keyboard focus (but a field on a portal can, of course).

This trigger becomes extremely useful when you are trying to give your user immediate feedback about invalid input. For instance, say you have a phone number and you don’t want any letter’s in it or perhaps you only want someone to be able to enter a maximum of one character in the middle initial field. The best way to explain is to show it in action, so I am going to create a Keystroke Trigger that will prevent the user from entering more then 5, numerical characters into a field called ZIP_CODE. You can do this sort of thing with a field validation, but when you do, the user has to make a mistake before you tell them what went wrong. With a keystroke trigger, you can process the input as it is typed, giving immediate feedback. This is something that simply wasn’t possible before FileMaker 10.

Figuring out the keys

To limit our ZIP_CODE field to 5 numerical characters we first have to determine which keystrokes the user has typed. FileMaker 10 has a new function Get ( TriggerKeystroke ) which gives the value of the keystroke that triggered the script. So if I was typing in my ZIP_CODE field and I hit the number 1 it would return 1 when I call Get ( TriggerKeystroke ). Simple enough. But we don’t really want to have to write a list of all the values we are going to allow so filemaker gave us another new function Code () which give us the Unicode Value for a given character. For Instance Code ("1") yields the number 97. This doesn’t seem like a whole heck of lot of use to us but the Unicode Values are organized in such a way that it’s much easier to isolate the values you need. There are many lists out there for finding the unicode value of a character. This is the one I used.

Note: Unicode is a vast character set. Luckily, for the most basic roman alphabet letters, numbers, and punctuation, the codes match the simpler and more familiar ASCII standard. None the less, if you need to deal with Japanese, Arabic, or Desert characters (for instance) you’ll have to deal with more complexity.

Allowing Certain Keys

Here are two very useful functions to show how using the unicode value makes life much easier:

KeystrokeIsLetter:

Let(
keyCode = Code ( Get ( TriggerKeystroke ) );

( keyCode >= 97 and keyCode <= 122 ) or //all Lowercase letters
( keyCode >= 65 and keyCode <= 90 )      //all Uppercase letters

)

The above function results in true whenever a letter (lower or uppercase) is typed. If you wanted to check for all these letters you would have to written a case statement with 56 different test cases! That would not have been fun. Also you’ll notice that a lowercase “a” (97) is different then an upper case “A” (65). This is actually really great because when you want to get more a little tricker with your triggers you can isolate upper or lower case letters as needed. We’re not really interested in letters here so lets look at the comparable custom function for numbers:

KeystrokeIsNumber:

Let(
      keyCode = Code( Get ( TriggerKeystroke ) );

      keyCode >= 48 and keyCode <= 57
)

Using this function we can make sure that they hit a number key, so we’re ready to get started.

Putting It All Together

Now that I can isolate the number keys it’s time to write on our script. The script that runs should return false if the incorrect keys are pressed. This causes FileMaker to cancel the keystroke, meaning it never appears in the underlying field. You’ve blocked it in your trigger script.

Our script should be very straight forward using the custom functions above. Here is the code of our script:

If[not KeystrokeIsNumber ]
  Beep
  Exit Script[Result: False]
End

Now that we disallow any non-numerical keys we need to make sure that we don’t allow entry of more than five characters, so lets add that logic:

If[not KeystrokeIsNumber or Length ( TEST::ZIP_CODE ) >= 5]
  Beep
  Exit Script[Result: False]
End Id

If you test this field, you’ll quickly discover a problem (and a pretty serious problem at that). Since we only allow number key presses, we’ve inadvertently blocked several useful keys: Arrow keys, the Delete key, the Tab key, and even the Enter key. When it comes time to edit a value you’ve already entered, nothing works. This is one of the problems that you will run into when you use these filters. You pretty much always want to allow the navigation keys to go through. Don’t worry though, here is another custom function for finding editing keys:

KeystrokeIsEditing:

Let(
   keyCode = Code( Get ( TriggerKeystroke ) );

   keyCode = 8 or 
   keyCode = 9 or 
   keyCode = 10 or 
   keyCode = 13 or 
   ( keyCode >= 27 and keyCode <= 32 ) or
   keyCode = 127 
)

Adding the editing keys to the code yields:

If[not KeystrokeIsEditing]
  If[not KeystrokeIsNumber or Length ( TEST::ZIP_CODE ) >= 5]
    Beep
    Exit Script[Result: False]
  End   
End

Now we’re are really close. There is just one final issue that we need to address. This one is even more difficult to spot then the arrow key problem. Let’s say someone were to select the number in ZIP_CODE field and then start typing. What would happen? Well if there were 5 characters already in the field, they just get a beep. But since they have text selected, and they are replacing it with a new entry, the keystrokes really should be allowed. That’s a problem. To fix it we can use another handy function that FileMaker has graciously provided: Get (ActiveSelectionSize). This function returns the length of the selection in the active field. In other words, if you select the word “Cardinals” in a field containing “The Cardinals are…imperfect”, then Get (ActiveSelectionSize) would return 9.

Final Answer

Our 100% complete script now looks like so:

If[not KeystrokeIsEditing]
  If[not KeystrokeIsNumber or (Length ( TEST::ZIP_CODE ) >= 5 and Get (ActiveSelectionSize) = 0)]
    Beep
    Exit Script[Result: False]
  End   
End

Now that we have our script we simply attach it to our field and we are done. To do this just right click on the ZIP_CODE field object and select Set Script Triggers... from the drop down menu. A window pops up like the one shown below. Now select the OnObjectKeystroke event and finally select your script. I would make sure that only browse mode is checked because you have many more keys that you would want to allow in find mode.

FileMaker-10-Script-Trigger

Phew, we made it through. Script Triggers are definitely a little bit of work to setup but I think they provide a wide range of new and interesting functionality. Happy Script Triggering everyone.

January 6, 2009 | Filed Under S.F.R, Syndicated | Leave a Comment 

Set Field by Name Exposed

In our article about FileMaker 10, we promised some tips on using the new Set Field by Name script step, which gives FileMaker the power of indirection. This is a complicated concept, and we’ll be posting some real world examples along the way, as well. But first, we’ll give you a full explanation of the new script step and function that make indirection possible.

Set Field by Name

The core of the new feature is the Set Field by Name script step. It works almost just like the Set Field script you know and love. But with Set Field you hard code the field you want to modify. Every time your script runs, the same field is “set.”

With Set Field by Name you get to use a calculation to determine not only the value you put in the field, but also which field gets set. An example will help. Imagine you have a database of the T-shirts you sell. Each T-shirt has 5 different prices, depending on the quantity someone orders. Your SHIRTS table, thus, has these five fields:

Now imagine you have a scripted process that updates one of these price fields based on a parameter. For instance, you might call the script and pass a parameter of 3 to update the third price value. Such a script might look like this:

If [ #P("which") = 1 ] Set Field [ My Table::Price 1; #P("newPrice") ] Else If [ #P("which") = 2 ] Set Field [ My Table::Price 2; #P("newPrice") ] Else If [ #P("which") = 3 ] Set Field [ My Table::Price 3; #P("newPrice") ] Else If [ #P("which") = 4 ] Set Field [ My Table::Price 4; #P("newPrice") ] Else If [ #P("which") = 5 ] Set Field [ My Table::Price 5; #P("newPrice") ] End If

>Note: The above script uses our custom functions for passing multiple script parameters. You can of course use any method you want.

Using Set Field by Name, you can make this script a lot simpler:

Set Field by Name [ "My Table::Price " & #P("which"); #P(newPrice) ]

This version of the script gets the same job done with a lot less work. Even better, it is automatically expandable. If you add a sixth price field, you can call this script with a 6 and it will set the new price field automagically.

>Note: That probably bears repeating because it really illustrates the power of indirection. This script, written today, can seamlessly work with fields that don’t even exist yet. With indirection, the direct coupling of scripts and fields is broken, enabling previously impossible powers.

The key is that it uses a calculation to determine the field name being set:

“My Table::Price ” & #P(”which”)

It is simple really. Just a text formula that results in the name of the field we want, complete with its table occurrence name.

This ability to target a field and value works in both Browse mode and Find mode, so it can be used to manipulate data generically, and to do generic find scripting.

GetFieldName

The script above has one substantial weakness. If, for some reason, you ever rename your Price fields, your script will simply break. Normally in FileMaker, of course, renaming things is no problem. FileMaker just fixes things up for you (because it references fields by an internal ID rather than by name). But it has no way to know how to rewrite your calculation to accommodate a new name.

Many people (myself included) would consider this an unacceptable tradeoff. First and foremost, our code should not break. Luckily, the good folks at FMI have anticipated this problem, and they’ve come up with a clever solution in the GetFieldName function. When you pass a field to this function (a real field reference, not the name of a field in quotes) it will give you back its name. Assuming your Price fields will always use the same naming convention, you can make your script safer like this:

Set Field by Name [ Left(GetFieldName(My Table::Price 1), Length(GetFieldName(My Table::Price 1) - 1) & #P("which"); #P(newPrice) ]

This version uses the Price 1 field as an example of the appropriate field name. It removes the 1 from the end of the field name, then inserts the appropriate number. Now things will keep working, even if you decide to rename the fields thusly:

Parameters

The GetFieldName function isn’t only useful when hacking up field names like the example above. You can use its results directly as well. For instance, you might write a script that performs some text manipulation you often need. This script can manipulate any field using Set Field by Name:

Set Field by Name [ Get(ScriptParameter); /** my complicated calculation **/ ]

If you pass the name of a field as a parameter, this script will set the field you specify. The complicated calculation can even use the GetField function to reference the field in question. A simple example might be:

Set Field by Name [ Get(ScriptParameter); Upper(GetField(Get(ScriptParameter))) ]

This script converts the contents of a field to upper case, and you can pass any editable field’s name as a parameter, making it totally generic. (Of course the script could be much more complex than this sample, incorporating multiple calls to Set Field by Name, conditionals, looping, and so forth.)

To keep your code safe, don’t call it like this:

Perform Script [ My Script; "My Table::Some Field" ]

The script parameter in that example is fragile because it will break if the field name (or table occurrence name) changes. Instead, call it like this:

Perform Script [ My Script ; GetFieldName(My Table::Some Field) ]

This version is impervious to name changes because GetFieldName will always return the right field name, even if you change it later. You still get all the flexibility though — you can still pass any field you want to the script to operate on it.

Calculation Fields

In the comments to our overview article on FileMaker 10, one of our readers, Michel Vincent, asked us about using indirection inside self-referencing calculated fields. This is even more mind bending, and I don’t have a good example (I hadn’t even thought of it before), but the core capability is there. For instance, suppose you have a calculation field called “Mind Bender” with this formula:

GetFieldName ( Self )

If you look at this field in Browse mode, it will contain its own name. If you rename the field, the content will update accordingly. You could combine this with various as-yet-unimagined calculations to produce powerful results. The reader envisioned a system of field naming conventions to help manage XML formatting of data, as an example.

>Note: In fact, I think naming conventions combined with these indirection features will prove to be a powerful combination. In our next article on indirection, you’ll see how we use our primary key naming conventions to streamline some common script code we use ever day.

Wrapping Up

Indirection — the power to determine which fields are modified when the script runs rather than when it is written — is a feature of many very advanced programming environments. In fact, in a more evolved form, it is what makes object oriented programming so flexible.

It has been possible for years to get some semblance of indirection out of FileMaker using complicated, slow, and fragile layout-based scripting techniques. But now, these features are fully supported, reliable, and fast. We are excited to see what our readers come up with.

January 5, 2009 | Filed Under S.F.R, Syndicated | Leave a Comment 

← Previous Page — Next Page →