AnsweredAssumed Answered

Counting in Related Tables that are not directly related.

Question asked by schmity on Jul 23, 2014
Latest reply on Jul 24, 2014 by schmity

Title

Counting in Related Tables that are not directly related.

Post

     I am trying to perform a count on a related table that is 1 table separated from where I want the count to take place.

     I am trying to perform a count in the Work Order table of how many related records there are in the bump table with a status field that = Open and am having some problems.  These tables are related by another table called Ops.

     Work Orders-->OPs-->Bump

     I wrote a calculation field in the Work Order table that says Count (Bump:fkWO).  This counts the number of records in the bump table that are related to the work order, it works perfectly.

     For the other calculation I need, I created a related table (Bump2) based of the Bump table with the following relationship

     Work Order:pkWO = Bump2:fkWO                         
              (pkWO is an auto assigned SN, fkWO is a calculation where bump:fkWO = Work Order:pkWO)

     AND               

     Work Order: matchopen= Bump2:status              
             (matchopen is a calculation that = “Open” and I want it to match whenever Bump2:status = “Open”)

     I created a calculation in Work Order table that says Count (Bumps2:_fkWO)

     When I place this field on a layout, it just shows a "?". 

     If I remove the pkWO = fkWO relationship, it will count ALL the bumps that have a status of open, so that part of it is working, but I need it to count only the ones related to that record in the Work Order table.

     I've used this method to count things before in tables that were direct parent/child tables, but never with ones that had a table separating them.

Outcomes