Dynamic nested tables

[DeathRobot]

I have the following tables:

 

Projects --< Invoices --< Applied Payments >-- Payments

 

On a Projects layout, I have a portal that show invoices, like so…

 

1.  $500 INV2014333-001

2.  $300 INV2014333-002

 

I would like to somehow create a list that nested payments from the Applied Payments table into that invoice list, like so…

 

1.  $500 INV2014333-001

          $250 received on 10/1/2014 (check #234)

          $250 received on 10/5/2014 (check #254)

2.  $300 INV2014333-002

          $300 received on 11/1/2014 (check #265)

 

Ideally, the number of lines would be flexible so that if there were only one payment on the first invoice the second invoice would move up a line. Also ideally, clicking on any row would go to the related invoice or payment record.

 

I assume there are ways to accomplish this (web viewer, SQL, dynamic value list?), but am not sure how best to start? Is this doable?

 

Thanks,

Michael

[Kris M]

maybe a list view layout from the Payments table occurence context that shows the data from the payments TO and the related invoices TO. Add a sub summary part to the list that summarizes on the invoice data to group the invoices payments and sort the list by that subsum field. Put one button on the layout that goes to related invoice record layout and one button that goes to payment detail record

[DeathRobot]

Thanks, Kris. That seems like it will work. However, this need to fit on an existing layout so I can't use list view…