A lot of apps have records that follow earlier ones: renewals, revised quotes, reissued tickets, this month’s inspection after last month’s. It’s tempting to connect them by matching a name, a ticket number or the latest date, usually with a pipeline or a formula query. That works until someone fixes a typo or two records share a date, and then it quietly points at the wrong record.
Store the earlier record’s Record ID# instead, and relate the table to itself.
Add a Numeric field like Previous Record ID. Create a table-to-table relationship with the same table as parent and child, and choose that field as the reference field. Now each record knows exactly which one it follows, and lookups can show the previous record’s values right on the form.
On the other side, a count summary field tells the old record whether anything replaced it:
If([# of Newer Records] > 0, "Replaced", "Current")Nothing has to go back and edit the old record, and if the new one is deleted, the old one goes back to Current on its own.
To fill the field, use a Formula - URL button that opens the add form with it prefilled:
URLRoot() & "db/" & Dbid() & "?a=API_GenAddRecordForm&_fid_10=" & [Record ID#]Swap 10 for your field’s ID, and add more &_fid_ values for anything you want carried over. Pipelines get simpler too, since an Update Record step can target the previous record’s ID directly instead of searching for it.
Want more detail? I wrote a full walkthrough here: https://www.topstep.tech/blog/quickbase-mark-old-record-expired-self-relationship