How large could your found set get?
For small numbers of records, it may be simplest to loop through the found set and use variables to count the number of unique dates
Could you define a relationship that matches all the records in your found set?
If so, then you might actually be able to define a value list that lists all the values in your found set. Since a value list automatically drops out duplicate values, the number of items in your value list will be the number of unique values in your found set.
Example for counting all the unique dates included in a user specified date interval:
Define two global fields gDate1, gDate2 in your table.
Define a self join linking these two global date fields to your date field and call your new Table Occurrence, "SelectedDates".
Table::gDate1 < SelectedDates:: Date AND Table::gDate2 > SelectedDates:: Date
Define a Value list "SelectedDateValues" and specify SelectedDates:: Date as the source field.
Specify "include only related values starting from" and select "Table"
Now the expression
Will return the number of unique dates in the date range specified in gDate1...gDate2.
BTW this is where a filemaker equivalent to "select distinct" and "Select DistinctRow" would come in very handy.
Since you need the result based upon a found set then you don't need it to display dynamically in Browse mode. You need to use a summary report.
thank you all for your suggestions! I tried the first solution, but couldn't quite replicate it.
A summary field was by far the easiest, and I found it well documented with sample file here: http://www.fmforums.com/forum/showtopic.php?tid/135476/
It just requires doing a sort each time before seeing the summary total of distinct values, but no big deal for my purposes...
I agree with the first responder: filemaker should have some sort of "getdistinct" function, which would make life easier.
Although the method you refer to might work in your scenario, it is problematic because it involves a relationship - thus it precludes reporting on an arbitrary found set (what if the record marked as 'unique' is NOT in the found set?).
I'd suggest you look at another technique, described here.
Now that's clever.
Thanks. i think i miss how what you suggest is different from what i have:
“summ” Summary = count of date
“Countbydate” Calculation: = 1 / GetSummary( summ; date )
“Summary” Total = total of countbydate
If so, what do you suggest to change?
I did find out though that the total of countbydate often doesn't show up by itself. Only when i change it to running total and back to total, total shows up.
Also, i am still using a small sample of my database. The real thing has 900K records. Would that be a problem?
Sorry, I looked at another file later in the same thread you linked to. You don't need to change anything - it's the same method as I suggested.
The real thing has 900K records. Would that be a problem?
Well, that depends on how many groups you have and their sizes (I think). In any case, you won't find anything faster than summary fields - unless you start storing some of the summary info. Then it will be fast in-between changes.
great. That should work then. Thanks again for your help.