FileMaker Fever

FileMaker 7 Wishlist: We need a relationships list too

With large database projects like mine with 40 base tables and 140 table occurrences, I need to be able to quickly lookup a TO. I really do. Maybe I don't have the best organized database diagram. But still. I've heard rumblings that improvements in the Database diagram area are in the works. All I can say is - the sooner the better.

FileMaker 7 Migration Tip: Use FileMaker 6 too

I should have started doing this sooner. I've found that the FileMaker 6 versions of files I'm migrating to 7 are really handy. I can be in the Database Design dialog of 7 and switch to FileMaker 6 and open the Define relationships dialog for the file I'm working on without disturbing my 7 dialog.

One of the chores of moving FileMaker 6 files into a single file 7 solution is fixing calculations that break in FMrobot because the relationships they are based on aren't defined yet in 7. What you get is a commented out calculation with the FM6 relationship name. I can open up the 6 Define Relationships dialog for that named relationship and see which fields I need to connect in 7 to make my calculation work.

To save time, I've made screenshots of the Define Relationships dialogs in 6 so I have a list of the names of the relationships and matching fields. I widen the dialog columns first so that I can see the full relationship and field names. Then I keep those lists in one corner of my screen for reference as I work in 7, creating relationships in the database dialog. Works great.

Another help is to open up the files you are migrating in 7 and use them for reference - this is also where you'll do your copying and pasting of layouts. So when I'm working with the Jobs table in my single-file database, I have the 7 version of the individual Jobs file open too. Looking at the simpler Relationships diagram for this single table is much easier on the eye - especially if you spend a little while organizing that diagram. I color-coded my individual file diagrams, so they were very usable for reference (this was when I thought I was going to release a multi-file 7 solution). Since the names are inherited from 6, it's easy to see the 7 version working and then replicate that in the single-file diagram.

As you might imagine, a big 20" screen or two screens can be an advantage here. If you've got the real estate, have these open:

Textedit: you'll want a text editor handy to hold copied calculations, relationships and field names.

Preview to see your screenshots of FM6 define relationships screens

FileMaker 6 versions of the files you are migrating.

FileMaker 7 versions of the individual files you are migrating. I keep them stacked up to the side so I can open them while having my single-file 7 database open as the main event.

This set-up will make you quite efficient. If you can spare the change, Waves in Motion's Analyzer 4 can be helpful too. More on that later.

FileMaker 7: Printing Field Definitions

If you've had trouble printing field definitions in FileMaker 7, it's because the functionality has moved and improved. The print button is now in the field list under Define Database in the lower left corner.

The second thing to know to avoid frustration is that the Print button only prints selected fields. If one field is selected, only one field will print. If you want the full list of field definitions, just select all (Cmd-A or Cntrl-A) when in the field list and click the Print button.

You can now be selective about which field definitions you print. It's not an all or nothing proposition anymore. Just shift-click to select a group of fields and cmd-click to select individual fields.

Now you can save trees when you print field definitions. You don't have to print out a really long list just to see a few calculations in hard copy. Another benefit that developers will appreciate is that you can give your customer a selective list of field definitions for documentation or training purposes. That could be handy.

FileMaker 7 and 6 hot deals

I was surprised to see that eDirect Software is selling FileMaker 7 for Windows full copies CD-only without the manual and box for $109.99. They are also selling FileMaker 6 for $69.99. I got directed by a Google ad on FM Forums. You can call them toll free at 866-449-5567. I'm not making money off this in case you are wondering. Just an amazing offer if you are in the market for FileMaker 6 or 7.

I haven't bought from eDirect before but they've got something that says "Overall Store Rating: 5 stars Amazon Marketplace" featured prominently and my guess is that they couldn't get away with that if it weren't true. Worth checking out if you need some extra copies of 6 or 7 for Windows.

Free FileMaker 7 Custom Functions

I just googled for FileMaker 7 custom functions and found Brian Dunning's nice little database of free custom functions. There are 121 right now. Here are some quick favorites:

Color (text). This custom function has about 150 pre-programmed colors in it and also will handle hex colors if you lead with a # symbol. Geoff Coffey.

CreditCardName ( CreditCardNumber ) Based on Brian Dunning's Card Validator, takes 15 or 16 digit credit card number with or without dashes and returns the card type. Also checks for validity. Geoff Wells.

MoneyToWords(NumberField). Takes a numeric value and converts it to words for a check. Example: 1234.56 = One Thousand, Two Hundred Thirty Four Dollars and Fifty Six cents. Don Wieland.

RightPosition ( text ; searchstring ). Shows everything to the right of the search string in text. Example: RightPosition ( "thequickbrownfox" ; "quick" ) = quickbrownfox. Bob Shockey.

You can copy and paste the full formulas for these custom functions into your own custom functions. Check this out!

FileMaker 7 Custom Functions Rock

I just got a big smile on my face a minute ago. I've been careful not to put spaces into table, field, layout and table occurrence names in building Studio Manager 7 because web publishing and other IT tools can't handle spaces. So much for user friendly!

I'm using underscores instead of spaces. It's readable but geeky. But sometimes I want to display something with spaces instead of the underlines. For example, I put the base Table name on each layout. Since I expect to need this ability to use spaces instead of underlines often, I decided to create a custom function for it.

You need the developer version of FileMaker 7 which I have (it's free if you are a FileMaker Solutions Alliance member and otherwise costs an extra $200 over the basic version).

It takes about 2 seconds to create a custom function like this. You just choose File/Define-->Custom Functions and then name it, name any parameters you need and put in the calculation which in this case looks like this: Substitute ( text ; "_" ; " " ). I named the function subSpace.

I got the smile on my face after using my custom function to get the underscores out of the table name with this calculation: TableName = subSpace(gTable).

I forgot to mention that I had already created a Custom Function for Get(LayoutTableName). That's where the gTable comes from.

At the most basic level, these custom functions serve as great little *Type-It-For-Me* shortcuts. I'll be covering Custom Functions more in the future as I find really useful ones to share.

Important Free FileMaker 7 v3 Upgrades

Today FileMaker Inc. announced and made available for download the 7.03 updaters for FileMaker 7 and FileMaker Developer 7. These are must have upgrades according to my sources and are recommended for all FileMaker 7 users. I'm still doing the download myself, so can't give you first hand information yet. The Mac OS X download is 26 mb.

Here's what the download page says about the upgrades:

FM7 v3: Includes changes to text editing, layout editing, scripting, calculations, import/export and more.

FM Dev 7 v3: Includes changes to script debugger, DDR, and Developer Utilities and more.

Go get the downloads now. There's a combo download for Mac and 2 downloads for PC.

The included 8 page ReadMe pdf has a thorough breakdown of what's been fixed, changed and added in these updates.