Shortcut for "Between Dates" Filter that INCLUDES the dates selected?

[tbtilton]

Hello everyone,

In my app, I have a lot of instances where users filter lists to include all list items that have a date from one selected date to another. One use case is where a user selects a start date and an end date of a “Pay Period” so that they can see their time cards submitted during that period. This seems hardly uncommon - seems like most of the “demo” apps are some version of a “trip planner” or “scheduler” that relies on filtering by dates and/or date ranges…

So as you all probably know, Adalo has built-in filtering for “Between” dates, but that filter EXCLUDES the 2 selected dates from the results. I can certainly see why this is that way, as “between” does not mean “between AND including”, but the fact is that when you ask a user to select a start date and an end date, they are assuming that their results will INCLUDE the start and end date. For instance, if I wanted to see all my time cards for last week, I’d probably select my start date as Monday, Feb 28, and my end date as Sunday March 6. I wouldn’t automatically think, OH, technically that period is actually “between” Sunday Feb 27 & Tuesday March 7, so I need to set those 2 dates as my start and end!.. ha.

There’s an obvious workaround to this with the new “Or” funtionality for filtering. However, that means I need to use 3 different filters: 1) “between” start and end date OR 2) “equals” start date OR 3) “equals” end date. This is OK, but it gets really cumbersome once you start adding multiple “AND” conditions to the filters. If your filter gets complicated, now you need to replicate that complicated filter 3 times per list. AND, if you have to have multiple different lists to allow for detailed searching/filtering based on user inputs, then it REALLY gets cumbersome.

For example, I have a project search functionality where I am allowing users to filter projects based on Project Stage, Project Type, Project Manager, Project Supervisor, and whether or not specialized equipment is required. With the way I have my DB setup, this requires 12 different lists each with 5 “AND” filter operators. That’s 60 Filters to set. If I wanted to add a date filter to this search that allowed users to select start and end period for project dates, then I’d have to add the 12 “AND” filters with “Between Dates” specified, but then I’d have to replicate the whole thing twice more per list to include the start date and the end date in the search results, so I’d need to manually create 144 more filters among all the lists to accomplish that simple task. That’s 216 filters in all just to effectively search a list on 6 possible parameters. God forbid I make an input error and have to file through all those lists looking for the mistake!

Does anyone know a shortcut? An easier way to get Adalo to return “Between AND Including” 2 selected dates? Again, I know why it’s set up that way, but fact is from a user perspective, when 2 dates are selected to create a date range, it’s assumed the returned range will INCLUDE the 2 selected dates, and I feel like Adalo should have a filter option to reflect that, to make it easier on us! It would be great to see an additional filter option like “From-To”, in addition to the existing “Between”, Before", “After”, and “Equal To”.

Anyway, if anyone’s got any insight, I’d greatly appreciate it.

[Flawless]

I’ve got an answer for you but I’m about to step away for breakfast. Give me just a bit and I’ll show you how you can achieve this easily without having to create a ton of filters.

[tbtilton]

Cool!

So, after weeks of sort-of settling on just doing it that long way I described above, I have been working on figuring on this pretty intensively this morning. I THINK I might have it. I’m currently working to test it out, but If I use text inputs(made invisible) to convert the date/time picker data to UNIX using formula INT(StartDate)-1 for the start date and INT(EndDate)+1 for the end date, that will give me a range in UNIX.

BUT, I now have to add UNIX time stamps (number format) into all of my DB Collections, which will write simultaneously when a record is created.

This is way simpler from a filtering standpoint, but WHEW I’ve got my app’s input functionality 90% done and going back through to make sure dates are always doubled into UNIX fields will take a while.

I’m just going to keep testing on this and wait to see what you have to say before I go making huge structural changes to my DB setup & Fields…

[Flawless]

You’re on the right track. Here’s how I was able to get it to work perfectly:
Add 2 new user date & time parameters: Viewing Start Date and Viewing End Date

On the screen where we have the filters with the 2 date picker inputs, we also have a button to update the times that the user is viewing. The button action is to update the logged in user’s viewing start date and viewing end date with the 2 date picker inputs -1 for the starting date and -1 for the ending date.

Like this:

Then, your filter can be set to between logged-in user’s viewing start date and logged-in user’s viewing end date:

[tbtilton]

Thank you for the extremely clear and thorough reply! Excellent solution. And versatile.

I had been trying to minimize the use of “single-screen-functionality” fields in my user data, but does it matter? I guess I was worried about too much DB interaction and/or cluttered collections slowing down the app. Do you find that to be the case or can we just go nuts with the number of fields we add to collections?

[AsherL]

Are you not able to do a shortcut and in the filter use DatePicker1-1 and DatePicker2+1, to avoid having to save the fields to a user?

[tbtilton]

@AsherL Yes, I’m pretty sure I’m sure. Adalo is weird and inconsistent with date handling and also about where you can and can’t use formulas. You can’t just +1 to a date-picker input in a filter for some reason. At least I can’t in my editor.

[tbtilton]

So, @Flawless , I may end up going with your suggestion after all. I’ve been working on it a bit more tonight and working with UNIX in Adalo isn’t as simple as I’d thought. Adalo logs the UNIX time according to GMT, and once you’ve switched it over to a number format and/or started doing math on it it, the system seems to stop doing whatever it does to adjust for time zone. So, trying to identify a date range or specific day gets real weird real quick depending on what time you’re using the app. I only really noticed this because I happen to be burnin’ the midnight oil so to speak, so all of a sudden my tests were going all wonky. And that’s because it started registering tomorrow as today because GMT is now in tomorrow. I started trying to work in adjusting for time zone manually by subtracting .2083 from the values, but then I realized DST is right around the corner and I was like NOPE this is too funky…

This is a great example of some really basic, core, necessary functionality that’s sorely needed and missing from Adalo. It is a real bummer to see all the effort going into shiny new buttons and whatnot when working with time like this is still such a disaster. I STILL can’t for the life of me figure out some kind of a way to get a Date Picker component to have a default date of the beginning of the year, so my reports can default to YTD… I’m just gonna do without. But hey, I hear that we’ll have mouse hover state effects for PWA soon. yay.

Sorry I’m a bit salty. Had a rough 14 HR workday in Adalo today, complete with glitching conditional visibility settings (several simple visibility settings that just didn’t work and after much hair pulling the components needed to be deleted and re-built and poof, magically worked…), editor hang-ups, a page that refused to allow components to snap together for some reason, with all the components always being a half-pixel off from each other and when one would snap together others would snap off, etc, completely breaking the layouts. After a couple hours had to just delete the screen and start over and POOF magic it works fine. Then there was the sudden loss of all data passing in 2/3rd of my app, which after about an hour I traced to 2 screens which had no errors and which have been built and working fine for weeks, and the only change that was made today was adding the links to the Home Screen on the nav bar (which shouldn’t break all the other data passing - believe me I checked). After much effort to delete and rebuild all the links to and from those 2 pages, and tracing every possible link in and out and the data passing, ultimately they just needed to be deleted and completely re-built from scratch to get them to stop breaking the data pass through. OH, and the best part: middle of the day the platform just stopped syncing for like a half hour apparently, then crashed out to the login, and when it finally came back online, I’d lost a half hour, 45 minutes of odds and ends tidying and debugging work, so for the rest of the day and night, I’ve been randomly running into and re-fixing bugs and little adjustments that I THOUGHT I’d made. Talk about feeling like your going crazy!

ugh… well, anyway, just venting. I feel like we all know Adalo is the bees knees. They really have built something great, and it’s allowing me to build something great for my company that’s going to fundamentally change the way we do business. So I’ll deal, but man, progress can’t come fast enough. I hope they hear our pleas for stability, reliability, basic functionality and performance above all else!

Oh, and thanks again for your help, I think this forum is the only thing that’s kept my sanity in check over these last few months.