I’m wanting to migrate an app from Google Sheets to purely Glide Tables, but I’m not sure how to replicate some features.
I have a dashboard that managers are able to click in and view weekly reports submitted by their staff. They see a list of Mondays that represent each week:
In Google Sheets I have Sheet #1 that is a log of submitted weekly reports and then in Sheet #2 I use =sort(unique('Weekly Reflections'!E2:E),1,false) to find each unique Monday and then sort them in descending order. I then use that column to create Relations & Lookups, generate some stats, etc.
It’s rather simple and straightforward in Google Sheets but I haven’t the slightest clue now to reproduce this purely via Glide Tables.
What would happen if nobody submits in a week? Would it show up as a row with 0 Submissions, or doesn’t show up at all?
I would expect it to show up, and in that case, I would have a helper table where you:
Add as many rows in advance as you need, say 100 rows for example.
Add a rowID column.
Use a lookup column, targeting the full rowID column that you have just added.
Use a “Find Element Index” column, pointing to the array of rowIDs, and find the rowID of the current row. This would give you an index column starting from 0 and ending at 99.
In another table, I would usually use a Choices table for this, add a “Date Start” column and add the first Monday in your database to the first row of that column.
Back to the helper table, use a single value column, target the Choices table, bring over the first value from that “Date Start” column.
Add a math column: DS + I*7 with DS being the Date Start, I being the index.
You would have a list of dates on Monday, ranging from index 0 to 99 with each one 7 days after the previous.
Via my method in Google Sheets if nobody submits in a week it wouldn’t show up at all, because the formula only finds the unique dates amongst the submissions, so naturally what isn’t submitted won’t be found.
