Hello. I'm trying to find the proper way to use the TableView filters to retrieve data between certain dates. The basics I have tried to just retrieve on the 1st date in the dataset and that's not working as expected.
Column1: 2021-09-01 8:00 (yyyy-MM-dd hh:mm) Column2: 2021-09-10 08:00 (yyyy-MM-dd hh:mm)
Column1: 2021-09-11 8:00 (yyyy-MM-dd hh:mm) Column2: 2021-09-19 08:00 (yyyy-MM-dd hh:mm)
Column1: 2021-09-20 8:00 (yyyy-MM-dd hh:mm) Column2: 2021-09-28 08:00 (yyyy-MM-dd hh:mm)
That's my sample data.
Distinct Filter: Column1: > [date20210902 08:00] AND Column2: < [date20210909 08:00]
Should return 1 line?
Or even more simple to see if it's actually working:
Distinct Filter: Column1: > [date20210911 08:00]
Should return 2 lines?
The time is hardcoded and placed into the dataset as shown.
I'm new to Confiforms so I'm learning on the job, Not even sure if I',m doing this correctly and have been reviewing the samples but can't seem to grasp it yet.
Anyone got ideas?
According to ConfiForms filters documentation you can supply your dates in [datetimeyyyyMMdd HH:mm] format and use :> or :< to supply ranges
What is important is to put a syntactically correct expressions, as
Column1: > [date20210911 08:00]
is not the same as
Thanks for the info Alex! I just have one additional question....
What is the best way to debug if a filter is NOT working properly. I have debug turned on and reviewing the confluence logs but I can't seem to determine why the filters fail. So it's basically trail and error until the filter is successful.
Got any tips?
Not sure what do you mean by debugging filters... Looking at confluence logs is certainly not the way to debug anything.
You say your filters fail... what do you mean by that? Do you see error messages?
Documentation on filters here https://wiki.vertuna.com/display/CONFIFORMS/ConfiForms+Filters
I am just unsure what do you expect could you clarify?
Ok. Let me see if I can explain a bit better than I have.
Here is the dataset:
BeginDate EndDate User
row1: 20210906 17:00 20210913 07:00 Bob
row2: 20210913 17:00 20210920 07:00 Fred
row3: 20210920 17:00 20210927 07:00 Jack
The dates are all Mondays with different start and end times. I would like to see only one line shown in Confluence when a user views this information based upon the time of the local user. So if it's 2021-09-07 @ 14:00 the user Bob would be shown. If it's 2021-09-13 @ 10:00 no user would be shown since it's not yet 17:00 but past 07:00.
When I try this in the Filter:
I get the user Bob showing. If I do:
I would expect Fred and Jack to show up but not Bob since it's past 17:00. But they do not. Or if I do:
I would expect Bob to show up because it's before 17:00 and the dates less than 2021-09-13 17:00 but Bob does not appear.
Do I have this filter usage all incorrect.
I'm basically trying to display one line based upon the day of the week. And on Monday there would be no user shown between 07:00 and 17:00.
I hope I clarified what I'm trying to do.
I think I actually have it now. Rather than using the [datetime*] function I can use
BeginDate:<[now] AND EndDate: >[now]
I think this will show the row I need for that week. I guess now I'll just have to try it and wait until Monday pops up.
Not sure why though the [date20210906 17:01] was not returning anything. Maybe the date format is incorrect between the stored value and what I'm filtering against?
Happy New Year, everybody! What better way to kick off 2022 than with a new Marketplace Partner Spotlight ✨ This month, we're getting to know Daniel Franz, co-founder of Fine Software. Fine Software ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events