+4Hi, I have two tables for which I'd like create a formula
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?
Best answer by TheTimeSavingCo
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
+28Hey @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
+31If 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
+4Thanks 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
+4I 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
});
}
}
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.