We are running a series of blog posts over at my product site: Studio Manager Story. As of December 15, we've got 7 posts on new features. We expect to release Studio Manager 10 on December 21st. In the meantime, we will be writing posts about the new features. We've got a calendar for job deadlines, find-as-you-type filtering and other tidbits that might give you ideas for your own applications. Half the battle is converting a vague wish into a clearly defined and aesthetically pleasing feature.
If you are using FileMaker, upgrade to FileMaker 10 if you haven't already. Now, what are all the things you've always thought you couldn't do and had to live with? Time to start removing obstacles and adding power to your database with Script Triggers.
Keep reading, there's a nice unlocked sample file at the end this story.
The genie is out of the bottle. Now you don't have to make your users do all the work to make things happen in your database. For example, if you need a script to assemble the perfect up-to-date value list, you can have it. If users have training issues and tend to enter the wrong data sometimes, you can trap those things on-the-fly and then train your users and clean up after them.
Maybe we are lucky that we didn't get Script Triggers earlier. FileMaker databases tend to be built with strong fundamentals. Developers haven't been enraptured making the database do fancy back flips – because it hasn't been possible till now. What's cool is that script triggers are so full-featured that there is a lot you can do with them without writing long scripts. Short scripts work wonders.
Here's where gold mining comes in. We just recently had the FileMaker Developer's Conference. Hundreds of FileMaker developers got religion at the conference and what we learned is that script triggers don't have to be difficult. They can be easy and make our lives and our users' lives easier.
Everyone is out there learning tricks and shortcuts that weren't possible till now. Gold mining is what you do to see what your fellow developers are telling and selling. There's more blogging than ever. Twitter has gotten an active group of FileMaker developers tweeting now. If you are lucky some of our top FileMaker developers will start selling us their tricks piecemeal.
Example files are being shared all over the net. My example is simple and easy. Maybe it will be the one that either fixes a problem you have with your database or gets you excited about learning more about Script Triggers. So here goes.
Timesheet Date Script Trigger. I have a Timesheet Entry Screen in my Studio Manager product. There is a timesheet for each person for each day. When you create a new timesheet, it fills in your employee number and today's date. Then it is up to you to enter line items for the work you've done entering job, billing category and time expended. It does the job well and quickly.
Except, every now and then a user will realize a little late that he wants the timesheet date to be yesterday not today. So he or she changes the date. Since every line item copies the timesheet date into itself automatically as it is created, if my user has entered 3 line items for 9/15 and then changes the timesheet date to 9/14, we've got an inconsistency that will show up in weirdness in time reports. I've used calculated field messages that show up on the line items to tell my user that he's screwed things up. But it is clunky. Script triggers give me a better and simple option.
Put a Script Trigger on the Timesheet Date field. Select the field. Choose Format/Set Script Triggers… (or Cmd-double-click the object). Check the OnObjectExit trigger and leave the Browse mode checkbox checked. Select a script that is near where you would like to put your new script for the Timesheet Date field.
Right now since I don't know how many of these script trigger scripts I'm going to have, I create a script folder called something like "TS Script Triggers". The TS is the Table abbreviation for Timesheets.
Here's my script. I'll explain it below. View it full size by clicking it.
What the script runs when my user exits the timesheet date field: Are there any line items? If so, does the timesheet date just exitted match the date in the first line item? No? Then throw up a dialog for the user and ask him how he wants to resolve the discrepancy. Default to changing the line item dates for him. Cancel changes the timesheet date back to the original value in case there was a data entry error. All done, no error messages to create and calculate –nothing.
Look at the script above. Yes, it does involve an extremely simple loop but I could have used a Replace command just as well. It is dead simple. I will never get confused trying to figure out this script.
Here's the unlocked sample file for your learning pleasure. There is a similar script trigger for Employee Number because you could have similar needs there.
Resources: I'll be adding to this list as quickly as I can.
Using Script Triggers in FileMaker Pro FMI Tech Brief
Six Fried Rice blog. From FileMaker Pro 10 Missing Manual author, Geoff Coffey. Lots of good posts on Script Triggers among other things.
Nightwing's 10 Demos for FileMaker Pro 10. Ray Cologon is author of the FileMaker Pro 10 Bible. The Active Highlight demo that highlights the current record in a list screen is a good place to start.
FileMaker Video on Layout Script Triggers. John Mark Osborne - 10 minutes.
The Philadelphia FileMaker User Group. Try Arbitrarily Navigating an Array of Fields in FMP 10 for some keyboard navigation.
I've now added table view layouts to 5 tables in a development version of Studio Manager. The results are even better than expected. Using tables with multiple subsummaries with sort and find buttons delivers an incredible browsing experience.
FileMaker is the king of the hill in the end-user database game. Excel database and standard Excel is no competition for the masters. The ease of putting buttons on the screen and making them do simple finds and sorts is unmatched. You get column resizing and reordering. You get adding columns from data in your database without going into Layout mode.
I've never experienced data browsing like this in my 25 years of building databases. One of my practices in building FileMaker systems is to have a list screen for every table in the database. I've used the List View for these so that I could label my columns the way I wanted. However, I may be replacing the list screens with my new Table Views because they are so much more dynamic and useful for quick analysis and reporting. [Part 1]
I heard about this but never got around to checking it out till today. Somehow or other I landed on John Mark Osborne's FileMaker Pro Beginner Tutorials page today. It lists about 40 free training segments of about 5 minutes each. Some of these are featured at the FileMaker.com site.
No matter. The point is John Mark Osborne does a great job and a lot of this beginner tutorial applies whether you are a beginner or not. It features a top FileMaker guru and master teacher talking about the new Features in FileMaker Pro 10.
That's not all. There are other topics like FileMaker Pro 10 Intermediate and another for Advanced. Again there are large numbers of free segments. The Intermediate and Advanced trainings are also taught by John Mark.
This is a try it and we think you will like it way of selling the VTC training. It's a gift. I don't know about you, but I'm watching these videos – the free ones at least. And, I'm telling my customers about them.
John Mark starts teaching FileMaker Pro 10 certification this Spring, so he knows the latest in a very thorough way. Even advanced developers will benefit from listening and watching John Mark teach the beginner, intermediate and advanced tutorial. He talks about a lot of fundamentals and expresses his opinions on best practices such as field naming.
This feature is a great one to implement the minute you purchase FileMaker Pro 10. You can drastically beef up the value of table views by adding sub-summary parts – lots of them. This feature is so good that I plan to add about 20 table view layouts to my own Studio Manager product.
Here's what you usually get in Table view:
In this example, the table is sorted by Filename which doesn't have a sub-summary on the layout. I tried adding a sub-summary above for the Drive field, so when I sort by Drive by clicking the Drive column heading, I get this:
I then added a sub-summary above for File Type and then a sub-summary below to allow me to show a record count. When I click the File Type column heading which causes a sort by File Type, I get this:
The layout I created looks like this in Layout mode:
I like this a lot because all I had to do was create this layout which takes about 5 minutes max. And I have multiple views based on the column my user clicks. This is browsing your data! This empowers the user. If your data is such that you really need multiple sorts to occur, you may need to add buttons for your sorts in the footer area.
Finally, the thing I like most is that I can do this with a single layout that is not complicated or hard to figure out. No tricks to remember or stacked fields or anything like that.
One more thing: the Modify Button. The user gets to add or remove fields to his or her view when he wants without going into Layout mode. Removing fields is especially great. You don't want a field? Just uncheck the checkbox:
Notice that you can add fields from here as well by clicking the plus button. If you do, you get this new Add Fields dialog:
This is ease of use folks! [Continue to: Part 2]