Find completion sprint from string

A challenge with Airtable formulas is that you are somewhat limited in what you can do, particularly since the number of sprints could be quite large - thus any sort of column expansion hacks won’t work. But if you don’t mind trying your hand at some basic coding, here are the steps (and code) to do exactly what you want.

Step 1: Create a new column in your table called “Highest Sprint”

Step 2: Create a new automation with a trigger of “When a record is updated” in your Sprint table.

Step 3: Add a “Run a script” action under the Trigger - the code you need is shown below.

Step 3a. Simply paste the code in and change the first line so that the table name matches whatever you called your Sprints table.

Step 3b. Add two input variables on the left side of the code block as shown in this image.

Step 4: Enable your automation.

Then each time any of your sprints are updated, the Highest Sprint column will automatically populate with the sprint of the highest number. If there’s just one sprint, then it will populate with that one sprint. Even if the sprint doesn't have a number.

Since the automation triggers anytime a Sprint record is edited, you can be sure that the Highest Sprint column will always be correct.

Having said all that, this is NOT the best way to implement what you are doing. Hanging on record update triggers is not a good idea for tables that are regularly edited. But if this table only ever updates as a result of periodic sprint planning in Jira - then it might suffice as an initial hack. Without understanding the full context of your use-case, I’m not sure how best to provide more help.

const sprintTable = base.getTable('Sprints')

let inputData = input.config()
let { recordId, sprintText } = inputData

let highestSprint = extractHighestSprint(sprintText)

await sprintTable.updateRecordAsync(recordId, {
  "Highest Sprint": highestSprint.sprintName
})

function extractHighestSprint(sprintText) {
  const sprints = sprintText.split(", ")

  let highestSprint = { sprintNumber: 0, sprintName: sprintText}

  sprints.forEach(sprint => {
    const numberMatch = sprint.match(/\d+/);

    if (numberMatch) {
      const sprintNumber = parseInt(numberMatch[0])
      const sprintName = sprint

      if (sprintNumber > highestSprint.sprintNumber) {
        highestSprint = { sprintNumber, sprintName }
      }
    }
    
  })

  return highestSprint
}