Find completion sprint from string

[Menia_Laina1]

Hello Airtable community!

I have created a Jira integration with Airtable. There is a field for Sprint, however, if a ticket was included in more than one sprints, all sprints appears seperated by commas. I want to extract only the sprint that is the highest number. I tried doing this by taking the last one (with the RIGHT() function) but unfortunately the string is not always alpahbetically sorted within the cell. Please see below:

Essentialy, I'm trying to find a way to extract the highest sprint. Any ideas how to either sort alphabetically within the cell or how to extract the highest using a formula? Many thanks!

[Jason_Hill]

I am unsure if it's possible in Airtable without using the API and another connection to extract the data to perform the function outside of Airtable. What needs to be done is a formula that splits the Sprint column into an Array. You then can use a formula such as Max. Sorry, I couldn't help.

[kuovonne]

It looks like your list of sprints is a synced text string. It also looks like all sprints in a single cell are the same color. 

It isn’t clear if there is a maximum number of sprints in a single field. If there is not, or if the number is more that four or five sprints, I would use a script. 

If there will be only a few sprints, this may be possible with formula fields. But it would also require knowing the exact pattern of sprint names to build the REGEX expressions. 

[apg314 · Accepted solution]

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
}

[Menia_Laina1]

Thank you all for the responses! @kuovonne @Jason_Hill @apg314 

I tried the solution suggested by @apg314 and it worked like a charm! Thank you so much!

The table is only used to created a dashboard with overview for each team we have so I think this will be perfect.

Thank you again for your help. 🙂