AnsweredAssumed Answered

Calculate number of related records through a join table

Question asked by jared944 on Dec 16, 2014
Latest reply on Dec 17, 2014 by philmodjunk


Calculate number of related records through a join table


Hello everyone,

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?