Prefilter Cal Viz

[ontarget]

Hi is it possible to
A. prefilter a fullcalendar Viz or,
does one have to:
B. copy the list and make a prefilter on the copied list then make a new Viz from that?

I cant seem to find any prefilter options
Thanks for any pointers!

 

[troester]

There's no prefilter option (like in googlemap) but it has a "Where clause". I never used it but it sounds like a "manual" prefilter.

BTW: If you copy your list you don't need to create a new viz, you can select this new list in your existing viz.

 

[cheesegrits]

Yup, I added the "Where clause" recently, for exactly that reason ... a "poor man's" pre-filter. It's good enough for most requirements for filtering a calendar.

-- hugh

 

[ontarget]

Great thanks for the replies - i will try that - I cant see the "where clause" in the viz options, i'm on Fabrik 3.9 but not on the latest commit so I will update first.

 

[cheesegrits]

Yup, that's an option I've added since the last release, so you'll need to do a full github update.

-- hugh

 

[ontarget]

Hi I updated from git and can confirm the where clause in the fullcalendar viz
How do i declare the clause? I have tried e.g.

• WHERE {aaa_calendar___category} = 'Teacher Leaders'
• WHERE {aaa_calendar___category} = "Teacher Leaders"
  
• WHERE '{aaa_calendar___category}' = Teacher Leaders
  
• WHERE '{aaa_calendar___category}' = "Teacher Leaders"
  
• {aaa_calendar___category} = 'Teacher Leaders'
  
• {aaa_calendar___category} = "Teacher Leaders"
  
• '{aaa_calendar___category}' = Teacher Leaders
• '{aaa_calendar___category}' = "Teacher Leaders"

Or should i be pulling the raw data?

Do i need a Select statement as the element {aaa_calendar___category} is a dbjoin element

Here is my raw output from one entry

Event Link: Go to Page<\/a> "},"19_230_2019-11-18 12:11:00":{"aaa_calendar___id":"230","aaa_calendar___id_raw":"230","aaa_calendar___date_time":"2019-11-18 00:00:00","aaa_calendar___date_time_raw":"2019-11-18 00:00:00","aaa_calendar___organisation_raw":"19","aaa_calendar___organisation":"ACCS","aaa_calendar___category":"Teacher Leaders","aaa_calendar___category_raw":"3","aaa_calendar___category_id":"1","aaa_calendar___category___params":null,"aaa_calendar___title":"Huw Test","aaa_calendar___title_raw":"Huw Test","aaa_calendar___county_p_raw":"13","aaa_calendar___county_p":"Kerry","aaa_calendar___start_date":"2019-11-18 12:11:00","aaa_calendar___start_date_raw":"2019-11-18 12:11:00","aaa_calendar___end_date":"2019-11-18 12:41:00","aaa_calendar___end_date_raw":"2019-11-18 12:41:00","aaa_calendar___datecheck":null,"aaa_calendar___datecheck_raw":null,"aaa_calendar___external_link":"","aaa_calendar___external_link_raw":"","aaa_calendar___current_user_raw":"621","aaa_calendar___current_user":"621","aaa_calendar___organisation_title":"TITLE: ACCS \/ Huw Test
LOCATION: Kerry","aaa_calendar___organisation_title_raw":"TITLE: ACCS \/ Huw Test
LOCATION: Kerry","slug":"230","__pk_val":"230","id":"230","rowid":"230","startdate":"2019-11-18 12:11:00","enddate":"2019-11-18 12:41:00","link":"\/primary-cal-2\/form\/19\/230.html?tmpl=component&Itemid=397","label":"TITLE: ACCS \/ Huw Test
LOCATION: Kerry","colour":"#ff05ff","formid":"0","status":"current_user621","allday":false,"customLink":"","details":"\/primary-cal-2\/details\/19\/230.html?tmpl=component&Itemid=397","custom":false,"_listid":"19","_formid":"19","_canDelete":true,"_canEdit":true,"_canView":true,"startShowTime":true,"endShowTime":true,"popupTemplate":"

Organisation: ACCS<\/p>\r\n

 

[troester]

You need the MySQL syntax, so only the column name and the value stored in the database
category = "3"

 

[ontarget]

Thanks Troester,
That works however the value is a multiselect checkbox so the value for category in aaa_calendar is Null and i believe the values are stored in aaa_calendar_repeat_category e.g parent_id = 232 has 2 entries for category "1" and "3"
should it be some sort join statement?
FYI i tried:
category FROM aaa_calendar_repeat_category = "3"

 

[ontarget]

Hi Troester / Cheesegrits
Thank you for your initial guidancewith this issue.
Can you help me further please.
Do you know if the WHERE clause in the calendar Viz can filter based on the values in another table ?
My initial test was with a non multiselect value and your solution worked for this e.g "category = "3" "
However as I require filtering on the value of a multiselect checkbox so the actual value is now being stored in the aaa_calendar_repeat_category table.
See my previous post I have tried various SQL statements but none of them filter the calendar as required.
e.g aaa_calendar_repeat_category.category = "3"
Thank you as always for your support

 

[cheesegrits]

The calendar uses the normal list model query builder, so it should build the same query that a list view would. So I would have expected the checkbox join element to be filled with the JSON representation of the selection ("[3,5]"). If it isn't, you may need to get creative, and do a subquery ... something like ...

WHERE aaa_calendar.id IN (SELECT parent_id FROM aaa_calendar_repeat_category WHERE category IN (3,4,5))

-- hugh

 

[ontarget]

Thanks Hugh
I tried both
"WHERE aaa_calendar.id IN (SELECT parent_id FROM aaa_calendar_repeat_category WHERE category IN (3,4,5))"
and I tried dropping the initial WHERE it gives an error:

0 __clone method called on non-object

I was thinking along the lines of using a calc element "aaa_calendar__categorycalc" to create a string containing all the values of the checkbox elements
e.g.
SELECT `category` FROM `aaa_calendar_repeat_category` WHERE `parent_id` = "232"
This would return values 1,3

Then in the WHERE clause in the calendar Viz pull the data from the calc element using a regex e.g
categorycalc LIKE "% [3] %" (or some regex that detects the presence of "3")

This would then filter the calendar on entries which have the multiselect value of 3 - happy days!
Do you think this approach would work?

 

[ontarget]

checkbox join element to be filled with the JSON representation of the selection ("[3,5]")

I just checked again the category col (checkbox element) value in table aaa_calendar is NULL

 

[ontarget]

Sorry Hugh,
I just realised my connection string had been knocked out of the calendar viz
aaa_calendar.id IN (SELECT parent_id FROM aaa_calendar_repeat_category WHERE category IN (3,4,5))

Works perfectly!!

Thanks for your help