AnsweredAssumed Answered

Combining 2 tables' records into one portal

Question asked by deathrobot on Oct 24, 2014
Latest reply on Oct 24, 2014 by 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

Outcomes