FileMaker Discoveries

Cool FileMaker 7 Feature #1

FM7 Gift BoxI was pleasantly surprised just a minute ago to see that when I deleted a field that was used by a script, I got notified of that fact. First, it’s good to be reminded that I’ve got a script that uses the field I want to delete. But second, the even cooler part is that FileMaker 7 tells you the exact script that uses the field. FileMaker 6 doesn’t warn you at all when you delete a field used by a script.

FileMaker Discoveries

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 Discoveries FileMaker Tips FileMaker Utilities

FileMaker 7 Custom Functions Rock

FM_Dev7_BoxI 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.

FileMaker Discoveries FileMaker Resources Migrating to FM7

FileMaker 7 Migration Bootcamp Day 1


I’m in sunny Santa Clara, CA staying overnight after spending my first day in FileMaker Bootcamp. This three-day course is fantastic if you are a serious FileMaker consultant/software developer and want to know how to get from 6 to 7 without tears.

You know the expression *there’s a pony in here somewhere*? Well, Danny Mack and Todd Geist of New Millenium Communications seem to have found a herd! As you know, I’m a fan of Danny’s company and tools MetaDataMagic and FMrobot. And I’m not the only one. At last year’s FileMaker Developer’s Conference in Phoenix, Danny received the FileMaker Excellence award “For Outstanding Innovation and Contribution to the FileMaker Platform”.

OK. Enough of the fluff. What did I learn today? This is a very strategic and hands on class and it’s getting late, so I’ll deliver the learnings as bottom lines:

There’s a terrific and sane way to get from FileMaker 6 to 7. New Millenium calls it Convert-Restore-Extend-Consolidate. I’m already convinced by NMCI’s thinking. It makes most sense with complex FileMaker databases to convert your files and just clean them up enough so they work under 7. Then add a new user interface table that points to your converted files and from there build new functionality in your UI file.

Eventually, you can move the old converted files from your 6 app inside your UI file if you like and replace the originals – without the huge effort you might have imagined. This brief description doesn’t do the approach justice. Read Adding a New Interface File to an Existing Solution, Later Consolidating Tables by Todd Geist in the free pdf Upgrading to FileMaker 7: Migration Foundations and Methodologies for the details.

FileMaker 7 is even more different than we’ve been thinking. That’s actually the problem – six thinking! There are so many new possibilities in 7 that confront with overwhelming options. One of the smart things to do, according to these guys, is to shut some of the doors that 7 has opened. I’ve been sensing the same thing. The outside validation I’m getting here is just the extra nudge I needed to follow my instincts and stay sane with this intoxicating product.

You don’t have to know all the answers with 7 to start using it. It is so flexible that you have lots of different ways to change course in midstream as you learn more. Because you can do things so many different ways and the power is virtually unlimited, you have options upon options even when you think you’ve backed yourself into a corner.

A little knowledge can be a dangerous thing, but perhaps these thoughts will help you avoid a pitfall or open up a new way of thinking. I’m hoping to deliver another installment tomorrow night.

FileMaker Discoveries Migrating to FM7

FileMaker 7 Security is Sweet

FM7_box_120px_wide.jpgAfter screwing up my courage and reviewing my error list from MetaDataMagic today, I decided to just go ahead and convert my files and see what happens. Looking at MDM’s conversion issues report and looking at my Studio Manager database, I could see that I’ll need to check a lot of things systematically but the nature of the problems is such that it might work to make the adjustments after conversion.

This breaks the general guideline suggested by FileMaker 7 migration guru, Danny Mack, but I’m planning on multiple trial conversions of my product anyway and wanted to see what would happen when I just threw the FileMaker 7 conversion switch.

First, the good news. The conversion took less than ten minutes. Nothing blew up. I didn’t need a fire extinguisher or anything. Remember, I did clean up the file references before converting and ran MetaDataMagic on my files and reviewed the error list and recommended fixes. For each recommendation, it tells you whether you should make the changes before or after you convert or that you can do it either way.

I have 4 levels of groups and passwords in my Studio Manager files. I wondered what would happen with them. My first experience was good. I was able to use my master password to get into my files after the conversion, using the master password for both the account name and the password as I had read I should. Whoopie!

My luck didn’t last long, though. When I clicked on a script-protected tab or file navigation button, I got my self-authored “sorry, access denied” message even though I was logged in with the master password. I wasn’t surprised – I wasn’t expecting miracles. I just went in and looked at my security calculations and security checking scripts looking for a culprit and couldn’t figure it out.

Next, I changed the master-access account name (formerly the group name) to “master” to match my security calculations but that didn’t work. The calculations were actually using the privilegesetname not the account name, but I was still getting my sea legs and wasn’t crystal clear on the distinction yet.

Notice that I was focusing my attention on technologies I was familiar with. Finally, I got smart and cracked open the new Define Accounts & Privileges command. There I saw what some of you already know – the master access privilegesetname is hard-coded to be “Full Access”. So I just went around and changed my security calculation fields to use Full Access instead of master and things started working!

One other little problem occurred around this. When I opened my files for the first time in FM7, I was offered the option to remember the passwords and I said yes to all. Seemed like a good idea at the time. But then I ran into the problem that I had not entered the master password in one of the files which has a special secret password and now it was set to automatically open with a lower level password by my OS X keychain.

Not to worry, I looked up keychain in the help and found the keychain entry for the file I was having a problem with and after futzing around for a while just deleted it. That worked! This time when I tried to open the file, I got an account and password prompt and could enter the high-end account and password. Good to go!

Now that I’ve conquered these little hiccups in my security learning curve, I am really getting psyched. I know how incredibly easy the keychain stuff is. Opening my FM7 Studio Manager files will be effortless. My system password protects all these saved account names and passwords along with all the other passwords I have. Thanks, FileMaker Inc., for making your password and privileges system compatible with the OS X keychain! FileMaker Inc. is owned by Apple, so I guess it’s about time, but thanks anyway!

For years I’ve steeled myself to the fact that the password and security part of FileMaker has been convoluted and unintuitive. It was the one thing in FileMaker I hated to use. Now, even though the new FileMaker 7 security is much more powerful, it’s easy. It’s straight forward. For a FileMaker geek like me – it’s fun!