Display Page Type in Repeater Webpart based on Form Control Selection

Kevin Kim-Murphy asked on March 6, 2015 22:35

Hi. I am (using Kentico 8.2 and am) trying to display data from a specific page type (i.e. Teachers) on a specific page (i.e. Course) where the user has selected the Teacher(s) from the Multiple Choice form control (on the Form Tab) of the Course page.

The Multiple Choice form control is setup to retrieve Teachers from the custom_teachers table with a basic SQL query:

select TeacherID, TeacherName from custom_teachers;

And that works as expected. The names of all the teachers show up in the Form Tab for the Courses page - and users can select multiple teachers to display on the Course page.

I have then setup a PagesDataSource on the Course page - which allows for the data entered in the Form Tab to be retrieved and displayed via web parts (and I have confirmed this is working).

Here is where I am having trouble - I basically want a Repeater Webpart that leverages the TeacherID(s) selected for the Course page (per the selected data by the user in the Form tab) to display data from the selected Teacher Pages. I cannot figure out how to do this.

I have tried a "where condition" - something like WHERE custom_course.Teacher = custom_teacher.TeacherID - but that doesn't work since those two data sources have not been joined via an inner join.

I can have also created a Repeater webpart with transformation to show ALL of the teachers - but I cannot filter based on the selected Teachers in the Course page From tab.

I am sure that there is a simple solution - I just haven't found a similar post or example to assist with this. Hopeful that someone can point me in the right direction - or tell me where I got off track in my attempts so far.

Thanks in advance for you assistance.

Correct Answer

Kevin Kim-Murphy answered on March 17, 2015 02:07

Ok. Wanted to provide an update on the solution here. Here is what I needed to do:

1) Use the Repeater with Custom Query web part
2) Configure the query as follows:

DECLARE @List VARCHAR(100);
SELECT @List = Teacher FROM custom_course
WHERE ##Where##

SELECT * FROM custom_teacher
WHERE '|' + RTRIM(LTRIM(@List)) + '|' LIKE '%|' + CAST(TeacherID AS VARCHAR) + '|%'

3) Configure the WHERE condition field in the Repeater web part as follows:

custom_course.CourseID = '{%CurrentDocument.GetValue("CourseID")|(identity)GlobalAdministrator%}'

AND Presto! Only the Teacher values selected in the Multi-Select Form Control in the page's Form Tab show up as the Teachers associated with this Course.

I have utilized the Multiple Choice Dual List (With Sort) Form Control (from the Marketplace: http://devnet.kentico.com/marketplace/inline-controls/dual-list-multi-select-form-%28with-sort%29) so I am still sorting out exactly how to Order By the order of the Pipe Delimited values in the Teacher field of the Course table - but that is a secondary issue to this problem and final solution.

Thanks Virgil for your insight on this - it got me moving in the right direction. I hope that this final answer will provide others with support they need.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Virgil Carroll answered on March 7, 2015 23:49

First question, what kind of data are you trying to display from the page? Are they page type field values or information from Page Editor webparts?

Second, can you provide the full query you are doing instead of the where clause? This might help us troubleshoot your issue.

What you are saying should be doable, but need some more information to continue to help you out.

0 votesVote for this answer Mark as a Correct answer

Kevin Kim-Murphy answered on March 8, 2015 00:06 (last edited on March 8, 2015 00:07)

Virgil,

Thanks for the reply - attempting to answer your questions below:

a) The Data I am attempting to display (from the Teacher's page) are Page Type field values - i.e. TeacherName, TeacherTitle, TeacherPhoto. Basic fields that I have created in the Teacher Page Type and populated with data for each teacher.

One Note: I have confirmed that the Teacher data is working properly by placing a repeater web part on the Courses page and setting it to Page Type: custom.teachers and then setting the path to the location of all of the teacher page types (/Teachers/%) with a basic transformation to display the given data (i.e. Eval("TeacherName"))- and the result is that I see ALL Teachers not just the ones selected in the Course form tab.

b) I am not actually doing a full SQL query - the WHERE condition is in reference to the "WHERE condition" field of the Content Filter section in the Repeater Webpart. I hope that clarifies that.

Again - partly because I feel my question was a bit long winded - I am simply looking for a way to filter the displayed content of the repeater webpart based on the selections made by the content admin in the Form Tab of the given page. In this case it is a Teacher/Course relationship where we have several teachers and several courses and we want to allow selection and deselection of Teacher(s) to be displayed on a given Course page.

Hope that makes sense.

Thanks again for your assistance.

0 votesVote for this answer Mark as a Correct answer

Virgil Carroll answered on March 8, 2015 04:33

Basically I see two challenges you are going to have with this and with those your better option is probably to use a QueryDataSource for this.

First, if I am understanding you correctly, you could have a many-to-many relationship with this call (i.e. multiple teachers selected to show on a course page RELATED to some teachers pages). The PagesDataSource and really the process are not meant to handle this fundamentally. If it was just one teacher to one course, you could use the PagesDatasource and set the WHERE clause to 'custom_course.Teacher IN (SQL Statement Querying the custom_teacher page type table)', but since it is many I do not think this will work, but you can try and prove me wrong :) (just do not have time to try it)

Instead I would create a custom query that you pass the custom_course ID field to in the WHERE clause. This query would select all the teacher IDs from the custom_course table and then pull up the fields you need from the custom_teacher table. Its going to be a complex query, but be your best for automating the process going forward.

Your other option and just a thought, is to tie the course to the teacher instead of your way (not ideal I realize) but would be a much easier query on the course pages.

Anyway probably not the answer you were looking for, but maybe try the IN command in the WHERE clause first to see if that works and please let us know what you do (whether I helped or not) so we can all learn from it!

0 votesVote for this answer Mark as a Correct answer

Kevin Kim-Murphy answered on March 12, 2015 19:00

Virgil,

I am making a little bit of progress here.

Using the Pages Date Source and the Repeater web part I was able to get a single Teacher to show up if selected (in the Form tab) using this WHERE condition:

(custom_teacher.TeacherID IN ( SELECT Teacher FROM custom_course WHERE custom_courseID = {%custom_courseID%} ))

The problem of course is the fact that the multi-select form control stores values in the Teacher column delimited by pipes. i.e. 1|2|3

So when I select more than one item via the multi-select form control I get the following error:

Caused exception: Conversion failed when converting the nvarchar value '1|2|3' to data type int.

I was hoping that I could find an easy way to parse those pipe delimited values in the WHERE condition - but I haven't found that yet.

I would prefer not to go a complicated QueryDataSource route - if I can just parse those pipes. Let me know if you have any thoughts.

I will let you know if I make any further progress.

Thanks!

0 votesVote for this answer Mark as a Correct answer