First you need three calculation fields to use as keys in relationships for today, this week and this month. All three should return a value of type date. You'll also need corresponding calculation fields for the month and week match ups.
cToday: Get ( CurrentDate )
cThisweek: Let ( today = Get ( CurrentDate ) ; Today - DayofWeek ( Today ) + 1
cThisMonth: Let ( Today = get ( CurrentDate ) ; Today - Day ( Today ) + 1
These three calculation fields must be unstored in order for them to update correctly. cThisWeek computes the date for the preceding Sunday. cThisMonth computes a date for the first day of the same month.
Now define two more calculation fields. These should return date also, but must be stored calculations instead of unstored:
Assuming you already have a date field, DateCreated that auto-enters the creation date...
cWeek: Let ( D = DateCreated ; D - DayofWeek ( D ) + 1
cMonth: Let ( D = DateCreated ; D - Day ( D ) + 1
Now you can define 3 different relationships to different additional table occurrences of your existing table. I'll call your existing table, MainTable.
MainTable::cToday = TodaysRecs::DateCreated
MainTable::cThisweek = ThisWeeksRecs::cWeek
MainTable::cThisMonth = ThisMonthsRecs::cMonth
To add TodaysRecs, ThisWeeksRecs, and ThisMonthsRecs to Manage | Database | relationships, select MainTable, then click the button with two green plus signs to make an added table occurrence of MainTable. You can then double click the new table occurrence to bring up a dialog box where you can change the name to match what I've used.
Now the following calculations will produce counts for today, this week and this month:
Count ( TodaysRecs::DateCreated )
Count ( ThisWeeksRecs::cWeek )
Count ( ThisMonthsRecs::cMonth )
(Any field from the designated table occurrence that is never empty can be used in the count funtion and you'll get the same result.)
If "table occurrence" is a new term: Tutorial: What are Table Occurrences?
i appreciate the suggestion. i have a couple of questions.
am i creating three additional occurrences and then relating each field to a single field?
and... why do i need to do this? i mean... this seems overly complex and extremely EXTREMELY cumbersome. can you explain to me why the count fields would not just work in the one table? why must i create all these other tables? it just seems like a simple calculation field with the formula in it should work. why does it not?
You need three relationships as each matches to a different set of records. One matches to all for today, the second for this week and the third for this month.
You could get the same counts with a single summary plus a find and this may also work for you.
Define a single Summary field as the count of your date field.
Perform a find for all records with today's date and you have the count for today.
Perform a find for all records for this week, (you can enter a date range such as 4/10/2011...4/16/2011) and the same field will tell you how many for this week.
In similar fashion a find for all records for this month will count the records for this month.
If you used a set of three filtered one row portals with portal filter expressions that select for today, this week and this month, these portals could display your three totals. A single relationship like this could be used for each portal (and the portal borders can be invisibile so that these look just like ordinary fields on your layout):
MainTable::anyfield X MainTable2::anyfield.
MainTable2 is a second occurrence of MainTable and you'd place a portals to MainTable2 on a MainTable based layout to get your 3 record counts. This last approach requires FileMaker 11.
i must have done something wrong. my database has 47 records and the calculated fields i made following the steps accurately (i believe) and the resulting calculations are giving the following results
total for today: 734241
total for this week: 734237
total for this month: 734228
Which method did you use, the three relationships or the three filtered portals?
The numbers look like what you get when you convert a field of type date into a field of type number. They should be the Count calculation fields I described in my first post.
Assuming that they are the calculation fields and are set to return a "Number" as their return types, then you'll need to double check the details of the relationships. Are each of the date functions set to return "date" is your date field a field of type date?
the week field doesnt seem to be counting. the one for today and this month are both counting
my calculation is Let ( D = Date Created ; D - DayOfWeek ( D )) + 1
What value do you see in this field? Your expression is a tiny bit different. I have + 1 inside the parenthesis, but this should make no difference in the computed value. Is this a calculation field or a field of type date with an auto-entered calculation?
I'm assuming Date Created is of type date and is the same field you used in the other two calculation fields.
all threee fields are the same. calculations and results are numbers. the day count and month count work fine. the week count displays no result at all. the field is empty.
It should work, I use this expression frequently and in fact is a key calculation used in a Calendar Demo I recently made available. I can't see any reason from here why it's not working for you.
You may want to upload a copy of this to a share site, then post the download link here so that I or another forum participant can download a copy and take a look at it to see if wee can spot why it's not working.
i would love to do that! i will work on that and then get it to you to look at. thanks,
Yes, this IS an old thread. FileMaker has changed since it was started.
You can use the Year calculation to set up match fields that only match to records of the current or past years. But with the release of FileMaker 12, you also have the option of using ExecuteSQL to access this data.