2 Replies Latest reply on Jul 24, 2014 6:53 AM by schmity

    Counting in Related Tables that are not directly related.

    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.