Link an Old Quickbase Record to the New One That Replaced It

Here's a situation that comes up a lot with contracts, agreements, subscriptions and anything else that gets renewed. You have a Create New Agreement button on an agreement. It makes a new record, copies over the key details, and saves the old agreement's Record ID# in a Predecessor field on the new one. The new agreement knows where it came from. The old one doesn't know it's been replaced.

You want the old agreement to show "this was renewed, here's the new one" so nobody chases a renewal that's already done.

This came up in this Quickbase Community thread. Below is the full walkthrough: a relationship from the table to itself, one summary field, one Formula - URL field, and a report for agreements that still need renewing.

The idea in one paragraph

Your Predecessor field is already a pointer from the new record back to the old one. Quickbase relationships work exactly like that: a child record holds its parent's key. So if you relate the Agreements table to itself and use the Predecessor field as the reference field, every agreement becomes a "parent" of the agreements that renewed it. Once that relationship exists you get everything relationships normally give you: an embedded report of children, summary fields and lookups. The old agreement can see the new one.

What you need before you start

• An Agreements table (any table works the same way).
• A Predecessor Record ID# field on that table. It has to be a Numeric field, because it holds a Record ID#. If yours is a Text field, make a new Numeric field and copy the values over first. Quickbase will only use a field as a reference field if its type matches the parent's key field.
• Your Create New Agreement button putting the current Record ID# into that field. If you're building the button now, the add-record URL looks like this. Swap 9 for your Predecessor field's ID, and add the other _fid_ values you already copy:

URLRoot() & "db/" & Dbid() & "?a=API_GenAddRecordForm"
  & "&_fid_9=" & URLEncode([Record ID#])
  & "&_fid_7=" & URLEncode([Customer])

Step 1: Relate Agreements to Agreements

1. Open the Agreements table and go to Settings > Table-to-table relationships.
2. Click + New Relationship.
3. Choose Agreements as the parent and the child. Quickbase is fine with a table related to itself.
4. When it asks how to relate child records to a parent, don't take the default new reference field. Pick your existing Predecessor Record ID# field instead. That's the whole trick: your existing data links up right away, with no backfilling.
5. Skip the lookups for now (or add the parent's Agreement Name if you want a friendly "Predecessor Name" on the new record) and create the relationship.

Naming matters a lot in a self-relationship, because both ends are "Agreements". Rename things as you go so you aren't left guessing later:

Now add the embedded report to the agreement form. Open Maintenance Agreement 2024 and you'll see Maintenance Agreement 2025 listed underneath it. For many apps, this alone answers the question.

Step 2: Add a summary field for the successor's Record ID#

A list is nice, but usually you want one clean link and something you can filter on.

1. Go back to Settings > Table-to-table relationships and open the self-relationship.
2. Under the summary fields section, click Add a summary field.
3. Choose Maximum of Record ID# and name it Successor Record ID#.

Why Maximum? Normally an agreement is renewed once and there's only one child, so Maximum just returns that Record ID#. If someone accidentally renews the same agreement twice, Maximum picks the newest one, since Record IDs only go up. Add a second summary field, # of Successors (a Count), if you'd like to spot those duplicates.

This also fixes something the original poster ran into. A report link field set up on Record ID# shows the current record's ID even though the link opens the right records. The summary field holds the successor's ID, which is what you actually want to see and filter on.

Step 3: Add a Formula - URL field that opens the successor

Create a new field of type Formula - URL named Successor Link with this formula:

If([Successor Record ID#] > 0,
  URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & ToText([Successor Record ID#])
)

• URLRoot() is your realm's address, so the formula works in any realm and survives app copies.
• Dbid() is the current table's ID. Since the successor lives in the same table, that's exactly the table you want.
• a=dr&rid= opens that record in display mode.
• The If leaves the field blank on agreements that haven't been renewed, so you don't get a link to record 0.

In the field properties, set the link text to something friendly, like Open renewal, and put the field near the top of the form. A form rule or a Formula - Rich Text "Renewed" badge is a nice extra, but optional.

Step 4: Find agreements that still need renewing

This is the reporting side of the question: show only agreements with no successor, especially ones about to expire.

The cleanest way is a small checkbox formula so the logic sits in one place:

• Renewed? (Formula - Checkbox): [Successor Record ID#] > 0

Then build a report on Agreements with these filters:

• Renewed? is not checked
• End Date is during the next 60 days (or whatever window you use)

Sort by End Date ascending and you have a working "renewals due" list. Flip the first filter to is checked and you have a "renewed" list for auditing. The formula field also works in summary reports and dashboards.

What it looked like when I tested it

I built this in a test app with four agreements. Maintenance 2025 has Predecessor = 1, Maintenance 2026 has Predecessor = 2, and the Pine Street Bakery support agreement was never renewed:

A few things worth noticing:

• Chains work. Each agreement points one step forward, so 2024 links to 2025 and 2025 links to 2026. You can walk a long renewal history one click at a time.
• The "not renewed" filter returned exactly records 3 and 4: the agreement that was never renewed and the current, latest agreement. Add the End Date filter and only the ones coming due will show.
• Blank shows as 0. An agreement with no successor shows 0 (or blank, depending on your field's display settings), so test for > 0 rather than "is blank". That's why the formulas above use > 0.

Another option: a formula query (no relationship needed)

If you'd rather not add a relationship, for example because the table already has a busy set of relationships, you can get the same Successor Record ID# with a formula query. This is the version I ran in the test app:

1. Add a Formula - Numeric field Record ID (number) with the formula [Record ID#]. (SumValues only works on numeric fields, and Record ID# doesn't count as one.)
2. Add a Formula - Numeric field Successor Record ID#:

SumValues(
  GetRecords("{9.EX." & [Record ID#] & "}"),
  12
)

Here 9 is the Predecessor Record ID# field ID and 12 is the ID of the Record ID (number) field. Note that the second argument is a field ID number, not a [Field Name]. If you write [Record ID (number)] there, Quickbase reads its value as a field ID and you get odd results. I got a huge number the first time because it summed Date Created!

The same Successor Link formula and Renewed? checkbox from Steps 3 and 4 work on top of it unchanged. The tradeoffs: SumValues adds IDs together, so it's only right when each agreement has one renewal. Formula queries also cost more performance on large tables, and you don't get the embedded "Renewed By" report for free. For most apps I'd go with the relationship and keep this as a backup.

Quick troubleshooting

• Your Predecessor field isn't offered as a reference field. It's probably Text. Make it Numeric (or create a new Numeric field and copy the values), then try again.
• The link opens the wrong record. Check that the formula uses the summary field [Successor Record ID#], not [Record ID#].
• Old agreements don't show a successor. Check that the new record's Predecessor field holds a plain number, like 1, with no formatting or text.
• Two renewals show in the embedded report. Someone clicked the button twice. Delete the extra record, or add a form rule or a # of Successors > 0 check that hides the Create New Agreement button once a renewal exists.

That's it: one relationship and two or three fields, and each old agreement shows what replaced it. If you have a twist on this, like renewals that live in a different table, ask in the community thread and I'll take a look.