2 Replies Latest reply on Mar 27, 2013 12:47 PM by Terri

    Count Number of Records from Related Table



      Count Number of Records from Related Table


           I have two tables:
           Workhorse Data - contains all the styles and each color available for that style. So there is one style and it has 5 colors so that style actually has 5 records.
           Workhorse Styles - I import records from the Data table but validate the style as unique only, so this only shows one record for each style. 
           I'm trying to figure out how to show on a layout from the Workhorse Styles table (Layout is called Reports) the number of colors that have a status of adopted for that particular style (this information is on the Data table). 
           Workhorse Data Table:
           Style 123456
           Color A - Adopted
           Color B - Adopted
           Color C - Dropped
           Color D - Adopted
           Reports Layout needs to show:
           Style 123456
           Count of colors adopted = 3
           I hope I gave enough information. I know how to add a summary field that is created from the Workhorse Data table and put it on this layout but it shows the total count of colors, not a count of only adopted colors. 

        • 1. Re: Count Number of Records from Related Table

               In FileMaker 12, ExecuteSQL can selectively count the related records.

               Without ExecuteSQL, there are three approaches that can give you the needed count:

               1) define a new relationship between the two tables. Use both the Style field and this calcualtion field: constAdopted as match fields in this relationship. constAdopted can be a calculation that just returns the text "adopted" in every record of Workhorse Styles.

               With this approach both a count function in a calculation in a workhorse Styles field or a "count of" summary field defined in Workhorse Data can provide the needed count.

               2) define a calculation field in Workhorse Data, cAdoptedFlag as: status = "adopted" with number as the result type.

               Now either a Sum function (workhorse styles) or a "total of" summary field (workhorse data) that totals the cAdoptedFlag will count the related records with status = "adopted".

               3) if you just need to display this count, you don't need to use it in a calculation or export it, you can use a relationship based on the style fields and put a "count of" summary field inside a one row filtered portal. The portal filter expression:: Workhorse Data::status = "adopted" would then be used to filter out all but adopted records from the count.

          • 2. Re: Count Number of Records from Related Table

                 #2 is the best option for me, it is similar to what I was trying to do but I was missing the sum function part. Thanks!