FileMaker Fever

FileMaker Help Trick + Cool New Mac Evernote

I pasted a page from FileMaker 9's online help into a TextEdit document because I wanted to have it handy for a project I'm working on. I had not tried clicking on the links in TextEdit but was pleasantly surprised today when I did. The link worked.

When you click on a link from a TextEdit document that contains an excerpt from FileMaker help in it, it links you to and brings up an html document on your computer in TextEdit for that particular topic you linked to. Very cool. At that point, you seem to be able to cruise around without using the Macintosh Help application.

By the way, I then tried copying and pasting some of the help into my FileMaker Notes Evernote notebook (Evernote is now available for Mac as a Beta). Unfortunately, the html links to my local machine were lost in the paste. Hopefully that will be fixed.

Because I think Evernote is a cool note-taking tool for FileMaker developers, I'll mention a few more things. You can see a bunch of instructional videos about Evernote. One reason Evernote is so cool is that you can access your data from any of your machines or from the web or from your iPhone or other mobile phone. The iPhone version of Evernote came out today! One of the things you can do is photograph things like your handwritten notes and diagrams in notebooks or on cocktail napkins. Your iSight camera will work too. Evernote does handwriting recognition on the photos automatically.

Relationship Graph Post-it Notes Pay Off

It took me a while, but now I am a believer. I just posted 2 notes into the relationships graph for Studio Manager 8. One note is a legend that shows what all my abbreviations mean and the other note is a general guide to how I have set-up and organized the relationships graph. I now see that these two notes should be a standard practice for any FileMaker database these days. Nice.

You can pick your colors for the post-its from the color wheel, so have at it!

Technorati Tags: Relationships Graph

Duplicate Record with AppleScript Trick

I received a request to tie my Studio Manager product to iCal yesterday. This is not the first time I have received the request. I am just recovering from a brief illness today and with that excuse thought I would see how hard it would be to AppleScript between FileMaker and iCal.

I have two books that should help me: AppleScript: The Missing Manual and AppleScript: The Definitive Guide. I looked at their tables of contents but didn't see any reference to iCal. So, I then typed "AppleScript+FileMaker+iCal" into Google. I got plenty of hits to investigate.

One of my first hits lead me to reading a comment about speed. And Bruce Robertson had a tip. He said if you run the following two line Applescript in FileMaker you can instantaneously duplicate a record and all of its children records.

So I decided to try it myself.

I created a FileMaker database called AppleScript_Playground and added three tables Contacts, Jobs and Tasks. These are tables in Studio Manager. I put in a few fields in each table. I figured eventually I would get to trying to import a set of tasks for a job into AppleScript.

But for now I wanted to try the trick.

So I created a layout in Jobs with a few fields and a portal to tasks. I entered some tasks. See illustration below.

Bruce said you should not have any calculated fields on the layout if you want the trick to work. I have no idea why, but he was right. Until I removed the last calculation field from the layout, it wouldn't work. You also need to have the ability to create the child records turned on and don't have the auto-enter serial number on the screen.

Anyway, if you have an auto-enter serial number for Jobs and that serial number is part of the key that links tasks to jobs, you are in business.

Here's my applescript compliments of Bruce:

Set sourceRec to (get current record)
Create new record with data sourceRec

There's only one script step in the script: Perform Applescript.

Bruce says it should be instantaneous with up to 50 related records.

At the moment this small triumph encourages me to continue my quest. Maybe this AppleScript stuff is worth looking into. I know a lot of FileMaker buffs swear by AppleScript...

UPDATE: I just tried it with Job Specs, a second related table. The duplicate as is above duplicated the original record plus the related records in both portals on the screen instantly! Try this out as a 20 minute project if you aren't already an AppleScripter who knows this stuff backwards and forwards.

Documented Issues in FileMaker 8 v1 and v2

According to FileMaker Inc., there are some significant issues in both FileMaker 8 v1 and v2. The company is working to release v3 soon which is expected to resolve the new issues introduced in v2. There is a good summary of the issues at the FileMaker Knowledge Base. It looks to me that you might be better off with the v1 release for the time being. If you are using FileMaker 8 or just about to start using it, you should read this yourself

There is one corruption issue in each of the versions which is my main concern. In v1, some batch operations and schema changes can corrupt indexes. Fortunately, those indexes can be easily rebuilt by turning the indexing on and off for that particular field with index problems. In v2, recovering a file corrupts the variable names in calculations. If you are using lots of variables, a new FileMaker 8 feature, you would want to check your variable names if you have to recover the file.

Developers might want to have a copy of both FileMaker 8 v1 and FileMaker 8 v2 around in case they want to switch between the two. For example, if you need to recover your file and you use variables a lot, you'll want to be recovering with v1.

Anyone with more real world information about this is welcome to share it here in the comments. We will revisit this topic with an update when additional information is available or when v3 is released whichever comes first.

On a personal basis, I recommend Studio Manager customers use FileMaker 8 v1 until v3 is available. Neither of the two issues identified in v1 should present problems in Studio Manager. If you are customizing Studio Manager extensively, you should keep these issues in mind, however.

Knowledge Base article #5907 says this about the issues in v1:

• Some calculations that require local client information (such as Get(WindowHeight)) are incorrectly calculated on the server. There are no known workarounds in this version.
• Certain batch operations and schema changes might cause indexes to become corrupt. These indexes can be rebuilt via the Define Fields dialog box by turning the index off, and then on again.

Knowledge Base article #5907 says that "while FileMaker 8.0v2 addressed key problems in 8.0v1, it introduced some new issues":

• When using a FileMaker 8.0v2 product (Pro/Pro Advanced or Server/Server Advanced) as a host with clients prior to 8.0v2, performing find requests on unstored calculations result in all records being found. This is particularly dangerous when used in scripts that perform operations across the found set, such as replacing data or deleting records. To avoid this, make sure that all clients are using FileMaker Pro 8.0v2 or FileMaker Pro 8.0v2 Advanced. You may utilize the Get(ApplicationVersion) function to check the client version.
• When using a FileMaker 8.0v2 product (Pro or Server) as a host, unstored calculations are always processed by the client application. In some network scenarios, this creates a significant decrease in performance, particularly when searching on these fields.
• When printing from Windows based clients, layouts that use fixed margins will experience a shifting of the image by several pixels.
• Recovering a file corrupts variable names in calculations.

Here is the advice from FileMaker Inc.:

During the period prior to this upcoming release, we recommend that you study the list of issues above carefully to best determine which version is right for you.

Technorati Tags: filemaker, FileMaker 8, FileMaker8

Useful FileMaker Blog for You to Check Out

I discovered a new FileMaker blog today that looks good. I'll probably have some more soon as I search for what's available these days. This FileMaker blog is called Bits and Pieces and it is written by Mikhail Edoshin who has been a FileMaker developer for about 8 years. The photo here is of Mr. Edoshin.

At first glance, Mikhail's blog seems to mainly have explanatory posts about various FileMaker calculations. But, there are some longer articles if you go back a little ways. The post I found most useful was called FileMaker field naming conventions written on November 6, 2005. Mikhail says that he's tried very complex naming schemes in the past but has decided that simple, natural-sounding field names like First Name are better than the coded ones. He explains why.

A couple other posts I thought looked especially interesting were Merge Expressions and Custom functions to simplify reading the current state of modifier keys.

All of this content is valuable and should be on your required reading list if you are a FileMaker developer or intermediate to advanced user. The value a developer like Mikhail can add is all the real-world, in the field detail and context that isn't provided in the online help or user manual.

Instead of merely responding to a filemaker forum question, a FileMaker blogger is choosing among his wide experience and picking something he thinks is worth sharing with a wider audience. Generally speaking, you can expect the shared item will have value to the community whereas a forum question might apply to only a few other FileMaker folks.

These experience-based reports and explanatory articles are really valuable. At minimum, they (1) give you a different slant on something you've read before, (2) remind or introduce you to functions and aspects of FileMaker development that you may not have found or thought about on your own and (3) provide extra commentary based on road-testing in the real world.

Blogs can add a lot to the FileMaker community. And some FileMaker developers, who may have previously relied on static web pages are, like Mikhail, have started blogging. Blogs, comprised of chronological posts, are convenient to create and operate, tell users what is most current, have great automatic archiving features and are linkable by post rather than whole web page.

A blog is much easier to maintain, gives you more Google juice due to the metadata it provides automatically and offers the ability for users to interact via comments. While providing a great service, Mr. Edoshin also introduces himself to colleagues and prospective clients. Speaking for myself, blogging is a great way to contribute and participate in the FileMaker community and incidentally gets the word out about you and your FileMaker business.

Prior to establishing Bits and Pieces in September, Mikhail created a website in 2000 that was devoted to filemaker. Some of that material may be migrated over to the blog over time but I wasn't clear that any has been migrated so far. If you would like to take a look a Mikhail's previous contributions about FileMaker, check out his Onega Software: FileMaker from the other side website.

Technorati Tags: filemaker, filemaker8

You Should be Accessing Your FileMaker Database from Home

If you are running FileMaker 7 or FileMaker 8, there's no excuse for not using FileMaker's robust and speedy remote access capability. All it takes to get it going is to have a fixed IP address for your FileMaker server (even if it is run from FileMaker Pro not FileMaker Server) and holes in your firewall for 5003, 50003 and 50006.

I'm not a networking expert, but the typical in-house IT guy or gal can handle this with a quick look at the documentation for the device on your network that is running your firewall. If you don't already have a fixed IP address, you can either get one or use a DNS service for a small monthly fee (less than $10) that will make your dynamic IP address operate like a fixed IP address.

You don't even need extra copies of FileMaker for home use. Just install the same copy of FileMaker that you use at work on your home machine. Since you can't be in two places at once, you'll never have a conflict between your identical installation codes. If you are already using a Powerbook as your main machine, run don't walk to get this remote access going. It's way too convenient to miss out on.

You and your employees should set-up your FileMaker database at work as a favorite host so you can easily log-in and get things done when the need arises. Most executives have days when they don't need to be in the office except for perhaps one little thing. If you've got your business data in your office database, you can usually do that one little thing and avoid the commute.

Also, if you've got your database set up for remote access, I can log-in and fix things on the spot even if I'm at my local Starbucks having coffee and away from my office. This is an amazing safeguard and convenience. Instead of your sending me your file(s) by email, I can often do a quick fix in 5 or 10 minutes and we are done. Same day, same hour as when the need arises.

My clients are designers. They often travel for press checks, photo shoots or simply meetings with clients in other cities. With remote access set-up, you and your employees can log-in and get very good performance from any broadband connection wired or wireless. Life goes on. Nothing changes. No disruption. Just a lot more convenience.

If you are still using FileMaker 5 or 6, remote access is a good reason to seriously consider an upgrade to FileMaker 8. Already some of my clients operate one database from multiple locations. If you are one of those firms and haven't upgraded yet, you need to check this out.

Technorati Tags: filemaker, filemaker7, filemaker8

FileMaker 8: Using ScriptParameters to Define Variables

Script variables totally rock. Every time I create or modify a script these days, I use script variables. You don't seem to have to use a Commit Records command after you use the Set Variable script step. One less script step to enter.

I got lucky this week and was able to convince a customer with a large FileMaker 5.5 custom system I have developed over the years to convert to FileMaker 8. I was able to do the conversion starting Thursday and finishing to the 97% level by Sunday night. I spent 25 hours. I brought all the 17 files into a single file, improved and standardized the look, eliminated a lot of layouts in favor of tab controls and put in a few other cheap improvements that were easy in 8.

Besides my own FileMaker system for my business, this is the first fairly big all-FileMaker-8 solution that I've worked on. My client loves it. He often uses his database remotely, so likes the improvement in remote access speed. He likes the clairvoyance, the better look of the type (remember he is coming from FileMaker 5.5). He likes the new tabs. He likes the simplicity of the single file. Just today I made a few more improvements remotely. Very cool.

The reason we moved to 8 is that my customer wants to make some substantial improvements to his system. That's the time to upgrade. When you'll be able to immediately take advantage of all the new development tools, efficiencies and options available in the current version of FileMaker.

Now. About defining variables in script parameters. Remember that you can put a calculation into a script parameter. And you can use the Let function to define variables in that script parameter.

This is very cool, people. This is the syntax direct from a working example:

Let ( [$$_no_dialog = 0; $$_Report = “Tasks”; $$_Detail=“Itemized”;$$_Start=“Proposals”] ; “” )

Notice that the result calculation is just quotes around nothing. All this does is define script variables. This takes the place of either being limited to a single script parameter or creating a combo script parameter with pipes or some other divider between parameters and then having to parse it out later. This approach eliminates the need for parsing!!!

With this lovely script parameter. I tell multiple scripts what is happening. I want a print dialog, I want to go to the Tasks report layout, I want the itemized version of the tasks report not the summary version and I started this sequence in proposals, so even though you use multiple scripts, return me to proposals when you are done.

Here's an excerpt from the reporting script:

Notice the last script step passes my button script parameter on to my generic preview and reporting script so that the variables for whether or not to show a print dialog ($$_no_dialog) and where (which layout) the button was pressed from ($$_Start) are available there.

I'm still learning to use standard naming conventions in naming my variables. And I've created a script called Clear Persistent Variables which I call from various scripts to clean up after myself.

I want to pass on thanks to the ones I know about who passed this trick on to me. Bob Harlow, a sharp and experienced FileMaker consultant in Marin county shared this trick with me. He said he got it from the inimitable Vincenzo Menanno of Waves in Motion fame and now working with Beezwax.

Technorati Tags: filemaker, FileMaker8

Address Clairvoyance and List Controls in FileMaker 8

Even though I knew in theory that clairvoyance and value list controls in FileMaker 8 were cool, it wasn't until I started tinkering with my address entry fields in Studio Manager that I found out how cool.

The thing about address fields is that everyone has them. Which of those fields can benefit from clairvoyance and list controls? I experimented with it and submitted my findings on my Studio Manager Bulletin blog. These checkbox features are just the kind of thing my clients can implement for themselves.

I'm sure you couldn't justify putting a value list on the zip code field in a lot of situations. But in a place where most business comes from just a few zip codes like in a major city, this might have some value.

By the way, the arrows and clairvoyance disappear seamlessly when you access the file using FileMaker 7. These 2 features are 7-Safe.

Technorati Tags: Clairvoyance, FileMaker 8, FileMaker8, List Controls

How to Speed Up Your FileMaker 7 and FileMaker 8 Databases

For all practical purposes, FileMaker 7 took away the limits for how complex and large a FileMaker database can be. What it didn't do is massively increase performance so that FileMaker 7 or 8 will always run at a acceptable speeds when processing and working with very large amounts of data.

Since you can now create big databases that run slower than you like, FileMaker Inc. wants you to know ways you can improve performance when you are using big files.

At Devcon 2005, FileMaker Inc. guru, Andy LeCates took some of his limited presentation time to advise us to turn off unnecessary indexing. Indexing a field means that every time something is done to modify the data in that field - such as entering, editing, replacing or importing - extra time will be taken to update that field's index.

By default, fields are set to Automatically create indexes as needed. And the easiest way to create your databases is to just leave that setting that way. Quick and dirty development that focuses on getting working versions up and running as soon as possible to get early user feedback, should leave this setting alone.

However, once you are in the polishing stages of development, if you have a reason to optimize performance, indexing is one useful place to look. You can go through any table and look to see which fields have indexing turned on. And turn some of those indexes off by clicking None and unchecking Automatically create indexes as needed.

If you look at the field definitions list, you may see a group of similar fields such as 8 rates fields and only one of them is indexed. You can probably safely assume that indexing rates is a rare need and just turn off indexing for the rate that had indexing turned on (automatically). It is better to avoid the performance hit of indexing than to make this rarely done find faster.

Technorati Tags: filemaker, filemaker7, filemaker8

FIleMaker 8: New Debugging Tools

Besides an improved debugger in FileMaker 8 Advanced, you now have two new tools in your debugging toolbox: Script Disabling and Data Viewer.

Script Disabling. I frequently have wanted something like this in longer scripts. When I'm working on a problem in one part of the script, I want the script to just run the part I am working on. Now that's easy by just disabling the lines I don't want to run with the Disable button in the Script Edit dialog box.

Data Viewer. This viewer makes it super easy to see what your script is doing - exactly. You can view the contents of any fields you want as the script goes through its paces.

Good stuff. FileMaker 8 Advanced is a must have for anyone who is developing a FileMaker database with any significant level of complexity at all. I consider the upgrade price of $299 to be a steal. But if you are someone who is developing databases, the very best way to get it is to become a FileMaker Solutions Alliance member and get it plus the regular version of FileMaker 8 as a benefit of membership.

Technorati Tags: filemaker8