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

    Count IF

    alexcam

      Title

      Count IF

      Post

      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
          raybaudi

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

           

          or

           

          a calculation field : Get ( FoundCount )

          • 2. Re: Count IF
            alexcam

            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
              alexcam

              No other way of perform it?

              • 4. Re: Count IF
                philmodjunk

                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
                  alexcam

                  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

                  Alex

                  • 6. Re: Count IF
                    comment_1

                    Which version are you using?

                    • 7. Re: Count IF
                      alexcam

                      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
                        comment_1

                        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:

                        http://www.winsoft-international.com/en/store/

                         

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