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: https://docs.kentico.com/display/K8/Adding+custom+methods+to+transformations

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

Eric Lund answered on January 16, 2018 14:54

Ricardo, I am trying to do something similar to this by using a Page Type repeater that needs to connect to a Custom Table to pull out fields from it where there is a match on the SalesRep ID in both tables...

I tried to implement your example above but I cannot get it to work or make the connection.

Could you provide more detail on how this is done? I need to pull multiple field values from the Custom Table into my Page Type repeater.

The Repeater displays details of a Sales Proposal.... and the Custom Table stores data on the Sales Rep themselves so I would like to be able to pull from it their Title, Email, Phone, Bio, Twitter account etc...

My Transformation is Text / XML

0 votesVote for this answer Mark as a Correct answer

Eric Lund answered on January 16, 2018 15:03 (last edited on December 10, 2019 02:31)

I am trying to do something like this:

{% salesEmail = CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.CMS_SalesReps").WhereEquals("SalesID",EvalInteger("RepName")).FirstObject.GetValue("SalesEmail"); "" |(identity)GlobalAdministrator%}

0 votesVote for this answer Mark as a Correct answer

Ricardo Escovar answered on January 16, 2018 16:21

Hello Eric... You should do something like this on your transformation:

<p><b>Sales title:</b> <%# CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.CMS_SalesReps").WhereEquals("SalesID",EvalInteger("RepName")).FirstObject.GetValue("SalesTitle")#> </p>

<p><b>Sales email:</b> <%# CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.CMS_SalesReps").WhereEquals("SalesID",EvalInteger("RepName")).FirstObject.GetValue("SalesEmail")#></p>

0 votesVote for this answer Mark as a Correct answer

Ricardo Escovar answered on January 16, 2018 16:26

In any case if you have multiples values I don't know if this is the best approach...

0 votesVote for this answer Mark as a Correct answer

Eric Lund answered on January 16, 2018 19:42 (last edited on January 16, 2018 19:43)

Thanks Ricardo! That works for me..... I did have to make some minor modifications to it to get it to work because I had named some things incorrectly... So my working version is this:

Sales title: <%# CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.SalesReps") .WhereEquals("SalesID",Eval("RepName")).FirstObject.GetValue("SalesTitle")%>

Sales email: <%# CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.SalesReps") .WhereEquals("SalesID",Eval("RepName")).FirstObject.GetValue("SalesEmail")%>

I had to do the transformation as ASPX... Is this possible to do in TEXT/XML ??? I tried a simple conversion over and it displays no results.... I may have to adjust how the Page Type field is identified.... Odd that TEXT/XML does not seem to give any results.

0 votesVote for this answer Mark as a Correct answer

Eric Lund answered on January 17, 2018 16:24 (last edited on December 10, 2019 02:31)

Is there any way to make this work in a TEXT / XML Transformation???

Sales photo: {% CMS.CustomTables.CustomTableItemProvider.GetItems("customtable.SalesReps") .WhereEquals("SalesID",RepName)).FirstObject.GetValue("SalesPhoto")|(identity)GlobalAdministrator%} Would display its value on the page so in the structure of that line of code wouldn't it just be listed like I have it? Do I need to wrap it in any special characters to make this function work in a TEXT / XML Transformation???

0 votesVote for this answer Mark as a Correct answer