8 Replies Latest reply on Apr 29, 2010 5:37 AM by comment_1

    Count IF



      Count IF


      Hello everyone and thanks for helping me sorting this problem out :smileyhappy:


      I would like to make something similar to a count_if, and have it displayed on the Header of a layout.

      I have a table with a date field in it the dates is a scheduled appointment now I would like to count the number of appointments perform in a certain interval of time and have then ever present in the Header part of the layout.

      If I perform a find like DATE .. DATE I get a found set number that is the number I would like to have always visible on the header. So that I can have a quick view of the number of visits in the next week month quarter...


      Any Ideas?

        • 1. Re: Count IF

          Create a summary field ( count of YourTable:: Id )




          a calculation field : Get ( FoundCount )

          • 2. Re: Count IF

            Grazie Daniele, however it does get me the correct result only for the current  find count, while I have several queries I want calculated at the same time.

            In few words I would like to have the Header contain a sort of status bar that says the found count for One Week, then For One Month, One Year and so on. I want something like saying: Count the records as if we were filtering by week, then to its side another that says: count the records as if we were filtering by month and so on (And they must not be dependable from the actual filter is in use at that very moment).

            • 3. Re: Count IF

              No other way of perform it?

              • 4. Re: Count IF

                You can define relationships that match records by week, month and year (that's three different relationships).


                Three different calculation fields that use count and reference a different relationship can give you your three counts.


                You can set this up off the date field of the current record or a date field you define as a global field.


                You'll need 3 calculation fields to identify the first day of the week, the first day of the year and the year:


                cWeekStart: DateField - DayOfWeek(DateField) + 1 (calculation set to return Date)

                cMonthStart: DateField - Month(DateField)  + 1 (calculation set to return Date)

                cYear : Year ( DateField )


                Using the current record, you'd enter Manage | Database | Fields, select your table and use the button with two green plus signs to make 3 more table occurrences of this table.

                Rename the new Table Occurrences SameWeek, SameMonth, SameYear

                Your relationships would be:

                YourTable::cWeekStart = SameWeek::cWeekStart

                YourTable::cMonthStart = SameMonth::cMonthStart

                YourTable::cYear = SameMonth::cYear


                Now you can define your count calculation fields:

                Count ( SameWeek:: DateField)

                Count ( SameMonth:: DateField)

                Count ( SameYear:: DateFIeld)

                • 5. Re: Count IF

                  Thanks Phil


                  It works as expected; I hoped for a more straightforward formula, but if this is the way to do it I will have to create many many new relathionships :smileywink:


                  Thanks again


                  • 6. Re: Count IF

                    Which version are you using?

                    • 7. Re: Count IF

                      I am using FM10 advanced I was wondering if it was worth to upgrade but in Europe they make a 1=1 to the US$ making it quite an expense :(

                      • 8. Re: Count IF

                        The advantage of v.11 in this aspect is that you can filter a portal - thus you can have several portals based on the same relationship, each filtered in another way.



                        BTW, I believe you can purchase Filemaker in US$ even in Europe - try:



                        Or just wait until the Euro drops down even further... :smileywink: