Automatically Mark the Old Quickbase Record Expired When a New One Replaces It

A question in the Quickbase community (original thread here) describes a pattern lots of ticketing apps end up with. When a ticket needs to be redone, someone copies it to make a new one. The new ticket carries a Temporary Identifier field that holds the old ticket's Ticket#. The goal: as soon as the new ticket is saved, the old ticket (the one whose Ticket# matches) should change to Expired on its own.

The good news is you don't need anything to go back and edit the old ticket. If the new ticket points at the old one by Record ID# instead of Ticket#, Quickbase can work out the old ticket's status by itself with a relationship, a summary field and a short formula. This guide walks through the whole setup, then covers a pipeline version for when the status has to stay an ordinary field.

The same pattern works anywhere one record replaces another: contract renewals, quote revisions, document versions, reissued permits.

Why matching on Ticket# is fragile

Matching a text field on one record to a text field on another works until it doesn't:

• Ticket numbers get typed, edited and re-used. One stray space or a fixed typo and the match silently breaks. Nothing tells you it broke.
• Relationships want a key. In Quickbase, a child record links to a parent through the parent's key field, which is the Record ID# unless you've changed it. Ticket# usually isn't the key, so a "match on Ticket#" approach pushes you toward pipelines or formula queries doing text searches.
• Something has to write to the old record. If the status is a normal field, a pipeline or script has to find the old ticket and update it. If that run fails, or someone deletes the new ticket later, the old ticket's status is now wrong and stays wrong.

The Record ID# never changes and is always unique, so it's the right thing to point at.

What you'll build

On one Tickets table:

I built this in a test app to check each step. The field IDs in the examples below are the ones from that app: Previous Ticket RID is field 10, Customer is 7, Description is 8 and Assigned To is 9. Your IDs will be different. You can find them under Settings > Fields (turn on the Field ID column) and swap them in.

Step 1: Add the Previous Ticket RID field

1. Open the Tickets table and go to Settings > Fields > + New Field.
2. Name it Previous Ticket RID and choose Numeric as the type.

It has to be Numeric because it will hold a Record ID#, and Quickbase only lets a field act as a reference field if its type matches the parent's key. If you already have a text Temporary Identifier field, leave it alone for now. You can retire it once the new field is filled in (see the cleanup tip near the end).

Step 2: Relate Tickets to itself

1. Go to Settings > Table-to-table relationships and click + New Relationship.
2. Pick Tickets as the parent table and as the child table. Quickbase is fine with a table being related to itself.
3. When Quickbase asks which field on the child holds the reference, don't accept the default new field. Choose Previous Ticket RID.
4. Skip lookups for now and create the relationship.

Think of it this way: in this relationship, the old ticket is the parent and every ticket that replaced it is a child. Because both ends are "Tickets", the names Quickbase generates get confusing quickly. Rename them right away:

That last lookup is worth adding. It shows the old ticket's number on the new ticket, which is nicer for people to read than a bare Record ID#.

Step 3: Add the summary field

1. Still in the relationship, scroll to the summary fields section and click Add a summary field.
2. Choose Count of the child records.
3. Name it # of Newer Tickets.

That field lives on every ticket and tells you how many tickets point back at it. For a ticket nobody has replaced, it's 0.

Step 4: Make Ticket Status a formula

Create a Formula - Text field named Ticket Status (or convert your existing status field, see the gotchas first if other things write to it):

If([# of Newer Tickets] > 0, "Expired", "Current")

That's the whole trick. Nobody has to update the old ticket. The moment a new ticket is saved with Previous Ticket RID filled in, the old ticket's count goes from 0 to 1 and its status reads Expired.

If you have other statuses (Open, In Progress, Closed and so on), keep them in a regular field and only let the formula override when the ticket has been replaced:

If([# of Newer Tickets] > 0, "Expired", [Work Status])

Step 5: Replace Copy Record with a button

Quickbase's built-in Copy Record copies field values, but it can't put the current record's Record ID# into Previous Ticket RID. Worse, it copies whatever is already in Previous Ticket RID, so a copy of a copy would point at the grandparent ticket. Use a Formula - URL field instead, named Copy to New Ticket:

If([Ticket Status] = "Current",
  URLRoot() & "db/" & Dbid() & "?a=API_GenAddRecordForm"
  & "&_fid_10=" & [Record ID#]
  & "&_fid_7=" & URLEncode([Customer])
  & "&_fid_8=" & URLEncode([Description])
  & "&_fid_9=" & URLEncode([Assigned To])
)

How it works:

• API_GenAddRecordForm opens a normal Add Ticket form with some fields already filled in. Nothing is saved until the user clicks Save, so they can still edit anything.
• &_fid_10= puts this ticket's Record ID# into Previous Ticket RID (field 10 in my app). Swap in your field ID.
• Add one &_fid_N= line for each field you want carried over, wrapped in URLEncode() so spaces and symbols survive.
• Leave out fields that should start fresh, like Ticket# (if it's assigned automatically), dates or resolution notes.
• The If([Ticket Status] = "Current", ...) wrapper hides the button on tickets that have already been replaced. More on that in the gotchas.

In the field's properties, set the link text to Copy to New Ticket and turn on the option to display it as a button. Then add it to the ticket form and remove the old Copy Record option from your team's habits (or hide it with a role setting if you can).

Testing it

Here's what I saw in the test app. Sarah Lee's ticket T-1001 is copied with the button to make T-1003, then Priya Patel copies T-1003 to make T-1004. Mike Chen's T-1002 isn't touched.

A good test run for your own app:

1. Open a current ticket and click Copy to New Ticket. Check that the carried-over fields are filled in and Previous Ticket RID shows the old ticket's Record ID#.
2. Save. Go back to the old ticket. Ticket Status should now read Expired, and the Replaced By report should list the new ticket.
3. Open the new ticket. It should be Current.
4. Delete the new ticket (in a test app) and confirm the old one goes back to Current. That's usually what you want, since the replacement no longer exists.

The pipeline alternative

Sometimes Ticket Status has to stay a regular field. Maybe users or other pipelines also set it, or an integration reads it. In that case, let a pipeline write "Expired" to the old ticket when the new one is created. You still want the Previous Ticket RID field, because it lets the pipeline update the old ticket directly instead of searching on Ticket#.

1. Trigger: Quickbase > On New Event in the Tickets table, with the event set to Record Created. Add a condition so it only runs when Previous Ticket RID is not empty.
2. Action: Quickbase > Update Record. Pick the Tickets table and set the record to update to the trigger's Previous Ticket RID value, using the variable picker (for example {{a.previous_ticket_rid}}).
3. Map Ticket Status to Expired.

When I ran the equivalent update in the test app, creating T-1004 (Previous Ticket RID = 3) flipped only T-1003 to Expired and left the other tickets alone, as expected.

Things to know about this version:

• It's a one-time write. If the new ticket is deleted later, the old ticket stays Expired until someone fixes it.
• Tickets that were copied before the pipeline existed won't change. You'd need a one-off update for those.
• Pipelines run shortly after the save, not instantly, so a user refreshing the old ticket right away might still see Current for a moment.

If you can use the formula version, it's simpler and can't drift out of sync.

Gotchas

Copying a ticket that's already expired. If someone copies T-1001 after T-1003 already replaced it, you get two newer tickets competing to be "the" current one. The If([Ticket Status] = "Current", ...) wrapper in Step 5 hides the button on expired tickets, which steers people to copy the latest ticket instead. You can also add a lookup on the new ticket that shows the previous ticket's status and a custom data rule that blocks saving when it's Expired.

More than one newer ticket. The formula only checks whether the count is above 0, so the old ticket still shows Expired no matter how many tickets point at it. If you want the old ticket to link to its newest replacement, add a second summary field in the same relationship: Maximum of Record ID#. Then a Formula - URL field can open that record with URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Newest Replacement RID].

Reports should usually show Current only. Formula fields built on summary fields can be used in report filters, so add Ticket Status is equal to Current to your main ticket reports and dashboards. Keep one "All tickets" report around for history.

Converting an existing status field. Changing a field to a formula wipes the values people typed into it, and anything that writes to it (forms, pipelines, imports) will stop working. If other things depend on that field, make a new formula field instead, or use the pipeline version.

Backfilling old tickets. If your existing copies only have the old Ticket# in Temporary Identifier, you can fill Previous Ticket RID once. Add a temporary Formula - Numeric field that finds the Record ID# of the ticket with that number (6 is the Ticket# field in my app):

If([Temporary Identifier] <> "",
  ToNumber(ToText(GetFieldValues(
    GetRecords("{6.EX." & [Temporary Identifier] & "}"), 3))))

Put it on a report next to Temporary Identifier and check the results. Blank means no ticket matched, which is exactly the kind of silent mismatch this setup avoids from now on. Then copy the values into Previous Ticket RID (export and re-import works well), and delete the temporary field. After that, the button keeps Previous Ticket RID filled in for every new copy.

Can't use summary fields? If you can't create the relationship for some reason, a numeric formula field with a formula query gives the same count:

Size(GetRecords("{10.EX." & [Record ID#] & "}"))

Here 10 is the Previous Ticket RID field ID. It works, and it's what I used to double-check the numbers in my test app, but formula queries can slow down large tables, so the summary field is the better default.

Wrapping up

Stop matching on Ticket# and point the new ticket at the old one by Record ID#. With a self-relationship, a count summary field and a one-line formula, the old ticket marks itself Expired the moment its replacement is saved, and it goes back to Current if the replacement is deleted. The Copy to New Ticket button keeps the link filled in, and the pipeline version is there for the cases where the status has to be a stored value.