How to Identify Prior Record in Child Table for Use with Current Record

Hello all,

I'm trying to pull the previous term's rent amount to use in calculating the current term's rent.

I have a parent table called Leases and a child table called Lease Terms. Lease Term records are categorized by the type of term (i.e., Primary Term, Primary Term Escalation, Option Term, Holdover). There may be multiples of each type (or none) except for the Primary Term so I also have a field for Term Number. 

When a new Term Record is created, I need the rent amount from the last term in order to calculate the increase for the new term. For example, if the last term was Option Term 2 and the annual rent was 100,000, I want to be able to pull that amount in to the record for Option Term 3 and use a formula to increase it, for instance by 3%, so to 103,000.

I can create summary fields for the Leases table and then look up fields to bring them back down to the Lease Terms table, but different leases have different numbers of options so this approach limits functionality to the number of option summary fields and look up fields I create. Is it possible instead to somehow create a formula that looks at the current term description and subtracts 1 and then points to the fields in the relevant record (e.g., Option Term 2 is current so it would look up the record that is for Option Term 1) without having to create specific summary and lookup fields that filter for each option record?

I'll also have to solve the problem of what happens if there are no prior terms of a certain type (e.g., if there is a primary term and and an option term but no escalation term in between) but I guess one step at a time...

Thanks in advance for any help!

AJ

------------------------------
Albert Campani
------------------------------