Hello, Coda.io newbie here, as well as a beginner with formula languages in general so any help is appreciated.
I have an events table with Name and Dates of the events. The Dates are just set to display day of the week name as the events recur every single week (so if it’s Monday’s event, I’ve just selected the first Monday of the year so that the day cell will just show Monday).
I then have another table where I would like to be able to select the event from a Lookup if the event day-of-the-week is the same as a Date_selection (separate from either table). So if I Select any date that is a Monday, I will be able to choose from the drop down Lookup menu ALL events that recur on Mondays.
I hope I explained that clearly. Let me know if you need any more information.
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 :
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 ) :
Incredible and detailed response, thank you so much. I can see why you are a community champion. However, I should have clarified that while I only care about the day of the week that events are on in the Events table, the “select day of the week” is needed to be a specific date-- as the goal of the sheet is to track attendance of individuals per event on any given date (which all gets saved to a log).
I’ve got everything else figured out, but as we have about 30 different events throughout the week, I was trying to make the logging process much easier by only showing the events that are hosted on the day of the week of the selected event.
Oh ! I see ! This should simplify things
And you also already have a datepicker on the canvas .
As far as I can say, you just literally need to use your datepicker as an interactive filter in item settings in the options of your Lookup field Event and it will give you only the events occurring on the date you chose in your datepicker .
Hmmm, I’m not sure this is right. It seems to just be giving me events that are in the Events table at that exact date. To clarify, the date in the Event’s table does not matter. Only the Day of the week. The same “Event 1” is going to happen on Jan 3, 10, 17, 24, 31, etc etc.
“Event 5” similarly will happen on Jan 4, 11, 18, 25, etc etc
I would like it so that went I use the date picker on any date, the event selector in Another Table only shows events that happen on that day-of-the-week.
UPDATE: Finally figured it out.
Under Lookup Options, Item Settings, Custom filter formula:
(WeekdayName(Day)=WeekdayName(selected_date))
Day being a calendar date from the table of all events, and selected_date being the chosen date for the other table.
Thank you Pch, even though you did not explicitly give me the right answer, you prodded me in the direction I needed to figure it out through experimentation.
! Welcome to Coda 
and to the Community 
!
) your setup but, I must say that in its state (with the dates displayed as days) it might leads to “errors” 
.
! This should simplify things 
