13 Replies Latest reply on Dec 17, 2014 8:01 AM by philmodjunk

    counting records for todays date

    gasolineman

      Title

      counting records for todays date

      Post

      i have a database that i would like a field that will display the total number of records created for todays date.

      ideally, i would like another field that will calculate the number of records for this week, and one for this month.  if possible.

      suggestions?

        • 1. Re: counting records for todays date
          philmodjunk

          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?

          • 2. Re: counting records for todays date
            gasolineman

            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?

            • 3. Re: counting records for todays date
              philmodjunk

              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.

              • 4. Re: counting records for todays date
                gasolineman

                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

                • 5. Re: counting records for todays date
                  philmodjunk

                  Which method did you use, the three relationships or the three filtered portals?

                  • 6. Re: counting records for todays date
                    gasolineman

                    three relationships

                    • 7. Re: counting records for todays date
                      philmodjunk

                      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?

                      • 8. Re: counting records for todays date
                        gasolineman

                        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

                        • 9. Re: counting records for todays date
                          philmodjunk

                          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.

                          • 10. Re: counting records for todays date
                            gasolineman

                            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. 

                            • 11. Re: counting records for todays date
                              philmodjunk

                              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.

                              • 12. Re: counting records for todays date
                                gasolineman

                                i would love to do that!  i will work on that and then get it to you to look at.  thanks,

                                • 13. Re: counting records for todays date
                                  philmodjunk

                                  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.