3 Replies Latest reply on Oct 24, 2014 4:18 PM by deathrobot

    Combining 2 tables' records into one portal

    deathrobot

      Title

      Combining 2 tables' records into one portal

      Post

      I have a table called PROJECTS. Each PROJECTS record has a "Project Total" which is the total value of that project. The Project Total can be balanced out to zero from either invoices or credits from other projects, so that PROJECT TOTAL minus INVOICES plus/minus CREDITS equals zero. ERD is:

      PAYMENTS --< APPLIED PAYMENTS >-- INVOICES >-- PROJECTS 

           AND

      PROJECTS --< CREDITS >-- PROJECTS

      Ideally, I'd be able to show a list on a PROJECTS layout which would combine credits from both the INVOICES and CREDITS tables, e.g…

      $7,500        Project Total

      $10,000.00        9/1/2014: Billed invoice 21154578900
      -$2,500        9/1/2014: credit applied to project 2014111

      $0.00            BALANCE

           OR

      $7,500          Project Total

      $1,000.00            9/1/2014: Billed invoice 21154578900
      +$1,500        9/1/2014: credit received from project 2014111
      +$2,500        9/1/2014: credit received from project 2014112
      +$2,500        9/1/2014: credit received from project 2014113

      $0.00            BALANCE

      Is there a straightforward way to combine these two tables into one list?

      Thanks,
      Michael