1 Reply Latest reply on Aug 14, 2012 9:21 PM by philmodjunk

    Counting related records only when certain conditions exist



      Counting related records only when certain conditions exist


       I am trying to do a calculation that counts the number of related records when two conditions exist. In the main table, I have a field called NumberOfRecords which I made a calculation that returns a number. In the related record there are two fields that must have certain data in to be added to the count. Both are dates. The data is inputted from another source.

      Date1 can be either "--" or a valid date

      Date2 can be either "--" or a valid date

      Only records were Date1 = "--" and Date2 = Valid Date should be counted.


        • 1. Re: Counting related records only when certain conditions exist

          Your data design requires that Date1 and Date2 be fields of type text instead of type Date. This is usually not the optimum method for storing dates. Date information entered into text fields won't be automatically checked for invalid dates during data entry and can't easily be sorted and searched correctly.

          Is there any reason why the values in these two fields could not be set up so that they are empty or store a valid date instead of using the "--" text to show that there is no date? That would be a simple way to Define these two fields as fields of type date.

          There are two basic methods you can use in all versions of FileMaker to count your related records. You can set up a filtered relationship or a filtered portal. Which works for you depends on what you intend to do with the count after you get it. The filtered portal option is best if you only need to display the count. If you need to use that value in calculations, the filtered relationship is the better option.

          See this thread: Sum_Calculation based on condition

          If you have FileMaker 12, you can also use the Execute SQL function: FMP 12 Tip: Summary Recaps (Portal Subtotals)