Calculate number of related records through a join table
I am attempting to set up a portal/table relationship that will tell me the amount of pending or in-progress orders.
I have a table "Orders" that had the following cartesian relationship :
Orders X Vendors
This way I can see all of the vendors through a portal in my orders layout. On every portal row I display a field that tells me how many "pending" orders are found in a table named "Order Line Items."
This calculation counts the amount of "pending" entries in a field named "order status". The calculation is found in my Vendor table with the relationship :
If ( IsEmpty ( Orders | Pending_Order Items::__PK_Order_Line_ID ) ; 0 ; Count ( Orders | Pending_Order Items::__PK_Order_Line_ID ) )
The relationship they both table share is :
Vendor (Vendor ID) = (Vendor ID) Invoice Line Item
Invoice Line Item (Line Item ID) = (Line Item ID) Invoice Join Orders
Invoice Join Orders (global_pending) = (order status) Order Line Items
If I visit the "Orders" layout directly after opening the database from a fresh start, the "Pending" calculation shows 0. If I visit the related tables (Invoice Join Orders) and return to the orders layout, than the "Pending" calculation displays the correct amount.
I think the discrepancy may have to do with me relating a 1) calculation through a join table and 2) having the calculation being unstored.
Is there any way to have the calculation automatically update through the join table, or is this impossible with a field set to calculate as needed?