Forums (Obsolete)

Member

Greg - 7/24/2013 5:50:35 AM

Display records from custom tables by page

Hello,

I would like to create a case studies list using custom tables.
Each case study is assigned to a single page using documentID (1:1 relation).

Could you please advice what should I put into WHERE clause in Data Source particle to achieve some sort of solution as below (to avoid creating 50 templates and to hardcode documentID):

WHERE documentID = <<curentDocumentId>>

Thanks
Greg

Kentico Support

kentico_zdenekc - 7/24/2013 6:26:09 AM

RE:Display records from custom tables by page

Hello,

If you're using a Web part in Portal engine (Design mode in CMSDesk), you can use context macro expression (follow the link for more details about macros) in the Where condition expression. There are some helping tools for entering macros.

The Where condition can be like:

DocumentID = {%CurrentDocument.DocumentID%}

Hope this will help.

Regards,
Zdenek

Kentico Legend

Brenden Kehren - 7/24/2013 7:14:50 AM

RE:Display records from custom tables by page

If you were using document types, this would be a piece of cake but since you are using custom tables it proves to be a bit more challenging as you need to get creative in how you create your links an get your "selected" values as a custom table item doesn't have a DocumentID field (unless you create one) and you can't use the built-in GetDocumentUrl() method because it's not a document type and isn't anywhere in a tree node. So try this and see how it works.

1). On your main case study page where you have a list of case studies add a URL alias to the page properties called {CaseStudy}. If you're URL is <root>/Case-Studies.aspx then you're alias would look like this /Case-Studies/{CaseStudy}.

2). Then in the item (and alternating item) transformation for the list, create the link to view the details.

<a href="/Case-Studies/<%# Convert.ToString(Eval("CaseStudyID")) %>.aspx"><%# Eval("CaseStudyName") %></a>

This should create something like /Case-Studies/293.aspx when rendered. Your field names may vary from what I have entered.

3). Then place another custom table repeater on the same page and set 2 important properties:

Visibility: set to this macro:

{% if (QueryString.GetValue("CaseStudy") != null) {true}else{false} @%}

WhereClause: set to this macro:

CaseStudyID = {%CaseStudy%}

On your initial custom table repeater that displays the list you will need to modify the repeaters visibility to return the opposite of the "selected case study" repeaters visibility so it doesn't so the list while you are looking at a selected case study.

{% if (QueryString.GetValue("CaseStudy") != null) {false}else{true} @%}

Good luck!

Member

Greg - 7/25/2013 8:11:38 AM

RE:Display records from custom tables by page

Thank you both for answer.
It will be relation 1:1 between case study and page and they will be listed on the homepage as a list (no where clause there) so the first response is good for me, but FroggEye, you helped me to solve another, similar problem where selected item was a problem in the same project!

Thank you both very much!

Kentico Legend

Brenden Kehren - 7/25/2013 2:15:16 PM

RE:Display records from custom tables by page

The solution would allow you to create a dynamic list of case studies AND display the details. I couldn't imagine if you end up having hundreds or thousands of case studies that you would want to enter a new document in the content tree for each one. This would also require you to have duplicate entry. If your custom table has a case study name, ID and all the other info you need, you can simply use a custom table repeater/grid and display the list on a single document, then create the links like I showed you above. I'm guessing you're importing or manually creating the case studies is that correct?

I've done this many times where dynamiclly changing data is in another system and imported into Kentico's custom tables and displayed dynamically on the page without having to create new documents each time. If you must insist on creating a new document for every case study AND you are importing them from another system, you might just forget all about creating the custom table record and use a "Case Study" document type.