Tag Archives: GetField

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

Easy Sorting of List Views, part 3

Update 22 Jan 2013: Demo file and screen shots have been revised to fix bugs identified by Matt Ayres and David Schwartz (see comments at the end of article).

Ever since I posted part 2 of this series, I’ve been torn between, on the one hand, wanting to move on to other topics, and on the other, the realization that I wasn’t quite done with this one yet. So, here is what I expect will be my final posting, and final demo (dynamic list sorting, v3 rev5), on this subject.

Thus far, we’ve looked at various methods to facilitate dynamic list sorting (by “dynamic” I mean that the field to be sorted is determined programatically). Most of these methods use two fields — one of them uses four — and you can see them all in part 2.

But in the back of my mind has been the knowledge that Ugo Di Luca pulled this off with a single field back in 2004 (EasySort.fp7, shared by permission of the author, and previously discussed last April in an article entitled Portal Sorting, pt 3).

Continue reading

Easy Sorting of List Views, part 2

Well, I thought I’d said everything I had to say on this subject, but yesterday afternoon I noticed a glaring omission in part 1’s demo — what happens if the user manually unsorts the found set?

The sort indicator doesn’t disappear the way a good little sort indicator should. Fortunately this is easily remedied, thanks to the Get(SortState) function. Continue reading

Easy Sorting of List Views, part 1

Earlier this year, I posted a three-part series on Portal Sorting, and part 2 focused on dynamically sorting a portal when a column heading was clicked. Well, with just a few tweaks, this technique can be applied to dynamic sorting of found sets, and of course the most likely place to employ something like this would be on a list view.

I should note that on very large found sets, sorts using this technique can be noticeably slower than traditional “hard-coded” scripted sorts. (Performance is fine with normal found set sizes.) The benefit of using this technique, is that a new field can be added to a layout and sort-enabled in about 60 seconds without touching the script itself.

Continue reading

Portal Sorting, part 3

Today we’re going to look at a couple more approaches to dynamic sorting, from opposite ends of the complexity spectrum. The simple one, portal sorting, circa 2002, is something I built in the FM 5.5 era. It uses a “smoke and mirrors” approach to achieve its objective, and apart from converting to .fp7 format, and consolidating into a single file, I’ve left it as is.

Behind the scenes this file has eight relationships from the parent table to the child; all based on the same keys, but with different sort orders. Continue reading

Portal Sorting, part 2

The other day we looked at static portal sorting, where the developer decides in advance how the portal will sort, and “hard codes” those settings into the portal. Sometimes, though, we want to provide users with an interface where they can dynamically sort a portal by clicking on column headings…

…and we’re going to look at a technique to accomplish this today. Continue reading

Selective Modification Timestamp

Demo file: 2010-11-25-selective-modification-timestamp.zip (requires FM 8 or later)

Have you ever wished for a modification timestamp that would only update if a user manually edited a field? In other words, a timestamp that would not update based on scripted actions, or non-field-based activity like creating, duplicating or importing records? This can be achieved in a variety of ways, but the technique here uses a simple auto-enter calc, which I was introduced to by Nick Orr at Goya Pty Ltd.

Define a timestamp field, ts_mod_selective, with this auto-enter syntax:

Let ( [
trigger = GetField ( "" ) ; // note the innovative use of GetField
ros = Get ( RecordOpenState ) ;
ies = IsEmpty ( Get ( ScriptName ) ) ;
ts = Get ( CurrentHostTimeStamp )
] ;
Case (
ros = 1 ; "" ;
ros = 2 and ies = 1 ; ts ;
ts_mod_selective // for FM 9 or later, we could use Self
// instead of specifying the field name
) // end let

Note: if you just want the date or time component, you can enclose all of the above in GetAsDate() or GetAsTime(). And of course, this technique can easily be adapted to work with modification account name as well.