Getting data from custom table related to Page Type

Axel Larsen asked on July 15, 2015 17:55

I'm new to Kentico and I'm working with the Job Opening page type. I've changed the JobLocation field to be a multiple choice list which is grabbing the id and name from a one of the custom tables.

I'm trying to create two transformations for a repeater - one lists the job opening title, summary, and Location. The second transformation is used as the Selected Item Transformation of the Repeater webpart so that when a person clicks on a job opening in the list, they're linked to that document's URL and see only the full details of that single document (job opening). Kentico comes with a template that does this but it just uses regular text fields in the Job Opening page type...

The problem is, since I've changed the JobLocation field to refer to an ID, when I use the Eval("JobLocation") statement it's showing the ID but I actually want to show the location's name.

I've tried using a Query data source that uses a query that selects the Job Opening details along with the location name (based on the Id saved in the JobLocation field) but then the Selected Item Transformation doesn't work; instead, the job opening document shows the entire list of job openings instead of the details of that single job opening document.

Is there a way to use a repeater based on a path in the tree but still grab that table data so that I can display the location name? If I was able to do that I think Kentico would know when to use the regular transformation versus the selected item transformation.

Something like:

<%# Eval("JobName") %>
<%# Eval("JobSummary") %>
<%# GetValueFromCustomTable ("customtable_Location", "JobLocation", Eval("JobLocation")) %>

//---------------------------------------------------------------------------// // GetValueFromCustomTable(string tableName, string columnName, string rowID)

Correct Answer

Virgil Carroll answered on July 15, 2015 20:02

You best bet would be to write a custom method to query the custom table and return the Location name you want. You can read more about how to do this here:

Plus if you are using a custom table, you can actually click on its CODE tab and download a full qualified class (this is of course assuming you are using Kentico 8+). You can then use the CustomTableItemProvider.GetItems("YourTable.CodeName").WhereEquals("YourID", [the location id from the transformation]). You can then access the correct field.

You can also do this as inline code in a script block in the transformation itself, but its better practice to do it as an outside method.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Axel Larsen answered on July 16, 2015 19:43 (last edited on July 16, 2015 23:09)

That is exactly what I was looking at and I'm glad to have some further explanation on the approach. I was even wondering where to place that functionality and whether I could pull down specific rows with a where condition so I will try out your suggestion.

I have a follow up question:

Is there a simple way to bring in the table data and cache it so that when there are numerous job openings, they aren't all trying to make so many calls to the database and instead reference the cache (if it exists in the cache)?

0 votesVote for this answer Mark as a Correct answer

Ricardo Escovar answered on January 27, 2017 10:01

Hello! I know that this is an old post. But as I needed something very similar, I found another solution without modifying any code file...

For this example:

  • AutorId is on the Page Type selected on the repeater.
  • Custom table AutorBio includes ItemId (which refers to AutorId) and his name.

Just include this line on the transformation: <%# CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.AutorBio").WhereEquals("ItemID",EvalInteger("AutorId")).FirstObject.GetValue("Name") #>

I hope this could be useful for others as it was for me.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.