2 Replies Latest reply on Mar 4, 2011 10:29 AM by BruceWyrwitzke

    Count records based on date criteria



      Count records based on date criteria


      Sorry for sounding like a moron - trying to make the transition from MS Access to FMP and it's been a slow process...

      I have a contact management database that stores basic contact info in one table and key dates in another.  My goal is to have a start page/layout that lists events for the next 7 days that I can click on and then open a layout with just those records.  Here's a simple example of what I would like on the "main page" when the databse opens:

      "There are XX birthdays in the next 7 days."

      I've tried setting a global variable in the key_dates table based on a calculation using

      count(month(Birthday) = month(Get(CurrentDate)) and day(Birthday) = day(Get(CurrentDate))

      but I keep getting errors.  Any help would be greatly appreciated.  Thanks.


        • 1. Re: Count records based on date criteria

          Count simply counts the number of fields that are not empty. What fields it counts depends on syntax and any relationships you define.

          Count ( RelatedTable::Field ) for example, returns a count of all related records where field is not empty. Thus, this is the most typical use of count to count records, with the relationship controlling which group of records are to be counted.

          Where are the birthdays you want to count? Do you have a table of records where each of these birthdays is in a separate record? Does your "main page" layout refer to this table or a different table in layout setup?

          If your "main page" layout refers to the table where these birthday records are recorded, you may want perform a find to locate those reocords, then use a Count type summary field instead o the count function to count your records.

          • 2. Re: Count records based on date criteria

            Thanks Phil.  I figured out a way to accomplish what I was looking for by using the following script:

            Go to Layout ["Contacts-Main"(Contacts)]

            Set Variable [$temp_date_1; Value: GetCurrentDate)]

            Set Variable [$temp_date_2; Value: $temp_date_1 + Admin_Options::g_Look_Ahead]

            Enter Find Mode []

            Set Field [Key_Dates::Birthday; $temp_date_1 & "..." & $temp_date_2]

            Perform Find []

            Set Variable [$$Birthdays; Value: Get(FoundCount)]

            Go to Layout ["Switchboard"(Contacts)]

            On the "switchboard" layout, I have a text block with <<$$Birthdays>> to display the number of birthdays in the next 7 (or whatever value the user sets with g_Look_Ahead).  Probably not the cleanest way to do things, but I'm learning...slowly :)