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.
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?
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.
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.
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.