FileMaker TIP: Next Work Day

I needed a function to find the next work day (excluding weekends). I came up with a pretty simple function that I like to call "NextWorkDay" - it takes any date as the parameter (theDate). You can use this as a function, or as a calculation (just replace "theDate" below with the field name).

Let ( [ dow = DayOfWeek ( theDate ); daysToAdd = Case ( dow >= 6; 9 - dow; 1)] ;
Date ( Month ( theDate ); Day ( theDate ) + daysToAdd; Year ( theDate )) )

Pretty simple - but it works well...

FileMaker TIP: Current Quarter Date Range

I do a lot of applications where users want reports or lists that take a date in the current record and filter or find all the records "In This Quarter".

The calculation isn't all that difficult - but it's a cool time saver. You can make a script that returns the result, or make a custom function.

Note that in this formula, I've created local variables - so you can use the $quarterStart and $quarterEnd dates in other places in your script.


Let ([
   $inDate = theDate; //can be passed as parameter
   $mo = Month($inDate);
   $yr = Year($inDate);


   $qStart = If ( $mo > 9 ; Date (10;1;$yr);
                 If ( $mo > 6 ; Date (7;1;$yr);
                 If ($mo > 3 ; Date (4;1;$yr);
                 Date(1;1;$yr)
               )));
   $qEnd = If ( $mo > 9 ; Date (12;31;$yr);
                 If ( $mo > 6 ; Date (9;30;$yr);
                 If ( $mo > 3 ; Date (6;30;$yr);
                 Date(3;31;$yr)
              )))
   ];
   $quarterStart & "..." & $quarterEnd
)

FileMaker TIP: Get Selected Text

The other day I came across the need to grab the currently selected text that the user had selected. I (mistakenly) thought there was already a built-in function to do that... ummm... nope.

So, I came up with a quick calculation to do it:

Middle ( Get ( ActiveFieldContents ) ; Get (ActiveSelectionStart) ; Get ( ActiveSelectionSize ) )

You can package it up as a function, or use it (like I did) in a script to set a variable to whatever text the user has selected. I hope this helps!

FileMaker TIP: Number Suffix

Here's a tip for adding the number suffix - like 1st, 2nd, 3rd, 4th, etc. You can use the formula below as a calculation, or put it into a custom function. If you use it as a custom function the parameter name would be "dataNumber":

Let( [
   isException = Case(
   Mod (dataNumber; 100) = 11 or
   Mod (dataNumber; 100) = 12 or
   Mod (dataNumber; 100) = 13; 1) ;
   last = Mod ( dataNumber ; 10 )
];
Case ( isException ; "th"; last = 1; "st"; last = 2; "nd"; last = 3 ; "rd"; "th") )

Pretty simple - but powerful!

FileMaker TIP: Calculating Age

Calculating the age of something (or someone) is a task that I come across a lot. Here's a simple calculation that will help get you the basics:

Let ([
  today = Get ( CurrentDate ) ;
  days = today - MyDateField;
  md = Date ( 0 ; days + 1 ; Year ( MyDateField) ) ;
  y = Int ( days / 365 )];
  y & " years, " & Month ( md ) & " months, " & Day ( md ) & " days"
)

The reason it works is that FileMaker is good at turning nonsense into dates (FileMaker stores all dates as integers internally). If you tried...

md = Date ( 0 ; days + 1 ; Year ( MyDateField) )

...in a "real" programming language - it would choke. But not so the case with FileMaker! You can (should) enhance the calc by checking the years, months and days for plurality and modifying the strings accordingly:

Let ([
  today = Get ( CurrentDate ) ;
  days = today - gDate ;
  md = Date ( 0 ; days + 1 ; Year ( gDate) ) ;
  y = Int ( days / 365 ) ;
  months =  If ( Month ( md ) = 12 ; 0 ; Month ( md ) ) ];
  y & " year" & If ( y > 0; "s, " ; ", ") &
  months & "  month" & If ( months  ≠ 1 ; "s, " ; ", " ) &
  Day ( md ) & " day" & If ( Day ( md ) > 0 ; "s" ; "" )
)

Happy age calculating...

FileMaker TIP: How To Trap For Modifier Keys

There are times when you want to allow some advanced functionality when a combination of modifier keys (shift, alt, ctrl, etc.) are held down. I've seen some developers go nuts with this - using one button with 5 or 6 functions based on what keys are held down...

OUCH.

I my opinion, not the best way to go - since the user has to "know" to hold down some combination of keys in order to make some functionality work. Instead, the user interface should be easily discoverable - and familiar to use. But I digress...

In FileMaker, you can tell which modifier keys are held down by using the function:

Get ( ActiveModifierKeys )

Here's what you'll get back:

Shift = 1
Caps Lock = 2
Ctrl (Windows) / Control (Mac OS) = 4
Alt (Windows) / Option (Mac OS) = 8
Command (Mac OS) = 16

If all modifier keys are being pressed (on a Mac), the function will return 31 (1+2+4+8+16).

It's no problem if you only want to check for a single key down - you could just use something like this:

If ( Get ( ActiveModifierKeys ) = 1 //Shift is down )
    # Do Some Action
Else
   # Do Some Other Action
End If

It gets a little more interesting when you want to check the combinations of keys - because you would have to trap for all the possible variations (On Windows):

Ctrl + Shift = 5
Ctrl + Alt = 12
Shift + Alt = 9
Shift + Ctrl + Alt = 13

Then if you (or your customer) is on a Mac, you need to add these:

Command + Shift = 17
Command + Option = 24
Command + Control = 20
Command + Control + Shift = 21
Command + Control + Option = 28
Command + Shift + Option = 25
Command + Shift + Option + Control = 29

Like I said, if you, as a developer, are in your right mind - you would NEVER have a customer have to hold down Command + Shift + Option + Control when clicking a button! However, I understand that sometimes "keys happen."

In searching around the interwebs - I found a number of interesting solutions to finding out what key(s) are being held down. There are some great custom functions out there - "KeysDown" by Peter Wagemans over on www.briandunning.com takes natural language-like arguments:

KeysDown ( "shift-ctrl" ; True )
KeysDown ( "ctrl-shift-alt" ; False )
KeysDown ( "ctrl-capslock" ; False )

There's another custom function by Arnold Kegebein on his gallimaufry blog that will create global variables to hold all the key states. You can then use syntax like this in your scripts:

Set Variable [$modifierKeys; Value: get.modifierKeys]
If [$$CTRL;]
   … do something …
ElseIf [$$SHIFT];
   … do something else …
End If

So, whether you use custom functions, create a script, or even a calculation - you'll always know the state of the modifier key!

FileMaker TIP: Checking For Leap Year

There may be times in your solution when you need to see if a certain year is a leap year. Here's the basic rules to determine if a date is a leap year or not:

A year will be a leap year if it is divisible by 4 but not by 100. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400.

So, there are a couple of ways to do it - the first way is to use "brute force" and look at the year to see if it fits the criteria above. It's a pretty simple calculation:

FileMaker 6 and below:

Case(
  IsEmpty(Current Date), TextToNum(""),
  Mod(Year(Current Date),4) = 0 and
  Mod(Year(Current Date),100) <> 0 or
  Mod(Year(Current Date),400) =0 , 1, 0
)

FileMaker 7 and above:


Case(
  IsEmpty(Current Date), GetAsNumber(""),
  Mod(Year(Current Date),4) = 0 and
  Mod(Year(Current Date),100) <> 0 or
  Mod(Year(Current Date),400) =0 , 1, 0
)

If you're using FileMaker Pro 10 or higher (or you want to make a custom function) - here's another way to express the same thing using the Let() function:

Let ( isLeapDay = Day ( Date (2; 29; Year ( gDate ) ) ); If ( isLeapDay = 29 ; 1 ; 0 ) )

This calculation simply creates a new date with the year in the field (or your parameter if you're making a function) and February as the month and 29th as the day. JavaScript will auto-convert this to either Feb 29, yr - OR March 1, yr. So if the day that gets returned is the 29th - it's a leap year, if not, then it's not a leap year.

You can also test if your calculation is working - by looking at this list of all leap years 1800-2400.

FileMaker TIP: Setting or Passing Multiple Variables At Once

There are times when I'm developing a solution that I need to pass multiple parameters to a script - or when I'm in a script, I want to define multiple variables at once rather than having a whole bunch of "Set Variable" steps - you can just use the "Let()" function do all the heavy lifting.

For example - I have a script that takes in a script parameter from a button, checks the current layout and then performs different logic based on the report button that was pressed:

On the button that calls the script I set the script parameter to the report I want:

Now, if I were to change the variables from local variables (that are only valid as long as the script runs) into global variables (are available until the user quits) - I can set all the values as a single script parameter:

Let ( [ $$WhatReport = "Customers" ; $$CurrentLayout = Get ( LayoutName ) ; $$ShowPreview =  0 ] ; "" )

In the above code I'm using the "Let" function to simply define all my global variables and then I can rewrite my script to look like this:

Pretty cool!

Now there are some pros and cons to this approach. On the pro side: I've greatly simplified my script. On the con side - I now have to put that "Let" function on all my buttons (and any other buttons I create) - on every layout that I want to call this script from.

Personally, I'm not a big fan of passing 100 variables from the calling button/trigger/object - unless I absolutely have to. I can't tell you the number of hours I've spent debugging because I've forgotten to set a certain parameter to the right value - plus, I really don't like having a lot of global variables hanging around when I don't need them (you can't set local variables when passing parameters, unfortunately).

Most of the time (there are exceptions, of course) - I try to keep things as simple and encapsulated as possible. So, taking our first script as the example - here's another tip: you can set multiple local variables within your script the same way as we set the global variables in our script parameter.

To do this - we need to execute a script - and because there is no script step to simply "evaluate" or perform this type of action - we need to set a single variable. In my case I'm setting a local variable called "$blah" to this calculation:

Here's the revised script:

I hope this technique will save you some time when you develop your solution!

FileMaker Tip: How and Why To Use Universally Unique IDs (UUIDs)

NOTE: Requires FileMaker Pro Advanced to setup.

When setting up your database - you probably already use some sort of unique record id that you can use when you relate data to/from another table. It's called a primary key. And, if you're like most of us, you probably use the handy unique number auto-enter option:

Now, there's absolutely nothing wrong with this approach - and most of the time - it will work just fine. However, I was working on a project for a customer the other day - and while their solution was working fine - they wanted to be able to create a runtime for their remote salespeople who sell in areas with limited Internet/phone service.

Their problem was - once the sales folks entered the data in their runtime - how to get it back into the system without creating duplicates. After all, if you use a serial number for your primary key - and you have multiple runtimes - everyone will have the same value for the "next record."

For example, if you have a contact list with a serial number as a primary key (let's say the "next" value is 100) - and you create runtimes - then the first contact that Salesperson A creates will have a primary key (ID) value of 100. And, the first contact that Salesperson B creates will also have a primary key value of 100. Then say that each salesperson enters a new invoice (which will also have the same "next" primary key values) for each of their respective new customers.

When you go to import the records back into the "master" solution at the office - you would have to do all kinds of key replacements, finds, updates, etc to ensure that every record ID is unique. That can get complicated very, very quickly.

The good news is - there's a simple solution - it's called a Universally Unique ID - or UUID for short. A UUID is a set of 32 digits that is "practically" unique. Here's the Wikipedia definition:

A UUID is a 16-byte (128-bit) number. The number of theoretically possible UUIDs is therefore about 3 × 10³⁸. In its canonical form, a UUID consists of 32 hexadecimal digits, displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 digits and 4 hyphens). 

For example:
550e8400-e29b-41d4-a716-446655440000

Whoa! Cool, right? So how in the heck are you supposed to use that as your primary key in FileMaker? The short answer is: you need to create a custom function - and set the auto-enter value to a calculation. Don't worry - it's easy!

First - cruise on over to Brian Dunning's fabulous repository of custom functions - and search for "UUID" in the search box:

You'll see a number of different options. I like the one called "UUIDRandom" written by Jeremy Bante of The Support Group. Just go to the detail page for the function, copy the code in the box - and go back into FileMaker and choose File -> Manage -> Custom Functions. Click the "New..." button and enter UUIDRandom as the function name, and paste the code into the space provided:

Click OK and dismiss the Custom Functions manager window. Now choose File -> Manage -> Database... and in each of your tables create a new text field. You can name it whatever you want - but I suggest that you use "UUID" somewhere in the name.

Once the field is created click the "Options..." button and click on the "Auto-Enter" tab and click the "Specify..." button next to "Calculated value". Enter "UUIDRandom" (no quotes) into the calculation dialog:

If you're building a new solution - you're all set! However, if you're trying to integrate a UUID into an existing solution - there are a few more steps - but this whole process is strictly a one time thing:

• For each table - you need to create a UUID value for all the existing records:
• Add your UUID field to an existing layout (temporarily);
• Go into Browse mode and show ALL records;
• Click into the field and choose Records -> Replace field contents...
• Choose "Replace with calculated result:" and enter "UUIDRandom" (no quotes) in to the calculation dialog like you did when you defined the field.

Now you have a unique value for all your records. The next thing you have to do is to create another text field in all your related tables (ones that are linked using relationships) - so that there can be a foreign key that will relate the master record to the children records.

You need to populate these foreign keys in the child table with the primary UUID from the master table. For example if you have a relationship that links Customers to Invoices by Customers::Cust_ID = Invoices::Cust_ID then you need to create a new field in Invoices called Cust_UUID and put the value of the customers UUID into that field.

To do that - we're going to force a lookup of the simply go to your child table (in the above case "Invoices" table) - put the Cust_UUID field temporarily on a layout, choose to replace the field contents via a calculation (like we just did above) - but this time - use your relationship to enter the value of the master record. In our example - we would use:   Customer::Cust_UUID.

Once you've done that - MAKE A BACKUP OF YOUR FILE BEFORE CONTINUING!


The next step is a biggie - so take your time! You need to change your relationships to use the UUID fields rather than your existing "id" fields. In our example - our relationship that linked Customers to Invoices would now be based on Customer::Cust_UUID = Invoices::Cust_UUID.

After making the relationship changes - you may also need to go back into your scripts and if you do stuff like reference the Cust_ID field when setting variables, or you copy/paste it - you need to use the UUID field instead.

Yeah, I know. That's a lot of work.

BUT, in the end - you'll have a solution that uses UUIDs and you won't have to worry about record id collisions - and in the future when you create new solutions - if you use this scheme right from the beginning - there's no "pain" at all.

FileMaker TIP: Passing Multiple Script Parameters (FileMaker 8.5+)

If you do a lot of scripting (and what FileMaker developer doesn't?) - you can cut down the number of scripts (and globals) you have to create by using script parameters on objects. Since you can only send a single parameter to a script (like you can only send a single parameter to a plug-in) I've seen a lot of developers who use a delimited string to send multiple parameters (like you would in a plug-in call). Something like: "red|blue|green".

Then, in their script, they would parse that incoming parameter into 3 values.

But, here's an easier way - just use the List() function to pass the parameters in:  List ( "red" ; "blue" , "green" ) and then use GetValue() to reference the individual values: GetValue ( Get ( ScriptParameter ) ; 1 ). The result would be "red" for the first GetValue, "blue" if you change the number to 2, and "green" if you change the number to 3.


Assume we have this script:

Then we create a button and specify that the action is to perform the navigation script (at the bottom of the "Specify Calculation" dialog is the place to enter your parameters):

Now when you click the button - your variables are all set in your script:

This is way easier than parsing it all yourself!

New Product: Documentorium Lite For FileMaker

OK, OK, I know we just brought our Documentorium for Servoy late last week... but I had a some FileMaker friends/customers ask if there was a FileMaker version available as well. It just so happens there is...

How To Build Simple, Complex Software

Judging from the response I got from yesterday's blog posting about customer-centric design - I thought I'd do a more practical follow-up. The feedback that I got went something like this: "Yeah, well, ok - but how do we accomplish customer-centric design (in processes as well as our software)?".

New TIP Postings - Coming Next Week!

I talk to a fair number of people who either work with Servoy or FileMaker Pro (or both!). I was recently talking to a person who was evaluating Servoy and because I've worked with both tools for so long, I find myself doing things "automatically" - that are often a revelation to the less experienced.

In that vain, I'm going to be posting some Tips for both Servoy users and FileMaker users - and probably some generic Tips as well.

What kind of tips would you like to see?

Everything Old Is New Again

Over the last 6 months or so - I've been getting a fair number of requests to do FileMaker Pro work.

Yeah, I know! Right?

I worked on some pretty cool projects for a wide variety of customers - and found that getting back into FileMaker is like riding a bike - you just never forget.

Sure, a LOT has changed in the product over the years - and there have been many excellent improvements (can you say Script Triggers, Conditional Formatting and Functions!), and you can now specify a calculated result for just about everything in the world (yay!).

But somehow FileMaker, Inc. has managed to keep most of the "good stuff" that makes FileMaker... well, FileMaker. It's still fairly easy to use (just don't get me started on table occurrences!), the scripting engine is the same (with more and better functions available) - and the Server product is just much better than in the "old days."

After doing so many years of Servoy-only development, I must admit it was a bit of "an adjustment" getting back into developing projects in FileMaker. A lot of the keyboard shortcuts that my mind had (somehow!) managed to remember still worked. Defining fields and setting validations, global fields, auto-enter options and all the rest were just as I had remembered.

In the few times that I got stuck with a function or script (looked logical - just didn't "work") - I was able to easily find the answer in one of the many FileMaker "superstar" sites (BrianDunning.com, AngelCityData.com, FileMakerMagazine.com, FMForums.com, FMPro.org, etc, etc.).

I found that I had to get myself back in the "FileMaker mindset" when creating scripts... where there is usually only one or two routes to get from A to B - and normally only one way from A to C. I also had to remind myself that when finding data you need to actually go to a layout that's based on the table you're data is in... but once I got back into the "mindset" - everything went as smooth as silk.

Along the way I was able to re-connect with some very old and dear friends from years past - and have had the chance to chat with people that I literally haven't talked to in 5 or 6 years (sometimes longer).

Although I was unable to attend this year's FileMaker Developer Conference in San Diego - I'm looking forward to have a beer (or two) with some old friends and looking forward to learning all the new, subtle tricks from today's "young guns."

To celebrate Clickware's re-entry into the FileMaker services/consulting/training arena - I wanted to contribute SOMETHING to the FileMaker community at large. So, I've put together a new series of 4 videos aimed at helping FileMaker developers deal with end users who may be new to FileMaker, and hopefully, reduce the amount of "How do you do a find" support questions.

The videos are FREE, and I have some other ideas for a "Developer" series as well - so if you have ideas about a FileMaker-related video you would like to see - please email me and I'll see if I can get it going.

I guess the pundits are wrong - you CAN teach an old dog new tricks! Rock on, FileMaker 11!

Why 2K?

Editor's NOTE: This is a moldie oldie that I pulled from a text dump archive. None of the links will work anymore (or 99% won't) - but the names and companies have NOT changed.

So it's a new year (and new Millennium - well, not until 2001, but close enough) and I just got back from the MacWorld 2000 convention in San Francisco, CA. I went to do a (free) session called "FileMaker and the Web - Just Do It!". The session went very well (250-300 in attendance - standing room only) - and hopefully they each learned something they didn't know coming into the session.

I've been going to this show every year since 1991. In the past it has been a great place to see other FileMaker developers, meet with FileMaker engineers, get the latest "buzz" on upcoming FileMaker releases, etc. I've also managed to do quite a bit of business either on the show floor, at a lavish FileMaker party, or because of personal introductions to potential customers by others. This year - not so much.

On the good side: I got the chance to see many old friends at the FSA dinner, had a lunch or two with some long-time FMI employees (and ex-Claris folks) and got a chance to talk with the FMI big wigs for an hour before the FSA dinner (at the Partner meeting).

On the room-for-improvement side: The show seemed small - although it still occupied both the North and South halls of Moscone Center in downtown San Francisco. A lot of the booth space seemed to be cancelled at the last minute because there were lots of "blank" spaces in the middle of the aisles that were hastily filled with food court chairs. There were a lot of hardware vendors hawking everything USB. In horrible colors. Everywhere.

Although the Apple booth was interesting, and they had some cool Cannon digital video cameras above each of the DV iMacs lining the booth, there were no "drop dead sexy" showings of the Air Port, no announcement of the 17 inch iMac, no G4 PowerBooks, etc. They showed off the OS X "Client" software - and it was pretty cool. Transparent drop-down menus (so you can see the video you're playing in the background), HUGE icons everywhere, a new Finder that will do a scrolling view (to the right) of folder contents... oh yes, the three little "stoplight" buttons in the upper left; green for "open", yellow for "show title only" and red for "close." (http://www.apple.com/macosx/)

There were many major Mac software vendors who just didn't show up - and those who did seemed to have only incremental upgrades to their titles. The classic was Microsoft. The Internet Explorer 5.0 demo was 5 minutes - showing off the new COLORS on the browser ("Yes, if you want blueberry, you got blueberry. If you want Tangerine - you got it!"), a couple of very cool new drag-and-drop features, and 10 minutes playing a lame game show that asked questions like; "What product lets you create cells and columns" (answer: Excel). Internet Explorer 5.0 was also named best of show... 'nuf said. There was a woman in one booth dressed as a printer (NO KIDDING!) - and another booth had a live jazz band. Ouch.

Maybe I'm expecting too much. I long for the "good old days" where seeing everything in the show would require 2 or 3 days. Where you scoured every single booth because sometimes you'd find extremely cool software written by a couple of guys in their garage just starting out. Where ALL the major software vendors would hold their "big" announcements for the show. A time when computers came in two colors; beige and black (NeXT). A time when losing $5 million a quarter was a BAD thing and the only companies with over a billion dollar market valuation were names like Coca Cola, Time Warner, etc.

Maybe next year I'll just fly up for the day.

DevCon-A-Rama

Editor's NOTE: This is a moldie oldie that I pulled from a text dump archive. None of the links will work anymore (or 99% won't) - but the names and companies have NOT changed.

Whoa - I can't believe how fast time flies! It's been five months since my last column... For those of you who read this column (bless you all!), I sincerely apologize for the long delay in getting "back into the swing" of things.

A lot has happened in the past few months:

• ClickWare merged with Stratisoft
  
  
• I've been shuttling back and forth between the Stratisoft headquarters in London
  
  
• I've been busy honing my skills with Oracle, Microsoft SQL Server 7.0, ASP (Active Server Pages), IIS (Microsoft Internet Information Server), ColdFusion, Lasso 3.5... well, you get the picture.
  
  

Besides all of the great technology stuff I've been working with - I've had the pleasure of meeting tons of new FileMaker developers all around the world. It's extremely exciting to meet people from Holland, Australia, England, France, Sweden, South Africa, etc. and talk about the ins-and-outs of FileMaker. It's amazing that everyone I've spoken to has the same problems/workarounds and basically the same feature requests: faster, better, and SOON! More on that subject in a moment...

Also, I had the opportunity to speak at this year's FileMaker Developer's Conference in San Diego. I believe the official attendance was around 1,500 people (almost DOUBLE last year's attendance)! It was a truly amazing sight. Wall-to-wall FileMaker enthusiasts from every walk of life. I personally spoke to policemen, firemen, school teachers, state government officials, in-house corporate developers, well-known FMP developers, new FSA members all gathered in one place - AWESOME.

I spoke on several subjects - Beginning Web, Intermediate Web, and "16 FileMaker Pro Secrets You've Just Gotta' Have" (co-lead with famous and fabulous Marc Norman of Prefix). We had near standing room only crowds for all the sessions - and I HOPE everyone who attended any of the sessions learned something new and/or cool.

In addition to the conference sessions, I also co-taught a Stratisoft post-conference training session on ODBC with the Jan Aleman (better known as Taff) of PDM the makers of the ODBC Plug-In for FileMaker. After resolving some initial technical snafus - the class went well. I even learned something from Jan about the ODBC plug-in that I didn't know before! In the class we learned all about the plug-in technology; ODBC and how it works; and we even built a fully-functional, cross-platform user interface in FileMaker that searched, added, updated and deleted data in a SQL Server 7.0 database running on a NT machine. Cool stuff!

Enough about me. The absolute highlight of the show for me was when FMI demonstrated the new 5.0 version. Unlike other NDA (Non-Disclosure Agreement) sessions that were held in the past, FMI really delivered the goods in regards to 5.0. They showed the new Web Companion and all the groovy technology that it supported, as well as the new features of the 5.0 application, they even showed (via video) the new FileMaker Pro 5.0 Server. Now, since that entire session was under non-disclosure, I can't really go into heavy details about what was said - but if you read the rumors floating about on Mac The Knife or you were lucky enough to catch the stuff posted on the Apple Insider before it got pulled... all I can say that there was that and more.

On a different note, for those "propeller heads" (techno-geeks) out there - I find the new technologies supported by IE 5.0 (especially on the Windows platform and coming "in fall" to Mac OS) especially interesting. The new buzz is XML and if I were interested in using some cutting edge technologies - I'd run out an get a book on XML. Although only IE 5.0 on Windows (at the time of this writing) supports a XML parser on the client, there are ways to use XML with IIS and ASP to deliver HTML out to any browser. Since FileMaker 5.0 will support being a ODBC datasource (see the public beta of the new FMP ODBC drivers at: http://www.filemaker.com/products/odbc/preview.html) I predict that we'll all be seeing many more uses for XML in the days and months to come... so you might want to start learning about it SOON.

Thunder Down Under

Editor's NOTE: This is a moldie oldie that I pulled from a text dump archive. None of the links will work anymore (or 99% won't) - but the names and companies have NOT changed.

G'day mates! I just got back from a trip to the land "down under", and let me tell you - there are some extremely excited FileMaker folks there.

Before I get to the details - let me apologize for the length of time that has elapsed since my last column. Between my newly minted Oracle skills and a whirlwind travel schedule, I allowed outside distractions (like keeping the lights on!) distract me from updating this homespun rant. I pledge to keep the updates coming on a more timely schedule in the future.

As I was saying, my wife, Brenda, and I just completed a two week adventure to the lovely land down under. After an extremely trying plane trip (3 movies, 4 sitcoms, 2 meals, 3 snacks and 14 hours) we arrived (at MIDNIGHT) to find out that the hotel was not expecting us for another three days. In typical Aussie customer service-driven fashion they leaped tall building in a single bound to accommodate us. We were granted a room with a hot shower (a GOD SEND) and a comfortable bed (chocolate mints on the pillows).

After sleeping a whopping five hours, my wife and I awoke to the full moon splendor of Darling Harbour (yes, I know - but that's how THEY spell it!). We walked out of the plush hotel (Park Plaza) across the street to the newly remodeled harbor boardwalk for a preliminary look-see. We were greeted by over 150 young people exiting a nightclub (quite sober - drinking WATER) at 6:00am and we instantly fell in love with the beautiful, tranquil city of Sydney. We walked around for about an hour, then had an "interesting" breakfast at a local eatery. When we asked for coffee, we were assaulted (in a nice way) by the dozens of choices ("short black, tall black, short white, capuccino, capuccino with milk, etc., etc., etc.). Being the Aussie "newbie", I asked for "just plain coffee". I received a nice steaming mug of... well... mud. I mean this coffee could strip paint! Normally a black coffee drinker, I doused my beverage in fresh cream and sugar (in neato pixie-stick-like packaging), and even then I was bouncing off the walls.

Being the consummate "tourista" we proceeded to the Sydney opera house, the Sydney Harbour Bridge and the trendy boutique neighborhood "The Rocks", finally settling at the FABULOUS bay side restaurant "The Oyster Bar." We enjoyed a Lobster Salad (I mean fresh-off-the-boat 1/2 a Lobster and some salad) with an exquisite bottle of Australian white wine, a beer, coffee and dessert (about $50US), returned to the hotel and crashed.

We were met my by our most gracious FMI representative (Steve McManus) at the airport at 6:15 am and were on the plane to Melbourne at 7:00am. We arrived in Melbourne and were whisked away via hired car (a taxi with no sign on top) to our hotel (the All Seasons Premier Grand Hotel). I put my bags down, and caught another taxi to a customer meeting (with our friends at "Customers for Life" in Cantebury). From there it was back to the hotel for a quick in-room rehearsal and then off to the Melbourne FSA meeting at yet another BEAUTIFUL hotel in downtown Melbourne (the Rockmans Regency Hotel).

WOW. What a meeting! Let me tell you - these FSA members were dressed for success. FMI provided pre dinner cocktails (VERY nice touch!) in the hotel lobby bar and the INCREDIBLE PR team from Kinetics Pty Ltd (THANK YOU Jody Lennon and Matina "don't call me Matilda" Mrakovcic) set up our computers and arranged all the last-minute details that combined to make the evening a terrific success. There were over 30 hard-core FMP developers in attendance and we all had a very nice time.

After the meeting it was back into a taxi, back to the hotel (around midnight), and up the next morning for two FileMaker Inc Conferences at the Melbourne Convention Center. As I awoke at 5:30am in preparation for a 7:00 am rehearsal, I was met in the lobby by the tireless Steve McManus as well as the ever-ready Aussie Webmaster Steve McDonnell. These guys were FRESH and READY to go. I mean, my gosh, you would think they had 10 hours sleep...

We did two seminars in Melbourne - one at 9:00am, and one at 2:00pm. I was only one tiny part of the team - there were four presenters in all. I talked about ODBC connectivity (using my G3 PowerBook I imported data from an Oracle 8 Enterprise database and Excel to create a mail merge letter, and showed some graphic tips and tricks), I was followed by Steve McDonnell who showed off FileMaker to Web connectivity (using Instant Publishing and HomePage 3.0 - truly an INSPIRING DEMO!), followed by a "tea" break (coffee, tea, and cookies... ummm... I mean "biscuits"), then the trainer extrodinare Ardsley McNeilly and the talented CD-to-FMP team from Debrany's in Singapore.

After returning to the hotel at 7:00pm we took a taxi to the airport for the 8:15pm flight back to Sydney. Wednesday night I did a seminar at the Apple Headquarters in Sydney along with the presentation team - and fell into bed at 10:00pm on Wednesday night. I was at the Powerhouse Museum at 8:15am (I was supposed to be there at 7:00am but I had a BAD sore throat and head cold) only to find out that the AV completely screwed up (couldn't project the computers to the screen). Thanks to Jodi Lennon and her team from Kinetics Pty Ltd (AGAIN!) they had a cab waiting to get a projector from a local dealer (along with a technician) and got us back on track - all before the 9:00am deadline.

After a rip-roaring two seminar sessions (ending at 5:30pm), we got in yet another taxi, and went to yet another beautiful hotel (the Park Hyatt Hotel) for the Sydney FSA meeting. These folks are FIRED UP about FileMaker! I met a lot of fun, interesting, and TALENTED developers who were (and are) true FileMaker fanatics. We had a wonderful pre-meeting cocktail hour, followed by yet another great presentation by Jodi Lennon and Steve McManus. Then it was my turn.

In typical style, I was a bit BRASH (I know, it's hard to believe), but I hope the presentation was relevant. Many thanks to Bob, Charmaine, and Patrick of BCP Distribution (the outfit that fulfills all the FileMaker orders in the Pacific Rim) - you were all incredibly good sports and I thank you for the great conversation and your incredible hospitality. At the end of my Robin Williams inspired diatribe, Steve McManus was kind enough to present me with an EXTREMELY COOL FileMaker watch. Needless to say, I was AGAIN completely blown away by the kindness, hospitality, and excitement from both my hosts and the FSA members. After a quick nightcap at the Marriott bar next door it was back to the hotel and a good night's sleep.

The next day (Friday), I wasn't feeling my best, but Brenda and I "managed" to have a great lunch at a local pub (don't order a burger with "the lot" or they'll put an EGG on your burger!!) and then explored a local casino. Las Vegas - look out! This air-conditioned mecca (which is why we even bothered in the first place) is very much like Las Vegas - except you have to pay for drinks (even when gambling) and the slot machines (called "Pokies") are DIFFICULT to figure out (even for a programmer!) - we had a great time. Brenda even won $350 AU! Cool!

From there, we were the guests of Steve McManus (and family - including dog "Buddy") and I was fortunate enough to be part of the crew for the final yacht race of the season (Steve is a HUGE sailing fan). WHAT AN EXPERIENCE! Two minutes of hell (when we "tacked" into the wind, or put up the big "kite" sail) and ten to fifteen minutes of heaven (sailing between the course pylons).We came in 4th (out of 32 boats) and FIRST in Steve's club - which was good enough to earn him the club championship! Boy, what a FANTASTIC experience!

As soon as we got back to the dock, Steve and I jumped into the car and met our wives at the "Footies" (Football or "Rugby" as my Aussie friends refer to it). Now, I must tell you - this is a no-holds-barred ballroom BRAWL! This makes the NFL look like a game for WOMEN. Basically - you get 6 "downs" and there are NO TIMEOUTS or breaks. Once you get tackled (and I mean TACKLED! - there was literally blood flowing from heads, knees, lips and arms!) you immediately pass the ball to a team mate and they then get their asses kicked, and so on and so on. What we call a "touchdown" is not as easy. You have to PLANT the ball in the endzone - no plant, no "TRY" (touchdown). WOW.

The beers were COLD and the pies (like Swanson Pot Pies - just no tins) were HOT, and the action was great. The only wrinkle was that the entire event was sponsored by Pepsi - and in typical YANK fashion - they overdid the sponsorship bit (i.e the sky divers at half time and the Pepsi cheerleaders with painted-on uniforms and a Pepsi logo on their asses...).

On Sunday we went on a "Bushwalk".. and I thought it would be a nice nature walk of a mile or so... but NO! It was a LONG walk (took 4 1/2 hours!!) and I was bushed. On Monday morning Brenda and I flew to Coolangatta (an hour north of Sydney) on the Gold Coast of Australia. Steve and his wife (name concealed to protect the innocent) graciously lent their timeshare condo to Brenda and I for the week. WOW!! Now I can't tell you the exact place we stayed (I'm under strict non-disclosure for fear that everyone in the WORLD would mob the place), but I CAN tell you that the bay we overlooked was FABULOUS. The water was 78 degrees and crystal clear. The surf was 4 to 5 feet and was excellent. I can also report that the food, surf, wine, local beers and Steve's EXTREMELY tolerant and FUN friends were without question the HIGHLIGHTS of the trip.

In conclusion, if you haven't been - GO TO AUSTRALIA - it's lovely! To my new mates down under - good onya and thanks for a fair dinkum time!

Out With the Old, In With the New!

Editor's NOTE: This is a moldie oldie that I pulled from a text dump archive. None of the links will work anymore (or 99% won't) - but the names and companies have NOT changed.

Happy New Year!!!

As the pre-Y2K festivities around the world begin in earnest, and every single show on television features its Year-In-Review episode, I too wanted to take a moment and reflect on the past, revel in the present and prognosticate on the future.

1998 brought a lot of change:

As the winter leaves fell from the trees, so Claris dropped all its consumer products and became FileMaker Inc. (FMI), and FileMaker Pro became 4.1. Along with the new name and logo came many new faces, and lots of changes within the organization. The painful transition caused quite an upheval in both the developer community as well as marketplace. Seeing many of my friends and cohorts at Claris being laid off by the dozens was very difficult. However, the resulting company is now focused on a single product, and, in theory, should be a lot more responsive to the developer community and the general marketplace. I caught a small glimpse of this new focus at the HUGE turnout for the third annual Developer's Conference in Monterey. The conference goers were (and are, from what I can tell) enthusiastic and the mood was optimistic about FileMaker's future (both the company, and the product).

The Web - basically everything changed. From tools for developing content to servers, to mergers (AOL and Netscape - who knew?), to Mr. Bill being center stage in yet ANOTHER Federal probe, the only thing consistent about the Web in 1998 is that it was (and is) a constantly changing landscape.

At ClickWare, we left behind our "terrible twos", released our most popular product to date - ClickStats Pro, created the ClickWorld web site, folded the ClickWorld website into this site, completed the technical editing of the first year of the FileMaker Advisor Magazine and in general, had a great year.

1999 will continue to be a year of change:

The merger fever of overvalued Web companies will continue, as well as the bevy of newly incompatible browser versions.

FileMaker Inc is rumored (according to MacTheKnife - http://www.mactheknife.com) to have a new release in the works - including a new Server version and a new Developer Edition. Hopefully, FMI will "lift the kimono" a bit higher at the upcoming MacWorld in San Francisco, to give us all a glimpse of what the new version will look like.

Hopefully, 1999 will see XML will catch on and the good folks at FMI will give us a way to use XML with FileMaker. Again, hopefully, FMI will also provide more ODBC functionality (i.e. FileMaker as an ODBC data source, FileMaker WRITE OUT SQL, etc.)...

ClickWare has a number of new products in development - slated for release by early summer. We are also on verge of becoming an Oracle Certified Applications Developer - so watch for more SQL tips and goodies (also contact us if you need Oracle and/or FileMaker Pro custom development).

And finally, I want to thank the thousands of you who continue to visit this site, and for all your great suggestions and comments. We want to continue to be a leading resource for FileMaker information in 1999 and beyond - thanks for your continued support.

Virtuosity

Editor's NOTE: This is a moldie oldie that I pulled from a text dump archive. None of the links will work anymore (or 99% won't) - but the names and companies have NOT changed.

So I'm in day 3 of a 5 day, $2,000 Oracle training class in Costa Mesa - between the packed classroom, complimentary expresso and deli-fresh bagels - I have an epiphany: FileMaker ROCKS!

Don't get me wrong - if you want to run an ATM, or you have a million or so records to process - a SQL based RDBMS (Relational DataBase Management System) is the way to go. For EVERYTHING else - just use FileMaker.

Take a class in one of these gigantic database systems - and you'll find out how much we take for granted in FileMaker. Like finds. Like WYSIWYG (mostly) reporting. Like data entry screens. Like the ability to assign elements different colors. Like ScriptMaker. Like defining calculation fields. Like... well... everything else!

For example - do a find in FileMaker for all customers in California or Florida with a balance due greater than $5000. The FileMaker way: Choose "Find" from the "Mode" menu - enter "CA" into the state field and "5000" in the "Balance Due" field, choose "Duplicate" from the "Mode" and change "CA" to "FL" - click continue. Done.

Now the Oracle way - first, go to the COMMAND LINE program (SQL*Plus), and enter the following:

select * from c.customers, i.invoices where sum(i.bal_due) > 5000 and (c.state = 'FL' or c.state = 'CA') and c.customers = i.cust_num

This is assuming you know what the fields are (you can use desc customers and desc invoices), and you have to know how to do an equijoin... etc., etc.

Now, TRAIN all your employees and customer's employees to do this kind of query. Even IF you do get the syntax correct - that's not the report - it just "prints to screen". If you want a report (god forbid!), there's another 3 day class on how to write SQL to get the report back. And, if you want a "Data Entry" layout - there are TWO more 4 day classes for the "easy-to-use" forms designer... OUCH!

Of course Oracle can run on just about anything - minis, Unix, NT, Linux, etc. (NOT Mac OS) and it can support 25,000 users (PLUS), and has the ability to manage up to 512 petrobyes (512,000 GIGAbytes)... BIG iron, BIG development costs and time, BIG databases. Which brings me to the point of this column:

The beautiful Oracle training center had Dell NT Workstations connected to a Sun server (in the Bay Area) via multiple T-1's and T-3's. We learned, then practiced our little examples, and the performance was VERY good. However, due to the fact that it was TWO people per computer (and my "training buddy" was less than cooperative) - I wanted to practice in my hotel room. I got back to the hotel, fired up NT Server 4.0, fired up Oracle Enterprise Edition, and was able to practice to my little heart's content. Did I mention that I don't have PC laptop? I do, however have an APPLE G3 PowerBook - and that's what I ran Oracle and NT Server on! Yep - Virtual PC 2.01 does it again. You can bet that lugging ONE computer is THE way to go. I'm planning on loading Linux and BeOS as well...

FileMaker Sequel

Editor's NOTE: This is a moldie oldie that I pulled from a text dump archive. None of the links will work anymore (or 99% won't) - but the names and companies have NOT changed.

That's "sequel" as in SQL (Structured Query Language). For those of you "on the fence" - wondering if you should upgrade to FileMaker Pro 4.1 - the answer is - kinda'-sorta'-ish.

If you don't know what SQL is - don't bother upgrading. However, EVERYONE should upgrade to 4.0v2 which is available on the FileMaker, Inc. site (www.filemaker.com).

Mac OS Users - the upgrade will clean up some bugs, speed network file opening, and other GREAT fixes - so upgrade when you can.

Windows 95 or NT Users - DO THE 4.0v2 UPGRADE NOW! FMI fixed some HAIRY BUGS in 4.0v2 (which is actually the same code fixes and updates in 4.1). For example - after working in ScriptMaker for a while - if you move around script steps - the machine would HANG (the first time). If you restart and edit the same script again (i.e. moving around script steps) and it HANGS again - there was a HIGH probability that your file would be PERMANENTLY destroyed. This bug has been fixed. There are a number of other bug fixes, speed enhancements (replace works MUCH faster), etc. It's well worth the time and effort of updating.

Also new to 4.1 are (from the FMI Web site):

EURO CURRENCY SUPPORT - FileMaker Pro 4.1 now provides cross-platform support for the new European currency symbol. To use the Euro symbol with FileMaker Pro, you must also be using an operating system and font that support the new symbol.

SUPPORT FOR CUSTOMIZED SOLUTIONS - In FileMaker Pro 4.1, you can see and use features enabled in the FileMaker Pro Developer Edition application, such as Kiosk mode, custom script menu name, custom help and about menu options and the removal of master access. For example, if a solution has been created using the FileMaker Pro Developer Edition to run in Kiosk mode then FileMaker Pro 4.1 will also run the solution in Kiosk mode.

I still haven't gotten the Kiosk mode thingie to work yet... maybe I'm just doing it wrong - but as you can see - most of the improvements have been "under the hood."

Back to the issue of SQL... the biggest (and only VISIBLE) update to FileMaker Pro 4.1 is the addition of the ability to IMPORT data from a SQL database via ODBC. If you don't know what ODBC is - don't bother to upgrade to 4.1.

For those of you who DO know what SQL and ODBC are - here's the "skinny" on ODBC/SQL integration in 4.1:

• You can IMPORT from a SQL datasource via ODBC (cross platform) by using the traditional "import" menu item in the File menu (choose the new "ODBC" file type from the pop-up menu [or combo box] when you're choosing the file to import from);
  
  
• You then select the datasource alias (has to be pre-configured with the ODBC control panel) and enter a username and password;
  
  
• You CAN specify the SQL select statement by either typing it in - or by using a 3 panel "assistant" to select the datasource, choose the column(s), and structuring the SQL where statement;
  
  
• FileMaker then does an IMPORT of the data (just like any other import) you specify the order and matching fields in your FileMaker db (just like any other import);
  
  
• You CAN script the auto-importation of data from a datasource (it stores the SQL select statement, data source, username and password with the import order - so DO click the "Restore" checkbox in the "Import" script step).

However there are some SERIOUS limitations with the released SQL plug-in in 4.1:

• You CANNOT WRITE back to a SQL datasource;
  
  
• You CANNOT use a FileMaker database as an ODBC datasource (i.e. no "READ" capabilities);
  
  
• You CANNOT specify a field for the SQL statement (i.e. each import from each datasource for each set of columns for each "where" statement must be scripted SEPARATELY);
  
  
• You CANNOT script the datasource name, username or password (would be able to make import connections "on-the-fly").

If you need the ability to WRITE back to a datasource - check out the EXTREMELY COOL SQL plug-in developed by Professional Data Management and Rumora Automatisering en Advies (http://www.profdata.nl/pages/english.html). From their website:

"The ODBC/SQL plug-in, available for download on this site, allows users to send any SQL-92 command from within their Filemaker Pro database to an ODBC datasource. In doing so, they can create, modify and delete tables and/or rows. Data can be sent to and retrieved from the ODBC datasource. The upcoming version 1.2 of the plug-in even allows users to import pictures, sound and movies from an ODBC datasource into a Filemaker Pro database. It will furthermore have support for ANSI and OEM character sets."

As of this writing - this is your only option for using a FMP database as a SQL or ODBC Client. Hopefully, FMI will add true ODBC compatibility in the near future.