Repeater where clause for multiple choice field

Sam Loveland asked on October 30, 2019 22:12

I have added a custom field to the Blog Post Page Type. This field is multiple choice, using checkboxes, allowing the author to select several options.

On a different page, I am trying to make a repeater which can filter these blog post pages based on one of those multiple choice options. How can I write the WHERE clause so that:

It uses a query string parameter as the value to compare against
It compares against all the selected values in the field

I am baffled on how to write this WHERE clause because I looked in the database, and the Page Type is saving the multiple choice options as a single string separated by "|".

Correct Answer

Peter Mogilnitski answered on October 31, 2019 02:26

As far I understood you want to check multiple in multiple, i.e. if you have options 1,2,3,4,5 defined in your page type and your query string gives you lets say 2,5. You want to get all the pages where either 2 or 5 or both are present. you need to transform your query string into WHERE:

'|' + multiChoiceField  + '|' LIKE '%|2|%' OR 
'|' + multiChoiceField  + '|' LIKE '%|5|%'

It would be easier to create a custom macro and generate this WHERE in C#.
Macro could be a bit hard to read but possible, for example if your query string parameter is categoryid and field name multiChoiceField

{%qs = QueryString.categoryid.Split(",", true); qs.Transform(" '|' + multiChoiceField + '|' LIKE '%|{#qs[DataItemIndex]#}|%' {#(DataItemIndex != DataItemCount-1)? \"OR\": \"\"#}")|(identity)GlobalAdministrator%}

2 votesVote for this answer Unmark Correct answer

Recent Answers

Sam Loveland answered on November 5, 2019 17:04

Thank you Peter! Super helpful.

0 votesVote for this answer Mark as a Correct answer