How would you do this? Range of dates in Choice

i have a choice component that is filled with every day of the year. Above that choice, I have another choice where someone selects a specific range of dates (week 1, week 2, etc). The list of days is then filtered to only provide the dates in the selected range.

Currently, I’ve done this by creating a separate sheet with 365 rows (every day of the year). Then I have another sheet with the Week Name (1-27), then 14 columns for each day of the two-week period. I think created a relation from the “All days” sheet to the “week” sheet that gives each day in the All Days sheet a week number and then I can filter my choice component from there.

How would you do this differently? I’m finding that since my choice needs to filter through 300+ rows, it takes a bit of time and there is a slight lag due to the processing that needs to happen.

1 Like

Do your weeks conform to the standard Sunday through Saturday or Monday through Sunday? Or is it every 14 days starting on whichever day the first of the year falls on? I’m just trying to understand the use case, because I’m thinking you could simplify a little bit by using the WEEKNUM formula in Google sheets to calculate week number for each day in your Days sheet, then create an array column of just the 2 week numbers in your Weeks sheet, instead of listing each day.

Are these choices being made inside or outside of a form?

1 Like

Hi @Jeff_Hager… the use case is for a new version of my Time Sheet app where a user selects a Pay Period (14 day period that begins on a Sunday and ends two Saturdays from the first day).

A user first select the Pay Period, then the day choices are filtered to only include the 14 days as choices for their time sheet.

The selections are being done pre-form.

1 Like

Ok, I would try the WEEKNUM thing if your data is in a google sheet instead of a glide table. Less columns for the day to week relation may help speed things up since you wouldn’t need it anymore.

Since you are doing this pre-form, you could try creating a week to day relation from the first week choice value and use the relation as the source for your day choice component.

1 Like

Thanks @Jeff_Hager… I think what I’d like to try to accomplish is a way to get rid of the “All Days” sheet entirely. It’s taking up 365 rows of data (and I’ll add another 365 rows come January when the year changes over).

I’m thinking of a way to do this dynamically, but I can’t figure out how to turn those dynamic results into choices in a choice component. That is, I’d like to use a Math column to calculate the start date of the chosen Pay Period, plus 1, then plus 2 in the next column, plus 3 in the next, and so on until I have 14 columns of User-specific results for the chosen Pay Period. I’m struggling though to find a way to pull those 14 rows into a choice component since they’re all in separate columns. I thought that maybe a joined list would do it, but the values in a joined list don’t show up separately when put into a choice component (since they’re joined). If I split that joined list, the split values also don’t seem to be values I can put into a choice component.

1 Like

I started having a play around with an almost identical approach.

  • Start Date
  • Start Date + 1
  • Start Date + 2
  • Start Date + 3
  • etc…

Then I thought, okay let’s join them all together using a template column…

And then then split that up…

And that’s where I hit a brick wall :brick:
I was expecting (hoping? praying?) that last column could be used in a choice component. But alas, no… :sob:

Here’s a thought. Choices need to come from rows instead of columns, so I think with your idea, we can restructure a bit.

Create your Week sheet with the week number in one column and the start date for that week in another column.

In your your Days sheet, create 14 rows and create a column numbered 0 through 13. Next add a single value column that will take the start date from a user specific column value that’s filled by the week choice component in a different sheet. Next add a math column that will add the single value date to the numbered column value. This should dynamically create all the days in a 2 week period based on the chosen week number and that will display uniquely for each user.

That should knock you down from 392 rows to 41 rows for the two sheets.

3 Likes

Exactly where I’m at!!

This sounds like it has potential… let me try and report back!

1 Like

Getting that tattooed on my forehead tomorrow :rofl:

2 Likes

You’re a genius!!!

The only change I had to make was to add a Template column that converted the Math column to text. For some reason, the results of the Math column came up blank when they were the values of the choice component, but when I created a simple template column that converts it to text, they came up right away.

This is honestly fantastic and I really appreciate the help @Jeff_Hager (@Darren_Murphy too!).

3 Likes

Glad to help!

Well now that we got that figured out, there’s this…

Not sure if it would help streamline anything, but something to think about.

4 Likes