1T - List of Dates

Hi there

Im trying to search for the the sum of items that fall on particular dates (ie searching for items on a list of dates)

I’ve used @Eli’s 1T - List of Dates plugin to return weekdays only between 2 ranges and can see this filling in values as dates in a text box.

Im not sure how to setup the following search however. I understand I should search for all dates in a range then filter for the dates I want so I’ve set up the search (which works) then tried filtering (which doesn’t :frowning: )

Any suggestions for what Im doing wrong?
Cheers - John

Is the initial search before filter added returning the desired results of all shifts between start date & end date?

Have you tried adding text on screen of the output of the date list to determine what it’s actually filtering for?

May wanna display some of this data on a test page to easily debug.

I may be wrong but the way I’m thinking about this off the top of my head your current search says this: (example data)

Do a search for shifts Start date between: June 1st and June 13th

Returning let’s say 3 shifts
June 1 5:00pm
June 3 5:00pm
June 7 3:00pm

If the date plug-in is spitting back
June 1,June 2,June 3 ,June 4,June 5

All with a default time of 12:00am none of the shifts would display as you’re not searching a date range rather trying to filter existing list (is in) by the exact date and time. Unless that plugin is returning every minute of every day of the dates you selected (is in) won’t find any valid filters.

Instead of filtering with (is in) try creating a range of those dates.
shift start date > date list first item
shift Start date < date list last item

Another alternative is just for this search have the date values all rounded down to start of the day. (Unless that won’t work with rest of your app logic)

That way the 3 shifts in example above rounded down would all be at 12:00 meaning it would be uniform with your date list and the is in operator would work to get your count. (Wouldn’t recommend but could work)

Hey @chris.williamson1996 thanks for responding. Super close!

I get data with the filter removed and also have a text field showing the filter output, but I think you’ve got it with your explanation of the exact date as this is part of what’s being returned
image

The date range fix doesn’t work because it’s testing for all dates within a range and removing my filter for weekends. But I think you are on the money - will have a think about the logic I need - the clue is the IS IN testing for the exact time. I do need to test for the time of day so will update this when I’ve figured it out.

Thanks again!

Golden! So it depends on your app but I think the rounded down option would work.

:Filter advanced this date:rounded down is in date list

This would take the example dates
June 1 5pm
June 3 5pm
June 5 3pm

And round them all down to 12am

Thus returning the records for each day.

In the case someone has 2+ shifts in a day it wouldn’t matter as both records are returned anyway and the rounding down is only to filter the list to return the record not to calculate shit totals.

Advanced filters aren’t ideal and I rarely recommend them but it would accomplish what you want.

Also this may help Calculate Working Days (Business Days) between a Date Range - #2 by romanmg

Not sure how to call the advanced filter with everything I have going on - simplified the search to just give me a count of returned items but still not getting any joy Appreciate your help :smiley:

1 Like

What you have above is returning plug-in date list value rounded down to day. (which it is anyway)

We need start time rounded down to day which is where advanced comes in.

Add a new filter constraint. The field is going to be advanced instead of start time (very bottom of constraint options)

Now you’ll do
Advanced: this shifts start time:rounded down to day is in date list.

(Delete constraint you have currently, advanced will be only one on the filter)

This video shows how to do advanced filter. Then use the constraint above and you should fingers crossed get the result you’re looking for

1 Like

@chris.williamson1996 thank so much for sticking with me on this one :smiley: and for taking the trouble to put your response together.

Im spending a lot more time writing reporting for my app features now so this is invaluable. Interestingly “is In” was not an option available to me so I used “Is Not In” and just flipped the logic of the list of dates to get what I wanted

This worked perfectly and Im now going to do some more complex reporting and pop a how-to in the forums for other to follow.

Thanks again!
johnnyweb

1 Like

This topic was automatically closed after 70 days. New replies are no longer allowed.