How to look up events recurring on the same weekday as selected date

[Pch]

Hi there @al_p ! Welcome to Coda and to the Community !

I’ve been trying to reproduce (see examples below ) your setup but, I must say that in its state (with the dates displayed as days) it might leads to “errors” .

And here is why :

If you format a Date field to display the date as a Day of the week, it’s just a display. Behind, there’s still a very precise date.
Which means that when you enter an event in your Events table and select the Monday 1/11/2021 and then adds another event but select the Monday 1/18/2021, they will both show up as Mondays but they are in fact completely different because the dates are not same.
And that could be problematic to filter your lookup in your other table .

And because when “pre-filtering” your Lookup field you can only compare “things” which have the same formatting (Dates needs to be compared to Dates, Text to Text, Numbers to Numbers, etc… this is true everywhere in Coda, not just in this case), the goal here is to flatten all this so the Lookup can correctly filter the events.
So I came up with 3 suggestions .

Option 1:
If you really don’t care about the dates underneath those days of the week and you’re completely 100% sure you won’t need those dates later, what you could do is :

Instead of using a Date field create a Select List where the Selectable items are, simply, the days of the week .

In your other table, once you’ve created your Lookup field (following this path : Right-Click on the appropriated field → Change column type → Lookup (somewhere near the bottom of the menu) → select your table Events) go to Lookup options. Once there, look for Item settings then go to Filter. In the dropdown menu, select Custom filter and click on Add filter.
Coda will invite you to select the field to use to filter the Lookup and here, you should choose Dates.

As you want this filter apart from both tables, under Dates there’s another dropdown menu where you should see the option Uses interactive filter (more info). Click on that and then on Create Control .

Coda will automatically create a Multi-select control on the canvas of your doc, gathering the “Dates” you’ve already put in your Events table and you’ll be able to select any Dates in that Control to pre-filter the Events in your Lookup field .

Another way to get to the same results would be to create yourself that (Multi)-Select Control on the canvas (which I did in my example) and select that Control in Uses interactive filter .

---

Option 2
After all that, If you’re actually not certain that you won’t need those “precise dates” later (see my other example below), the other suggestion I can make is to keep the Date format of your Dates field in your Events table but display it as classic date. And then add a text field to this table.
In this new field, just add a WeekdayName() formula (more info) that will give you the name of the day for the date you put in your date field :

thisRow.Date.WeekdayName()

The rest follow the same Interactive filter principle but using this new text field instead of the date field

---

Option 3
This is a bit more complex but it uses your (I think ) actual setup (with you Dates formatted as Day of the week)

As I said above, you can only compared “things” with the same format.
In this case, your Dates are still Dates but they might be different even if they look the same.

So, to be able to pre-filter the items in the Lookup field, this time, you’ll need to create a Select Control on the canvas which will be used to filter the items in the Lookup later but also to convert the Date format into a Text format so whatever Monday you select in your Events table (1/11/2021 or 1/18/2021) it will interpreted as a Monday .

In the Selectable items of this Select Control you should put a formula looking like this :

[YourEventsTable].Dates.FormulaMap(CurrentValue.WeekdayName()).Unique()

What it does is list all your dates in your Events table and for each (CurrentValue) dates in that list (FormulaMap()) gets the WeekdayName() (as the result of a WeekdayName() formula is given in a Text format). Then, it only keeps the Unique() results.

Now, in your Lookup field, in the Item settings, instead of just select or create an interactive filter, you’ll need to use its counterpart, the formula Matches():

WeekdayName(Dates).Matches([Select the day])

What it does is gets the WeekdayName() from your dates in your Events table and convert them as a Text format and then check if any events matches the option selected in your new canvas Select Control. And it works because now both are in a Text format

And, that’s it

Don’t hesitate, if something is not clear .
It’s not always easy to explain in a simple way all those things , so all this may lack clarity .

Small add-on to the Option 3 : If you need more help to understand how FormulaMap() works and what it does, you can check this post (which helped me a lot with this specific formula ) :

FormulaMap() : When and where?

Hi there ! As I’ve said in this post, I’m currently struggling with FormulaMap() Thanks to the explanations of Paul, I was able to understand what the formula does in this case but I still have troubles to apply it correctly in other cases. I’ve used it, on my own, a number of times, principally to “merge” different fields into another one but I admit that when it comes to use it for other reasons, I never know if, why, when and how… I can instinctively “feel” I should …