Proper use of filters in TableView macro

[Glen Collins]

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?

Thanks.

Glen

[Alex Medved _ConfiForms_ · Accepted solution]

Hi

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

Column1:>[date20210911 08:00] 

Alex

[Glen Collins]

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?

Thanks!

Glen

[Alex Medved _ConfiForms_]

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?

Alex 

[Glen Collins]

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:

BeginDate:[date20210906 17:00]

I get the user Bob showing. If I do:

BeginDate:>[date20210906 17:01]

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:

BeginDate:<[date20210913 16:00]

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.

Thanks.

Glen

[Glen Collins]

Alex,

    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?

Thanks!

Glen

[Alex Medved _ConfiForms_]

Hi

Verifying what you have said about the filters...

Please see this screencast http://recordit.co/NGiTWIlRZA

As you can see it works just fine and correctly filters the datasets

Alex