Part 2 of this training series continues on with FileMaker Scripting, Calculations, Relationships and Reporting from the guy who actually wrote the book, Scriptology: FileMaker Pro Demystified. With over two decades of experience teaching FileMaker, John Mark Osborne will help you understand tough FileMaker concepts with ease. Intermediate and advanced FileMaker techniques will be taught throughout the three part tutorial series. In order to better understand every nut and bolt that goes into designing a solution, a single file will be created from scratch. The chosen solution is an invoicing solution for its familiarity to a wide audience and flexibility of applying techniques to other solutions. The completed FileMaker file at each stage is provided to assist with the learning process.
Manual performing a find is pretty easy so why would you need to automate it?
Replicating a manual find is pretty easy to do but is the first step to creating a much larger script.
Since the status toolbar will eventually be hidden, the continue and cancel button will need to be recreated.
Certain buttons, fields and other objects don't belong in find mode so hide them when entering find criteria.
If a user cancels a scripts, the consequences could be devastating. One script step can prevent this issue.
Dialogs displayed by the FileMaker application make sense to the developer but not necessarily the user. This section will show how to suppress a FileMaker error message and display your own custom dialog.
When Set Error Capture is turned on, it is important to know every error that could occur. An unknown error could possibly cause havoc with your solution.
Adding a method for users to redefine errant find criteria is a convenient ease-of-use feature.
Dynamic or adaptive scripting is more difficult but saves time in the long run.
Here's our chance to improve on the manual find process and show a list or form view layout depending on the number of records found.
Layouts from the same table occurrence will store the same found set while a layout from the same source table but a different table occurrence will keep a different found set.
Let's combine the information we learned about table occurrences from the previous video with the Go to Related Record script step.
The final step for saving and restoring the found set is to make it work on all layouts regardless of table.
Let's add the Allow User Abort and Set Error Capture sub-scripts to the scripts we programmed previously so as to bring them up-to-date.
There's a small issue with the invoice duplication script written in Part 1 of this video tutorial that can be solved with Set Error Capture and some new code.
A discussion of the calculation dialog features and the general design of a calculation function.
The pervasive calculation dialog enhances so many features but each iteration has subtle but important differences.
This seemingly simple formula is faced with many decisions about how tax is calculated.
The first grand total on the data entry layout will use a calculation field to total fields from or related to the current record.
The second grand total will use summary fields to total the found set of records on the print layout.
Filtering a phone number to a consistent formatting helps users recognize numbers more easily.
Validating a phone number for the correct number of digits allows the auto-enter formatting to work properly.
Browse mode text formatting overrides layout formatting across all layouts and can be prevented with several methods.
Three methods for calculating when an invoice is overdue.
Making a found set of all overdue invoices makes it easy to send out reminders.
Parsing text is very handy when importing data from another application since it's not always stored in a FileMaker friendly format.
Replace Field Contents is often a better choice than Set Field when working with multiple records.
Some parsing jobs are more complicated than the word functions can handle so nested formulas are necessary.
Presented are two ways to copy data from the billing to the shipping fields.
A simple method for tracking modification information for a single field rather than the entire record.
This technique allows modification tracking of any number of fields and results in a list of changes in chronological order.
Users are often confused when modification tracking fields don't update when a portal is modified. You can explain how the information is another table all you want but it's probably easier just to program it to work the way they want.
Tooltips provide assistance to users without taking up valuable screen real estate with help text.
We've used the Hide Objects feature extensively up to this point but it may not be clear the best way to hide an object on a blank portal row.
Determine the number of work days so you can tell a customer when a shipment will arrive.
A brief overview of all the features in FileMaker Pro Advanced.
Recover isn't a FileMaker Advanced feature but it should be since it is so often misused.
Plug-ins extend the ability of FileMaker and are available from a variety of third party manufacturers.
The DDR documents your FileMaker solution so you can analyze it.
Contains a variety of features including runtime, kiosk and encryption.
The Script Debugger allows you to slow down your script so you can pinpoint bugs more efficiently.
To help you with the debugging process, a couple of bugs were introduced to a script so you can see the debugger in action.
Custom function allows calculation code to be centralized, making it easier to update the formula if it is called in multiple locations.
Think of recursion as a loop in a calculation where your code can be repeated over and over with slight variations.
Let's apply recursion to phone formatting, allowing users to determine the formatting type and number of digits in a phone number.
While we are making the phone formatting formula adaptive to any number of digits using recursion, let's do the same for validation.
Custom Menus allow you to add, delete or change FileMaker Pro menus and menu items to suit the needs of your users.
Dynamic or adaptive menus adjust to the current layout.
Demonstrates how to share a file from a single machine in order to test multi-user programming scenarios.
Record locking prevents two people from editing the same record at the same time in a multi-user solution.
Trying to delete a record that is locked is met with the same record locking message as trying to edit a locked record.
Let's make the message for the delete record script adaptive to the context of the layout.
With a few changes, we can adapt the delete record script to delete a portal row.
Deleting a line item from an invoice is a little trickier than the standard portal row delete script.
Global fields and script variables act differently in a multi-user scenario.
Let's review the scripts previously written to see if any could cause record locking.
Sometimes the best way to get around record locking is to think creatively.
Think of this as a bookmark feature, allowing you to retrieve a found set that was saved previously.
Marking records takes a turn when you consider multiple users.
A multi-key is a return separated list of values used in a relationship to create an OR relationship.
Let's automate the creation of a multi-key with a script and then store it in a global field so the user can just click a button.
Restoring a found set can be done with the Go to Related Record step.
Transactional processing bundles multiple actions into a single transaction.
With transactional processing, record locking issues can be resolved.
Empower users with globals, calculations and scripts to create their own merge or form letters in browse mode.
Using the Substitution function, the merge letter will be transformed into a real letter.
A layout for printing will be to be required for proper output.
The Replace Field Contents script step will be employed to provide a calculation result to the Letter field.
Let's resolve the record locking issues in a creative way using a calculation field.
The Form Letter feature won't be complete without the ability to save letters for future use.
Multiple methods for inserting a saved letter into the global field interface.
A warning should be issued to users to make sure they want to update a letter.
Here's some neat tricks to allow text formatting via global fields, scripts and calculations.
A series of script steps and calculations format the text according to the choices in the global fields.
With a couple script steps, the custom form letter solution can send form emails.
Record the history of each time you send out a form letter or an email.
Believe it or not, I make tons of mistakes from which I will create a learning experience.
John Mark Osborne has been working with FileMaker Pro for over 20 years. He earned his FileMaker Pro degree working on the frontlines of Claris technical support. John was the Technical Lead for FileMaker, serving as an escalation path for other technical support representatives and writing a large portion of TechInfo (now called Knowledgebase), their technical information reference. John also acted as a liaison between the technical support department and the FileMaker development team, reporting bugs, attending product meetings and acting as resource for the development team.
John Mark is best known as the author of the popular FileMaker Pro web site Database Pros. The Database Pros web site has been on the internet for 20 years and gets over 35,000 hits a day by offering free FileMaker Pro tips, tricks and techniques.
John Mark is also well known as the co-author of the wildly popular Scriptology book and CD ROM. John Mark launched a hands-on training class based on his Scriptology book and it has become so popular that it is now taught at MacWorld Exposition in San Francisco and New York. Most recently, John Mark was one of twelve trainers in the United States authorized to teach their new FileMaker Training Series of classes.
John Mark has been a featured speaker at the FileMaker Developer Conference for 15 years. At the 1999 Developer Conference, he was awarded the FileMaker Pro Excellence award for "creation of outstanding technical resources".
John Mark is a former technical editors for Advisor magazine working on the tips and tricks section which is the most popular column in the magazine.
John Mark has recorded beginner, intermediate and advanced tutorial videos for FileMaker 8, 9, 10, 11, 12, 13, 14 and 15 totaling thousands of hours.
John Mark Osborne is an FBA member (FileMaker Business Alliance) working closely with FileMaker, Inc. and their most important customers. John Mark Osborne has also passed the rigorous FileMaker 7, 8, 9, 10, 11, 12, 13, 14 and 15 certification tests and is proud to display the FileMaker 7, 8, 9, 10, 11, 12, 13, 14 and 15 certification logos.