2 Replies Latest reply on Jun 11, 2012 12:42 PM by JosephOrtega

    How to count how many times an email address appears in a table

    JosephOrtega

      Title

      How to count how many times an email address appears in a table

      Post

      I'm making a solution that will tell me how many of my customers have participated in various promotions that we've had. I have a table that has a record for each time a customer has participated in a promotion. I'm trying to figure out a way to make a report that lists the customers that have participated in the most promotions. I need to figure out how to make a calculation that counts the number of times a particular email address appears in that table. 

      I'm a FileMaker newbie. I've been searching the internet for days and reading all about calculations, but I haven't had any luck figuring it out. I'd really appreciate it if anybody has any ideas how I should go about this, or if anybody could point me in the right direction. Thanks! 

        • 1. Re: How to count how many times an email address appears in a table
          philmodjunk

          You need to research aggregate functions and summary fields.

          Using a summary field:

          Define a summary field in this table as the "count of" any field that's never empty. The email address field will suffice in this case.

          On a list view layout based on this table, add a sub summary layout part "when sorted by" the email address field. Place your summary field in this sub summary part.

          Return to browse mode, show all records and sort your records by the email address field. The summary field will now show a total count for each email address. If you take another look at your sort records dialog, there's an option at the bottom where you can 'reorder' the records based on a summary field. You can use this option while sorting on your email address field to put the grou of records with the largest count first, then next largest group second, etc.

          If you remove the body layout part from your layout and just keep the sub summar part, you can get a report with one row for each email address and a count of how many times it is present in the current found set.

          Using an aggregate function in a relationship.

          Define a relationship such as:

          Customers::Email = PromotionHistory::Email

          Define a calculation field in customers as:

          Count ( PromotionHistory::Email )

          and you'll get the count of all records in Promotion History with an email address that matches the current record in Customers.

          Using a a summary field in a relationship:

          If you place the summary field from option one on your customers layout, it will also display the same count as the count function does.

          Using a summary field in a filtered portal.

          Say you have this relationship:

          Reporttable::anyfield X PromotionHistory::anyfield

          If you put the summary field into a one row portal to PromotionHistory on a ReprtTable layout with this portal filter expression:

          PromotionHistory::email = "MyName@domain.com"

          Then the summary field will report how many times "MyName@domain.com" appears in the PromotionHistory table. Versions of this expression may be used so that the email address to be counted can be selected from a value list and/or typed into a field.

          • 2. Re: How to count how many times an email address appears in a table
            JosephOrtega

            Thank you! This definitely gets me off to the right start.