Counting in Related Tables that are not directly related.
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.
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)
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.