Sprint name automatically picking according to the date

[Suraj_Prakash]

Hey everyone,

I have a table under one page(sprint assignment). That table has sprint start and end date, and sprint name.

Now whenever I create a story and put the date. I want that story automatically pick sprint name according to the date that I choose.

For example

sprint name | sprint start date | sprint end date

SP1 | 22nd Apr | 5th May
SP2 | 6th May | 30th May

Now I have a story and choose 22nd May as completing date for the task, and I want Sprint name column automatically pick SP2.

But I am not sure how it will happen so if anyone knows then please help me here. Thanks

[Pch]

Hi @Suraj_Prakash and Welcome to the Community !

Is this what you’re trying to achieve ?
(Look at the row named SP2 in the sample below)

The formula I used in the field Name is this one :

If(
  thisRow.End.IsBlank(),
  "",
  Concatenate(
    "SP",
    Sprints.End.Sort().Find(thisRow.End)
  )
)

What it does is: As long as the End date is still blank, the name will be left blank ("").
Otherwise, it Concatenate() the text "SP" and the visual position (Find()) of the value within thisRow.End in the whole sorted list (in an ascending order) of end dates in the table Sprints (Sprints.End.Sort())

So, when you add an End date falling in between 2 End dates already in the table, the whole field Name will recalculate the values according to that new End date.

Now, the thing is that if you delete a row in the Sprints table, the whole field will also be recalculated and what was SP3 could become SP2 which I don’t know, might maybe lead to some confusion …

[Suraj_Prakash]

Hi Pch,

Thanks for helping me but I am trying to solve something else.

So let me try to explain it again so you can help me better.

I have a story(work story). And I make column date and I choose “10th May” under that story. Now I have another column that will automatically pick SP2 under that.

The story is on somewhere else. So Logically it should work like this.

If story.date is 10th may then check in which Sprint it will come under. It’ll check start and end date column and find that this date comes under SP2. then it’ll automatically pick SP2 on that table.

Thanks for help.

[Pch]

Hi @Suraj_Prakash !

I think I see what you mean now … Well, I hope I do …

So, you want to retrieve the appropriate Sprint, from your Sprints table, for each task in a Tasks table depending on when their due dates falls when compared to the Start and End dates of your sprints (if I got this right this time )

Depending on how you want your intervals to work for your Sprints (as they could be strictly inclusive, strictly exclusive or a mix), this might need some adjustments but here is the formula I used in the single select relation field Sprints in the table Tasks :

Sprints.Filter(
  Start <= thisRow.Due AND thisRow.Due <= End
)

(Note that both Start and End are in fact CurrentValue.Start and CurrentValue.End )

And what it does is: It takes the table Sprints where I’ve stored their Start and End dates and compare those dates to the value in thisRow.Due to return the appropriated sprint …
(Sorry, I’m a bit too short on time to give you more explanations )

I hope this will help you this time

[Suraj_Prakash]

Hey Pch,

It worked for me. really thanks for helping me out.

[Pch]

My pleasure @Suraj_Prakash !

Glad to know this was, finally (), the result you were expecting !

[Suraj_Prakash]

Hi Pch,

I am really thankful that you helped me last time, I again need the same help.

This is coda view so u can help me quickly.

Let me explain the problem so you can help me better.

The problem is there is a “team” column under “sprint table” and “sprint” column has the formula, which should pick sprint name according to the date (“date” column) and the team member.

Now I want the formula to be worked like this. If a team is “Data Science” then the sprint column(from sprint table) will go to “view of Master All Sprints” table and choose all the rows which have “data science” in the team. It’ll go to look for the time period according to the date that is mentioned in “date column” under the same sprint table, and then the formula will pick the sprint name from “view of master all sprints” and put the name in “sprint” column under sprint table.

Right now, I cannot filter the raw according to the team that’s why formula is not working.

Formula

=If(thisRow.Team="Data Science",ifblank([View of Master All Sprints].Filter([Start Date]<thisRow.Date AND [End Date]>thisRow.Date),"Put the story date"), If(thisRow.Team="delivery & assistance",ifblank([View of Master All Sprints].Filter([Start Date]<thisRow.Date AND [End Date]>thisRow.Date),"Put the story date"),""),"")

[Pch]

Hi @Suraj_Prakash !

Thanks for sharing a sample doc !
I’m not entirely sure if this is what you’re looking for but I’ve got this :
(See the field [Sprint 2] in the table [sprint table] in the sample below)

[View of Master All Sprints].Filter(
[Start Date] < thisRow.Date AND [End Date] > thisRow.Date 
AND Team = thisRow.Team
)

This should return the Sprint from [View of Master All Sprints] according to the team mentioned in thisRow.Team and the date in thisRow.Date compared to [Start Date] and [End Date]

Now I can see that you’ve tried to do more things with your formula but I can’t tell what exactly …
(Note that you don’t need the = at the start of a formula in a table … Coda won’t know how to handle it)

So, I just added another field called [Sprint 3] in the sample below that will return a message if the Filter() formula doesn’t find anything (if there’s no team in thisRow.Team and/or no date in thisRow.Date in the table [sprint table]) …
This will only work in a text field though… If you use this in a relation/linked relation field, Coda might not appreciate the text value as an answer …

[View of Master All Sprints].Filter(
[Start Date] < thisRow.Date AND [End Date] > thisRow.Date 
AND Team = thisRow.Team
).IfBlank("Some info are missing")

Sorry if I misunderstood something … But I still hope this helps a little

[Suraj_Prakash]

Hey Pch,

Thanks again for helping me out. The problem has been resolved and it’s working perfectly.

Really thanks for the help

[Pch]

My pleasure (once again) @Suraj_Prakash !

Glad to know this is now working as expected !