FM 13: Anti-deduping, part 1

Recently there was a question about cleaning up a found set on one of the FileMaker discussion forums. When a question of this nature arises, it’s typically some variation on “How can I remove [or delete] duplicate entries?” But this was the opposite: For a given found set of customers, how can I omit those whose Zip codes only appear once in the found set?

In other words, keep the records whose Zips appear multiple times and banish the others.

Note that the challenge was starting from a subset of records. If the challenge had been for all records in the table, one could simply search on ! (find duplicate values) in the Zip code field. However, this trick won’t work when starting from a found set rather than all records. And constrain won’t help here because it doesn’t play nicely with the ! operator.

Off the top of my head, I suggested…

Sort by Zip code, then loop through the found set from top to bottom… using GetNthRecord() test the current record’s Zip code against the previous record and also against the next record. If both tests are negative, omit, otherwise go to next record (and of course exit after last).

As it turned out, it was a one-time cleanup task, and my suggestion was good enough. But I had a nagging feeling there were better-performing ways to go about this, and today’s demo file,  Anti-deduping, part 1, presents four different methods. I encourage you to download it, experiment, and add your own methods or variations… perhaps you’ll come up with a faster approach, in which case, needless to say, I hope you’ll post a comment at the end of this article.

9-21-2014 2-36-38 PM

Continue reading

FM 13: Popovers in Portals

26 Aug 2014: fixed minor bug in “tab control in a portal” demo and updated article text.

The popover object in FileMaker 13 presents developers with a wealth of new design opportunities (a number of which were discussed in this article: FileMaker 13 Popovers). But if you’ve tried to use a popover inside a portal, you may have noticed that embedded portals, tab controls and slide controls within those popovers simply do not work as expected… or at all, for that matter.

Well, today we’re going to look at a simple work around, which was inspired by Ray Cologon’s brilliant Screen Popovers demo, and also utilizes David Thorp’s clever technique to enforce popover modality (i.e., prevent the user from dismissing the popover until certain conditions have been met)…

8-25-2014 4-53-35 AM

…with help from these three demo files: Continue reading

Shaking the Dependency Tree

Editor’s Note: few names in the FileMaker community command as much respect and  admiration as that of today’s guest author, Darren Terry. This article is a distilled summary of Darren’s popular “Shaking the Dependency Tree” presentation, as seen at the PauseOnError unconference, the FileMaker Developers’ Conference, and FMPUG user group meetings.

8-7-2014 9-27-05 PM

Demo file: Shaking The Dependency Tree.zip 

INTRODUCTION

FileMaker Pro has a very powerful calculation engine built into it. The calculation engine is exposed to us in a number of ways: calculation fields, script steps, conditional formatting, filtered portals, web viewers, chart objects, etc. But it can be a challenge to determine precisely when various kinds of calculations will refresh, and what causes them to refresh. Continue reading

Extending FileMaker’s PDF capabilities with PDFtk Server

One of the great things about FileMaker is its ability to not only create a PDF from a single “print job”, but to take a series of separate jobs and concatenate them into a PDF via the “Save Records As PDF [append]” feature, like so:

7-13-2014 2-20-22 PM

(The above code comes from PDF Catalog with Table of Contents, which appeared on this site last year.) Basically, anything you can print from FileMaker, you can instead output as a PDF.

But what if you have existing multipage PDFs stored in container fields, and want to combine them into a larger PDF? This is something FileMaker cannot do natively. Recently I had a need to do just this, and happened to mention it to Jon Rosen at ErgoSoma, who suggested I take a look at a cross-platform utility called PDFtk Server.

7-15-2014 8-23-53 AM Continue reading

Process Lock that Clears Itself

If you build multi-user business systems, you may have had a need at one time or another to temporarily “lock” a process, so that only one user at a time can perform it. A typical drawback to most process locking approaches is that the lock does not automatically clear if the user who locked the process crashes or otherwise bails out of the system abnormally.

Well, I have some good news: Bob Gossom of Absolute Advantage has come up with a very clever, and well-documented, self-clearing process lock technique, which I’m pleased to share here with his permission.

6-24-2014 2-33-36 AM

Continue reading

Show/Hide Formatting Bar

Note: A special thanks to Jason DeLooze for sharing this technique, which neatly remedies a small, but vexing, annoyance.

Demo file: Show-Hide-Formatting-Bar.zip

6-13-2014 10-04-55 PM

Have you ever wanted to have a script toggle the Formatting Bar on/off in browse mode? If so, you will discover that FileMaker does not provide a “Show/Hide Formatting Bar” script step.

Continue reading

FM 13: Virtual List Charts, part 2

Picking up where we left off in part 1, today we’re going to take a look at examples 2 through 6 in the Virtual List Charts demo file (the demo has been updated since part 1, so I recommend downloading a fresh copy).

5-16-2014 4-08-08 PM

We covered example 1 and most of the general concepts last time, so today we’re mainly going to touch on specific points of interest, but to briefly recap… Continue reading

Unicode

Update 5 May 2014: article and demo have been updated as per observations by Jeremy Bante in the comments section.

This weekend I threw together a Unicode utility file (unicode-characters.zip). It takes the values 1 through 65535 and converts them into Unicode symbols, and makes it easy to copy characters and values into the clipboard.

5-5-2014 12-10-29 PM

(The original version of the demo had 99999 records, but as pointed out by Jeremy in the comments section below, the range 65537 – 99999 is a repeat of the range 1 – 34463, with 65536 being a special case.)

Any value over 99999 the Char function will interpret as multiple characters, and bear in mind that multiple values are interpreted a) from right to left, and b) in five digit blocks with leading zeros, except the leftmost one will not have leading zeros.

Continue reading

Live Development + “New Record”

Recently there has been discussion in the FileMaker community regarding a known problem with live development (i.e., modifying database schema while other users are connected to the system). The problem is that under certain circumstances, the “New Record” command and script step will fail.

4-28-2014 5-10-52 PM

The aim of this article is to identify some specific circumstances under which New Record will fail, and also identify some specific circumstances under which it will *not* fail, and is based on a series of tests conducted using FMS 13v1 and a couple clients running FMP Advanced 13v3.

Disclaimer: These are preliminary findings and do not claim to be exhaustive or conclusive. If you are doing live development, you should test for yourself and draw your own conclusions.

Methodology: A system with two tables, Sales and Employees, was used. For all tests, the developer opened Manage Database and then did something (or in the case of the first test, nothing), but then did not click OK — instead the Manage Database dialog was left open. In no case was the “OK” button clicked.

Continue reading

FM 13: Virtual List Charts, part 1

4-20-2014 11-18-24 AMToday we’re going to look at applying the virtual list technique to FileMaker charting with the goal of producing a reusable chart “object”, or rather, a series of chart objects. We’ll need more than one because while certain attributes (e.g., chart title) can be set programmatically, others, including type (e.g., column or line), must be hard-coded into the chart object.

We’ve already explored Bruce Robertson’s virtual list on this site a number of times, but briefly, you create a utility table in your solution to facilitate non-standard viewing, reporting, etc., and pre-populate it with “more records than you’ll ever need”. The records in this table will derive their data “virtually”, by parsing it from an array — typically one or more $$variables.

Well it turns out the technique can be applied to charting as well, and today we have a demo file, Virtual List Charts, that contains six examples: three for Web Visits…

4-20-2014 2-03-32 PM

Continue reading