99 Bottles of Beer – in FileMaker 11

A while back I took a PHP course. The instructor pointed out a website that had demonstrations of the old song ’99 Bottles of Beer’ as implemented in hundreds of different programming languages (1,342 different languages when I looked). 99 Bottles of Beer is a good beginners programming exercise, because it involves a loop, with a variable changing within that loop. You have to handle an exception (the 1 bottle of beer at the end causes a change from plural language to singular language).

I happened upon the link recently when cleaning out my bookmarks. (the first indicator that I am avoiding real work). I checked out FileMaker and the last version there is FileMaker 3. Many things have changed since those early days. We have script variables and web viewers now, so I undertook to update the code with a version for FileMaker 11 (the second indicator). I learned a few things in doing this.

Clearly this needs to be updated….


FileMaker 3 is the latest version? Someone needs to fix this.

The Old Way of doing things


Note that in FileMaker 3, it was necessary to create fields to hold the number of Bottles and the text for the song. The programmer also chose to use the Speak command so that the database would actually speak the line of text, a nice, but rarely used feature of FileMaker.

My version


Here is my version. I start by checking to make sure there is one and only one record in the database. If there are more, I deleted them all and start again. If there are less, as in zero, I create one.

Next, I clear out the text field and refresh the window. Otherwise it looks like nothing is happening.

Setting up the variables


With FileMaker 11 (actually since FM 7), we don’t need to create fields just to hold stuff for running a script. We can now just declare a variable on the fly. It exists only while running the script. Here you can see where I am setting up the variables, the number of bottles and then the two variations of the line of text, the Regular Line and the Last Line (the one with only one bottle in it.)

The Last Line variable


I am using the equivalent of a merge field by tagging the text I want to replace in the value. $NoOfBottlesOfBeer will be replaced by the number as the loop progresses. The Regular Line variable is basically the same, but using plural language.

Finally, the Loop


The loop runs through and substitutes the current number of bottles into the $CurrentLine text.

I experimented with using a Refresh Window step, but the Speech Module, seems to override everything and the only thing that would allow the screen to refresh was a short pause.

I used the Substitute function to replace the merge text in the $LastLine variable with the actual value.

The Interface


Click the Run button and the script runs, populating the field with the text. Click the ‘With Sound’ option to have the song drive you truly mad. More work could be done to expose all the different voices. Imagine the joy of listening to this in the ‘Deranged’ voice…

I should have quit here, but then, in the drowsy time before sleep set in, it occurred to me that this all could be done without any fields or records. I could feed the text into a web viewer instead of a text field.

Web Viewer version


What you are seeing on the screen is a Web Viewer with HTML created within FileMaker, no web required.

Web Viewer script


This one leaves out the ‘With Sound’ option. It also leaves out the need for any fields or records. All the actions are done within local variables.

I added a variable for $HTML code, basically a wrapper to put around the lines of the song, so I can feed it to the Web Viewer.

I submitted a simplified version of this script to the 99-Bottles website. I felt that the two new features, local script variables and the use of the Web Viewer justified the new version.

The key here is the Set Web Viewer script. All you need to do is add the text URL:”data:text/hmtl,” before your HTML code.

Delete all the records


This was an interesting fact. To run this script, there don’t have to be any records in the database.

Look Ma, no Records!


Note that there are zero records in this screenshot. I deleted all the records and the script still runs. No fields, no records required, just the script and an appropriately named Web Viewer.

What I learned

• Sometimes Refresh Window doesn’t do the job. Use a short pause to cause a refresh. Both reloading the web viewer and the Speech function seems to override the Refresh Window.
• To display a Web Viewer Content, no records are necessary
• I need to do some real work.

Anyone wanting to share my joy in procrastination, the file is here.

Related Articles:
Where Am I? Using FileMaker Go 12 to track your Location
Changing Window Names based on User Input
FileMaker Progress Indicators using a Web Viewer and Animated GIFs

Comments are closed.

%d bloggers like this: