Multiple Columns from Custom Table in Repeater

Fahlgren Mortine asked on July 28, 2016 20:23

Good afternoon. I have a list of products which are managed as a Page Type. I also have a Custom Table of retailers which sell the products. In the Editing Form for the products Page Type, I have a Custom Table Item Selector which allows an administrator to select a retailer from the Custom Table via the Name column value, returning the Logo column value. A text/XML Transformation then outputs the logo value to the ASPX page. This worked perfectly well until I was asked to include more information for the selected retailer. I added a Website column to the retailers Custom Table, but I see no straightforward way to output this in the current text/XML Transformation. The Custom Table Item Selector in the Page Type only allows a single column value to be returned.

Kentico v8, ASPX templates

Recent Answers


Joshua Adams answered on July 28, 2016 21:46

You can create a custom macro method in which the parameter that you pass in is the id stored by the other selector. Then use the custom method to return other properties. Usually I will also have another parameter which you can choose what field to return. Custom Macro Method Instructions

After that, just use the namespace and function name in your transformation, and as long as you register your class correctly, it should show up and give you intellisense.

//returns Object Name
//BPMacros is my custom namespace and then MyFunction is the name of my function declared in my class
{% BPMacros.MyFunction(FieldThatContainsIDofObj, "ObjectName") %}

//returns Object Description
{% BPMacros.MyFunction(FieldThatContainsIDofObj, "ObjectDescription") %}
0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on July 29, 2016 09:01 (last edited on July 29, 2016 09:03)

Hi Fahlgren,

I would suggest one approach which might appear counter intuitive. Is it possible for you to make fields that you you want to show for a particular retailer, part of the page type. The moment you select a particular retailer you populate all other fields in the page type using depending fields property and get these values from your custom table.

This will allow you to use just a repeater to show all the properties from a Custom table without writing any extra code.

Let me know if in case I understood your question in other way than you expected it to be understood.

Cheers, Chetan

0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on July 29, 2016 15:07

Chetan, this makes sense except that new retailers are likely to be added, along with their respective logos, and I need to make sure this is easily maintainable via the CMS for the marketing folks who maintain the site.

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on July 29, 2016 15:50

Yes, we do this very often. You need to provide Editor access of the custom table to your marketing folks and they can add new values as and when needed. A little training in this regard goes a long way.

Thanks, Chetan

0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on July 29, 2016 16:54

Ah, I misunderstood your initial suggestion, Chetan. If I add an additional Custom Table Selector that is attached to the retailer Website column, I can make it dependent upon the previous Custom Table Selector for the Name column, but how do I tie the two together so that when one is selected the other automatically selects the same row in the custom table?

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on July 29, 2016 18:28

I'm on the way home. Let me reach home and provide you queries.

We won't be needing custom table item selector. There is an option in every field of page type at the bottom has depending fields/ depends upon.

We will make use of it.

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on July 29, 2016 19:47

This seems like you would be storing more than you need to and also would make it so if you update the custom table item, then this approach won't show the updated items...unless there is something custom behind the scenes. You already have the data that you need in your custom table, so all you need is a link to it, or some id that you would select. Once you have the id, use the custom macro to return your results based on your id. That way your data is always up to date, and you aren't storing values from another table and doubling up your data.

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on July 29, 2016 20:32 (last edited on July 29, 2016 20:37)

Hi Joshua, I agree with somepart of what you are saying. However we have to look at one thing.

  • 1. In order to link a record between to different tables there will be some amount of extra information that will always be required and so is the case iwth what I ahve suggested.
  • 2. Yes, updating a custom table record later will update page information directly. However, I think this it is not a major limitation. This approach is very clean, flexible and dynamic. We can always use customtable Item Event handlers. It will not be more than 5-10 lines of code. The only reason I am advocating this because of its flexibility for Content Editors and Business. Article to do so http://devnet.kentico.com/questions/custom-table-event-handler
  • 3. It gives a lot of visual comfort and confidence to editors while populating data. WYSIWYG.
    1. I have tried different approaches to play around this problem in the past. No solution except one using GUID is relaible when you will be moving from one server to another.
  • I will be happy to see your solution as it will solve some of my purpose too.

    Cheers, Chetan

0 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on July 29, 2016 20:33

Hi Fahlgren, There is this article on how to use dependent fields in Kentico with images. This article is from kentico team.

Cheers, Chetan

0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on July 29, 2016 21:45

Chetan, the example link you sent is to use dependent fields to display other fields if the primary field has been completed. I use this functionality often, and I'm actually using it in this instance to display the Retailers select list if a certain checkbox has been checked earlier in the form. However, I do not see how this helps in this situation. There's no example of how you can use the value of a previously completed field to query a custom table, or otherwise bind the values via macros.

0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on July 29, 2016 21:47

Joshua, this makes sense, and I may go this route if I can't find a better way. It is definitely appealing to not duplicate data, but I'm trying to avoid writing a custom code-behind SQL statement for the page in question. Unless you know of another way to pull this data from the custom table based on a value from the page type.

0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on July 29, 2016 22:14

I have a thought. Can I just create a Transformation for the Custom Table and call this via a CMSRepeater control along with a Where condition?

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on August 1, 2016 17:31 (last edited on August 1, 2016 17:32)

Fahlgren, you could just use the custom table api and avoid a sql statement completely besides setting the where clause. You can use the CustomTable class and use a where condition which would be your primarykey = your stored value in your document or other custom table. After that, no other sql is needed.

https://docs.kentico.com/display/API9/Custom+table+data

Also, the nested repeater approach could work as well, you would just need some conditionals and error handling for items which may not exist, etc.

0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on August 2, 2016 14:38

I ended up using a cms:CustomTableDataSource and cms:QueryRepeater in the ASPX page and I sent it a WHERE clause based on the ID of the retailer(s) in question from the code-behind. May or may not be the most efficient solution, but it's pretty clean. Thanks for the help!

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on August 2, 2016 15:42

Nice, glad you found a solution.

0 votesVote for this answer Mark as a Correct answer

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