I have a list of entries in a Sales data type with the fields Date and Amount. I am trying to search with a constraint that will allow me to return all records within a known week, say week 9 for instance.
I know we can use the :extract modifier on the right side of the constraint, however I actually need it on the left side if that makes sense.
This is what I really need as a constraint:
Do a search for Sales… where Date: extract (week) = 9
Any ideas?
Well, you can’t do this easily in vanilla Bubble. What you want is the start date/time of week 9 and the end date/time of week 9, right? So that you can configure a search constraint as some date being after start of week 9 and before end of week 9.
I actually have a set of server side actions that can do this (easily give you those dates), but as I’m fond of saying lately, these do not return with any alacrity, so I have not made them available.
In the meantime, you can load moment.js in your page and do some JavaScript, but that’s probably not the answer you had hoped for. (You can also do this - potentially with less accuracy - via vanilla JavaScript – using solutions that are outlined here: https://stackoverflow.com/questions/16590500/javascript-calculate-date-from-week-number).
We do wish that Bubble implemented some more useful date/time functions than the extensive date formatting options it currently has. This can be fixed, and the fix is server-side actions mostly (this can also be done with client-side plugins but interface-wise this makes little sense to me for reasons I will not go into here). But the catch 22 is that SSAs (while possible right now) are, practically-speaking, worthless at the moment due to speed issues (which I’m pretty sure are not speed issues, but artificial limits on CPU time that such features are allocated).
So, if you want to solve this problem immediately, you are going to have to become a date geek like me and just load up some libs and do it in the page.
Try using an advanced filter:
If the week number is static, then you can just type it in. If it’s dynamic, then you can use some input’s value or value from the database to finish out that advanced filter expression.
Hopefully this is what you were looking for!
Gaby at Coaching No Code Apps (formerly Coaching Bubble)
Courses & Products, Tutorials, Private Coaching, and High-level Development
An advanced filter I was using for dates items created on was extremely slow. How would this method compare?
