1 Reply Latest reply on Dec 17, 2014 7:10 AM by philmodjunk

    Calculate number of related records through a join table

    jared944

      Title

      Calculate number of related records through a join table

      Post

      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?

       

      Thanks!!

       

      Jared

       

        • 1. Re: Calculate number of related records through a join table
          philmodjunk

          As best I can reconstruct them from the info posted, you would have these relationsships:

          Orders>----X----<Vendor----<InvoiceLineItems--?--InvoiceJoinOrders|------<Orders | Pending_Order Items

          I'm not sure I have that correct and am puzzled as to why you have two tables of Line items or are these two occurrences of the same data source table?

          And what purpose does the join table serve?

          And you have a portal to Vendors placed on your Orders layout...