Counter for Unique Dates

[glenbeigh]

Hi all. Forgive the newbie question. I have a found set of appointments for which I need to know how many unique dates occur for these appointments. For example, I might have 40 appointments during the month, but they're spread across only 20 unique dates. I need my script to count the number of total unique dates which contain appointments (in this case 20, not 40). Any ideas? Thanks very much in advance for any help.

[Reed]

There's probably a better way to do this, but here is an example using a value list for the date field. The value list can be used in a calculation field to generate a return delimited list of the unique dates in the field (The ValueListItems() function). The ValueCount() function can then be used to return the number of items in this list.

unique.fp7.zip

[glenbeigh]

Reed, thank you very much for this idea. I appreciate your sample file. However, the value list for Dates doesn't depend upon the found set; instead, it returns all the unique dates in the field.

My found set of appointments could be any range of dates from one month to one year, usually a number of months.

I suppose I could set up a self-join relationship that depends on the current range of dates and then use the date field in this new table for the value list?

Might there be a more elegant way to do this? I figure this isn't such an uncommon need. I was hoping to be able to just Count the date field and be done with it... haha. Thanks again for the help.