How to filter out repeat dates for a Choice component?

[matt45]

Hello,

I have a weekly progress report that employees submit via a form and all the data gets dumped to a connected Google Sheet. One of the questions (a Choice component) asks “Which week are you reporting?” The three options are pulled from a Google Sheet that change dynamically via some formulas.

As an aside, the purpose is so that if employees miss a week they can retroactively submit reports and I’m still able to generate reports with data being associated with the proper week. Now to my problem…

What I would like to be able to do is to remove an option if a employee has already submitted a report for a given week. So let’s say they already submitted reports for the weeks of 7/10/2023 and 7/17/2023 then only 7/24/2023 would be visible. So far I have been unable to figure how to properly filter out dates.

For proof of concept I made test columns with some text:

Filtering the data to have only unique values from the Test column worked.

Now to try the filtering with some dates:

When I filter the data with “is not” nothing is filtered.

However, when I filter a column with a date manually typed it the date was filtered out.

So it seems Filter Data on this Choice component with date values is not a viable option. What would be another approach to filtering out dates?

[Darren_Murphy]

@matt45

What I would like to be able to do is to remove an option if a employee has already submitted a report for a given week. So let’s say they already submitted reports for the weeks of 7/10/2023 and 7/17/2023 then only 7/24/2023 would be visible. So far I have been unable to figure how to properly filter out dates.

Here is what I would do (all in the Glide Data Editor - no sheet formulas):

• Firstly, coerce your dates to integers, both in your Choices table and your Submissions table. This ensures that you won’t get tripped up by any dangling time bits, or variations in device date formatting. Use the following in a Math column:

Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)

• Next, in your Choices table create a Query column that targets your Submissions table and apply the following filters:
  – User Email is Signed In User, AND
  – Integer Date equals This Row → Integer Date
• Once the above is in place, you can use the Query column as a filter on your Choice Component (Query column is empty)

[matt45]

Hi Darren,

I appreciate your reply. I think changing the dates to integers is a great idea, something good to have learned and keep in my back pocket for sure. The change to integers enabled me to actually use the data in a query.

However, I didn’t quite grasp how to use the Query column as a filter, but instead I was able to use an If-Then-Else column to get a list of unique Mondays for my Choice component. So problem solved!

Thanks again, Darren!