The Index Indexes makes finds and other features fast. Think of looking for a word in a book by flipping through each page. It’s much quicker to go to the index where it will tell you each page where the word is contained. This is exactly how an index works, it only stores each word once per field across all records. There are two types of indexing: Minimal and All. Minimal means only one index type has been created while All means two index types have been created. The most commonly used index type is a Word index and can only be created for text fields. Up to 100 characters of each word are indexed. Words are defined most often by spaces but can also be determined by commas, periods, colons and other characters. A word index does not differentiate between uppercase and lowercase. Searching for “FRED” also finds “fred”. A Value index is used for searching number, time and date fields as well as relationship key field matching. Up to 100 characters of each return-separated value are indexed. While searching can be done on partial text in a Word index, only complete values can be searched for in a Value index. When you think about it, why would you want to search for a partial number or date. Indexes are utilized in a variety of features other than finds such as value lists based on the contents of a field and unique validation. Understanding how an index works can help you better design a database.
Claris Commercial Few people know that Claris produced a commercial and it aired on national television. It was one of those favor things so it only played once. I'm not saying it's anywhere close to the impact of the Apple Super Bowl commercial but it's fun to have a look.
Download the Claris commercial
This technique has no real use in the real world. Say what? Yes, you read that correctly! Think of the technique I'm about to cover as weight lifting for your FileMaker mind. It's about solving a complex problem, learning new scripts steps and functions, and generally getting smarter so the next time you tackle a problem you'll be more prepared. I believe in conditioning so much, I often write scripts from scratch I could easily copy and paste. But I digress. All I can say is if you want to build up your FileMaker muscles and learn some new tricks then keep reading.
The Challenge The goal of this programming exercise, is to change the font characteristics of any selected text using scripts and calculations. That includes color, style and size. While the font could also be changed with this technique, the three characteristics will be enough to help you if you feel the need to change the font too. But remember, this technique is for increasing your brain capacity and not for real world application.
Why it's Useless If you want someone to be able to change the font characteristics in browse mode, just have them use the Format menu. I normally hide this menu with Custom Menus cause changing characteristics in browse mode overrides layout settings. In other words, if someone changes the characteristics in browse mode then no matter where that field is used, it will use the browse settings. What you end up with is a hodgepodge of font characteristics that make print or other data entry layouts indiscernible.
I'm off topic again but I think this is a very important point to make. The only time I see this as a viable solution is in an article I just wrote about form letters. Otherwise, it's never a good idea to allow font changes in browse mode and therefore a waste of time to program the solution covered in this article. I only present this technique to strengthen your FileMaker mind.
The Interface As with most interactive interfaces, I'll be using global fields. I'll use three global fields to represent Size, Color and Style characteristics. Create xsize as a global number field and the other two as global text fields.
xsize xcolor xstyle
I always find it best to offer an example of what the interface might look like in order to better understand where we will be going in this article. This is especially true in this article since you probably never thought about scripting this process. Basically, the idea is to make your size, color and style choices, select your text and then click a button to apply the settings.
Grabbing the Text Let's build the formula that applies the font characteristics in pieces so it's easier to understand. The first step is to grab the selected text. Since we want the formula to adapt to any text field, we'll use functions that allows for indirection.
The great thing about these functions is they need very little explanation because they have been named by FMI so well. All we need to do is return these Get functions to the Middle function and it extracts the selected text. The GetField function is also required to evaluate the text as a field name. Otherwise, it's just text that has the same name as a field and not a reference to a field. We'll see this later on as well but with a completely different function.
FYI: There is a function called Get(ActiveFieldContents) but it grabs all the text from the field. All we want is the selected text.
Text Formatting Functions We'll start with the text size since it's the easiest of the three to construct. To change the font size, we'll employ the TextSize function. The TextSize function is easy to use since a character can only have one size applied to it. If you apply a new size, it overrides the previous size. All you need to do is refer to a field, instead of hard coding a number, and you have a formula that adjusts to the input in a field.
TextSize(<Active Field Selection>; MYTABLE:xsize)
If you're wondering what <Active Field Selection> means, it's simply a reference to the Middle function formula discussed above. I just used a reference so we can focus on the TextSize portion of the formula.
FYI: There's a counterpart to TextSize called TextSizeRemove which sets the size of the font back to the default. The default is whatever is set in layout mode.
The next step is to feed the result of the TextSize function into the TextColor function. To make the formula as adaptive as possible, let's create a table called COLORS so the RGB values don't have to be hard coded into the formula. The table should contain a text field for the name of the color as well as a three number fields for red, green and blue.
color red green blue
You'll also need a relationship between MYTABLE and COLORS. Use the xcolor and color fields as the keys.
You'll also want to create a value list based on the contents of the color field so any color that gets added to the table will automatically appear on the popup menu attached to the xcolor field.
Changing the text style is the most complex part of this exercise. The TextStyleAdd function is simple enough but we might want to feed in multiple styles at a time. For example, text could have both bold and italic styles applied at the same time. In order to allow for multiple selections, a check box will be used for the interface on the xstyle global field.
The problem is how to efficiently feed the multiple values into the TextStyleAdd function. If you read the online help that comes with FileMaker, it shows an example like the following:
Instead of using square brackets for each style, as with other functions, a single parameter with plus signs is employed. At first this is perplexing but then you think about how a check box stores values as a return-separated list. With that knowledge, a simple substitute could change the return-separated into a list separated by plus signs.
TIP: All text formatting can be removed from a field using the TextFormatRemove function. You can employ it in a script but I prefer an auto-enter calculation so it removes all formatting from those web browser pastes as soon as the user exits the field <TextFormatRemove(Self)>.
Bring It All Together Let's take the formula we just constructed and place it in a script. There are a few extra steps but place close attention to the Insert Calculated Result step which contains the formula we've been covering.
There are a couple of things that need to be done before applying the formula. The first step is to check that text is selected. The Get(ActiveSelectionSize) is checked for two possible states. If no field is active, Get(ActiveSelectionSize) will return a null value. However, if a field is selected but no text is selected (just a blinking cursor), the Get(ActiveSelectionSize) will return a zero.
Before applying the text formatting, the Get(ActiveSelectionStart) and Get(ActiveSelectionSize) are both recorded into a variable with a simple space separation. The reason is the Insert Calculated Result loses the active selection and must be restored. Therefore, the Set Selection follows the Insert Calculated Result step and restores the highlighted text.
It's Not My Fault I told you at the beginning of this article that this technique was useless so don't blame me if you feel like you wasted your time LOL. Hopefully you understood the purpose and can now flex your FileMaker muscle a little bigger with the knowledge you gained. Maybe you won't use any of the techniques covered here tomorrow but I'm sure some day you'll reach back into the vault and pull out some tidbit to create your own custom solution.