Tag: Unique

Level: Intermediate, Version: FM 13 or later

Unique Records Revisited… again, part 2

Kevin Frank

In part 1, we looked at a new way to count unique entries in a sub-summary report based on an arbitrary found set of sales data. To avoid redundant explanation, this article will assume the reader is familiar with part 1, but in a nutshell…

1. we used a pair of FM 13 summary list fields
2. to create found-set-aware multiline keys
3. which we related to special purpose table occurrences
4. which provided the basis for related value lists of salespeople
5. the ValueListItems function allowed us to grab lists of unique salespeople
6. and the ValueCount function allowed us to count them

The demo in part 1 was based on a single “flat table” of data. Today we’re going to unflatten that table, and see how the technique can be applied to a properly normalized system, i.e., with separate tables for Sales, People and Zones, related like so…

graph 1

Continue reading “Unique Records Revisited… again, part 2” →

Level: Intermediate, Version: FM 13 or later

Unique Records Revisited… again, part 1

Kevin Frank

Several recent postings on this blog have offered variations on a theme: using the new-in-13 summary list field type as the basis for a multiline relational key. The net effect is to produce a relationship that is found-set-aware. (If you aren’t familiar with summary lists, you can read about them here.)

Last week we looked at basing a value list on one of these “summary list relationships”, to facilitate counting unique values within the current found set…

4-2-2014 8-02-41 AM

…and today we have a demo file, FM-13-Count-Unique-v1, that extends the concept to sub-summary reporting.

4-1-2014 10-57-14 PM

Continue reading “Unique Records Revisited… again, part 1” →

Chart, Level: Intermediate, Version: FM 13 or later

FM 13: Column Chart on Found Set, revisited

Kevin Frank

Today I’m going to address a shortcoming in the “FM 13 Column Chart on Found Set” demo in my previous article. Specifically, I took a lazy path of least resistance re: calculating the chart title.

3-27-2014 11-47-00 AM

The challenge was to count the number of states in the found set, and since there weren’t many records in the original demo I decided to use a recursive custom function to build a list of unique states, and then the ValueCount function to count the number of entries in that list.

And that was fine. When the found set was small. Continue reading “FM 13: Column Chart on Found Set, revisited” →

Level: Intermediate, Version: FM 10 or later

Unique Records Revisited, part 2

Kevin Frank

When you consider how easily most common reporting tasks are accomplished in FileMaker Pro, the lack of a built in, clear cut method to count unique values within a given found set seems a bit surprising. But where there’s a will, there’s a way, and in part 1 we looked at a method that required the found set be sorted. Well I have some good news: today’s demo (Count Unique – Three Variations, 6.4 Mb) has no sorting requirement.

Here we have a flat file (single table) of contact information. For any given found set, the primary key (ID) will of course be unique, but each of the other fields will potentially contain duplicates, and we want to be able to quickly count the unique values without having to first sort the found set.

The basic approach can be summarized in a few words: Continue reading “Unique Records Revisited, part 2” →

Level: Beginner, Version: FM 8 or later

Unique Records Revisited, part 1

Kevin Frank

A while back I posted a technique to identify and count unique records. I recently discovered that while the technique is 100% reliable in terms of identifying unique records, under certain circumstances it will fail to count them correctly, due to what I believe is a bug in the way summary fields work. You can easily reproduce the bug by following the instructions in this demo file: broken summary field

Fortunately there are various ways to skin this particular cat, and today we’re going to look at one of them (demo file: broken summary field workaround).

The method for indentifying unique records hasn’t changed. Continue reading “Unique Records Revisited, part 1” →

General, Level: Intermediate, Version: FM 10 or later

Ranking Entries in a Summary Report

Kevin Frank

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

Yesterday we looked at a simple method to flag unique entries in a found set. This time, we’re going to look at an additional use for this technique, using the same data set and demo file as last time.

As you may recall, we have a simple table of sales data, and previously we produced a summary report sorted by salesperson, but reordered by total sales, so that the top performing salespeople appeared at the top of the report.

Continue reading “Ranking Entries in a Summary Report” →

General, Level: Intermediate, Version: FM 10 or later

Identifying Unique Records

Kevin Frank

[Update 15 May 2011: see this posting for additional information about this technique.]

Demo file: 2010-11-21-count-unique.zip (requires FM 10 or later)

A question that comes up regularly on various FileMaker forums is some variation on “I have a table of sales data for my organization. For a given found set within that table, it’s easy to produce a report grouped by salesperson showing number of sales, total sales amount, etc…

…and at the bottom of the report, I can easily display grand totals for number of sales and total sales amount…


Continue reading “Identifying Unique Records” →