Creating formula that checks the conditions in another table

[kpiasecka]

Hi, I have two tables for which I'd like create a formula

• Table 1
  • Feature Name
  • Feature End Date 
• Table 2
  • Sprint Number
  • Sprint Start Date
  • Sprint End Date

I'd like to create a formula that checks if "Feature End Date" is between "Sprint Start Date" and "Sprint End Date" and if so, assign a Sprint Number accordingly. How can I achieve that in airtable?

[Mike_AutomaticN]

Hey @kpiasecka!

1. Create a link field between Table 1 and Table 2 if you do not have that yet.
2. Create a lookup field on table 2, for "Feature End Date".
3. Create a formula field on Table 2 called something like "Check"

AND({Feature End Date} >= {Sprint Start Date}, {Feature End Date} <= {Sprint End Date})

4. Formula above will return 1 if the condition "Feature End Date" is between "Sprint Start Date" and "Sprint End Date" is true, and 0 if the condition is false.

You would now have a way of checking which Sprints should actually have a Sprint Number. This can be further used as a trigger to assign a number to it automatically with certain naming convention for instance.

How exactly are you numbering sprints? Just numbers, starting at 1 and increasing by +1 for each new sprint which matches conditions? Should the order be consistent with Sprint Start Date? Any other info would be super helpful!

Mike, Consultant @ Automatic Nation

[TheTimeSavingCo · Accepted solution]

If you want this automated, I think you're going to either need to:
1. Write a script for this
2. Create an automation system that will go through each Feature and link it to all the Sprints, use formula fields (see Mike's post) to determine whether the Feature falls in the Sprint, and then link the Feature to it
  - You'd use two linked fields, one for the automation to process the data, and the other to actually indicate that the Feature is within a Sprint

[kpiasecka]

Thanks Mike! I followed the steps that you mentioned, however it looks like I had to associate the Sprint Number (Table 2) with Feature (Table 1) for that to work. Only when I created a column in Table 1 with "Sprint" and assigned it manually, "Check" column in Table 2 was populated. This is something I'd like to be populated automatically in Table 1

Sprint numbers are as follows: "Year-Quarter-Sprint#" e.g. 2024-Q1-Sprint 1

[kpiasecka]

I resolved that by using script + automation

let table1 = base.getTable("Table 1");
let table2 = base.getTable("Table 2");

let table1Records = await table1.selectRecordsAsync();
let table2Records = await table2.selectRecordsAsync();

for (let record of table1Records.records) {
    let endDate = record.getCellValue("End Date");
    let sprintNumber = null;

    for (let sprintRecord of table2Records.records) {
        let sprintStartDate = sprintRecord.getCellValue("Sprint Start Date");
        let sprintEndDate = sprintRecord.getCellValue("Sprint End Date");

        if (endDate >= sprintStartDate && endDate <= sprintEndDate) {
            sprintNumber = sprintRecord.getCellValue("Sprint Number");
            break;
        }
    }

    if (sprintNumber) {
        await table1.updateRecordAsync(record.id, {
            "Due Sprint Number": sprintNumber
        });
    }
}