the beezwax buzz : the beezwax buzz | beezwax.net

Re-Thinking is the new Rebuilding

2013-06-18T11:11:42Z

Republished from the original InspectorPro Wiki article.

Sometimes you just need to start over and to take a fresh look at how you are doing things. Many of you know that I usually like to start things anew, but its also understandable that this is impractical at times.

However, with the release of FileMaker 12, I was inspired to dive deep into how InspectorPro 4 (IP4) was architected and change my way of thinking. It isn't about REBUILDING per se, but rather this is about RE-THINKING your solutions as you move to FM 12.

At its core, InspectorPro is itself a FileMaker database. The previous version, Inspector Pro 3 (IP3) was originally architected as FileMaker Pro 7 file, and continuously remodeled as we moved all the way through FileMaker Pro 11. InspectorPro 4 on the other hand was built as a FileMaker 12 solution.

I wanted to tell you more about the approach I used. Hopefully it will give you some ideas as you start to re-think, and ultimately rebuild, solutions in FileMaker 12.

Re-thinking InspectorPro 4 in FileMaker 12

In 're-thinking' IP4, with FileMaker 12, I was able to do a number of things. Primarily I was able to dramatically improve performance over IP3. There were a number of contributing factors that led to this improved performance. But one of the core changes was the very extensive use of the new ExecuteSQL function in FileMaker 12.

Here is a side by side census of some of the changes from IP3 to IP4:

FileMaker 12's Database Design Report (DDR) outputs more XML metadata than previous versions. IP4 processes this using 13 more XSL Files, and 8 more basetables than IP3.

The big architecture change in IP4 is in the number of Table Occurrences (TOs) that we're able to do without: 491 fewer.

Removing those TOs ended up removing 510 relationships between them as well. We also managed to make creative use of repeating calculations and drop the number of fields, scripts and calculations significantly.

Here is what the Relationship graph looked like in the older product, IP3 (zoomed out to 25%):

One thing that really jumps out is the DEPENDENCY table. It's the Grand Central Station of the interconnections between entities.

The big 'Ah-Ha!' moment during development was when we realized the bottlenecks in the DEPENDENCY table. It had the largest number of records imported, and had both many text and number fields that were indexed.

Removing almost all the indexed text fields gave us a dramatic boost in performance. So, for example, if a field depends on a value list for validating a field, then a record exists in the DEPENDENCY table to represent this.

With IP3 we stored the DDR IDs of these items, along with the names of the items ( all were indexed ). Similarly if a field could be referenced in a relationship, value list, script step, calculation, or simply on a layout. In each of these cases a record will exist in the DEPENDENCY table to represent this.

So when looking into the dependency table it just means different things from the perspective from which you look.

Trimming the Graph with ExecuteSQL

Once we got rid of so many indexed text fields in IP4, how are we able to show the names of these items? Because we have the DDR ID of the item, we simply relied on ExecuteSQL ( 2,858 uses of it to be exact ) to pull through the name of an item when we need it. Here is an example of pulling the name of an account:

ExecuteSQL ( "SELECT name FROM account WHERE id = " & ACCOUNT_ID & " AND database = 'Contact'" ; "|" ; "¶" )

Here is what the graph looks like in IP4, also zoomed out at 25%:

The end result, as mentioned above, is many fewer table occurrences and relationships. Another big benefit is that this database has fewer elements to track and navigate around. There is simply less code to maintain.

The trade off with this approach, is that it relies on SQL statements. Using this approach, you need make sure these statements are not going to break if you change the names of your fields or tables. But there are great solutions to these challenges. You can use GetFieldName in a Custom Function, and by doing so you get the added benefit of knowing that this field is referenced by the ExecuteSQL function.

You also need to be comfortable with SQL. Don't worry…there are already some great resources available inside the FileMaker community for learning more about SQL. Here's a good summary of FileMaker 12 Execute SQL resources.

When FileMaker 7 was introduced there was so much new in that release that I often thought that 'Rebuilding' was the way to go. But after talking to many people over the years about their solution I have realized that sometimes that is an impossible and overwhelming task. Indeed, it is. So now the way I look at it is instead of 'Rebuilding' I like to think of it as 'Re-Thinking'. You can start over and look at what you had before ... move it into place, rework it slightly, or rebuild it using new techniques that you have learned.

From Worst To Best

One of my favorite sayings is: &nquot;The worst developer is me, 2 years ago&nquot;. Hopefully your skills improve over time ( attending conferences, user groups, mailing lists, forums, etc… ). FileMaker gains new features, like ExecuteSQL, where simply a shift in adopting a new feature can lead to dramatic changes. Plus sometimes it's essential to do some spring cleaning and lighten things up.

With FileMaker 12 there are so many ways you can 'Re-Think' your databases. There's now support for External Container Fields, the new Layout Object Design Surface, many enhancements in the new FileMaker Server 12, and a new version of FileMaker Go ( free ) where you can build mobile solutions.

I invite you to 'Re-Think' your solutions and hopefully free yourself from database baggage you don't need to keep carrying along. The result may be less code, that's easier to manager, and better solutions, that are easier and faster to debug. And hopefully you'll have more time to enjoy with family and friends.

Thanks!

— Vince

Vince Menanno is the creator of InspectorPro and Director of FileMaker Development at Beezwax.

Button Up 2: Building State-Aware Buttons

2013-05-27T01:04:26Z

Our previous post highlighted the new capabilities of the venerable FileMaker button. In FileMaker 12's Design Surface, the once-humble button gains newfound expressive power, enabling it to respond fluidly to user hovers, taps, and clicks via what we termed interactive formatting. The catch: interactive buttons — whether in FileMaker 12, or on any other platform — are prone to semantic misinformation, in which interactive formatting unintentionally misleads and confuses users.

In this post, we'll learn two lightweight techniques that will give full control of button appearance and behavior. With these two new tools in hand, we'll be able to fully — and easily — meet the semantic challenge, and create better, more intuitive solutions.

Before getting started, we invite you to download the companion demo file. This file provides a (sort of) real world demonstration of the challenge of semantic misinformation, and illustrates the solutions we'll present in this post.

The Challenge: In the demo file, elements of a bottom button set independently change their availability as business rules act on live data. The first tab demonstrates how standard buttons offer no clues to their availability, forcing users to rely on mere trial and error. Here, a user learns that William Shatner, while spiffily attired, lacks the right stuff to provide secret decoder rings.

Conditional Love

Whenever we want to add state-aware behavior to interactively formatted buttons, the tool we'll reach for is conditional formatting. FileMaker developers have long trusted this tool to provide real time visual feedback informing users of the state (condition) of data. Now, with the advent of FileMaker 12's Design Surface, conditional formatting becomes the perfect complement to interactive formatting. Used in concert, these two formatting tools will let us create interactive, state-aware buttons that remain semantically meaningful as data changes.

Let's begin with a basic approach, a technique we'll call a conditional overlay:

With a button selected, let's open Conditional Formatting and add a rule that will evaluate to True when we want our button to be disabled. For a "delete" button, our rule might evaluate to true when the current record's status is anything other than "draft", while for an "OK" button in a modal dialog layout, our rule might evaluate to true until all required fields have valid values. Since conditional formatting rules evaluate from top to bottom; we'll place our state-aware rule last, to ensure button-disabling rules always take precedence.
Assign a solid conditional fill color (and optionally text color) that will communicate a state of unavailability to users when our button is closed for business.
Last, add this same conditional check to the button's code, so that the script linked to this button will also silently exit when our conditions are met. (In production, this test should be abstracted so that script and formatting rely on a single test. This might be an unstored calculated field, a script-triggered global variable, or perhaps best of all, a custom function, as we use in our companion demo file).

Tools of the Trade: Applying a conditional fill is straightforward, particularly if we first set up a lightweight custom function to do any heavy lifting required, say, by one Bullwinkle J. Moose.

To Tell the Truth

Here's what we get for our efforts: When our disabling conditional rule evaluates to False (when our button is active) conditional formatting will have no effect, and our button will be unchanged. But now, when our conditional rule evaluates to True (when our button is inactive), conditional formatting will spring into effect, and our button will assume the "inactive" appearance defined in our conditional rule.

Having our button be visibly inactive in normal state is a good start. But here's the crucial bit: Our button's interactive behavior will also fundamentally change. When it's in effect, conditional formatting will effectively "turn off" our button's interactive formatting, preventing it from visually responding to user actions. Users can hover, click, tap, or even tickle our buttons to their hearts' content: As long as our disabling condition evaluates to True, our noble button will now stand as rigidly still as a highly trained royal guard. Minus the funny hat.

This gives us the ability to toggle at will between visually dynamic, active buttons and static, inactive objects. When one of our buttons is open for business, its interactive formatting will clearly advertise this to our users; when it's closed, our conditional override will now clearly signal its inactive state to users. We've met our semantic button challenge. The enhancements we learn in the rest of this post will be further feathers in our buttons' already finely festooned fedoras. Huzzah!

Success! Thanks to the Conditional Overlay, users immediately know that Fearless Leader can in fact receive evil-grams or supply us with a coveted decoder ring, but that other functions are off limits to this nefarious villain.

The Mighty Cascade

Our first technique works because conditional formatting rules supersede interactive formatting. This is what makes it possible for us to "turn off" our button's interactive formatting. Let's take a quick step back to see how and why this happens:

Design Surface is built on Cascading Style Sheet (CSS) technology. The heart of CSS is the cascade, an elegant and compact specification determining how object formatting (styles) will flow predictably from abstract, generalized rules to the most detailed object- and state-specific level. CSS is designed to anticipate — and in fact encourage — conflict at different levels of the cascade. This architecture allows us to apply general styles with a broad brush, and then hone in on specific objects as desired. CSS adjudicates conflicts using a simple test: the rule applied at the most specific level of the cascade always wins.

CSS's ability to assemble formatting rules applied at different levels and economically resolve conflicts between these rules is the basis of its elegance and power. Whether on the web in general, or in Design Surface specifically, best practice is to apply a lean set of "inexpensive" formatting rules at the most generalized level possible, then override these general settings on specific objects and states (where rules are more "expensive") only as needed.

Each specific implementation of a cascade has a specific hierarchy that determines its behavior. The Design Surface cascade looks like this:

Map of the Territory: The Design Surface cascade gives us an essential master view of how objects will behave and interact throughout our solutions.

Through the Looking Glass

With that under our belts, let's try a quick pop quiz:

"FileMaker 12 conditional formatting can now assign transparent fills. What will happen when a conditional rule that 'fills' a button with a completely transparent color (RBGa 100%,100%,100%,0) evaluates to True?"

(a) The button's appearance won't change. The transparent conditional fill will allow upstream fill properties to cascade through unaffected.

(b) The button will become transparent. The transparent conditional fill will effectively "erase" upstream fill properties, completely removing them.

(c) There's a "pop" who now?

Scanning the virtual room, pretty much everyone chose (a) — except that kid in the corner who keeps mumbling to himself in proto Klingon. No surprise, really. Common sense tells us that a transparent layer will simply allow whatever's behind it flow through, like laying clear glass over another object. Group hug, high fives all around, and… ruh roh… the correct answer is (b).

Why? It turns out that our young Trekkie simply applied the foundation principle of the cascade: In the cascade, when rules governing the same property conflict with each other, the more specific rule always wins outright. So if we conditionally apply a transparent fill, its CSS fill attributes (specifically, its background-image and background-color properties) will completely replace all upstream values, whether they be solid fills, gradients, or images. As a result, a transparent conditional fill will indeed act as an "eraser", leaving an object truly transparent.

Less is More

The ability of conditionally-applied formatting to completely override upstream styles opens a door to our second technique, the "conditional clear". Here, we apply conditional rules just as before, this time assigning transparent fills when our conditional tests evaluate to True.

What does this give us? For starters, conditionally cleared objects can completely disappear from our interfaces on demand, and this is very useful indeed. In practice, it's often preferable to completely remove these objects when they're not available to users. Some examples: Portal row delete buttons; Search field clear buttons; Action buttons not available to users of certain privilege sets. In cases like these, less is often more, both aesthetically and semantically.

The conditional clear also opens further doors to richer design possibilities. For example, we can use it to effectively "scratch off" a button to reveal objects hidden beneath it. As another example, we can apply a partially transparent conditional fill. Interestingly, unlike in our earlier example, this semi-transparent fill will visually interact with the objects behind it (since they're separate objects and not part of the same object cascade).

A final bonus: Conditional clears are more maintainable than their conditional overlay cousins. If we change a layout's theme or copy buttons across layouts, objects using conditional fills may need to be updated one by one. In contrast, the conditional clear by definition is at home in any theme, be it dark, light, or filled with gradients or rich images.

I Can See Clearly Now: The conditional clear allows buttons simply to vanish when inactive. Our users now confidently send the ever-hip Austin Powers their groovy-grams, never missing the ability to send him an email or evil-gram, since these buttons are completely hidden.

One More Thing

The conditional overlay and conditional clear provide the needed tools to successfully meet the semantic challenge posed in our previous Button. Up. blog post. We can now readily create and maintain buttons that communicate clearly and transparently, and our solutions and apps will be the better for it.

But the best is yet to come. In our next series post, we'll step things up another notch and take these techniques beyond the button, creating complex and expressive native design patterns like state-aware toggle switches, progress bars, and status indicators.

Stay tuned…

— Brian

Follow Brian on Twitter

Summarize how often databases are opened in FileMaker Server

2013-05-30T22:22:40Z

We needed to quickly know how often various databases were being opened on a client's server.

If you have Access logging enabled and are using a Mac OS based system, this shell snippet will give you a nice summary of how often each database has been opened:

 awk -F\" '{ if($3 == " opening database ") print $4;}' '/Library/FileMaker Server/Logs/Access.log' | sort | uniq -c

Unfortunately, it won't show you a count for databases that were never opened within the scope of the current access log.

Simon.

Fail2Ban with FileMaker Server

2013-05-06T14:49:08Z

I needed to configure an FileMaker Server (FMS) that would be an isolated system and accessible via the Internet.

Besides other precautions, like locking it down on the firewall as much as possible, changing the default SSH port, etc., I wanted to give the system some ability to defend itself against malicious access. FMS will prevent this to some extent, largely due to how the FMS Admin Console and the FileMaker client are designed.

But in order to get a bit more protection, I wanted to install Fail2Ban, an open source package that installs easily on Mac OSX, and scans your log files looking for suspicious activity. I couldn't find any filter files online for use with FMS however, so I'd have to create my own. The main task to do this is to create the needed regular expressions that will trigger an alarm. This is what I have so far in my filters.d/filemaker-client file:

# Fail2Ban configuration file
#
# Author: Simon Brown
#
# $Revision: 1 $
#

[INCLUDES]

# Read common prefixes. If any customizations available -- read them from
# common.local
before = common.conf


[Definition]

_daemon = fmserverd

# Option:  failregex
# Notes.:  regex to match the password failures messages in the logfile. The
#          host must be matched by a group named "host". The tag "<HOST>" can
#          be used for standard IP/hostname matching and is only an alias for
#          (?:::f{4,6}:)?(?P<host>[\w\-.^_]+)
# Values:  TEXT
#

# The filter for Administrator failures may not be useful,
# since in testing this always reported null as the IP when
# using the .jnlp based admin tool.   

failregex = .*\[<HOST>(:0)?\]\" authentication failed on database.*
            .*Administrator authentication failed: .*\[<HOST>\].*
# Option:  ignoreregex
# Notes.:  regex to ignore. If this regex matches, the line is ignored.
# Values:  TEXT
#
ignoreregex =

This was based on what I saw when generating password failures and also the document at http://help.filemaker.com/app/answers/detail/a_id/7275. The administrator authentication, it turns out, may not be that useful, since oddly the FMS Admin Console failures only listed the IP address when using the command line tool fmsadmin (!). Also, I haven't verified yet whether there are any useful messages generated in the other log files. Because of Fail2Ban's design these would require separate filter files anyway.

With the filter file done, I now needed to configure the jail.conf file to use my new filter. Although I used MacPorts to install Fail2Ban, I preferred the config file provided on our Ubuntu Linux systems, so I'll be relying default actions configured in jail.conf in my jail.conf file (which you will need to create):

[filemaker-client]

enabled = true   
port    = 5003,http,https
filter  = filemaker-client
logpath  = /Library/FileMaker Server/Logs/Event.log
maxretry = 6

I had some additional work to do to get to this to work with rules or even the ones provided with MacPorts. The Fail2Ban source generally assumes you'll be using the iptables firewall for blocking bad addresses, but we need to use either ipfw or pf on Mac OSX. Since I'm using the action "macros" that Ubuntu uses, I can easily change that for all active filters by setting the banaction variable:

banaction = ipfw

The "ipfw" value corresponds to the file at action.d/ipfw. Unexpectedly to me, I needed to edit the localhost value there in order to get the ipfw rules written out correctly, so be sure to set it to your system's IP address instead of the 127.0.0.1 address that's in there by default.

Simon.

Curl HTTP Post from FileMaker

2013-03-13T04:05:22Z

I was trying to test out some HTTP post transactions with the curl command, and things weren't working.

One glitch was due to how the curl command parses its options. All characters, including spaces can be significant, so "-X POST" is not the same as "-XPOST". This difference might be hidden if calling from the shell, which trims extraneous spaces in parameters, but I was calling via the bBox plug-in's curl function, and this calls the command directly from FileMaker.

After dealing with some extraneous double-quotes that were in the data (cutting & pasting values a bit too quickly!) I ended up with a script step that looked something like this:

Set Variable
[
  $r;
  Value:bBox_Curl ("-XPOST"; // use a http post transaction
  "-d {'type':'sample_data','targetModule':'bbox'}"; // data we are sending to http server
  "-H Content-Type: application/json"; // desired result type, which is JSON
  "http://httpbin.org/post") // server handling our request
]

Simon

bBox 0.56 Now Available

2013-03-11T20:16:44Z

I've just posted a maintenance release version 0.56 of bBox, a free utility plug-in for FileMaker.

A few commands have been modified to accept more parameters. The curl command, in particular, now accepts up to 7 parameters for the complex calls required when doing SOAP transactions, for instance.

Error handling has also been modified, with errors now getting written out via syslog. This change is generally transparent, but Python script and execution errors are now consistently written out, and the change to syslog also worked around some buggy behavior seen with the OS function perror printing portions of previous messages to the log.

Python functions are now also using Python version 2.6.7, up from version 2.5.6 used in the previous release.

A Python usage tidbit. Sometimes the needed module might not be available to the version of Python VM that the bBox plug-in uses. A way around this is to append the needed module to your Python path:

# Must explicity add the PyObjC path.
sys.path.append ("/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/PyObjC/")

Sadly, one thing has left us in order to move things forward: PowerPC support. This version requires as a minimum an intel based CPU and Mac OS X 10.6.

Simon

Debugging Apache with OSXS 10.8 Server 2.2

2013-02-26T04:04:23Z

Besides being a bit of a mouthful, debugging Apache configuration issues has gotten slightly more complicated after Apple released the Server 2.2 update for Mac OS X Server. Formerly, you could do a basic configuration check this way:

# apachectl -t

Now however you need to first set an environment variable and specify a path to the configuration file:

# export SERVER_INSTALL_PATH_PREFIX=/Applications/Server.app/Contents/ServerRoot
# apachectl -t -f /Library/Server/Web/Config/apache2/httpd_server_app.conf -D WEBSERVICE_ON

The above assumes you are running as root, where "#" is prompt.

Simon

Code Signing a FileMaker Runtime

2013-02-22T17:13:38Z

I wanted to code sign a runtime to avoid issues with users who had the higher security level set on their 10.8 based systems. Although lowering these permissions or holding down the control key when the app is launched will all help with this issue, code signing seems a reasonable and desirable, and removed the support issue.

There are a few ways to get the needed certificates, but in my case I got them as part of my Mac OS Developer account at Apple. I won't go in to the details right now (possibly I'll update this later to include this), so here I am assuming you've already gone through the needed steps to get the certificates into your keychain.

I had tried this previously using the codesign command, but kept hitting various issues. Recently however I updated to XCode 4.6, so I decided to try again. There is a possibility I could've gotten this to work by creating a dummy XCode project, and adding the needed build phases to copy the files over. But some testing on that front showed that it wasn't straightforward process to get this to happen. The codesign command is however pretty straightforward, but there was one unexpected step. I would get the following error:

object file format unrecognized, invalid, or unsuitable

Turns out that before signing, you must first run the following in Terminal:

export CODESIGN_ALLOCATE="/Applications/Xcode.app/Contents/Developer/usr/bin/codesign_allocate"

Then this is all you need to do to complete the signing process:

 codesign -s "Developer ID Application: Beezwax Datatools, Inc." /Volumes/Data/Users/sibr/_Clients/beezwax/Code\ Sign\ Test/Code\ Sign\ Test/Code\ Sign\ Test.app

Simon

Button. Up.

2012-12-15T00:13:22Z

Consider the humble button.

Since the very first moments of FileMaker cosmos, the button has been an essential element of our platform. Over the years, across major and minor product releases, the unassuming button has remained a trusted constant and a dependable workhorse.

In daily practice, the button is as essential and transparent to our solutions as water to a fish. (As an interesting conceptual challenge, try envisioning a button-free solution). And yet, the button's heroics on our behalf go overlooked and unsung. We lavish design and development attention on fields, portals, tab controls, web viewers - virtually every interface element except the unassuming button.

In this series, we'll offer our old friend some long overdue attention. We'll demonstrate several new techniques available in FileMaker 12 to greatly enhance its appearance, behavior, and meaningfulness. The button's simplicity will help us isolate and demonstrate several modern UI and UX concepts, which we'll then be able to apply not only to buttons, but throughout our interfaces.

It's Alive!

So why does the button get so little attention? Perhaps its utter simplicity infers that it's just too trivial for our consideration. Maybe since we FileMaker developers are data developers, the unassuming button flies beneath our data-driven radar. Or perhaps we carry impressions and practices from the earlier history of our platform, when the button was limited to being a visually passive, static object in a world where users increasingly were expecting real time interaction.

But the times, they've been a-changin'. As we know, the release of FileMaker 12 has brought a quiet revolution to the platform's interface structure and capabilities. The new CSS-driven Design Surface introduced what I'll term "interactive formatting", enabling objects to visually interact with users in real time. This behavior comes for free with every modern CSS-based theme in FileMaker 12, and can, of course, be readily tuned and extended to add further expressive depth and nuance.

No element in the FileMaker universe experienced a more profound upgrade in FileMaker 12 than the button, which was suddenly transformed into a visually active - and interactive - element. Its new ability to give users direct visual feedback as they hovered, tapped, and clicked, gave the button greatly expanded powers of expression. The invigorated button could now communicate information much more directly and succinctly. In response, buttons everywhere rolled over and popped up in celebration. Huzzah!

Of Mice and Semantics

The visual impact of Design Surface has been immediate and extremely positive. FileMaker 12 interfaces feel zippier, more modern, and more intuitive: In a word, users love them.

But there's more happening here than just good looks. FileMaker 12's interactive formatting lets us create interfaces that are more semantically meaningful. When objects - and especially buttons - respond interactively to user actions, they communicate who they are and what they will do for the user if clicked. We used to achieve this with tooltips, static explanatory text ("click this button if you want to do that…"), and changing the cursor from a pointer to a hand. Now, interactive formatting can handle this for us, much more succinctly and far more gracefully.

This has a profound impact: When a FileMaker 12 button changes appearance in direct response to a user's hovers, clicks, or taps, it's telling to the user that it's an active, "living" object ready to perform an action on her behalf. Even more: As it interacts visually with the user, this button offers a tacit agreement: "Click or tap me, and I'll perform the action promised by my label or icon."

As modern UI and UX theory informs us, interfaces that more directly communicate their purpose and function to users are more intuitive, require less training and support, and provide far greater user trust and satisfaction. While the updated button's good looks may be what first catches our eye, as developers and designers, the more subtle goal of honing and enhancing its semantic meaning is fully worthy of our ongoing attention and best efforts.

Clearly State Your Intentions

Out of the box, Design Surface does everything it can to help our buttons communicate their purpose to users clearly and accurately. But of course, there's a catch. (There's always a catch). In many cases, we developers will need to extend FileMaker 12's default button formatting to preserve our buttons' semantic meaningfulness and integrity.

Buttons often need to alter their behavior as our solution's data changes. For example, a button to update a record's status might depend on our data having met certain milestones; A delete button may be available only for draft records, for users of an administrative privilege set, or as users hover over a portal line item; Or a browser-style search field may have a clear button only having meaning when search text is present.

State-dependent buttons like these will at times be active and available for users to click, and at others be disabled and unavailable for their use. Because the rules of when state-dependent buttons will and won't be available are dependent on solution-specific business rules, we can't expect FileMaker - or any platform - to handle this automatically. Instead, it's our challenge to ensure that state-dependent buttons accurately communicate their availability in real time, as live conditions change.

Keeping the Faith

Remember a moment ago, when our buttons's interactive formatting made a tacit agreement with users?

What happens as our state-dependent button becomes functionally inactive? If it continues to respond interactively to hovers, taps, and clicks - as it will do out of the box - our user will likely click it once again, still expecting the promised result. But this time, our button will break its agreement, failing to deliver as advertised.

The result is semantic misinformation. Our state-dependent button's interactive formatting is now misaligned with its functional behavior. To correct this, we might display an alert, ("You can't perform this action now"). The problem is, while this is true, it's no more satisfying than walking into a store displaying a prominent "Open" sign, only to be told, "Welcome. We're closed; please leave." Or perhaps our button will simply exit silently, leaving the user to wonder why the formerly trustworthy button has stopped working without an explanation. Again, this will be unsatisfactory to our user.

Semantic misinformation is jarring, confusing, and degrades trust. At best, users will construct an internal matrix of rules telling them when to ignore the visual cues our buttons are giving, ("Don't click the submit button unless you've entered something in this and that field…"). Even in this best case, daily use and new user training are much more difficult, as users must manually learn and convey rote rules that can't be inferred from the interface. At worst, users will lose trust, potentially avoiding our solutions, or viewing them as unreliable.

Happily, once we've recognized this issue, it's easy to ensure that state-dependent objects always remain semantically meaningful, even as our data changes. And the simple steps we'll take to implement this will improve both our solutions and users' experience and confidence in using them.

Same Bat Time, Same Bat Channel…

Up next: We'll describe - and provide working examples - of two lightweight methods to implement dynamic, state-based formatting: The first, a time-honored conditional formatting standby that's still useful in many situations; The second, a new, nimble FileMaker 12-specific technique that takes full advantage of Design Surface's advanced capabilities. Stay tuned…

Thoughts? Questions? Have a different point of view? Leave a comment and let me know - I look forward to your feedback.

- Brian

Follow Brian on Twitter

Notes on backing up a wiki in Lion

2012-11-21T00:25:41Z

Apple made some major changes under the hood for wikis with Lion (Mac OSX 10.7). If upgrading from a Snow Leopard (10.6) wiki, one issue you’ll need to address is how your backups work. For some related background, see this technote from Apple: Lion Server: Migrating and copying wiki data from Mac OS X Server v10.6

First, verify in the Server application that the Service Data is where you think it is. If relocated from the default at /Library, you’ll still see any earlier versions of the wiki data there, so you could easily be fooled into thinking this was the correct folder. The data is always stored in a Library folder at the root level of a volume however, so there shouldn’t be too many places to look.

Next is to consider what to use for making your backups. Time Machine is certainly viable here, and is aware of how to correctly save the wiki data. But if you haven’t already done so, you should patch your system up to the latest version of Lion (or upgrade to 10.8), as there were issues with bad wiki backups in earlier versions of Lion. In our case, we use CrashPlan for most of our Mac OS backups. This means we had a little extra work to do to get consistent backups.

Backing up wiki attachment data is quite straightforward – just point your backup utility or script to the Library/Server/Wiki folder. The wiki posts are stored in a postgres database however. You could just backup the data in Library/Server/PostgreSQL, but backing up a live database this is almost never recommended, and postgres is no exception.

What I did instead was create a short Python script at /etc/periodic/daily/700.wiki_backup to dump the postgres collaboration database. Here’s the script:

#!/usr/bin/python

# NOTES
#   - should run as the _postgres user.
#   - the backupDir folder will need to be created and chowned to the pgUser user

# 2012-10-24 simon_b: created script

import os, time

# GLOBALS

dbName = "collab"
backupDir = "/Volumes/Data/Library/Server/PostgreSQL/Backup"
backupFile = "wiki_dump.sql"
backupPath = backupDir + "/" + backupFile
pgDumpCmd = "/usr/bin/pg_dump"
pgUser = "_postgres"
wikiDB = "collab"

# EXECUTE

print "backup path:", backupPath
print
print os.system (pgDumpCmd + " -v -U " + pgUser + " " + wikiDB + " -c -f " + backupPath)

This will create a sql dump every day. But in the off-chance the CrashPlan backups don’t work as expected, I’ve created another file at /etc/newsyslog.d/wiki_backup.conf:

/Volumes/Data/Library/Server/PostgreSQL/Backup/wiki_dump.sql   640       3     *    $D0   JB

This will keep up to 4 copies of the sql dump, compressing the older copies.

Server 2.2 Update

If you’ve applied this update, you need to make three changes to the above script. This is because the postgres daemon is no longer listening on port 5423 for connections. Also, the OS version of pg_dump is no longer in sync with the version used by Server.

A stripped down version of the modified script looks like this:

#!/usr/bin/python

import os, time
todayStr = time.strftime("%Y%m%d")

dbName = "collab"
backupDir = "/Volumes/Data/Library/Server/PostgreSQL/Backup"
backupFile = "wiki_dump.sql"
backupPath = backupDir + "/" + backupFile
pgDumpCmd = "/Applications/Server.app/Contents/ServerRoot/usr/bin/pg_dump"
pgSocket = "'/Library/Server/PostgreSQL For Server Services/Socket'"
pgUser = "_postgres"
wikiDB = "collab"

print "backup path:", backupPath
print
print os.system (pgDumpCmd + " -v -h " + pgSocket + " -U " + pgUser + " " + wikiDB + " -c -f " + backupPath)

– Simon

The First Step to Great Solutions

2012-10-29T18:28:21Z

How do you begin designing a new database or application? If you start with an Entity Relationship Diagram (ERD), you're doing it wrong.

It's natural for a technical person to begin categorizing things into entities – a company has contacts, which have addresses, etc. But this process immediately narrows your perspective to a small subset of the issues at hand, blinding you to many more important challenges and opportunities.

FileMaker is particularly suited to people who are overburdened with immediate business needs: "Get us out of this burdensome spreadsheet!" Bringing order to the chaos and easing labor demands is often a relatively quick task in FileMaker. But organization and automation are not the goals of a great consultant – merely the means. Your biggest value as a consultant is rooted in your ability to facilitate the creation of better solutions to their problems, particularly those of which your client is not even aware.

So don't begin mapping your solution in that handy ERD of yours, because you're not mapping the real problems or solutions. It's a sure way to let their existing assumptions and solutions crystalize into what is supposed to be a newer, better system.

The most significant obstacles and victories are found behind the assumptions many people don't think to question. The consultant's job is to help uncover and assess these assumptions, especially assumptions about what they do and how it's measured. This involves questions like "What does this really mean?" and "Why is this important?". Once you begin to understand their domain, your goal is to expand the realm of possibilities in search of better solutions. This is sometimes called divergent thinking. "To have a good idea, you must first have lots of ideas," as two-time Nobel prize winner Linus Pauling put it.* It doesn't need to be a long process of brainstorming, but you need to at least be sure that you're not blindly following the first path that presents itself. Then you can begin honing in on a particular strategy and structure – convergent thinking.

A successfull database or application will therefore help the client better understand their business before the system is even built. This should teach you what is really important about their data before you begin solidifying the system architecure. The system itself can then help them quickly isolate and understand the key learnings provided by their data. In other words, the system will help users prioritize their data and tasks in a way most aligned with their company's objectives. Conventional interfaces can't do this because conventional consultants don't understand how to value what they're organizing. The interfaces are therefore often built with an eye for uniformity and from the perspective of the architecture.

Do your systems simply re-organize spreadsheets or do they bring to life the inner workings of the business? Don't let a good ERD fool you into thinking you know what's going on. If you want to build outstanding solutions, you need to begin by asking the right questions – lots of them. And that's the first step to great solutions; you need to play the fool.

Ignorance is the most important component for helping others to solve any problem in any industry.... You probably don't know much unless you're willing to first play the fool. Ignorance is not such a bad thing if one knows how to use it, and all managers must learn how to do this. You must frequently approach problems with your ignorance; not what you think you know from past experience, because not infrequently, what you think you know is wrong.

- Peter Drucker (source)

* Brown, Tim. Change By Design: How Design Thinking Transforms Organizations and Inspires Innovation. 2009. p. 67.

Sending HTML Email from FileMaker

2012-08-17T15:34:57Z

It comes up time and again: "How can I send a formatted email from FileMaker?" You see, FileMaker natively supports only plain text emails; you're on your own if you want rich text or HTML. But it is possible! Here's a list of options for (nearly) painlessly sending HTML emails from FileMaker.

Apple Mail

In this scenario, FileMaker uses AppleScript to send an HTML email through the Apple Mail client on OS X. Pretty much the only requirement is that the person sending the email has an email account configured in Mail. Of course, this isn't something you can often depend on. It's also exploiting an undocumented behavior, so I would be skeptical of its dependability with future versions of Mail. Still, it's handy for its ease of implementation.

Email_with_Apple_Mail_2013-03-08.zip

EDIT 03/08/2013: Cleaned up test settings; updated ancillary custom functions.

ScriptMaster Plug-In

In this scenario, FileMaker uses an external function from the ScriptMaster plug-in to send an email. ScriptMaster provides an example function, but our demo provides an enhanced version that allows for pretty much every combination of features. You might even use it to send a plain text email with multiple attachments. (FileMaker natively support only a single attachment.) This approach gives you maximum features for relative ease of deployment. Oh, and it's free! The catch is that Java is increasingly going out of fashion these days (speaking as an OS X user), so the plug-in may run into issues on some users' machines.

Email_with_ScriptMaster_2013-03-14.zip

NOTE 3/14/13: The function provided in this example occassionally fails to send the message, encountering an error like "no object DCH for MIME type multipart/mixed." I suspect the solution might be found in threads like this one, but I no longer use ScriptMaster and have thus not pursued any resolution.

EDIT 3/14/13: Removed test addresses.

EDIT 11/06/12: Added required body tag to several tests.

EDIT 03/08/13: Updated test settings.

Web Service

In this scenario, FileMaker posts parameters to a web service that then sends the email for you. All the client needs to do is post the parameters to a web server, which is good for compatibility across various client configurations. The web service can be in any language capable of providing an API accepting GET or POST requests. We've done it using PHP and Ruby.

GET is the easiest post method for FileMaker because all you need to do is load the URL in a web viewer. That means even FileMaker Go can send HTML emails using this approach. But the biggest drawback is that there's an inherent character limit to GET requests. They're also not as commonly supported as POST. POST requests are therefore the preferred method, provided your solution is capable of sending them. In FileMaker, this requires a plug-in. Most any utility plug-in will be able to send POST requests.

You don't need to write your own web service to use this approach. You can also post to free services such as JangoMail. This approach can yield more advanced features like campaign management and analytics. It also supports GET requests, so you can run the demo on your iPhone or iPad if you wish. (The following demo requires a free JangoMail account to function.)

Email_with_Web_Service_2012-11-02.zip

Other Plug-Ins

If you don't mind spending a few dollars, consider purchasing a plug-in built specifically for email handling. It might be worth the features and time saved. I have very little experience with these plug-ins, but I've added a few to our comparison table based on their marketing materials. (My apologies for the formatting.)

EDIT 11/5/12: Also check out this handy plug-in comparison by filemaker-plugins.com.

Method	To, From, Bcc	HTML	Rich Text	Plain Text	Edit in Client	Attachments	Header Support	SSL	iOS-Compatible	IWP-Compatible	Server-Side Compatible	Mac-Compatible	Win-Compatible	Plug-In Dependent	Comments
AppleScript	Yes	Yes	No	Yes	Yes	Yes*	No	Yes	No	No	?	Yes	No	No
ScriptMaster	Yes	Yes	Yes*	Yes	No	Yes	Full*	No	No	No	Yes	Yes	Yes	Yes	Java-dependent
Web Service	Yes	Yes	Yes*	Yes	No	Yes	Full*	Yes	No	No	Yes	Yes	Yes	No+	Ruby/PHP on server
360Works Email	Yes	Yes	?	Yes	No	Yes	Full	?	?	Yes	Yes	Yes	Yes	Yes
Mailit 5	Yes	Yes	Yes	Yes	Yes	Yes	Full	?	?	?	?	Yes	Yes	Yes
SMTPit	Yes	Yes	?	Yes	Custom window	Yes	Partial	Yes	?	?	Yes	Yes	Yes	Yes

* Feature possible, but not supported in our demo files

+ You can post to a web server using JavaScript. (See Brian's fmGo post on using the web viewer as a JavaScript engine.) Most of our implementations, however, use an existing plug-in or something like the free BaseElements plug-in.

Caveats

Mail clients will have varying support for HTML and CSS features. See Campaign Monitor's handy reference for more detail on what many of the popular clients support.

-Donovan

Just a Slice, Please…

2012-08-13T21:02:26Z

Here’s an interesting FileMaker challenge:

How can you dynamically slice off a set of values from a repeating field in a related table, the way you’d grab a column of values in a spreadsheet?

It’s easy enough to do this for a single repeating field in the current record, but how do you do this with a repeating field in a different table, operating not just on a single record, but an entire record set?

While the solution to this challenge isn’t immediately obvious, this post will explain how to handle this challenge quickly and elegantly with FileMaker 12. Follow along by downloading a copy of the completed demo file. Here’s a quick peek at the finished solution:

Tools of the Trade

Repeating fields are one of FileMaker’s great secret weapons. When properly used, they can help denormalize and condense related data into compact and flexible structures that can save space and boost performance. In this example, we’ll use not only FileMaker’s native repeating field functions, but also Will Baker’s definitive set of repeating field custom functions.

Flattening Repeating Data

As the screenshot above suggests, we’ll create a matrix-like structure in the parent table from which we’ll take slices as desired. To prepare for this, the first step will be to create a field in the child table that will translate the child table’s repeating field into a flattened string, where each repetition’s value will be nestled between delimiters, like this:

<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |

We could try this using the native List() function to translate repeating fields into list format, then use Substitute() to replace the list’s carriage returns with delimiters. This will fail, though, because List() omits null repetitions. So if, say repetition 2 is empty, repetitions 3-5 will jump forward a slot, which will defeat our downstream calculations.

Instead, we turn one of Will Baker’s repeating list custom functions, RepListOmitTrailingNulls(), which returns a complete list of repetition values including null entries. This ensures that empty values won’t misalign the flattened structure, but will be properly delimited.

As a bonus, Will’s function dynamically determines the last repetition that’s populated and only returns list values up to that point, so the flattened values will scale perfectly for any number of repetitions, and will consume the minimum resources needed. Here’s how the flattened string looks in our child table:

Peeling Off a Slice

With this in place, back in the parent table, we now assemble a matrix-like structure using List ( child::flattenedValues ). This gives a list that’s structured like this:

<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |

Now we need a way to take a vertical slice. For this, we’ll create a custom function that accepts our list of flattened values and a target repetition number. Our function will step recursively through each value in our list, using the delimiters to locate and then extract the target repetition of the current line. Then, we’ll need it to recursively call itself, repeating this action for each line, and finally returning a list of these extracted values. Here’s our custom function, GetRepValuesFromList():

Let ( 

[ 

count_values = ValueCount ( values ) ;

value_1 = GetValue ( values ; 1 ) ;

values_other = MiddleValues ( values ; 2 ; count_values-1 ) ;


pos_start = Position ( value_1 ; "|" ; 1 ; rep-1 ) + 1 ;

pos_end = Min ( Position ( value_1 ; "|" ; 1 ; rep )  ; Length ( value_1 ) ) ;

chars = pos_end - pos_start ;

rep_value = Middle ( value_1 ; pos_start ; chars )

] ;


Case ( 

  count_values = 0 ; 

    "" ;


  count_values = 1 ;

    rep_value ;


  List (

    rep_value ;

    GetRepValuesFromList ( values_other ; rep )

  )

)

The String’s Not the Thing

Our custom function works perfectly, letting us fluidly slice any vertical set of repetions at will, and returning these values to us as a list. Now all we need to do is to get the returned column’s total. Easy peezy… just replace our list’s carriage returns with semi-colons and pop it into a native Sum() function, right?

Well, no, not so fast. Sum (<slicedValues> ) may produce a perfectly valid-looking expression when viewed in a Data Viewer. But it’s not a valid expression, because a sliced value string like "22;11;21;" is, well, just a string, rather than an actual set of delimited numeric values, as aggregate functions require. Happily, this is quickly resolved by assembling our full expression as a string and then wrapping that string in turn within an Evaluate() function, like so:

Evaluate ( "Sum ( " & values & " ) " )

Evaluate() kindly transforms our string into a valid expression, and our Sum() function now works like a champ. Et vÃ³ila! We’re finished!

Worth Repeating

This is a simple example of how flexible and powerful FileMaker 12 repeating functions can be. Our technique is light and fast, and it can scale flexibly to any number of child repetitions or child records without modifications.

Note also that this technique can be readily extended in several ways. For example, to allow users to search from a parent table for child records containing values in a specific repetition, or to use aggregate functions to further analyze the related repeating data.

Download the demo file … and let us know what you think.

Enjoy!
- Brian

Follow Brian on Twitter

XML Parsing with FileMaker and bBox

2012-08-04T02:17:23Z

In order to more easily work with XML data, I’ve added the libxml2 library to version 0.54 of the bBox plug-in.

Among other things, libxml2 provides support for XPath 1.0 queries, which can be used to easily extract or set values from XML data, so XPath was my first choice. Although XML has lost a bit of its shine as JSON has come into vogue, XML is still integral to many systems, including FileMaker. Also, the XPath routines can be used for similar syntaxes, so for instance, you might find it helpful for scraping data from a HTML page.

The first two functions aren’t very interesting, but they are essential. XPathInitialize is used to intialize the XPath environment (surprise!) and do the initial parsing of the XML source. XPathFinalize releases the memory used for the tokenized XML and the XPath environment. XPathEvaluate is where most of the fun happens, as this is where we pass in our XPath statements and get back our results.

Getting Started

Even with a few script steps and some simple XPath statements we can do some interesting stuff. To start off, we’ll need some XML to experiment with. I’ll use a modified version of the example XML from wikiboooks.org:

<library>
<description>A list of books useful for people first learning how to build web XML web applications.</description>
<book>
  <title>XQuery</title>
  <author>Priscilla Walmsley</author>
  <description lang="spanish">Este libro es un recorrido muy detallado, a travÃ©s de y completo del Lenguaje de Consulta del W3C. Cubre todos los aspectos clave de la lengua.</description>
  <format>Trade press</format>
  <license>Commercial</license>
  <list-price>49.95</list-price>
</book>
<book>
  <title>XQuery Examples</title>
  <author>Chris Wallace</author>
  <author>Dan McCreary</author>
  <description lang="english">This book provides a variety of XQuery example programs and is designed to work with the eXist open-source native XML application server.</description>
  <format>Wiki-books</format>
  <license>Creative Commons Sharealike 3.0 Attribution-Non-commercial</license>
  <list-price>29.95</list-price>
</book>
<book>
  <title>XForms Tutorial and Cookbook</title>
  <author>Dan McCreary</author>
  <description lang="english">This book is an excellent guide for anyone that is just beginning to learn the XForms standard.  The book is focused on providing the reader with simple, but complete examples of how to create XForms web applications.</description>
  <format>Wikibook</format>
  <license>Creative Commons Sharealike 3.0 Attribution-Non-commercial</license>
  <list-price>29.95</list-price>
</book>
<book>
  <title>XRX: XForms, Rest and XQuery</title>
  <author>Dan McCreary</author>
  <description lang="english">This book is an overview of the key architectural and design patterns.</description>
  <format>Wikibook</format>
  <license>Creative Commons Sharealike 3.0 Attribution-Non-commercial</license>
  <list-price>29.95</list-price>
</book>
</library>

Setup

We can consider the XML above as our database that we will be running our queries against. The FileMaker script steps required to do a basic query are pretty simple. Here’s a snippet that does most of what you might need:

Set Variable [ $result; bBox_XPathInitializeFromText (0; DEMO::xml_source)
#
If [ $result = "" ]
   Set Variable [ $result; bBox_XPathEvaluate(DEMO::xpath_expression; DEMO::xpath_node_delimiter)
End If
#
Set Variable [ $ignore; Value:bBox_XPathFinalize ] #

XPath Examples

The XPath statements, in their simplest forms, look identical to unix style file paths. These are what you’d be passing as the path_expression above. Our first example just returns all titles from the above XML:

/library/book/title

A slightly more complex form that returns all titles and their retail prices would be (“//” returns the following node from anywhere in the hierarchy):

//title | //list-price

And here we use one of the XPath functions to get the last book in our list:

/library/book[last()]

Conclusion

XML does have its arcane side, so some capabilities will require some effort to learn, and may be beyond what the current implementation in bBox can handle. Also, XPath functions are fairly strict in their interpretation of XML, so any poorly formed XML is likely to be rejected by the bBox_XPathInitialize call. Use the Console utility or go directly to your system log at /var/log/system.log to check for more detailed information. The xmllint command may also be helpful here.

There is quite a bit more that can be done with XPath, so don’t stop looking here for examples. You can also pair up the XPath functions with the bBox’s curl function to pull in remote content.

Simon

Our First FileMaker 12 Certified Developers

2012-08-03T21:07:51Z

We’re pleased to announce three Beez who earned the title of FileMaker 12 Certified Developer.

Vincenzo Menanno, Director of FileMaker Development
Will Baker, Senior Developer
Brian Schick, Senior Developer

FileMaker 12 Certification is the official credential offered by FileMaker, Inc., certifying a developer as achieving an "essential level of knowledge, experience and skills in developing FileMaker 12 solutions."

Vince, Will and Brian were among the first developers anywhere to receive this designation since FileMaker 12 Certification was announcedÂ in July.

FileMaker Certification At Beezwax

At Beezwax, our FileMaker Certified developers are an important resource in mentoring other developers, representing us in the FileMaker community and establishing best practices in building client solutions.

We look forward to announcing other FileMaker 12 Certified Developers in coming months.

Congrats, Vincenzo, Will and Brian!

FileMaker 12 SQL: Crafting Structural Beauty

2012-07-24T22:41:58Z

It's hard to believe it's been only a few months since SQL was introduced as part of FileMaker 12.

In the short time since, developers have quickly put this new capability to use with style and passion, publishing FileMaker SQL tutorials, demos, and development tools almost too many to number. Some early highlights include:

Greg Lane created an excellent webcast and helpful twitter posts explaining the basics and the finer points of SQL use in FileMaker 12.
Kevin Frank launched an insightful series of articles exploring the nuances and deeper capabilities of the ExecuteSQL function. And Kevin's ExecuteSQL unconference session at FileMaker DevCon was heavily attended, giving evidence of growing community interest.
SeedCode released SQL Explorer, a powerful and free tool for creating well-structured SQL queries directly within FileMaker.
Andrew Duncan created a set of SQL-based custom functions and explained how SQL can retrieve metadata, enabling us to query our tables and fields directly within SQL queries.
Beezwax released InspectorPro 4, rewritten in FileMaker 12 and taking advantage of ExecuteSQL to go from 636 to 135 Table Occurrences.

With all this activity, it's easy to forget that our use of SQL in FileMaker solutions is still in its early stages. We're just getting our toes wet in the vast ocean of SQL. This is a very good thing: The best is still to come.

Getting Our Toes Wet

Looking at our early use of FileMaker SQL, many examples in the wild use an ad hoc, left-to-right structure. Often, these queries are written as linear recipes: Start with fields to be retrieved, place them in a SELECT clause, set the matching table in the FROM clause, then tune the WHERE, and ORDER clauses until the query returns desired results. For example:

Select Title, Duration From Movie Where Duration < 90 Order By Title

This is a fine query: It's simple, direct, and fast. In this kind of basic SQL, a left-to-right, ad hoc approach is natural, and likely the way most of us get started. We can accomplish a great deal with this form of query. Many of us have eliminated scores of TO's from our FileMaker 12 solutions using basic queries just like this.

But this approach doesn't scale well as queries become more complex. For example, let's extend this query to ask which theaters are playing movies between 1 and 2 hours long:

Select Movie::Title, Theater::Theater_Name, Coalesce (Showing::Showtime, '[Call for
Details]') from Movie Join Showing on Showing::Id_Movie = Movie::Id Join Theater on
Theater::Id = Showing::Id_Theater Where Lower (Theater::Region) in ('Santa Barbara',
'San Francisco') And Movie::Duration > 60 and Movie::Duration < 1200 Order by Movie::Title

This is still a simple query, but it's suddenly become very difficult to read. Are the inner joins valid? Do the columns in the select clause agree with the tables and columns in our other clauses? We'd need to pull this query apart to answer these basic questions. It's easy to foresee that as we added more clauses, tables, fields, and conditions, a tipping point would soon be reached where this query would become incomprehensible.

Millau Viaduct, world's tallest bridge. source: http://bestbridge.net/Eu_en/millau-viaduct.html

The Beginning of Craft

Let's step back a bit and reflect on our shared experience as FileMaker developers. For many of us, our initial discovery of FileMaker was exhilarating. We found that we could do amazing things with this malleable and powerful application, and we were overnight heroes! In time we took on increasingly complex projects, running into walls here and there, and parts of our solutions became unwieldy. Sooner, or perhaps (ahem) later, we learned the importance of structure for managing complexity. We adopted naming conventions, learned the importance of structuring code, separated long ad hoc scripts into small, reusable parts, and adopted other best practices. We — and our solutions — are now the better for it.

As we learned to gracefully handle complexity, we became craftspeople.

I'm privileged to know many FileMaker developers for whom crafting elegant scripts, custom functions, data structures and (of course) user interfaces is a pursuit of functional excellence and aesthetic beauty.

I'm confident the same soon will be said about how we approach FileMaker SQL. As our experience with SQL deepens in the coming months and years, we will have more opportunity to apply many of the best practices we use in FileMaker scripts and data structures to craft SQL structures of greater beauty, clarity, and resilience.

Creating Structural Beauty

With this aspiration in mind, let's take another look at this same query, but this time set in a different structure:

SELECT
      movie.title
   ,  theater.theater_name
   ,  COALESCE ( showing.showtime, '[call for details]' )

FROM
      movie
      JOIN showing
          ON showing.id_movie = movie.id
      JOIN theater
          ON theater.id = showing.id_theater

WHERE
      LOWER ( theater.region ) IN ( 'santa barbara' , 'san francisco' )
      AND movie.duration > 60 
      AND movie.duration < 120

ORDER BY
      movie.title ASC

This example uses these simple techniques to create structural clarity:

Primary SQL Keywords are outdented and capitalized to emphasize essential clause structure.
Secondary SQL keywords are capitalized inline.
Other elements, including data table and column names, are rendered in lowercase, again focusing attention on structural keywords.
Native SQL dot nomenclature is used, rather than FileMaker "::" syntax.
Individual clause elements are each placed in parallel on separate lines; for example, each field in the SELECT clause appears on a separate line.
Required punctuation is placed prior to elements; for example, commas in the SELECT clause precede column names, making it much easier to locate missing or extra delimiters, and facilitating copying/pasting lines.

source: http://bestbridge.net/Eu_en/millau-viaduct.html

Imparting Semantic Meaning

Structuring a query like this may at first feel foreign and perhaps even unnecessary, particularly if we've experienced some initial success with ad hoc queries. But there's a very big payoff to a structured approach: Once you've trained your eye to understand SQL structures, well-structured queries become easy to quickly scan and understand. They expose the key parts of a query directly to us.

By contrast, the earlier ad hoc version of this same query was nearly impossible to grasp as presented. Linear form blocked meaning. The difference between these approaches becomes increasingly pronounced as queries grow in complexity.

Well-structured queries are semantically meaningful, and help us craft SQL that is lighter, more flexible, and more powerful. Said differently, the pursuit of well-crafted SQL isn't an affectation ... but a core part of the craft of creating SQL. As Steve Jobs has it, "Design is not just what it looks like and feels like. Design is how it works."

One more lesson from our collective FileMaker experience also applies. As a community, we agree on the importance of crafting well-structured, consistent scripts. But we don't believe there's only one way to do this, and a number of structural and naming conventions are used in our FileMaker community (there is a similar range of practice in the SQL world as well).

I suggest approaching writing structured SQL in this same spirit. Choose a set of conventions that will help you create clear, consistent, self-documenting SQL code, and make it your own.

Diving into the Deep End

One especially thoughtful aspect of FileMaker 12's new ExecuteSQL function is the way it masks so much of SQL's native complexity. This dramatically lowers the bar for FileMaker developers getting started with SQL, letting us ignore native SQL issues like data typing, potential injection attacks, character escaping issues, and more. At the beginning — and later when we want to quickly execute a simple query — this is great convenience.

But when we're ready to take our SQL skills to a more advanced level, this masking of native SQL can become a hindrance. At this point, we may need to stop reyling on FileMaker to mask SQL's quirks and instead take a bold plunge into the deep end of the pool. For me, this means explicitly casting FileMaker values into correct SQL types, learning and using standard SQL functions instead of FileMaker variants, and following accepted SQL conventions. My goal is for the SQL I create to be fully readable to a SQL developer who knows nothing of FileMaker.

As before, this may raise some eyebrows. But once again, this approach offers significant payback for the effort invested.

First, SQL written like this is immediately clear to SQL professionals who won't recognize Filemaker-specific syntax, making it possible to share questions, problems, and inspirations with native SQL developers. Second, working in native SQL makes it much more natural to step directly into deeper features of the SQL language.

As a long-time FileMaker developer, I can report that — when well structured — native SQL queries are much less scary than I'd imagined. As a poor swimmer who can barely float, I can also say that the water in the SQL pool is really quite nice. Jump in!

source: http://www.successupermarket.com/blog/the-most-beautiful/the-most-beautiful-bridges/

[news release] InspectorPro 4 for FileMaker 12

2012-07-19T13:31:43Z

[news release]

Beezwax Releases InspectorPro 4 for FileMaker 12

Leading Developer Tool Now Up To 10x Faster, Provides Deeper Analysis for FileMaker 12 Databases

Mac version available now. Windows version coming soon.

MIAMI, Fla. â€“ FileMaker DevCon - July 16, 2012 - Beezwax Datatools, Inc. released InspectorPro 4, a major new version of the leading developer tool for FileMaker 12 database analysis. Rewritten from the ground up to take advantage of FileMaker 12’s new architecture, InspectorPro 4 is up to 10 times faster than previous versions at processing FileMaker Database Design Reports.

New features provide deeper analysis of database elements, a central dashboard, localized UI options, and powerful data visualizations - enabling FileMaker developers to discover more about their databases than ever before.

“InspectorPro 4 is blazing fast. We took advantage of many new features in FileMaker 12, such as ExecuteSQL to rewrite the product from the ground up,” said Vincenzo Menanno, creator of InspectorPro and Director of FileMaker Development for Beezwax. “A new centralized dashboard, sophisticated in-depth reports plus InspectorPro’s unique data visualization capabilities, all enable deeper discovery of critical database information that FileMaker developers need to manage during the development process.”Â

InspectorPro 4 introduces the following features for FileMaker database analysis:

Central Dashboard - InspectorPro 4 consolidates critical information about database elements into a central UI.
Deeper Analysis - Comparison, dependency and other analysis reports offer expanded information about database elements, as well as deeper levels of information.
Localized UI - InspectorPro 4 will be available with interfaces in English, French, German, Japanese and several other languages.
Data Visualization - Script Universe and Security Perspective visualizations have been updated for FileMaker 12 development.

Beezwax is demonstrating the new InspectorPro 4 at the 2012 FileMaker Developers Conference in Miami, Fla, July 16-19, 2012.Â

FileMaker DevCon Special Pricing is available through July 31, 2012: click “Buy Now” on the InspectorPro product page.

More information about InspectorPro 4 is available at www.beezwax.net/products/inspectorpro

About InspectorPro

InspectorPro is the leading tool for diagnosing, debugging and documenting FileMaker databases. InspectorPro works quickly to identify hard-to-find issues, compare database versions for changes, and identify all dependencies, unreferenced items and uses of any element in a solution. InspectorPro works by using a FileMaker Database Design Report (DDR) to generate a list of database problems that need addressing. InspectorPro proactively provides suggestions for developers to fix the generated list of database issues, and unique data visualization enable developers to discover more about their FileMaker solutions.

Compatibility & Availability

InspectorPro 4 works with FileMaker 12 databases and is available now for Mac OS X 10.6 Snow Leopard and OS X 10.7 Lion. A version for Windows is scheduled for release in Fall 2012.

FileMaker Pro 12 Advanced is required for a developer to generate the DDR (Database Design Report) to be used for analysis. The previous product version, Inspector Pro 3, is available for purchase for use with FileMaker Pro versions 8 through 11.

InspectorPro 4 is priced at US$495 for new customers, and is available for purchase and immediate download at www.beezwax.net. A Team License (for up to 5 users) is $1,295. A full-feature trial download is available. Upgrade pricing, and options for Educational and Non-profit pricing are available via Beezwax.

About Beezwax Datatools, Inc.

A recognized industry leader in FileMaker Pro development, Beezwax has completed more than 1,000 successful custom database and web projects for over 400 satisfied clients.Â Headquartered in Oakland, CA, BeezwaxÂ distributes widely-used products and tools for leading developers, and is a Platinum-level member of the FileMaker Business Alliance. More info is at www.beezwax.net.

Media contact:
Jay Gonzales
510-835-4483
info@beezwax.net
www.beezwax.net

FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries.

bBox 0.53 Now Available

2012-07-07T04:59:50Z

Version 0.53 adds the usual mix of some new functions plus a few fixes for previous issues.

The new functions are:

bBox_GetCharacterStyle - get typeface and color information for a given character
bBox_Base64Decode - takes Base64 encoded data and returns the result as either a container or text object
bBox_Base64Encode - pass text or a container reference to get a Base64 encoded version

There were also some tweaks to the Python GetVar & SetVar functions, and a fix for an ExecuteSQL issue on FMP 11 & 12. This version was tested against a pre-release version of Mac OS 10.8 (Mountain Lion), and no issues were found.

The Base64 encoding function is admittedly incomplete. Hopefully the next release will have some improvements to this function.

Simon

Go, JavaScript Go!

2012-06-08T16:44:31Z

Introduction

You gotta love FileMaker Go. Go’s ability to effortlessly create data-driven mobile apps and extend existing desktop data applications to mobile users is transformative. But sooner or later, almost all of us run up against one of Go’s core limitations: Its lack of native plugins.

Most of us understand this isn’t an oversight, but rather a basic constraint imposed by the architecture of Apple’s iOS. Still, the inability to extend Go’s native toolbox through plugins often limits the capabilities and expressiveness of Go solutions. Many developers and users have probably daydreamed of how much better their mobile solutions might be if only they could find some way of extending Go with plugin functionality. If only… if only…

In FileMaker Go 12, we can now do exactly this.

Giving New Life To Your Mobile Database

This article describes how you can create virtual plugins for FileMaker Go 12 using JavaScript and FileMaker 12’s new fmp:// protocol in your Go solutions — a technique we’ll call “JavaScript Go”. Using this new technique, you’ll be able to leverage the vast power of JavaScript — as well as popular JavaScript libraries like jQuery, zepto.js, and underscore.js — to add power, functionality, and excitement to your mobile solutions.

As usual, a complete demo file is available at the end of this post. Thanks to WorldCloud for use of their FileMaker hosting services, we're able to offer a live, hosted demo version of this database at: fmp://shared-hosting–2.filemaker12.com/javascriptGO

OK. Let’s get started…

A Quick Review. Or, "How To Create Your Very Own Monster in 4 Easy Steps"

The underlying techniques we’ll be using here repurpose several steps described in my recent blog post Native Web 2.0 Controls in FileMaker 12 Layouts. I explained how FileMaker 12’s new fmp:// protocol lets us integrate native web controls and design patterns as full citizens of our Pro and Go visual interfaces. In our current post, we’ll flip this technology on its head. This time, we'll use the same fmp:// protocol to create JavaScript engines that silently spring to life to add plugin-like power and capabilities to our hosted or stand-alone Go solutions any time we want. (Note that although we’ll focus almost exclusively on FileMaker Go, this technique works equally well on any hosted or shared Pro 12 solution).

Let’s quickly recap the steps explained previously, this time following in the footsteps of that immortal classic, “Young Frankenstein”.

Step 1: Find a Body

As the movies teach us, a budding mad scientist building a creature from scratch must start with a body. When the young Dr. Frankenstein needed a body for his creature, he was forced to unearth a cold, hulking corpse in the dead of night. We FileMaker Go developers have it much easier: We’ll just place a named Web Viewer on our Go layouts, staying nice and cozy as we do so.

Step 2: Select a Brain (and Make it a Good One)

The movies also make clear that once we have our body, we must find it a brain. This step trips up so many mad scientists; time and again their well-meaning sidekicks bring back the worst specimen in the entire Brain Repository. We won’t make that mistake: We’ll return from our lab with speedy, lean, industry-standard JavaScript, ready to insert that into our Web Viewer.

Step 3: Give Your Creature Life

Once we have a Body and have inserted our Brain into it, we face the age-old question of how to bring them both to life. To animate his new creation, Young Frankenstein had to risk life and limb, high atop a questionably designed gothic castle on a dark and stormy night. We’ll be able to bring our JavaScript virtual plugin to life simply by injecting custom JavaScript and live FileMaker parameters into our Web Viewer whenever we want it to live. If only Dr. Frederick Frankenstein had known of FileMaker 12…

Step 4: Teach Your Creature to Speak

Finally, our now-living creature must be able to communicate to us. Here’s where FileMaker 12’s new fmp:// protocol really helps us out: In earlier versions of Go, Web Viewers trying to converse with native FileMaker scripts made awkward grunting noises, just like so many matinee creatures. Igor, Frau BlÃ¼cher and company needed an entire movie to resolve this tricky conundrum, ultimately resorting to a dangerous brain transference procedure to enable their monster to communicate clearly. In our case, FileMaker 12’s new fmp:// protocol makes it easy: Our Web Viewers will speak directly and eloquently to our native FileMaker scripts right out of the box, with no need for expensive brain surgeries our HMO’s likely won’t cover. Huzzah!

And Now to the Laboratory…

Let’s roll up our sleeves and look at the details of how we can build a JavaScript Go creature of our own:

jsGO Web Viewer

We’ll start by simply placing a Web Viewer on our layout, which will act as the “body” for our JavaScript “brains”. Let’s call it “jsGO.view”. I’ve found a couple of gotchas — the Web Viewer can’t be hidden on an inactive tab or beyond the visible area of a layout, and Go performance is best if each layout gets its own copy of “jsGO.view”.

jsGO Custom Functions

JavaScript Go uses a small set of lightweight custom functions: “jsGO.fmpWrap” provides a context-aware wrapper for the new fmp:// protocol, so whether we’re running a local or hosted Go solution, or a hosted or shared Pro solution, our JavaScript will be able to communicate with our FileMaker scripts. The custom functions “jsGO.scriptWrap” and “jsGO.webWrap” help us easily wrap JavaScript snippets in valid script and HTML5 wrappers; Last, a set of hash custom functions allow us to pass named parameters to our core scripts (see the next section for more info). All of these custom functions are fully generalized, so we can just paste them into any solution then get on with our work.

jsGO Scripts

Our technique uses 2 scripts: The first, “jsGO.listener” will simply listen for results our JavaScript returns to our solution via the fmp:// protocol, then place these results in a global variable, $$jsGO.results.

Our second script, “jsGO” is the script that will do the heavy lifting, and the script that will be the public face of javascriptGO. We’ll invoke our jsGO script whenever we call our virtual plugin, much as we’d normally call a standard plugin in Pro. Let’s take a closer look at how it works:

Our jsGO script will need to be able to accept many types of data, and varying numbers of parameters, depending on the JavaScript functionality desired. To handle this gracefully, we’ll choose the widely-used method of passing named parameters in a hash format. We’ll require each call to jsGO to pass a “function” parameter telling our script what we’re asking of it; beyond this, we’ll accept any number of additional parameters needed. This will let our script accept variable types and numbers of parameters, then parse each parameter passed to local variables we can integrate into our JavaScript.

After we’ve parsed our parameters, we’ll use a simple if / elseif structure to locate the function requested. If a null or unknown function is encountered, we’ll return an error (via $$_jsGO.error) and exit. Within our if / elseif structure, once we’ve located the desired function, we’ll define the custom JavaScript that will perform that task. In the simplest cases (like requesting time in milliseconds, getting random numbers, etc.) we’ll only need static JavaScript. More typically, we’ll inject live FileMaker data passed via our parameters directly into our JavaScript, so that our virtual plugin will be able to act on live solution data. Whatever task we’re performing, our JavaScript will include a return function enabling it to speak to our listener script with its calculated results.

Now comes the fun part: We’ll wrap our JavaScript snippets inside script and HTML5 wrappers and insert this into the “jsGO.view” Web Viewer on our active layout, bringing our creation to life. Once we’ve done this, we’ll pause and wait briefly for our Web Viewer to do its work and communicate its results back to our jsGO.listener script. As soon as we have these results, our script will resume.

Last, we may perform optional cleanup if, for example, our $$jsGO.results need to be reformatted a bit or have characters un/escaped. And then, jsGo exits, allowing the calling script or object to process our JavaScript Go results and continue its work. Here’s the main body of our "jsGO" script:

# Parse Parameters

Set Variable [ $_value; Value:#P ( "value" ) ]

Set Variable [ $_regex; Value:#P ( "regex" ) ]

. . . (omitting similar steps for brevity) . . .

# â–¼ Single-Pass Loop â–¼

Loop

# Set Custom JavaScript by Function

If [ IsEmpty ( $_function ) ]

Set Variable [ $_error; Value:10 ]

Set Variable [ $_error_message; Value:"Required parameter 'function' not received." ]

Exit Loop If [ True ]

Else If [ $_function = "number.toHex" ]

Set Variable [ $_javascript; Value:Let ( the_script = "

var num = " & Int ( $_param_1 ) & " ;

var result = num.toString(16);

self.location = " & sQuote ( jsGO.fmpWrap ( "jsGO.listener" ; "" & "" ) ) & "+result; ";

jsGo.scriptWrap ( the_script ) )]

. . . (omitting additional function definitions for brevity) . . .

Else If [ $_function = "regex.strip" ]

Set Variable [ $_javascript; Value:Let ( [

text=Substitute($_param_1; ["'";"\'"];["\"";"\\\""];["¶";"\n"];["=";"–"]); pattern = $_param_2 ;

the_script = "

var str=" & sQuote ( text ) & " ;

var patt = " & pattern & " ;

var resultStr = str.replace(patt,'');

self.location = " & sQuote ( jsGO.fmpWrap ( "jsGO.listener" ; "" & "" ) ) & " + resultStr ; "

];

jsGo.scriptWrap ( the_script ) )]

Else

Set Variable [ $_error; Value:10 ]

Set Variable [ $_error_message; Value:"Unknown 'function' parameter received." ] Exit Loop If [ True ]

End If

# Set web viewer and wait for response

Set Variable [ $_start_timestamp; Value:Get ( CurrentTimeStamp ) ]

Set Web Viewer [ Object Name: "jsGO.view"; URL: "data:text/html," & jsGo.webWrap ( $_javascript ) ]

Loop

Exit Loop If [ $$_jsGO.results ≠ "" or Get ( CurrentTimeStamp ) ≥ $_start_timestamp + $_timeout_seconds ]

Pause/Resume Script [ Duration (seconds): $_loop_interval ]

End Loop

# Reset web viewer and handle any errors

Set Web Viewer [ Object Name: "jsGO.view"; URL: "data:text/html," & jsGo.webWrap ( "" ) ]

If [ IsEmpty ( $$_jsGO.results ) ]

Set Variable [ $_error; Value:20 ]

Set Variable [ $_error_message; Value:"No results were returned from the web viewer." ]

Exit Loop If [ True ]

End If

# Do any Cleanup if needed

If [ $_function = "" ]

Else If [ $_function = "values.unique" ]

Set Variable [ $$_jsGO.results; Value:Substitute ( $$_jsGO.results ; "," ; ¶ ) ]

. . . (omitting similar steps for brevity) . . .

Else If [ $_function = "number.toHex" ]

Set Variable [ $$_jsGO.results; Value:Upper ( $$_jsGO.results ) ]

End If

Exit Loop If [ True ] End Loop

# â–² Single-Pass Loop â–²

Is It Friendly?

Any useful plugin must be easy to integrate into our solutions and simple to use. We understand that any FileMaker plugin will be complex under the hood, but we also expect this complexity to be hidden within the plugin. Our technique meets this test nicely.

Adding JavaScript Go to any solution is very simple, because it’s designed to be quickly bolted in to any solution: We simply add jsGO Web Viewers to the solution’s layouts, paste in our custom functions, paste in our scripts, and… that’s it. Just that easily, we can now call a JavaScript Go function from any script or layout object in this solution as simply as we’d call a normal FileMaker plugin. The most noticeable difference is that instead of making a true function call (as with a true plugin), we instead run our jsGO script. Otherwise, the concepts — and the simplicity — are virtually identical. For example, here’s a sample call to jsGO:

Perform Script [

“jsGO”; Parameter: List (

# ( "function" ; "regex.match" ) ;

# ( "value" ; [filemaker_value] ) ;

# ( "regex" ; [regex_pattern] )

)

]

Putting On The Ritz

We're finished in our laboratory. But the movies make clear we're not truly finished until we've displayed our creation before a community of skeptical scientists. So let’s bring JavaScript Go out onto the stage, cue the band, and see what he can actually do for us:

Out of the box, JavaScript Go knows some nifty steps. JavaScript string and array operations offer us a great deal of power, and number, date, and time methods bring some nice extensions to our native FileMaker toolbox. These native JavaScript functions give us new capabilities, and in some cases offer more precision (getting time in milliseconds, more accurate random numbers, etc). Not a bad start…

The band takes the tune up a half step, and JavaScript Go kicks it up a notch, showing off it’s ability to do RegEx — the powerful pattern matching tool used by many modern programming environments, including JavaScript. Want to find all phone numbers — regardless of formatting — anywhere in a string? Or all html or xml tags — even unexpected ones? Or maybe you’d like to find all zip codes in your text, but only if they’re followed by a 4 number suffix? And then you’d like to replace ones meeting certain criteria with other strings of your choosing? We can do any of these complex operations in only a single call to our virtual plugin.

As the band notches it up another half step, JavaScript Go now shows off a bit of what it can do using JavaScript libraries like jQuery, zepto.js, and underscore.js. We can store these directly within our mobile solutions, maximizing performance and ensuring these libraries will work offline if needed. Using these powerful libraries, we can extend JavaScript’s native toolset even further to let us do things like industry-standard RSA encryption (locally on the device, without any round trips to a server), AJAX operations, and much more heavy lifting than we can review in the current post. (But… shameless teaser… stay tuned for future posts).

So far, everything’s going great. But what’s this? Someone from the audience runs up and pulls the plug on our internet connection, setting our iOS device to Airplane Mode and leaving JavaScript Go without a network connection of any kind. The crowd gasps! Now the band unexpectedly takes it up a full step and a half — to D# minor! — just as a klieg light shatters, causing a small fire to break out onstage. Smelling imminent failure, the previously friendly crowd of scientists reaches in unison for a hidden supply of tomatoes (apparently stored beneath their seats). Will our JavaScript creature degenerate into an incoherent lump of technology, perhaps grabbing Fay Wray and making an ill-advised attempt to scale the Empire State Building in the process?

Not at all. JavaScript Go smiles to itself, then does the seemingly impossible, continuing to perform flawlessly without missing a beat — even without a network connection! Except for functions explicitly built to query live data sources, everything still works perfectly, including our extended libraries, because we’ve cached them locally in our solution. Our solutions hum along seamlessly, our potential crisis is averted, and those rotten tomatoes disappear into non-existence as our audience sees how robust and versatile our virtual plugin is. The ghost of Fay Wray (who never belonged in our movie anyway) breathes a sigh of relief from its final resting place.

Conclusion

JavaScript Go offers FileMaker Go 12 developers the opportunity to move beyond the box of Go’s stock functionality. We can now create mobile solutions that are richer, more powerful, and more full-featured than previously possible. At the same time, we gain access to JavaScript’s world-wide development community, and a vast library of JavaScript functions and libraries. And as with many of the most powerful FileMaker Pro plugins, JavaScript Go is endlessly extensible, ultimately being limited only by our imaginations. It will be exciting to see how our development community puts these exciting new capabilities of FileMaker Go 12 to use.

Enjoy!
-Brian

Follow Brian on Twitter

. . . . . . . . . . . . .

Demo File

Our demo file now comes in 2 flavors: You can access a hosted version of file at: fmp://shared-hosting–2.filemaker12.com/javascriptGO. Note, however, that this hosted file doesn't offer admin access to scripts and custom functions. If you'd like to tinker under the hood or load this file locally, you can download an unlocked copy of the demo file: javascriptGO.fmp12

The Benefits of Automated Testing

2012-05-26T21:21:13Z

Software changes over time. Your companyâ€™s web site this year does things you had no idea you needed two years ago. How can we keep the cost of change manageable?

Automated testing is one way. Especially for large software projects, the practice of automated testing can dramatically reduce the cost of adapting software to new business necessities.

As a developer, when I upgrade to a new version of software or add a new feature, automated tests tell me right away when Iâ€™ve broken a working feature. Automated tests find bugs instantly. Manual testing can also find bugs, but itâ€™s slower and more expensive. So professional software development teams use automated tests extensively.

Sometimes we can get to a deadline sooner by leaving out automated tests, but it increases the cost of development from then on, at least until we take the time to write the automated tests. We call shortcuts like going without tests "technical debt" — taking those shortcuts in development can speed you up in the short term, but then youâ€™re stuck â€œpaying the interestâ€ on the debt in the form of extra time and cost.

Predicting and Avoiding Unpredictability

One of the most difficult issues in software project management is the inherent unpredictability of software project schedules. The only predictable task, in any field, is one youâ€™ve already done before. But in software, if youâ€™re writing code thatâ€™s been written before, youâ€™re doing it wrong â€” you should just copy the existing files into the new project!

As Joel Spolsky said in 2001:

This means that almost everything we do as software developers is something that has never been done before. Herman the Handyman, who just installed a tile floor for me, has probably installed hundreds of tile floors. He has to keep installing tile floors again and again as long as new tile floors are needed. We in the software industry would have long since written a Tile Floor Template Library (TFTL) and generating new tile floors would be trivial. … Everything you do is on the cutting edge by definition. So by definition it is unpredictable.

In the face of this inherent unpredictability, we can at least remove much of the avoidable unpredictability. One of the biggest causes of avoidable unpredictability is tracking down bugs. An unexpected nasty bug can blow your development budget and your schedule. Because automated tests let you find bugs quickly, tests reduce the risks of software project budgets.

What Programmers Do, and Why Catching Bugs Faster is Such a Big Deal

A lot of people think that software development consists mostly of typing code into a computer, with the occasional error that needs to be fixed. A little arithmetic will show how wrong that is.

Nearly all programmers are adept typists, capable of speeding along at 90 words per minute. A line of code is typically about five words worth, so if a programmer is just typing in code from a printout, he or she can type about 18 lines of code per minute, or 1080 lines per hour, or 8640 lines per day.

But, on real-life software projects, programmers might produce between 3 and 30 lines of code per day, or up to 100 lines in extreme circumstances on small projects. So theyâ€™re spending at least 99% of their time doing things other than typing in production code.

Where does that 99% of the time go? Itâ€™s spent on understanding what the existing code does (this alone is about 50%), on figuring out what the new code needs to do, on organizing the code, on testing to see what the code does, and on hunting down bugs in the code to make it work right. In particular, people often underestimate the enormous amount of time even the best programmers have to spend ferreting out their own mistakes.

How Automated Testing Works

I have a bug to fix. I load up the test runner and push the Run button, and a green progress bar showing the progress of the run of its test suite appears. A few seconds or minutes later, the test run is complete, and I see that the bar is still green, so the system is in working order on my workstation. I add a new test, which consists of writing a few lines of code that demonstrate the bug Iâ€™m going to fix; I hit Save; and I hit Run again. This time, the bar turns red, and shows him that the test Iâ€™ve just written is failing. I fix the bug, push Run again, and the tests go green again â€” telling me not only that my fix worked, but also that I didnâ€™t break anything else in the system in the process.

There are some things about this scenario that are crucial to getting the benefits of automated testing:

First, I didnâ€™t have to wait long to get the results of the automated test run. If it took ten minutes, or an hour, or overnight, to get the results, I wouldnâ€™t run the test suite before I started developing, and thereâ€™s a possibility that the code or the test suite would already be buggy. When I finally ran the test suite, and the tests went red, Iâ€™d think I had introduced a bug in an unrelated part of the system, and Iâ€™d waste time trying to figure out how I could possibly have broken it. For some projects, itâ€™s worthwhile having a separate suite of â€œslow testsâ€, which can be run less frequently, but itâ€™s crucial to keep the normal test run snappy in order to make it useful.
Second, I didnâ€™t have to inspect the output of the test run in order to figure out whether the tests passed or failed. The test suite contains not just the script to exercise the system, but also the expected output, and I get a simple red/green indicator that everything is okay, out of a suite that may be thousands or tens of thousands of individual tests.
Third, I didnâ€™t have to wait for a separate Testing Team to write the test. QA teams are very useful for many software projects, but some cases, itâ€™s efficient for the developer to write the test, even if the QA team found the bug originally.
Fourth, I tested the test â€” I verified that if the bug was present, the test failed.

In this case, Iâ€™m testing the test by writing the test before the code thatâ€™s supposed to make it pass. This approach is called Test-Driven Development (TDD) or Behavior-Driven Development, and itâ€™s widely believed to improve code quality and speed up development, for a variety of reasons that Iâ€™m saving for another blog post. But, while TDD is more convenient in many cases, I still get a lot of benefit from automated tests written after the fact.

Of course, itâ€™s still possible that I could introduce a bug that the existing test suite wonâ€™t catch. I still have to be careful and think about what Iâ€™m doing. But a good test suite will catch almost all of my slip-ups.

Conclusions

There are other benefits of automated tests besides just reducing the number of bugs. A kind of automated test called an â€œacceptance testâ€ serves not only to catch bugs, but also to document customer requirements. There are special tools for maintaining automatic acceptance tests in a form thatâ€™s understandable and even editable by non-technical customers: FitNesse and Cucumber are two of the most popular.

Automated â€œunit testsâ€ also provide a similar value for reusable modules of code, documenting what those modules do, and donâ€™t do, to future programmers — as well as documenting how to use them — enhancing the practical reusability of those modules, reducing future development effort. And test-driven development is widely believed to improve the internal design of software â€” something thatâ€™s invisible to users, but reduces the cost of later changes.

Automating the tests that can be automated means that human QA testers can focus their effort on finding trickier bugs, including problems with usability, which gives customers much more value per QA hour spent. Many consider these benefits more important than merely catching bugs.

Finally, itâ€™s not enough to know that automated tests are a good idea; how do you decide what to test, and how much testing is too much? Hold that thought; weâ€™ll discuss these topics in later blog posts.

List cron entries for all users on Mac OS X

2012-06-15T17:18:50Z

I needed to investigate why a script was being run, and I was pretty sure cron was launching it. The problem was, little useful information was being logged, and no one was sure how it had been set up or what user it was under. Although launchd is now the preferred mechanism on Mac OS for these kinds of things, you’ll still see many systems where cron is being used.

First, you want to become the root user. The following Terminal command is one way to accomplish this:

sudo -s

From here, one option is dump the contents of all files in /usr/lib/cron/tabs:

cat /usr/lib/cron/tabs/*

This might be a bit messy, and doesn’t show which user the entries belong to without tracing back to the original file. A cleaner way to do this is in bash is:

for user in $(dscl . list /Users); do echo $user; crontab -u $user -l; echo; done

Don’t forget to also check in /etc/periodic and use the atq command for other places that cron like jobs could be configured.

Finally, I have had issues on Linux systems where a phantom cron entry persists, even after it has been removed using the crontab command. For this, I had to restart the crond process to clear out the old entry.

bBox 0.52 Now Available

2011-10-07T22:30:13Z

A new version of our free bBox FileMaker plug-in is now available.

One change in this release is a new option for the bbox_EvaluateList function that allows treating a string as an array of chars. Here's an example of what this expression and its result might look like:

bBox_EvaluateList ("abc"; "$i & \": \" & $v"; "")

	1: a
	2: b
	3: c

This release also includes a new "intel" version of the plug-in. The primary reason for adding this is to provide access to a newer version of the Python API's. The standard "universal" version is linked against Python 2.5, whereas the intel version is linked to Python 2.6. This change does not affect the Ruby interface and has very little effect on any of the other bBox functions.

You can download the latest version of bBox at http://www.beezwax.net/download/bbox.

Learn more about the bBox plug-in a http://beezwax.net/bbox and http://bbox.beezwax.net/ .

Getting a FileMaker label's text in a conditional formula

2012-05-15T21:04:35Z

I wanted to do some conditional formatting, such that when a global field had a certain value the label would change its appearance. For example, if my sort column was “address”, then I wanted the column header containing the text “Address” to highlight.

I had a hunch that the Self function could help. I tested this by attaching a conditional formula to a label:

Let (
   $$n = Self;
   0
)

Using the Data Viewer in FileMaker Advanced I could see that the label’s name was then getting “leaked out” to the global variable $$n in the conditional statement. So, for my use case above, I could simply write the following:

TABLENAME::globalField = Self;

Using Cocoa's PDFDocument class from FileMaker

2012-05-13T05:41:47Z

I’ve been looking for a way to interface with some Cocoa classes in the Mac OS X API from FileMaker for a while. My initial target was PDFDocument, part of the PDF Kit suite of classes. For more information on Cocoa’s PDF Kit see here: Introduction to PDF Kit Programming Guide

I’ve had a couple versions of this working for some time, but I could never get anything that worked consistently across the various OS and Python combinations (Python is my default tool of choice for these kinds of things). Changes to both the Cocoa interfaces in Python and the OS architecture kept getting in the way. But I may of finally hit it.

Below is a Python script I’m using to test this out. It uses the PyObjC module, which is the more recent method of interfacing Python with Cocoa classes. To make interfacing between Python and FileMaker easier, I’m also using bBox, a free FileMaker plug-in I wrote that allows integration between FileMaker and Python.

import sys, os
import getopt
from pprint import pprint
#
# For some OS versions we must explicity add the PyObjC path.
sys.path.append ('/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/PyObjC/')
#
from Foundation import 
from Quartz import 
#
# Set by the calling FMP script. Path of PDF that we will examine.
# sourcePath
#
# Set by the calling FMP script. Path to store the modified version of PDF.
# destPath
#
# Subroutine that does all the work.
#
def addAttributes(filepath,destPath):

url = NSURL.fileURLWithPath_(filepath)
pdfdoc = PDFDocument.alloc().initWithURL_(url)

attrs = NSMutableDictionary.alloc().initWithDictionary_(pdfdoc.documentAttributes())

<span class="keyword">print</span> attrs

attrs[PDFDocumentTitleAttribute] = <span class="string"><span class="delimiter">'</span><span class="content">PDF TITLE</span><span class="delimiter">'</span></span>
attrs[PDFDocumentAuthorAttribute] = <span class="string"><span class="delimiter">'</span><span class="content">A. Author and B. Author</span><span class="delimiter">'</span></span>

pdfdoc.setDocumentAttributes_(attrs)
pdfdoc.writeToFile_(destPath)


#
# Execute the subroutine with the given paths.
addAttributes (sourcePath, destPath)

The above script is stored in a FileMaker field. Here’s the script used in FileMaker to call the Python script:

Set Variable [ $pysourcePath; Value:"/Applications/Safari.app/Contents/Resources/SearchMagGlassTemplate~snowleopard.pdf" ] Set Variable [ $pydestPath; Value:"/Users/Shared/bboxversion.pdf" ]
Set Variable [ $pyscript; Value:bbox pyobjc::pdfattributesscript ]
 #
 # Compile the script & setup context.
Set Variable [ $compileresult; Value:bBoxPythonCompile(0; $pyscript) ] If [ $compileresult â‰  "" ]
Exit Script [ ] End If
 #
Set Variable [ $ignore; Value:bBoxPythonSetVar("sourcePath"; "/Applications/Mail.app/Contents/Resources/ActionTemplate.pdf";"s") ] Set Variable [ $ignore; Value:bBoxPythonSetVar("destPath"; "/Users/Shared/bboxversion ofpdffile.pdf"; "s") ]
 #
Set Variable [ $executeresult; Value:bBoxPythonExecute(0) ]
 #
 # For unknown reasons, should NOT call finalize here in bBox 0.52 and possibly earlier versions, so currently disabled.
// Set Variable [ $ignore; Value:bBoxPythonFinalize ]
 #
Show Custom Dialog [ Title: "Python Result"; Message: $execute_result; Buttons: â€œOKâ€ ]

The key change was adding in the path for PyObjC. This may only be necessary because bBox interfaces with Python using its C based API, so it probably wouldn’t be necessary in a “normal” Python script. Having systems that have Python.org’s version of Python could also account for the differences I’ve seen, but bBox is only linked to Apple’s version of the Python framework so that would be surprising cause for the difference.

Since I am working on getting a new version of bBox out I’ll hopefully get a bit more clarity on this issue and maybe a few more examples as I test out the new version.

Getting a File Path easily in OS X

2012-05-08T23:25:26Z

It comes up fairly frequently for me that I need the path to a file I have selected in the Finder. With some futzing, you can already do this. Some techniques are

drag file to Terminal window
copy path from Get Info dialog
drag file to TextEdit window

But I was hoping for something a little better.

Applet in Toolbar

The first choice is a revised version of a simple AppleScript applet I wrote quite a while ago. You can download the applet here: Copy Path.app

Unzip if needed, and save the applet somewhere safe. Then drag it into a Finder window’s Toolbar, so that the applet icon is now on all your Finder windows:

To use, select one or more files in the Finder, then click on the applet icon in any Finder window (hold the Shift key if you’d like AppleScript/OS 9 style file paths). You can now paste the path text wherever it’s needed.

One interesting side note about the AppleScript used here. It turns out to be difficult to impossible to get the state of the modifier keys using just AppleScript. Creating a small command in C was required, which I then call with AppleScript’s do shell script command. I probably could’ve also built the applet using Xcode and AppleScriptObjC, but compiling the command seemed more straightforward, at least until I learn more about AppleScriptObjC.

Service Method

Another approach, and the one I think I like slightly better when using Lion (10.7), was to create a Service in Automator. This was dead simple, but I have a ready version for you here: Copy Path.workflow

To use, first copy the .workflow file into your Library/Services folder, and then double-click to open. You can immediately close it. Select one or more files or folders in the Finder, then choose Finder->Services->Copy Path. Here’s an example of it being used:

Unfortunately, in Snow Leopard (10.6) this does not work as well. You can only get the path to a file (not a folder), and it uses the older AppleScript/OS 9 convention for path strings.

the beezwax buzz : the beezwax buzz | beezwax.net

Re-Thinking is the new Rebuilding

Re-thinking InspectorPro 4 in FileMaker 12

Trimming the Graph with ExecuteSQL

From Worst To Best

Button Up 2: Building State-Aware Buttons

Conditional Love

To Tell the Truth

The Mighty Cascade

Through the Looking Glass

Less is More

One More Thing

Summarize how often databases are opened in FileMaker Server

Fail2Ban with FileMaker Server

Curl HTTP Post from FileMaker

bBox 0.56 Now Available

Debugging Apache with OSXS 10.8 Server 2.2

Code Signing a FileMaker Runtime

Button. Up.

It's Alive!

Of Mice and Semantics

Clearly State Your Intentions

Keeping the Faith

Same Bat Time, Same Bat Channel…

Notes on backing up a wiki in Lion

The First Step to Great Solutions

Sending HTML Email from FileMaker

Apple Mail

ScriptMaster Plug-In

Web Service

Other Plug-Ins

Caveats

Just a Slice, Please…

Tools of the Trade

Flattening Repeating Data

<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |

Peeling Off a Slice

<rep1> | <rep2> | <rep3> | <rep4> | <rep5> | <rep1> | <rep2> | <rep3> | <rep4> | <rep5> | <rep1> | <rep2> | <rep3> | <rep4> | <rep5> |

The String’s Not the Thing

Evaluate ( "Sum ( " & values & " ) " )

Worth Repeating

XML Parsing with FileMaker and bBox

Our First FileMaker 12 Certified Developers

FileMaker 12 SQL: Crafting Structural Beauty

The Beginning of Craft

Creating Structural Beauty

Imparting Semantic Meaning

Diving into the Deep End

Recommended Reading

[news release] InspectorPro 4 for FileMaker 12

Beezwax Releases InspectorPro 4 for FileMaker 12

bBox 0.53 Now Available

Go, JavaScript Go!

Introduction

Giving New Life To Your Mobile Database

A Quick Review. Or, "How To Create Your Very Own Monster in 4 Easy Steps"

Step 1: Find a Body

Step 2: Select a Brain (and Make it a Good One)

Step 3: Give Your Creature Life

Step 4: Teach Your Creature to Speak

And Now to the Laboratory…

jsGO Web Viewer

jsGO Custom Functions

jsGO Scripts

Is It Friendly?

Putting On The Ritz

Conclusion

Demo File

The Benefits of Automated Testing

Predicting and Avoiding Unpredictability

What Programmers Do, and Why Catching Bugs Faster is Such a Big Deal

How Automated Testing Works

Conclusions

List cron entries for all users on Mac OS X

bBox 0.52 Now Available

Getting a FileMaker label's text in a conditional formula

Using Cocoa's PDFDocument class from FileMaker

Getting a File Path easily in OS X

<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |
<rep1> | <rep2> | <rep3> | <rep4> | <rep5> |