Forum Discussion

AJCampani's avatar
AJCampani
Qrew Cadet
5 years ago

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 Ter...
Relationships
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
5 years ago
Let's start with a low tech solution as it seems pretty complicated to know which is the previous record.

Quickbase supports tables being related to itself.  My suggestion is to do that and name the field for the [Related Lease] to be called [Related Previous Lease].  Then do your lookup fields and name then like [Previous Rent], [Previous Terms] .. whatever fields you need.

Each lease will have a button on it to add a Lease Extension or Renewal.  So at renewal time you launch off he old lease to create anew one.  That also has the advantage that you could chose to auto populate many fields by enhancing that Add Lease button as for example the  [Related Building] will be the same and the [Related Client] will be the same.

It would also be possible to have a report link field on the lease to show all the leases for the same client and property.


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
AJCampani's avatar
AJCampani
Qrew Cadet
5 years ago
Thanks Mark! I'll give this a try. I was hoping to avoid user input for the prior term to lessen the risk that they choose the wrong term since it will ultimately populate rent rolls for each mall tenant (and cause major headaches at the end of the year if its wrong). If I want to lessen that risk, without getting too complicated, do you have any guidance as to what avenue might accomplish this, even if it takes outside help (so I know what yo ask for)?

------------------------------
Albert Campani
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I think that it could be done automatically and then there could be an overrode by authorized Roles.

    Feel free to contract me directly by my email signature and we can take this off line.

    But if you think you can figure this out yourself, I think that the answer would be to pre calculate the the highest Term (Summary Maximum) so far for each of these Lease Types

    Primary Term Escalation
    Option Term
    Holdover

    That would assume that you already have a structure where 1 Primary Lease has many Child Lease Extensions (Escalations, Options or Holdovers) .

    Then create Summary fields to get the Maximum (only) [Record ID#] for those three.

    Then we make a button to ADD Lease and it would know which Previous Lease to attach to and it would pre-populate the field for Related Previous Lease.

    Give that a try and feel free to post back with questions or contact me by the email in my signature line for one on one help.  It's not a big project, probably an hour to get it working.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • AJCampani's avatar
      AJCampani
      Qrew Cadet
      5 years ago
      Thanks! That's a great idea - I'll make an attempt at it now.

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