Quarterly Planning - Pull Dates

[Kristin_Grimpe]

I feel like I’m overcomplicating this. I plan my writing business by quarter assigning tasks to week number then date (so something due this week is due week 14, start date 04/11/2022, due date 04/17/2022). What I want to do is be able to select the week number and have the table automatically pull the start date and end date of that week. I have made a quarter table with dates, a week table with dates, and used lookup columns to attempt to pull this data into my tasks table but I get the "not a valid row reference) error. What do I need to do to be able to pull these dates?

[Jean_Pierre_Traets]

Dear @Kristin_Grimpe welcome to the Coda Make Community,

I am rather sure that this post contains the resources you are looking for:

How to get the corresponding month name from a weeknumber?

good to see you solved this part of the puzzle @Xavier_Rabilloud ! you might want to go even one step further. sometimes you have two months in one week. below the steps to force a choice. [image] [image] and in the last step I make some hard coded choices and these you can put as you see fit. Here is equal or greater than 4 days [image] As always, this can be put into one formula. However to understand the steps, this might be easier to follow. Sequence(thisRow.from,thisRow.to).Filter…

Credits: @Christiaan_Huizer

[Kristin_Grimpe]

Thank you, but that seems to be calculating month based on week. I’m looking to pull the info that’s already in a table–like a VLookup in Excel.

[Paul_Danyliuk]

Hey @Kristin_Grimpe, welcome to the Community!

I assume you get the error on the Deadline column? Those are dates so the column should be of Date type, not Lookup.

Forget the VLOOKUP and don’t mind the name. In Coda, Lookup column/data type means “row(s) from a table”. E.g. in your doc, Project is a single-select Lookup from Projects and Week is a single-select Lookup from Weeks. If you’re pulling dates, text values, numbers etc from those linked rows, those are not lookups but dates, text values, numbers etc

So yeah, if you need to pull in the end of the week into Deadline column:

1. Make it a formula thisRow.Week.End.
2. Make it a Date column.

---

Oh, and just in case you’re missing it. You DON’T HAVE TO calculate e.g. Deadline as another filter formula based on week selection. E.g., DON’T EVER do this:

Weeks.Filter(
  CurrentValue.[Week #] = thisRow.Week.ToText()
).End

This is a common “I’ve been using Excel’s VLOOKUPs” way of solving this. Everything is wrong with this:

• Linking data based on text values instead of lookups (row references)
• Misusing ToText() to band-aid the above: “cast” a row reference to text
• Not using .First() to unwrap the list-of-one
• Using this in general.

In Coda, once you have a row reference selected/calculated into a lookup column, you can read associated properties by simply:

thisRow.Week.Start
thisRow.Week.End
thisRow.Project.Name

etc

[Kristin_Grimpe]

THANK YOU SO MUCH! That worked perfectly and is super easy to understand. I knew I was getting caught up in my Vookup head.