"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
Create a Password Management solution!
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Window Locking
 |
Everything Changes
 |
Subsummary Unique Count
 |
Scripted Change Log
 |
Abstracted Log
 |
|
Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
|
Document Management Videos
|
Quick Tip:
|
Date Search Techniques
Some date search features in FileMaker may not be obvious. For example, you can type in a range of "Monday...Friday" or "Mon...Fri" to locate all weekday dates. Or, you can use wildcards to locates all dates on January 1st, regardless of the year, by typing "1/1/*". You can use curly brackets to specify a range of months, such as "{1...6}/*/* to locate all the birthdays in the first 6 months of the year. Try these same wildcard techniques with time fields too. Play around with these tools, and I'm sure you'll be impressed with the flexibility. And let me know if you figure out some combination of these new features that let's you accomplish some really cool technique. |
|
|
FileMaker 20 Video Tutorials
|
Fun Stuff:
|
FileMaker Developer Worthiness
Top 10 ways you know your developer is NOT worthy:
- Spells "FileMaker" with a lowercase "m"
- Uses inches or centimeters instead of points in Inspector
- Recommends WebDirect deployment over FileMaker Pro
- Creates a solution with one file for every table
- Doesn't know Claris and FileMaker, Inc. are the same company
- Isn't certified for latest version of FileMaker
- Isn't an FBA member
- Charges less than $100.00 per hour
- Uses a rainbow of colors on his interface
- Provides a quote without a requirements document
|
|
|
The Philosophy of FileMaker recommends PCI!
|
RSS Feed
|
|
|
|
One-Trick Pony
|
Level: Intermediate
Version: FileMaker 17
Category: General
Tuesday, July 23, 2019 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Don't be a one-trick pony and apply the same FileMaker feature to solve every problem. That's like wearing a tool belt and only putting one tool on it. FileMaker has many options, each with it's own unique advantages and disadvantages. Over-relying on a single tool will result in a subpar solution. Try to learn all the features in FileMaker so you can choose the right instrument for the right job.
Script Triggers
Script triggers are one of the most versatile features in FileMaker, causing budding developers to overindulge in this multitalented tool. It really can solve just about any problem, but should it? It's kinda like building a house with one tool, like a hammer. It's a flexible tool but you'd be so much better off adding a screwdriver to the mix.
There are so many examples of how not to use script triggers that I'm going to focus on a simple example. Take, for instance, this discussion that took place on the FileMaker forums. Someone wanted to clear a field when a selection was changed. Let's imagine you have a subcategory field with a popup menu that is conditional based on the choice from a popup menu on the category field. It doesn't really matter what the choices are on the category or subcategory fields, just that the subcategory choices change based on the choice from the category field. If a choice is left in the subcategory field when a new category is selected, the user might think it's a valid choice.
A script trigger can easily solve this problem. Since the category field is a popup menu, just use the OnObjectModify trigger attached to the category field to run the following script:
Set Field [MyTable::subcategory; ""]
It's really that simple so why consider any other approaches? The problem occurs when you consider the possibility of script conflict. I see it happen all the time. I wrote about an example in an article titled Ultimate Find where a script that named the window based on the current table conflicted with a find script and overrode find mode errors. The more scripts you run, the more chance you have of conflicts. However, it really only takes two scripts to cause a problem. Not only that, script triggers are layout level and need to be applied, in this case, to every layout where the fields resides.
Let's take a look at a schema level solution that will work on every layout as well as every platform including Macintosh, Windows, PHP, WebDirect and FileMaker Go. Script triggers don't even work on WebDirect! So, let's try an auto-enter solution, attached as an auto-enter calculation to the subcategory field:
Evaluate(Quote(""); category)
Don't forget to uncheck the auto-enter option to "do not replace existing field value (if any)" or the field will never clear. This may seem a little tricky at first but it's pretty simple with a little knowledge of the Evaluate function. Evaluate is defined by the online FileMaker 17 help as:
Evaluates an expression as a calculation.
All that means is you can type in a calculation formula or reference a field containing a formula in the first parameter of the Evaluate function and FileMaker will evaluate it as if it was a calculation field. In fact, you don't even need a second parameter as shown by the format of this expression:
Evaluate(expression{;[field1;field2;...]})
The curly quotes designate an optional parameter for field dependencies. You can list one field or many fields enclosed in square brackets to trigger this formula to auto-enter when the field is changed. By referencing the category field, the subcategory field is cleared each time the category field is modified.
TIP: When a formula is typed directly into the first parameter of the Evaluate function, it needs to be enclosed in quotes. If a reference to a field containing a formula is specified, the quotes are considered inherent.
Reporting
Another common mistake is for folks to use calculation fields for reporting rather than subsummary parts and summary fields. I admit subsummary reporting was one of the toughest aspects of FileMaker to learn when I first started. While calculations have far more depth simply by the sheer number of functions, they are easier to grasp onto, especially if you have background in spreadsheets. That doesn't mean you should learn how to use subsummary reporting, it just means I get why folks use calculations for reporting.
That's not to say the calculations can't be used for reporting. Every database has at least some simple reporting via calculations. For example, I often use an aggregate function to sum up a portal. This is the proper use of a calculation. What I'm talking about is when amateurs employ tens or hundreds of calculations, usually with aggregate functions, to create a report that could easily be fashioned using a subsummary report. Not only that, the report would be far more flexible with subsummaries since it can be altered by changing the found set. No aggregate function can match that adaptability.
Some people claim the subsummary reporting mechanism is limited. A common example is the need for cross-tab report, much like what you might see in Excel. FileMaker has linear reports that display vertically. Listen, I get that people are used to reading their reports a certain way but mimicking an Excel report in FileMaker limits flexibility because calculations attempt to substitute for what can easily be done using found sets of records. Records are what make a database a database. They can be found, searched and generally rearranged in almost any manner desired.
Have I mentioned the speed issues yet? While calculated reports may display nimbly in a single-user scenario with a handful of records. Take that same solution to a server and see what happens when there are thousands of records and remote access. It's likely to be unusably slow. A similar report can be produced quickly and react nimbly using the correct FileMaker features. It just looks a little different.
Steering back towards the point of this article, you need to utilize all the features in FileMaker and apply them in the right scenario. Don't just do something cause it works. There are usually good ways to accomplish a task and bad ways. And, sometimes the bad way becomes a good way in a different scenario. Know your tools and use them at the right time.
Cut, Copy and Paste
I often call Cut, Copy and Paste the Good, the Bad and the Ugly. At no point should they be used to transfer information from one field to another. They should only be used to transfer information to the clipboard. In addition, the user should be aware the button is using the Clipboard by naming it something like "Copy Email' or "Copy Address". Imagine a user clicking one of your buttons and destroying the contents of their clipboard. They might have something important in there and they'll be confused as to why it's gone.
Again, I get it. Cut, Copy and Paste are easy to understand. When you first open the Script Workspace, it can be daunting with the sea of steps to select. Then your eyes settle on Cut, Copy and Paste and you feel a rush of comforting ocean breeze on your face. Ok, now I'm just being silly but you get the idea. People use the evil trio of steps because it's easy. I'm telling you to break out of your comfort zone and learn a new FileMaker feature every day.
There are many examples out there but let's keep it simple. Imagine you want to copy a primary key from one table, visit another layout showing records from a different table, create a new record and paste the primary key into the foreign key so the parent and child are linked. It's a very common scenario in FileMaker programming.
Yes, this script will work but it's seriously flawed because it destroys the contents of the clipboard. I get why folks do it. They are comfortable with Cut, Copy and Paste so they use what works for them. Still, that's no excuse for not learning the right way to do things. All you need to do is go on the forums and someone will show you the right way to do it... for free! They will tell you to use Set Variable and Set Field. Think of Set Variable as a clipboard in FileMaker and Set Field as a way to grab data, via a calculation, and place it into a field.
This is not the only problem with Cut, Copy and Paste. Since these script steps require the field on the layout, you cannot remove the field or run the script from another layout based on the same table occurrence. It basically marries the script to the layout. The difference with Set Variable and Set Field is they don't require the field to be on the layout, just that the context is correct. Think of this as going out on a date rather than a marriage. Set Variable and Set Field do require knowledge of context but it's far less binding than requiring the field on the layout.
Record Locking
Anytime a record is modified, it becomes locked. When a record is locked, nobody else can edit it manually or with a script until it is released. A single record is easy to check for locking. All you need to do is use the Open Record/Request script and check for a 301 error. I cover this technique in another article titled Reconciling Record Locking.
BTW: There are many ways to unlock a record but the basic idea is to commit it by navigating to another record or layout.
The point of this article is to emphasize record locking over multiple records, such as can occur when looping through records or using the Replace Field Contents feature. If you don't have access to all the records in the found set, you will not be able to write to the locked ones. I try to avoid looping scripts and the Replace Field Contents script step and usually it's pretty easy to do so. For example, let's say you want to mark the current found set of records so you can come back later and find those records. If you run a script that places a unique marker on the records, some of the records may not be marked because they are locked.
I've used an "x" as the marker in the example above to simplify the exercise. The point is, no matter what value you place to uniquely identify a record, it will require the record to be open, which is a difficult task when many people are utilizing the database. To solve this problem, you simply have to learn more about how FileMaker works rather than relying on the basics. Covering this topic would take an entire article on it's own and I've already written about it in an article titled Saving a Found Set. I'd recommend reading the article but the basic idea is to grab unique identifiers from the records themselves, which is not subject to record locking, and store them in a multi-key to be later restored through a relationship and the Go to Related Record script step.
It's a simple change in perspective. Instead of trying to write to a record, simply grab a value from it. Problem solved! I do realize this takes years of experimenting and development but that's not my point. My point is, don't be lazy and keep educating yourself on a daily basis. I literally learn something new almost every day even though I've been working with FileMaker for over two decades. It's a lifelong learning process if you want to be excellent at what you do.
Execut SQL
ExecuteSQL is one of those functions I find folks from other platforms abuse. It was introduced to reduce relationship graph clutter. Prior to ExecuteSQL, developers often created relationships for focused jobs like a single script or calculation. ExecuteSQL doesn't replace relationships, it's just a good alternative if you don't need the relationship for anything else but a single feature. ExecuteSQL is very good at what it does but it doesn't replace the full functionality of the relationship graph. It's just meant to declutter just like Conditional Formatting, Filtered Portals, Hide Object, Placeholder Text and many other features.
If you employ ExecuteSQL properly, it becomes a great tool. What ends up happening is, SQL experts decide they don't want to learn FileMaker and try to use ExecuteSQL to handle jobs that are better assigned to other features. The tough part is coming up with a simple example that demonstrates poor use of ExecuteSQL so I'll use logic instead. Basically, you should only use ExecuteSQL when it is far better than any other feature. Honestly, that doesn't happen very often. I did write about how I use ExecuteSQL in an article titled Preferences and Options. This article contains the perfect storm of answers as to why to use ExecuteSQL. In this article you'll see exactly how ExecuteSQL is far better than a solution using multiple relationships.
Keep in mind that ExecuteSQL is very slow compared to other features. FileMaker is not an SQL based database system. ExecuteSQL should be applied to scenarios very carefully when all other solutions are exhausted. Don't be like the guy I encountered on the forums who thought ExecuteSQL could solve all questions on the forums. It's about picking the right feature for the right job so make sure you have a tool belt with lots of tools and you know how to use each one.
Conclusion
These aren't the only examples of how I've seen folks abuse FileMaker but I only have so much room in an article. I try to learn something new about FileMaker every day. It might be something small or something outrageously big! It might be something you discover while fiddling around or maybe it comes from a blog article you read. The key is to keep on learning and improving your skill set so you don't become a one-trick pony!
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
Comments:
|
|
|
Michael Rocharde
|
07/27/2019
|
|
|
John, as always this is a very interesting and thought-provoking article. I particularly liked the idea of using the Evaluate function instead of script triggers, something I had never thought of doing. Unfortunately, when I tried to use it for something I had already done, i.e., clearing a radio button selection from one field when another one is selected, it didn't work as it created a circular reference but it was worth experimenting to find out.
The point of mentioning that is that we, as developers, have to constantly experiment and try different ways of doing things. Not all of those ideas will work; in fact, many of them won't but FileMaker™ allows us to try many different ways of doing something; quickly discover that one doesn't work and then try another. I often refer to FileMaker as my 'stream of consciousness' because of the speed that one can try different ideas.
|
|
|
|
