Count specific dates in portal?

[krh]

Hello Everyone.

I am trying to get a count on how many entry dates are in september of 2010. i am using the following calculation

If(Month ( attendence::A_DATE )="9" and Year ( attendence::A_DATE )="2010";Count (attendence::A_DATE);"")

And I get the count of all the entry dates. How do I get it to only count the September dates?

Thanks!!

[bruceR]

You're making an assumption about how FileMaker processes calculations that isn't correct. I can see how you think it might work; but it doesn't work that way.

For one thing, you are going to have to more explicitly process each item in the list of dates. There are lots of ways to approach the problem.

One way is to create a new field, dateNum, calculation;

Year( A_Date) & right( "00" & month( A_Date); 2) & right("00" & day( A_Date); 2)

Then 9/23/2010 becomes 20100923

Then this would work:

Let( date_list = list( attendance::DateNum);

patternCount( date_list; "201009")

)

[comment]

patternCount( date_list; "201009")

Careful there: October 9, 2020 is not that far away.

[bruceR]

Good point, thanks. An alternative approach would be... ?

Maybe a decimal version? 2010.0923?

[LaRetta]

Hi krh,

How do you want to use the result? You can perform a find for 9/2010 and then use summary report to count them. You could use a filtered relationship using a global date field in the relationship, or since you have vs. 11, you could use a filtered portal and count them.

You can use a global from anywhere (in example, it is from preferences). Any month/year date you type into global will assume that is the month/year you want filtered. Note the script trigger on the global field.

HowManyDates.zip

[LaRetta]

Actually, Michael, I like this idea. And I can't see why it wouldn't work. I can't make it break depending upon the example you provided (please see attached). Can you explain what I don't see?

Of course I'm not crazy about the extra field in child AND extra calculation in parent but I'm not crazy about slow speeds on filtered portals (when very large data-sets) either. But at least the result is a calculation which would be more flexible than the filtered portal count.

datenumbers.zip

[comment]

You are not using the proposed calculation (8 digits instead of 6).