Category Archives: Version: FM 12 or later

FM/SQL Portal Filter + Dynamic Sort

Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed)

browseaksortcitycomp Continue reading

Set Variable by Name

Recently, in the midst of various reporting and charting projects, I’ve found myself wishing for an easy way to “dynamically instantiate” one or more variables… in other words, set them by name, with the name determined programmatically, as opposed to being hard coded.

This issue has come up from time to time on this blog over the last five years or so, most notably in Dynamic Variable Instantiation, where I observed that…

2016-04-06_074440

…and then proposed an unwieldy mass of gobbledygook as a workaround, e.g.,

2016-04-06_083324

Actually, the preceding isn’t too bad for occasional use, especially if the value you’re passing to the variable is as simple as the GetSummary string shown above. Continue reading

PDF Trickery

If you produce reports or other FileMaker output in PDF format, here’s a little trick that can be a real time saver in terms of development.

Imagine this: a client receives a nightly report via email in PDF format, and asks you to make sure it will fit on a single page in landscape orientation. The report always contains three groups of 12 records, with each group preceded by a leading subsummary, a latticework of horizontal and vertical gridlines, etc.

It is a busy, crowded report, but with patience and fine tuning, you manage to get everything to fit on a single page. Just. Barely.

Now the client wants you to add a trailing subsummary to each group, but of course still expects everything to fit on a single page. The report is already at 8pt type, with tight vertical margins, and you are not looking forward to a bunch of time-consuming heroic measures to accommodate this request, so instead you employ the following trick. Continue reading

Successfully Find Duplicate Values Within A Set Of Records

The need to isolate duplicates within an existing found set has often been a source of frustration for developers. A couple weeks ago we examined a technique by Ralph Learmont demonstrating that, contrary to popular belief and experience, the ! (find duplicates) operator can, under certain conditions, be coerced into isolating duplicate values within a found set.

4-24-2015 3-24-21 PM

Here’s Ralph’s original statement of the problem:

There’s a quirk in the way Filemaker deals with duplicate records. This makes it difficult to find duplicate records WITHIN a found set of records. If you try to constrain the Find to the current set of records, you might discover extra spurious records appearing. These records have “partner-duplicates” outside of the found set. These unwanted spurious records will appear as single occurrences when you inspect a sorted column. Technically they are duplicates. It’s just that their partners lie “outside”, hidden in the omitted slab of records.

And why it’s a cause for concern:

I think it’s important to explain why finding duplicates in a found set is something that often appears to work… and it won’t be apparent there actually IS AN ISSUE unless one goes to the trouble of actually sorting results and checking for those “single-occurrences”.

While the technique was blazingly fast, it seemed to me there was something a bit voodoo-like about Ralph’s previous demo, as well as my attempt at simplification. Apparently Ralph wasn’t satisfied with it either, because he has provided a new demo (Find Duplicates in Found Set) showcasing a more straight-forward technique, and kindly agreed to share some of his thoughts about it as well. Continue reading

Non-Breaking Reporting Groups

Today we’re going to look at a reporting challenge that comes up from time to time, and a technique that can be useful to have in your developer bag of tricks.

In the demo file, Non-Breaking-Groups, we have a simple personnel file containing employees spread across all 50 U.S. states, with between two and nine employees per state.

1-20-2015 8-45-43 PM

We have a requirement to produce a report showing personnel grouped by state, like so:

1-20-2015 4-16-27 PM

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

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

Generating Sample Data

Have you ever wanted to generate sample data for one of your solutions that was random in certain ways, but within a defined range or scope? For example, assuming an invoicing solution with existing tables of customers and products…

  • choose 20 customers at random
  • create between 4 and 8 invoices for each of them
  • dated anywhere between 1 September and 31 December 2013
  • choose products at random
  • from the subset of products where price is between $30 and $50
  • create between 5 and 25 line items per invoice
  • with a quantity for each line item between 1 and 24

Wouldn’t it be nice to wave a magic wand to solve challenges like this? Well, I have some good news: Jesse Antunes and Geoff Coffey made that magic wand available, in the form of a custom function, back in 2007 on the SixFriedRice blog (Creating Random Numbers in FileMaker)… but I confess that I didn’t really grasp how useful it could be till Beverly Voth mentioned it in her article on Aggregates in Filtered Portals.

12-31-2013 2-24-17 PM