Table of Contents

• Welcome to the first issue of Get ( Answers )
• Save a container field's contents to the desktop and open it
• Include buttons in your tab order
• Fitting that 72 point merge field on your layout
• Find tables easily in the relationships graph
• Automatically select the last tab viewed in a tab control
• Start the week on Sunday OR Monday
• Find everyone with a birthday this month
• Randomly and evenly distribute records in groups

Welcome to Get ( Answers )

Welcome to the first issue of Get ( Answers ) from the Support Group. Whether it's as developers building systems for our clients, as trainers in front of a classroom, or as consultants sitting down one-on-one with customers, we're constantly being asked questions. Some of them require complex solutions; others have quick answers, though not always immediately apparent. Our goal is to collect some of the latter in Get ( Answers ). All of the following tips and tricks were collected over the last month, inspired by real questions our customers and students posed to us.

With FileMaker 7 introducing a completely new architecture, FileMaker 8 extending it with a host of user and developer-level features, and now FileMaker 8.5 bringing web-browser functionality inside FileMaker (as well as other features), there's plenty of questions to be asked. If you have one, write to us at getanswers@supportgroup.com. ↑TOC

Save a container field's contents to the desktop and open it

Starting with FileMaker Pro 7, container fields could be used to store virtually any kind of document. But how to get them out again? Manually, you can use the Export Field Contents… command to copy the file to your desktop (or other location), and then open it as you would any file. Using a script, you could script the Export Field Contents step, then use the Send Event step to open the file, but the challenge was figuring out the proper syntax for the file path for naming the exported document and where to save it (if you didn't want to save it next to your FileMaker file). With FileMaker Pro 8 or 8.5, it's a snap.

Assuming the file is in a container field named containerDocument and that you want to save it to the user's desktop, all you need are two steps. First, use the Set Variable step to create a local variable (we usually name it $path). Set $path to the following formula:

(you could also use Get ( DocumentsPath ) to save it in the Documents or My Documents folder). Then use the Export Field Contents step, select containerDocument as the target field, and when it comes time to specifying the output file, just type $path in the path list and check the box to automatically open the file after saving. ↑TOC

Include buttons in your tab order

Starting with FileMaker Pro 8, buttons can now be included in the tab order. By default, they won't be so enabled, but in Layout mode, when you use the Set Tab Order command, you will see arrows to the right of each button (point left to the button) representing where that button appears in the tab order. Once you've added a button to the tab order, and then tab to it in Browse or Find mode, you can trigger the button by pressing the Return or Space keys on your keyboard. ↑TOC

Fitting that 72 point merge field on your layout

This is an oldie, but still a goodie. Have you ever had trouble fitting a merge field on your layout when using very large field names and/or font sizes? For example, if you add the following merge field:

to a layout, and format the text object to a font size of 72 points, you may have trouble fitting other layout objects around it. The trick is to highlight the very first < character and format it with the large font size, and then format the rest of the text with a much smaller size, like so:

The resulting text object will be much smaller and easier to position correctly on your layout, but it'll still show up outside of Layout mode with the larger size. ↑TOC

Find tables easily in the relationships graph

One of the features introduced in FileMaker Pro 8 was the ability to quickly select all occurrences of any table in the relationships graph. Just click on one table occurrence, and then press Ctrl-U (Command-U on a Mac) or select the option from the pop-up menu on the icon just to the left of the view percentage along the bottom. One immediate use of this command is to select all occurrences of a particular table and make sure they're all colored the same for easier identification. ↑TOC

Automatically select the last tab viewed in a tab control

FileMaker Pro 8.5's new object names give us new scripting control over navigating to specific tabs in a tab control. Here's one way of automatically recording whichever tab a user has active in a global variable for use later in a script.

Define a new calculation named tabTrigger. The calculation can result in Text and should have as many repetitions as tabs in your tab control. In Storage Options…, make sure the calculation is unstored. Now, assuming each of your tabs has been given a name (in our example the tabs have been named workTab, homeTab, and otherTab), enter the following formula for the tabTrigger field:

Now place the tabTrigger field on each tab. Make each field clear and without a border, and non-enterable in either Browse or Find mode. On the first tab, format the field to show only repetition 1 (i.e. 1 though 1); similarly, on the second and third tabs format the fields to show only repetition 2 or 3.

The result of the above calculation formula is always going to be blank text, so nothing will show in these fields, but each time you move from tab to tab, it will be re-evaluated, and the Let part of the formula will set the global variable $$lastTab to the name of the current tab (so be careful to keep the names). When a user leaves the layout, we can still refer to $$lastTab to know on which tab the user was last. To script returning to the layout (let's assume this tab control is on a layout named ContactAddress) and the last tab, first take whatever value was in $$lastTab and place it in a local variable (here named $targetTab), and after your Go to Layout step include a Go to Object step, using the local variable to calculate which object:

↑TOC

Start the week on Sunday OR Monday

Often we are asked to produce reports that show total sales (or student attendance, or some other number) by week. In order to produce these reports, we need some calculation that determines the week in which any activity occurred. What we can do is calculate the first day of the week that any given date falls in, and then group our activity by that first day.

For example, let's say we had records of sales every day in July 2006 up until Monday, July 10th. If we reduce each date to the first day of the week it falls in, then July 1st (a Saturday) is in the week starting 6/25/2006, the next seven days are in the week starting 7/2/2006, and the last two days are in the week starting 7/9/2006; we can then produce reports using these calculated dates. The simplest formula we've seen for determining the preceding Sunday for any given date (unless said date is already a Sunday) is:

But what if you want to run your weeks from Monday to Sunday, and need to find the preceding Monday instead? You can't just add one to the preceding Sunday, because then you'd get weird results when activity fell on a Sunday (in the above example, the preceding Monday for 7/9 would be the following day, 7/10). You need to do a little "shifting" first to your original date, subtracting the offset between Sunday and Monday, find the preceding Sunday, and then add back the offset to find the Monday to start your week.

Similarly, if you ween to start your week on Tuesday for some reason, you just use and offset of 2: Div ( date - 2 ; 7 ) * 7 + 2. ↑TOC

Find everyone with a birthday this month

With FileMaker Pro 8's ability to use wildcards in date searches, finding everyone with a birthday this month is easy. Assuming it's July, in Find mode, enter the following in the date field:

This will find everybody with a birthday in July, regardless of day or year. ↑TOC

Randomly and evenly distribute records in groups

A recent student in one of our classes had the need to randomly assign records to a numbered group (using a field named Group Number), and wanted to make sure the groups were as evenly populated as possible (one use of this technique would be to create random seating charts). One solution would be to start with Group 1, randomly select a person and assign them to that group, then continue with Groups 2, 3, 4 and so on, randomly selecting another person and assigning them to the group, looping through all the groups and making sure that you never selected a person that had already been assigned earlier. Our solution took the same essential idea and tackled it from a different angle: take ALL the people and randomly order them, then loop through them one by one, placing each in the next group until we ran out of groups and started over with the first. It boiled down to one calculation field, one global number field, and three script steps.

First, we defined a calculation named Rnd, equal simply to the function Random, and we made sure it was unstored so it would continuously evaluate as a new random number. gNumberOfGroups was a defined as a number field with global storage; the user would fill that in first with the number of groups to create. Our script consisted of just three steps:

The calculation for the Replace Field Contents script step used the following formula:

which will number the records in order from 1 to whatever number is in gNumberOfGroups, then start over at 1 again and again. ↑TOC

Regards,
Chad

Chad Novotny
Vice President - Technology