2 Replies Latest reply on May 24, 2014 7:24 AM by schmity

    Find number of related records with a certain value

    schmity

      Title

      Find number of related records with a certain value

      Post

           Trying to find a way to count the number of records with a certain value in one field.

           Table 1- Work Orders

           Table 2 Discrepancies

           Each work order is related to many discrepancies.

           I have a field in the work order table that shows the total number of related discrepancies.

           Each discrepancy has a status field that can only be set to "Open" or "Closed".  How can I write a calculation to show the total number of related records with a status of "Closed"? 

           Basically trying to show a percentage complete for each work order.

            

           thanks!

            

        • 1. Re: Find number of related records with a certain value
          philmodjunk

               I am assuming that your current relationship looks like this:

               WorkOrders::__pkWorkOrderID = Discrepanices::_fkWorkOrderID

               Option 1:

               If you have FileMaker 12 or newer and want to try using SQL, ExecuteSQL can be used with a count function and a WHERE clause that specifies a specific status.

               Option 2:

               You can add a new table occurrence of Discrepancies and link it to Work Orders like this:

               WorkOrders::__pkWorkOrderID = Discrepanices|Closed::_fkWorkOrderID AND
               WorkORders::constClosed = Discrepancies|Closed::Status

               Where Discrepancies|Closed is the name of the new table occurrence for Discrepancies and constClosed is a calculation field define to return a constant value of "Closed". Since this relationship only matches to Closed discrepancies with the same work order ID, Count ( Discrepancies|Closed::_fkWordOrderID )  will return the count of all closed Discrepancies records for that Work Order.

          • 2. Re: Find number of related records with a certain value
            schmity

                 Perfect.  Used option 2.  I'll have to try and learn how to do the Execute SQL someday.

                  

                 thanks!