5 Replies Latest reply on Mar 23, 2011 12:14 PM by hannahcho

    How can I count the values



      How can I count the values


      I would like to count the number of people who sign up for each month for in stance in April, May, Jun, July so that I can track the traffic for each month. I have a drop-down calendar when they sign-up and I want to count up the number by month.

      What kind of script do I need to use and is there any way the File Maker Pro will notice the date "04-12-2011" as "april" and count it as "1"? thanks much!!! I have been trying a lot and not successful so far.

      thanks  a million.

        • 1. Re: How can I count the values

          If each sign up is a separate record that includes a SignUpDate date field, you can set up a summary report with sub totals for each month.

          1. Start with a calculation field, cMonth defined as SignUpDate - Day ( SignUpDate ) + 1. Set this calculation field to return "date" as its return type. This calculation converts the date to the date of the first day of the same month for all dates in that month so we can sort and group by this field.
          2. Define a summary field as the "count of" signUpDate  (any never empty field will serve for this count).
          3. Create a list view layout and add a sub summary part "when sorted by" cMonth.
          4. Place your cMonth and Summary fields in this sub summary part to show the sub totals for each month.
          5. If you do not want to see the individual sign up entries grouped under each sub summary part, click on the body layout part label and press the delete key to delete it.
          6. Return to browse mode, sort your records by cMonth in ascending order and you'll have your report. (If you have FileMaker 9 or older, you must print or preview this report to see the sub totals.)
          • 2. Re: How can I count the values

            Thank you so much for your prompt answer. I have tried but since I just started using it it was very hard and not successful. I am looking for a way that shows the result on the same layout with the other data. I would rather not make the summary report but create a field that counts, so if the field is "april" count as "1" - this is my functioning. How can I make it as a script? is it possible for me to make one of this kind? one of big challenge is that the date field has value like "04-15-2011".

            thank you so so so much for your kind help. Today is my forth day of File Maker Pro. Your kind help is greatly appreciated.

            • 3. Re: How can I count the values

              $count  + If (application received =  "april"; 0 ;1) --> this script only add value "1" if the value happened in April. How can I total up?

              • 4. Re: How can I count the values

                If you just want to see the count for all records of the same month on your sign up layout, you can do this with a relationship called a "self join". This will likely introduce concepts and terms that are new to you so don't hesitate to ask questions if something doesn't make sense to you.

                I'm going to call your table "SignUpRecords" and the date field SignUpDate. I will also assume that you have defined cMonth as I recommended earlier.

                1. Open manage | Database | Relationships.
                2. Click the box labeled "SignUpRecords" to select it
                3. Click the button on bottom left with two green plus signs on it to make second "table occurrence" of this table. You haven't created a new table, just a "box" on this window that also refers to SignUpRecords.
                4. Drag from the cMonth field in one table occurrence box to the cMonth field in the other. This will give you this relationship:

                  SignUpRecords::cMonth = SignUpRecords 2::cMonth

                5. Now click the Fields tab and create a calculation field with this calculation: Count ( SignUpRecords 2::cMonth )
                6. Place this calculation on your layout and you'll see the count for all sign up records dated with the same month as your current record.


                If you'd like to learn more about table occurrences and how they control the function of FileMaker databases: 

                Tutorial: What are Table Occurrences?

                • 5. Re: How can I count the values

                  I figured out how to total up using summary field "total of" but i do not want to create the count field indicating if it happened in april, it gives the values "1". How can I just total up the sign-ups happened in april without making additional field for the new value for each month?

                  thanks a lot.