"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
Create a FileMaker Calendar
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.
|
Beginner, Intermediate and Advanced Video Training
|
Quick Tip:
|
Record Locking
In FileMaker 6 and earlier versions, record locking occurred when a guest on the network clicked into a field. That's was all that was required to prevent others from changing the record. Unfortunately, this also prevented other guests from copying data from a locked record and other basic features. FileMaker 7 has changed how record locking works. In order for a guest on the network to lock a record, he must actually modify a field, allowing user to work with a record without locking it. However, the most important change is when scripting for record locking. You don't want to test if the record is locked by setting a field to a value. FileMaker 7 introduces the Open Record/Request script step which attempts to lock a record without modifying it. If the record is locked, an error of 301 will be returned. If the record is not locked, this script will lock it until the Guest exits the record manually or the Commit Record/Request script step is intiated. |
|
|
Beginner, Intermediate and Advanced Video Training
|
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
|
|
|
Create a FileMaker Calendar
|
RSS Feed
|
|
|
|
Noteworthy
|
Level: Intermediate
Version: FileMaker 17
Category: General
Tuesday, February 19, 2019 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ahhh... the ubiquitous notes field. Seems simple, right? In a way it is. Just slap down a field and resize it to show more data. Add a scroll bar and you're done. The problems occur when you are trying to locate an important note, print a report or perform a find. Data can be strewn all about a text field that allows free form entry and supports up to ten million characters. Imagine hundreds of different users trying to follow the exact same format for data entry. Pandemonium! This article will discuss two major techniques for storing notes along with subtle tweaks to help make organization better.
Notes Auto-Enter
The most common request I get for a notes feature is a single field with a scroll bar so notes can be added freely. The problem is the notes get jumbled differently on each record. One user puts the new note at the beginning of the field and the next at the end. Some users put the date and others don't. This creates a hodgepodge of data that can be difficult to discern. Different people have different styles so you can't really blame them. The best you can do is curb their data entry.
It's pretty easy to make an Auto-Enter calculation to keep a history of notes clean and organized. It's not guaranteed to stop people from randomly entering notes any way they want into an open field but it can help. Start by creating a global text field called "xnotes" and add it just above the existing "notes" field on your layout.
Add an Auto-Enter calculation on the "notes" field:"- " & Get(CurrentTimestamp) & " by " & Get(AccountName) & ": " & xnotes & "¶¶" & Self The dash signals the beginning of the note so each one is easy to find. The date and time are added next, followed by the account name of the person entering the note and then a colon to separate the timestamp and the actual note. New notes come from whatever was typed into the "xnotes" global field. Finally, two carriage returns are added to separate each note with vertical white space, followed by the existing notes using the Self function. Make sure to uncheck the option to "Do not replace existing value of field (if any)" or no new notes will get added past the first one.
If you really want to stop free style note entering, you can prevent entry into the "notes" field via the Inspector. Auto-Enter can still add to the notes when they are entered into the "xnotes" global field. However, the notes won't be scrollable. If you want to allow scrolling, your best bet is to set the "notes" field as read-only using the Manage Security feature. Even though the field is set as read-only, the auto-enter will still perform it's duty.
NOTE: The Self function simply refers to the field where the formula is attached. It was introduced alongside the Conditional Formatting feature in FileMaker 9.0 to allow for adaptive formulas to be applied to multiple fields at the same time. However, the Self function can be used anywhere the calculation is attached to a specific field such as a calculation field, auto-enter or validation formula.
Clean Up
This formula works great in most situations. The first problem is cleanliness. I don't like the two carriage returns that are added to the end of the notes field when the first entry is made. Since the notes field is blank on first entry, the Self function produces nothing so all you get is the new note followed by two carriage returns. It's fairly easy to solve with a conditional statement:
Case(
IsEmpty(Self);
"- " & Get(CurrentTimestamp) & ": " & xnotes;
"- " & Get(CurrentTimestamp) & ": " & xnotes & "¶¶" & Self
)
This formula is fine but I like cleaner code when it's not too much work. Sometimes it takes longer at first to clean up your code but after a while it gets easier. Soon you'll be writing clean code from the start or at least recognizing areas that are ripe for trimming. Here's the simplified code:
"- " & Get(CurrentTimestamp) & ": " & xnotes &
Case(
not IsEmpty(Self);
"¶¶" & Self
)
The repeated code was moved out of the conditional statement and the 'not' operator was added in the test portion of the Case statement. A blank ("") result is not needed since the default for the Case is null.
Clearing the New Note Field
It bothers me that the new note global field still contains the note that has already been added to the notes history. You have to delete it before adding another note. Even more bothersome, the new note appears on each record since it's a global value. Yes, it's much better to erase the value in the new note field to provide a better user experience.
Let's try an Auto-Enter calculation on the "xnotes" field to clear it. This segment is about Auto-Enter so let's keep it all in the family. Plus, auto-enter is schema level so the formula will auto-enter no matter where the field is used. That includes all layouts and platforms such as custom web publishing, FileMaker Go and WebDirect. Here's the auto-enter calculation attached to the "xnotes" field:
Evaluate(Quote(""); notes)
I use the same technique in tons of other solutions to clear a field when another is modified. The basic idea is for the Evaluate function to enter a blank value when the referenced field from the optional second parameter is modified. The second parameter allows me to add a dependency attachment to the "notes" field. While the theory seems sound, it doesn't work. Unfortunately, the inherent dependency on the "xnotes" field erases the new note before it can be added to the "notes" field. I tried all different kinds of iterations but here is a situation where a different approach is required because the order of operations can't be changed.
FYI: The Evaluate function requires FileMaker formulas to be surrounded by quotes in order to be evaluated, much like a string of text. Otherwise, FileMaker will try to interpret the values as fields, functions and operators. If the first parameter of the Evaluate function is a reference to a text field containing a formula, the quotes are not needed since the contents of a field are already considered quoted.
It seems a Script Trigger is going to be required to subvert the order of operations so I defined the following script:
This script is then attached as a script trigger to the "xnotes" field, firing when the field is exited, validated or saved (it makes no difference which trigger in this scenario). The order of operations allows the Auto-Enter to occur first followed by the triggered script. Unfortunately, clearing the "xnotes" field triggers the Auto-Enter calculation again, causing an empty note with just a timestamp to be added to the history. Adding a test for the emptiness of the "xnotes" field to the Case statement in the formula attached to the "notes" field fixes the problem:
Let(
@NewNote = "- " & Get(CurrentTimestamp) & ": " & xnotes;
Case(
IsEmpty(xnotes);
Self;
not IsEmpty(Self);
@NewNote & "¶¶" & Self;
@NewNote
)
)
The Let function has also been incorporated to eliminate duplicated code and make the formula easier to read.
A Better Approach?
A simple Auto-Enter calculation has turned into a complicated formula handling several exceptions. A script was also necessary to increase the usability. And, don't forget the global field required to complete the solution. More clutter generally equates to a more complicated database. It's simply more difficult to find what you are looking for when there are more scripts, fields, value lists, layouts, etc. to sift through.
Maybe this is not the ideal job for an Auto-Enter calculation? A button driven script might be easier to program and add less clutter to the database. Let's add a button that runs a completely script driven notes organizer:
The entire script can't be seen in the screen shot above so here's the complete formula from the Insert Calculated Result on Line 3:
"- " & Get(CurrentTimestamp) & " by " & Get(AccountName) & ": " & "¶¶"
The formula is very similar to the first technique covered at the beginning of this article, minus all the references to Self and the "xnotes" field. That's because the Insert Calculated Result script step can place the result of a formula at a cursor point so there's no need to preserve the existing data. Auto-Enter requires a references back to the original field or it will replace everything instead of adding to the existing data. Don't forget to uncheck the Insert Calculated Result option to "Select entire contents" or it will replace the existing data!
BTW: Insert Calculated Result and Set Field are essentially the same script step except that Insert Calculated Result requires the target field to be on the current layout. On the other hand, Set Field can't insert a calculation result in the middle of existing data without a lot of calculation wizardry.
Before running this script, name the "notes" field "Notes Field" via the Position tab in the Inspector. When attaching the script to the button, make sure to pass along a script parameter of "Notes Field" so the Go to Object step on line 3 functions properly. I often use Go to Object instead of Go to Field to provide indirection so the script can be used for any field or in any table or file without modifying the script.
When the script runs, the Set Selection step positions the cursor at the beginning of the notes field using a start and end position of zero (0) so the Insert Calculated result can add the timestamp at the beginning. Otherwise, Insert Calculated Result script step will place the new note at the end of the current content. The second Set Selection step moves the cursor back two characters so the user can type at the end of new line rather than at the end of the two carriage returns.
With this approach you can't really prevent folks from entering data directly into the notes field. You can turn off access to the field via the Inspector but script steps can override this setting. Besides, users will be entering directly into the field anyhow and have nothing stopping them from switching to another location in the field. Then there's the scrolling issue. Not even security can solve the issue by making the field read-only or users won't be able to type into it.
On the bright side, this script is completely dynamic, only requiring the naming of the field and the passing of the script parameter to function on any field in any table or file. But, what I like the most is the simplicity. There's only one script. That makes it easy to locate and dissect. With the Auto-Enter approach, there are several components working together, making it difficult to decipher the solution. While you still may choose the Auto-Enter approach for the schema advantage, don't discount how simplicity affects the ease of editing your database at a later date or by another developer. Simpler is also easier to move from one solution to another. This benefits you as well as your client or company, costing everyone less time and money.
Notes Portal
Another method for creating a notes feature (and, my favorite) is to define a table instead. This takes a little extra work but keeps notes highly organized. It also allows for notes to be categorized, searched by date and even reported using subsummary parts. There's nothing tricky to a notes portal. Just create a table with a foreign key field, a notes field, a date field and maybe some other fields like account created, account modified, timestamp created and timestamp modified. I also like to add a category field for some clients which can assist in sorting or filtering a portal.
Make sure the relationship option to "Allow creation of records in this table via this relationship" is checked on the Notes side or the portal won't have a blank row to enter new related records. It's also a good idea to allow for cascading deletes with the "Delete related records in this table when a record is deleted in the other table" so you don't orphan notes records when a parent record is deleted in the Tips table.
I also like to add a simple script to select the blank portal row so the user doesn't have to scroll to the bottom of the portal to enter a new note:
Now for the good stuff! A portal is so much more versatile than a field. Let's start with some conditional formatting that turns the fields different colors depending on the option selected from a popup menu on the category field. This makes the different categories of notes discernible at a glance:
You can also organize the portal by sorting by one or more fields like the category and date field:
I've chosen to sort by the category and date field so the most important categories come to the top of the portal in descending date order.
The last thing I'd like to do is provide a simple filter for the portal so you can look at notes by a single category. If we go for the simple approach of adding a standard popup menu, we'll only be able to show one category at a time unless we complicate the technique further. If we simply change the popup menu to a check box set and use the PatternCount function, the solution stays fairly straightforward, allowing users to check as many boxes as they want:PatternCount("¶" & TIP::xcategory & "¶"; "¶" & NOTES::category & "¶") TIP: Check boxes store multiple values in a return separated list. This works perfectly with the multi-key feature of relationships, allowing for an OR relationship to match individually on each value checked. Unfortunately, this makes the relationship unusable for showing notes without the global field so this approach was not selected.
Whenever a global field is referenced inside a filtered portal formula, the portal will not refresh without some help. This can be done by adding an OnObjectModify Script Trigger to the "xcategory" field that refreshes the portal. There's no need to refresh the entire screen using Refresh Window script step. The Refresh Portal script step will do just fine:
I've also added an initialization Set Field step to the Startup script to make sure all categories are checked on open. No refresh is needed in this scenario.
More complicated filters could be applied to the portal. For instance, you could provide a filter as you type on the note. However, I think I've made my point. A notes portal is more versatile by far! It's your job to take what you've learned and apply it to your own unique solutions.
Watch Out!
Clients are always asking for notes field. Sometimes you need to question their motives or the notes field becomes the dumping ground for everything when a targeted field would suffice. If you don't educate the client, it's likely some feature might not be possible down the line. For example, the client may want a report based on a value buried in the notes field and you'll have to tell them it's not possible. Better to ask the client what they are putting in the notes field to find out if they need additional fields possibly with value lists to standardize data entry.
I remember one client that asked for a dozen notes fields in a single table. I asked why they needed so many notes fields and by the end of our conversation, we had created additional fields with popup menus to better store their data for finding, sorting and reporting. The moral of the story is you can't let your client define the structure of a database. I've had the hair pulling luxury of adopting hundreds of these ill conceived database nightmares over the years. Just ask a few questions and you, your client and adoptive developers will be happier.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
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:
|
|
|
Greg Hardy
|
06/26/2019
|
|
|
Great article John. Thanks. I have tried to make the same fm file to train myself but on my portal it keeps saying I can't modify the field until the PK field has a valid value. How did you get around this please?
|
Response by: John Mark Osborne
|
06/28/2019
|
|
Sorry for the long response time. I just got back from vacation. Looks like you don't have a value in your primary key field. You might have created the record prior to turning on the auto-enter serial number feature. You can add serial numbers to existing records with the Replace Field Contents feature.
|
|
|
|
Bernard Max
|
02/21/2019
|
|
|
John, many thanks for these tips & tricks! Your imagination and creativity are limitless...
|
Response by: John Mark Osborne
|
02/21/2019
|
|
Thanks for your kind words. I really appreciate it. I'm just sharing what I've learned from the rest of the FileMaker community.
|
|
|
|
Anna Kochergina
|
02/21/2019
|
|
|
John, I was really delighted with your article about Notes you titled “Noteworthy”. Your ideas & suggestions are really interesting and may indeed dramatically increase performance when users would endeavor to perform quick searches within not completely structured notes. You even q a solution within the solution! The one-to-one relationship between your two tables is a great trick. I’m quite sure this will inspire the whole developers’ community. Taking 8nto account your suggestions and scripts’ samples, I even tried to go further but had very quickly the feeling I started a new development of an EDM (Electronic Data Management) 😉. I for sure went too far but it’s just because you opened too many doors!
|
Response by: John Mark Osborne
|
02/21/2019
|
|
If you ever get inspired to create something based on something I wrote about, feel free to share!
|
|
|
|
Scott Payton
|
02/20/2019
|
|
|
Zoho CRM needs to see this! Lol They have a lot of great things going on with them, but regarding notes, they need to make a change. That change would be when one goes into a customer record, it would be nice to immediately have the notes file present and going to the most recent "dated" notes for said record. Of course, keeping the contact info close so as to call, email chat with customer. You have to scroll down to practically, the bottom of the page to see the notes. It’s takes too much time and needs to be seen immediately, especially when we are all busy!
|
|
|
|
