Table of Contents

• Get ( Answers ) - June 2008
• [ANN]: If you love FileMaker, we're a great place to work!
• Upgrading from FileMaker Pro 6 and earlier
• Printing "Records Being Browsed" by default
• Formatting merge fields in a text box
• Knowing when a user is logged in from Instant Web Publishing
• Sharing runtimes on a network
• Finding and deleting duplicates
• Computing the Median value
• Creating serial numbers without "gaps"
• Removing formatting from text data
• Creating an index/table of contents for a long report

Welcome to the June 2008 issue of Get ( Answers )

It's June, and that means it's just about time for DevCon (boy, does it come early this year)! I'll be presenting two sessions this year, both along the lines of the types of tricks, techniques, and design philosophies we talk about here in Get ( Answers ). The Support Group will also be represented by our fearless leader Rich Coulombre, Nena Guthrie - Managing Director of our San Mateo and Los Angeles offices, Karyn Witzel - Managing Director of our New York office, and one or two of our systems engineers. If you're going to be at DevCon, you can be sure to find us inside avoiding the heat.

In this issue we're answering a number of question readers have submitted in the last month or so, and — in the spirit of last issue's one longer article — also demonstrating a somewhat complicated technique we developed for a customer recently (though admitedly less involved than last issue's). All following an important announcement.

If you've got a question you'd like us to try to answer, please send it to getanswers@supportgroup.com.

Regards,
Chad

Chad Novotny
Vice President - Technology ↑TOC

[ANN] If you love FileMaker, we're a great place to work!

It's true, we've added some great team members from the ranks of our readers.

The Support Group is one of the largest and most successful FileMaker consulting firms in the world, with offices in Boston, Los Angeles, New York and the San Francisco Bay Area. We're looking for seasoned (not necessarily certified) FileMaker developers to join us.

Our team consists of full time, salaried professionals who design and implement attractive and functional FileMaker and web-based systems for our clients in business, education and government. We have some leading, industry-recognized, FileMaker gurus on staff here who work with ALL our developers as part of a team of true, high-caliber experts.

If you're interested in learning more, visit our website at http://www.supportgroup.com/hiring or contact Jeff Turner, General Manager, at jturner@supportgroup.com or at (978) 376-6374. ↑TOC

Upgrading from FileMaker Pro 6 and earlier

Jack asks the $64,000 question:

"I am running FileMaker Pro 6 on Mac OS X 10.4.11. I think that I have read that the basic structure of FileMaker changed between versions 6.0 and 7.0. Hence, I have been afraid to upgrade lest I lose info in my databases.

"Can you confirm or erase my fears?"

Heh. Yes, FileMaker 7 was THE big change of the decade, sort of on par with the switch from Mac OS 9 to Mac OS X. Things certainly changed, and with all of the improvements came, for a few, some pain as well.

But it's totally worth it! Everything we discuss in Get ( Answers ) is based on the FileMaker 7 structure (and, in many cases, features introduced in versions 8, 8.5, and 9, such as script variables, web viewers, custom menus, and conditional formatting). FileMaker, Inc. no longer supports versions earlier than 8.0, so there's even more reason to upgrade.

Depending on the complexity of your existing FileMaker 6 system, there may be some tweaking needed for migration. But even if everything doesn't work exactly right post-conversion you're not going to lose the information in your databases; on converting to the .fp7 format, FileMaker Pro will actually be creating new files, so your old files will always be there.

There are lots of resources online for those ready to make the switch. FileMaker has a Knowledge Base article to get you started, which also contains a link to a lengthy PDF document. More white papers are available to members of FileMaker's TechNet. And we at The Support Group are available for conversion projects of any size; if we can assist you, please contact our office closest to you. ↑TOC

Printing "Records Being Browsed" by default

Reader Michael in Illinois asks: "When some users go to print a record the drop down print box will says 'Current record', while for others it says 'Records being browsed'. Any idea how to make the latter the default? Is it a filemaker setting?"

Well, Michael, when the Print command is selected, FileMaker uses the last choice made regarding these settings. So if the last print job you did was of the found set, the next time it will by default be for the found set. There's no global FileMaker setting that you can select to make one or the other always the default.

If you want to take some extra steps, and have access to FileMaker Pro Advanced, you can simulate such functionality, on a file by file basis, using Custom Menus.

In Manage (or Define) Custom Menus, edit the File Copy custom menu and select the Print… menu item. Override it's action to perform the script step Print and under Specify print options, choose Records being browsed. After you're done editing the File Copy menu, at the bottom of Manage Custom Menus select Custom Menu Set 1 as the default menu for this file.

Now, whenever you select Print… from the File menu, or press Ctrl-P or Command-P, the option Records being browsed will be selected by default, regardless of what you chose before. To see this in action, check out the sample file. ↑TOC

Formatting merge fields in a text box

Rudy asks how to apply different types of number formatting on merge fields contained within a larger text box on a report layout, where the text had been copied from a Word document.

If you select a text box object, under the Format menu you'll see that you can choose to change the options for display of text, numbers, dates, and times. This will apply to all text and merge fields in the object. However, if you double-click the text object so that you can highlight the text within the object, you'll see that you can only apply text formatting.

Unfortunately, any one text object can only have one number, date, or time format, applied to all contained merge fields. Instead of having one large text object, try using multiple objects, one for each merge field; then you'll be able to apply different formats. You can use Sliding/Printing options (also located under the Format menu) to deal with objects that may end up with blank space inside them when the merge fields are processed. ↑TOC

Knowing when a user is logged in from Instant Web Publishing

We've been asked this many times recently, so it's worthy of repeating: How can you tell if a user is logged in to a solution through Instant Web Publishing rather than through the FileMaker Pro client? The answer can be obtained through use of the Get ( ApplicationVersion ) function. In an opening script, you may have something like the following, to move the user to the appropriate layout:

If the user is logged in through the web client, Get ( ApplicationVersion ) will return Web 9.0v3 (for example); if using FileMaker Pro, it would be Pro 9.0v3. You can thus also use this funtion to make sure users are logging in with a minimum version number: if you're using web viewers, make sure GetAsNumber(Get(ApplicationVersion)) ≥ 8.5. ↑TOC

Sharing runtimes on a network

Eric wants to know: "If you create a runtime application in FileMaker Pro 9 Advanced, can that data be shared across a network if no one has the FileMaker application?"

The answer is: sorry, no. FileMaker Server can host runtime applications, but in order to access them, clients will need FileMaker Pro. While that might seem a bit draconian, think of it from FileMaker's point of view: if you could, large groups would only need to buy one copy of FileMaker Pro Advanced, rather than a copy for each of their users. And then FileMaker wouldn't have the resources to keep giving us great new tools! ↑TOC

Finding and deleting duplicates

Larry writes: "I have a name and address database with duplicate entries. How do I find, and delete, the duplicates?"

The first thing we have to know is: what's a duplicate? Is it people with the same name? People with the same name and address? Once you decide what combination of fields creates a unique entry, the following script will help. In this case, we'll assume that, in a table named Person, two or more records with the same values in FirstName, LastName, and PhoneNumber are duplicates.

First, define a number field called Dupe. Then write this script:

The Sort step sorts by FirstName, LastName, and PhoneNumber. The calculation in the If step is:

The GetNthRecord function (introduced in FileMaker Pro 8) allows us to look to the values in a record other than the current record. So we loop through each record and see if all the values are the same as the previous record (when sorted). Once you're done, find all the records where Dupe is 1, probably review them first, and then delete all the found records.

To see an example, download the sample file. ↑TOC

Computing the Median value

Gerald asked for a suggestion to find the median value of data in FileMaker. For those that need a refresher, the median value of a set is the value in the middle when the set is sorted. If there are an odd number of values, say eleven, the median will be the middle value (the sixth in this case). If there are an even number of values, then we would get the value in between the two middle values; if there are twenty values in our set, we'd use the average of the tenth and eleventh.

In order to find the median salary of a set of found employees, first sort by salary and then use this calculation:

A similar approach can be used with related data; just make sure the relationship (and not just a portal) is sorted. To see an example, download the sample file. ↑TOC

Creating serial numbers without "gaps"

Stephen wrote: "I wonder how one can create a perpetual serial number based on the previously completed record i.e. if I have an application form with serial #4, the next one created will have serial #5 if I commit the record; but if I choose to delete the latter record half filled/completed, the next serial number will remain the same (i.e. #5) as if I have never created a new record with serial #5."

Using auto-enter options for your serial number field, you can specify whether serial numbers are generated when the record is created, or when it is committed. If you specify on commit, you can get the result you're looking for. After the fourth record is committed, the next value will be 5. When you create a new record, if you delete it before committing it, the number 5 won't have been "used up" and will only appear on the next committed record.

Be careful with this, though. We only use the on commit option with serial numbers that are "public facing" (such as an invoice number) and aren't used for any kind of relational integrity. Serial numbers used as key fields should ALWAYS be entered on creation. Otherwise, weird and terrible things can happen; for instance, you won't be able to add related records via a relationship that uses that serial number, since it won't exit yet.

But for other, "less critical" fields, on commit is fine. If you delete a record after committing it, the serial number won't reset, but it probably shouldn't. You can also use other text in the serial number options, or pad the number with zeros, to get something like INV#00052. ↑TOC

Removing formatting from text data

Marjie writes: "When I copy and paste information from other sources to an FMP file, I'm usually delighted that the special format stays and is available to me in that field when I create a report.

"However, sometimes I want the information to give way to the special formatting of the field as I designed it! I know there is a simple solution but I cannot find it. I resent having to create an extra field to paste information for reading, and then typing that information (often a phone number) to another field to achieve reformatting. What have I forgotten?"

This can be tricky. When you've stored text formatting as part of the field value, it's going to appear in any occurrence of that field. But instead of retyping the data, why not let FileMaker do the work for you? If the field is PhoneNumber, create a calculation field with the formula:

Then place this field on your layouts, and use layout formatting to apply whatever font, size, color, etc. you want without worrying about other formatting "leaking through". ↑TOC

Creating an index/table of contents for a long report

A customer recently challenged us to come up with the following. The were using FileMaker to print out a simple catalog of products they were exhibiting at events, with each event having it's own custom catalog. Many products had a long description, as well as many other field, so any given page of the catalog might only three or four products. At the end of the report, they wanted a index of all the products, with the corresponding page number in the catalog.

The first complication is that any given product record will need to show up twice: once in the full catalog and again in the index. That's easily handled by FileMaker Pro 9's Save Records as PDF, with the Append to existing PDF option; we really create two reports, and then merge them together. But how do we know, for each product, what page it appears on?

We first approached it this way: We started by definin a number field appearsOnPage. Then, once the appropriate product records were found and then ran the following script:

By getting getting the last page number, and then setting the last record to have that page number, and then omitting the last record, we gradually built an index.

However, this script takes a long time with lots of products, as it needs to reprocess the report for each one. It also has a possible error: if, in Preview mode, the last record starts on page 21 but breaks across to page 22, it will be listed on page 22.

After a bit of thought, we came up with a better solution. We started by creating a web viewer with the following calculated URL:

and placing this in the footer. We then ran the following script:

The web viewer, using the Let function, sets a repetition of $lastRecordOnPage to the ID of each product during the time we're in Preview mode, one for each page. Since it's in the footer, it uses the last record's productID as the value to be set in the variable repetition. When we leave Preview mode, the data viewer might show something like:

We assume we're starting on page 1, which is represented by $p. We then loop through each record, setting it to $p (plus an additional $offset if we wanted to start the main catalog page numbering on something other than 1). Whenever we see that the current product record's productID is the last one on the page (by looking to the repetition of $lastRecordOnPage with repetition number $p), we increment $p by one in anticipation of the next record appearing on the next page.

This whole process is dramatically faster than the first approach, since it only needs to process Preview mode once. How much faster depends both on the total number of records and the total number of pages that are required in the main report.

To see this technique live, download the sample file. Since it uses the Append to PDF option of Save Records as PDF, you won't get the full result without FileMaker Pro 9. ↑TOC