Category: Version: FM 13 or later

Level: Intermediate, Version: FM 13 or later

A Fresh Approach to Deduplication

Kevin Frank

Editor’s Note: Today I’m pleased to present a guest article by Jon Rosen featuring a creative and performant approach to removing duplicate records.

Recently, I had a situation where I had a found set of more than 500,000 records, but over ⅔ of them were duplicates. To remove the dupes, I initially went with the time-honored method I’ve been using for the last twenty years. I’ve been using it so long, that the original version used global fields because script variables hadn’t been created, yet. But now it seemed to run glacially slow on this large file.

After giving it some thought, I came up with a new method of deleting duplicates that turned out to be simpler and many times faster than the older method. So, let’s start with a review of the original method. There’s a good chance you may be using it yourself.

Eliminate Duplicates (Old Method)

Continue reading “A Fresh Approach to Deduplication” →

Level: Intermediate, Version: FM 13 or later

PSOS – Run Script in File not open Locally

Kevin Frank

Update: See Jason Wood’s suggestion in the comments section for making this technique more secure.

INTRODUCTION

Today I want to take a look at a certain Perform Script On Server (PSOS) behavior, and for those who find this behavior to be inconvenient, propose a work around.

Here’s the behavior, or misconception, actually: a server side script initiated via PSOS (apparently) cannot access files on the server unless the user already has those files open locally. But of course there may be circumstances where you’d like to be able to access files server side that the user does not have open client side, either because the user’s credentials do not allow access to those files, or because you’d rather not have to open the files client side merely to facilitate a PSOS call.

If we consult the online help entry for running scripts on server, the official word is that server-side scripts can access other FileMaker files only when…

…in other words, if you want PSOS to be able to access files, you need to either a) already have the files open client side, or b) throw caution to the wind, and configure the hosted files to auto-open with pre-entered credentials.

Hmmm… I think we can agree that from a security standpoint “b” is a non-starter, and the whole point of this article is to avoid “a”. Fortunately there is a “c” option not mentioned above which I’ll get to shortly, but first let’s demonstrate the issue. Continue reading “PSOS – Run Script in File not open Locally” →

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

2-Column Magic Value Lists

Kevin Frank

Disclaimer: the techniques shown in this article are provided on an as-is basis. Use with appropriate caution and at your own risk.

Acknowledgements: today’s article would not have been written without the inspiration provided by Bruce Robertson, John Ahn (FM 12 ExecuteSQL Unconference Session), Andries Heylen (Magic Value Lists), Marcelo Piñeyro (How to Extend FileMaker Value List Sort Capabilities) and Jonathan Fletcher (It’s Sorta A Value List Thing).

Introduction

Last year, in Custom Field-Based Value Lists, we looked at a technique to enable single-column field-based value lists to display their contents in entry order, as opposed to standard index-based alphanumeric order. For example using the contents of this field (from a single-record table) as the basis for a value list…

2019-10-13_135535.png

…we came up with a way to sort our value list (a.k.a. “VL”) items like so…

2019-10-13_135628

…as opposed to what you would get with a standard field-based VL, i.e., this:

2019-10-13_135646.png

I ended the article by saying…

Today’s article looked at value lists based on return-separated values in a single field. In an upcoming article we’ll explore implementing a similar technique for sorted two-column value lists based on entries in a dedicated value lists table where each list item lives in its own record.

…and now, nineteen months later, am ready to pick up where we left off last time, and to avoid repetition, will assume the reader is at least somewhat familiar with the issues and techniques covered in that article (Custom Field-Based Value Lists).

Note: if you aren’t clear on why a 2-column value list might be preferable to its single-column counterpart (or to a custom value list for that matter), check out Thinking About Value Lists, part 1.

Demo Files

Continue reading “2-Column Magic Value Lists” →

Level: Intermediate, Version: FM 13 or later

Conditional Summary Report Header

Kevin Frank

Recently I received an email wondering…

When printing a multipage report sorted by a sub-summary, is there a way to display the sub-summary heading on the following page when it breaks across two or more pages?

There are various approaches one might take, and I actually buried one of those approaches inside an article + demo file posted on this site back in 2013 (PDF Catalog with Table of Contents). At the time we didn’t have “Hide Object” so I used conditional formatting in conjunction with a special-purpose “helper field” to flexibly display the conditional heading.

Well today we’re going look at an approach that doesn’t require a helper field, and you are welcome to follow along in this demo file if you are so inclined: conditional-summary-report-header.zip

2018-09-30_18-36-20 Continue reading “Conditional Summary Report Header” →

Level: Intermediate, Version: FM 13 or later

Fast Summaries Re-revisited

Kevin Frank

My recent “Virtual List on Steroids” presentation at DIG-FM, and the preparation of a revised version to give at dotFMP next week, has prompted me to reconsider certain assumptions re: optimizing performance both locally, and across a LAN and/or WAN.

2018-05-29_094509

Specifically, there are two things we’re going to look at today. First, a way to dramatically speed up sorting on related data. Then, having incorporated that trick into the Fast Summary approach, we will compare Fast Summaries vs. Multi-Finds under various scenarios.

Demo files (structurally identical):

Continue reading “Fast Summaries Re-revisited” →

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

Custom Field-Based Value Lists

Kevin Frank

Introduction

Does this sound familiar? You’ve rolled out a solution that includes a single-record Settings table where authorized users can enter/update various system-wide settings, including one or more fields where the items will appear in popup menus… and then comes the inevitable question: Why don’t items in popup menus appear in the order I entered them? 

2018-03-09_14-44-10
The “problem”: field-based value lists sort alphanumerically

It’s a reasonable question, and in the past you might have replied, “Because field-based value lists rely on a field’s index, and their sort order is alphanumeric”… but after reading today’s article your new answer will be: “No problem — let me take care of that for you.”

2018-03-23_120634
From demo #4: field-based value lists mirroring the order of their underlying source fields

Interestingly, the above is accomplished with the same value list attached to all three popup menus, and no script triggers… or scripting of any kind for that matter. Read on to learn how.

Continue reading “Custom Field-Based Value Lists” →

Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Reporting, part 3

Kevin Frank

Update 30 September 2021: while this article is worth reading for background information, I now recommend using the approach utilized here: Virtual List Reporting, part 4

Last year I posted a couple articles on the topic of virtual list reporting (part 1 and part 2), a.k.a. VLR. Today we’re going to look at some additional things you can do with VLR, and to avoid a lot of repetition, this article will assume the reader is familiar with the material covered in part 1, wherein benefits are extolled and fundamentals explained.

2017-07-21_18-46-06

Demo file: VLR-part-3.zip  (3.4 Mb compressed)

Did you notice in parts 1 and 2 that we were limited to producing a single report at a time? Yes, we could sequentially output them and concatenate them into a PDF, but we couldn’t display multiple reports on-screen simultaneously, nor could we interact with them in browse mode.

This time around we’re going to look at a method to produce multiple independent reports concurrently, each in its own window, based on a single virtual list table. This will entail generating a large number of dynamically instantiated $$vars[withReps], so we will also explore a way to automatically clear them out (without the overhead of having to keep track of them during instantiation).

Disclaimer/Warning: before running multiple Customer reports, open your Data Viewer and activate the Watch tab. You don’t want to display the Current tab with 5K or more variables in memory, unless you’ve got a lot of… time… on… your… hands.

Continue reading “Virtual List Reporting, part 3” →

Level: Intermediate, Version: FM 13 or later

PDF with Embedded Links

Kevin Frank

Author’s note: I’d like to offer a big thank you to Christian Schmitz of Monkeybread Software, not only for creating and continually improving a fantastic plug-in, but also for promptly and patiently answering my many questions.

A few weeks ago we looked at creating a table of contents for a PDF containing nine individual reports. The article ended by asking a somewhat rhetorical question…

Now that we have a table of contents for our master report, wouldn’t it be nice if we could hyperlink each TOC entry to its corresponding report within the PDF?

2017-02-10_180551

FileMaker doesn’t provide a built-in way to accomplish this, but it can be done using a combination of the MonkeyBread plug-in + DynaPDF Lite, and this will be explored in my next article.

…and today we’re going to dig into the nuts and bolts of what it takes to make this happen.

As usual, we have a demo file (Embedded PDF Links), but since it will only work for developers having licensed copies of the above-mentioned products, here is a PDF showing the end result: Report with Hyperlinked TOC.pdf

Continue reading “PDF with Embedded Links” →

Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Table Of Contents

Kevin Frank

A few years back I did an article on producing a PDF catalog + table of contents, with the TOC based on a vendor table and the catalog entries based on a products table. Today we’re going to use a different approach to generate a table of contents, building on techniques explored in last year’s Virtual List Reporting series… specifically we’re going to generate one big PDF combining nine reports, prefaced by a table of contents.

2017-01-30_173715

Demo files: virtual-list-toc-v1.zip and virtual-list-toc-v2.zip

2017-01-30_201617

Continue reading “Virtual List Table Of Contents” →