I have a list of events with a “start” datetime (timestamptz, in supabase) field. I’m struggling to find a good way to filter on this when using building longer formulas in the formula builder. Particularly when I need to return results that are only partial matches.
For example, below, if I have two nested ‘filterbykey’ formulas, and I want to include all “start” dates that start with “2025-05-10”, how would I do that?
This is a small piece of a larger formula, but I’ve removed everything else for testing/illustration.
I do have the dates plugin active, but have still been struggling with keeping all the date and time values correct while developing. I have been using individual ‘date’ and ‘time’ fields up until now, but am wondering if that is really the ‘correct’ way to do things–or if I should convert to using a proper datetime field.
dates are always tricky to work with but if you want to just look a part of the date you can use the subText so if your filtering on start with 2024-05-10 you could do subtext(start_date, 0, 10). If your search is this complex tho I would recommended using JS with AI assistance its much easier to test and to read for bigger formulas and with the help of AI the jump isn’t so hard
I previously had tried subText quite a bit (and have used it in other areas), but couldn’t get these dates to actually compare against each other. Turns out you have to also use toText as well.
It appears that ‘contains’ can not be used on date values, so you must convert to text…just returning a portion of the ‘date’ type is not enough to convert it to text–you must explicitly do so using toText.
Which means this is the formula I needed to just see if two dates fell on the same date (one in the database, one in a variable on the front end):
Haven’t put it into the larger formulas yet, but at least now I know it works to compare pieces of two datetimes.
And I know this would be more efficient in Supabase @Broberto. As always, I appreciate your advice. I will keep working to get some of my more complex functions pushed over there longer-term.
However, I don’t love the idea of having different pieces of the logic in different places as I’m building and experimenting. When I do that I tend to forget where all the moving parts are!
As clunky as copy and pasting these formulas are in WeWeb*, at least I can iterate quickly and see the impact on the data in realtime.
*Side note to WeWeb: I’m sure I’ve already seen this feedback elsewhere, but if we could copy and paste the no-code formulas in/out of weweb (i.e., to a text editor to edit or save for later), it would be a huge QoL improvement. I’m sure there are good reasons why this does not exist, but would be nice!
