I have candidate applications sent directly to a Big Table. Everything works great, except when trying to query applications from “today” or “yesterday”.
Each candidate has “date submitted” data in the Big Table. I query that against a “Now” math column to find all candidates who applied today. Both are formatted the exact same.
Example: the table itself is showing me 8 applications have come through with today’s date (7/8/2024). However, the query count is only counting 5. Seems odd that it isn’t pulling everyone with today’s date, but it IS pulling a few of them.
I’ve tried using “is todays date” and “is within todays date”, and both only count 5 applications.
This has always worked with regular Glide tables, so I’m not sure what I’m missing.
I would try using a math formula to do this, but I haven’t tried to see if there are any computation limits in Big Tables.
- Add a math column to calculate today’s date.
YEAR(N)*10^4+MONTH(N)*10^2+DAY(N) with N being the Now value.
- Add a math column to calculate yesterday’s date.
YEAR(N-1)*10^4+MONTH(N-1)*10^2+DAY(N-1)
- Add a math column to calculate the record’s date.
YEAR(D)*10^4+MONTH(D)*10^2+DAY(D) with D being the date submitted column.
- Add a relation to relate today’s date against the record’s date, make it a multiple match. That should give you today’s records.
- Do the same for the yesterday one.
1 Like
You are an absolute rockstar! This did the trick! Would you happen to know the “week” calculation? Using WEEKNUM(Date) was rough for me in Big Tables.
Do you expect the week to start on Sunday or Monday?
Just an example for you, Glide natively considers the week to start on Sunday (July 7 till July 13, next week starts July 14).
What we want here is Sunday would belong to the previous week, and the week starts from Monday, ends at Sunday.
The trick is moving the weeknum formula 1 day back, so Sunday becomes Saturday (pushed to previous week), Monday becomes Sunday (still “this week” based on how weeknum is calculated), and so on until Saturday becomes Friday. The push back of 1 day only affects Sunday, which is exactly what we want.
Finally, here’s the formula you need. Get the year in for the full context.
YEAR(D)*10^2+WEEKNUM(D-1)
Do the same for the “now” and create a relation.
3 Likes
Wow, you’re a lifesaver! Thank you, truly. This is incredible!
1 Like
