Getting value from custom table in transformation

Nick Basham asked on June 29, 2018 19:26

I have a custom table (we'll call it JobListings for this example) containing a field ( Agency ) that references another custom table ( AgencyRoster ) using the custom table item selector form control. So the value saved to the Agency field in JobListings is the GUID for the AgencyRoster item.

The default transformation for Agency displays the GUID, however, I want to use the GUID to pull other information from that record, e.g., an AgencyName field.

I was able to retrieve the AgencyName for the first item with: {% Current.GlobalObjects.CustomTables["customtable.AgencyRoster"].Items[0].GetValue("AgencyName") %}

I tried to do a query to pull out the item based on GUID, but it's just passing Agency in as a string, not a variable with the GUID info: {% Current.GlobalObjects.CustomTables["customtable.AgencyRoster"].Items.Where("ItemGUID LIKE Agency").GetValue("AgencyName") %}

Is there a way to escape Agency so I can use it within the transformation? Or is there a completely different way to do this that I'm missing? Due to various restrictions, I'm really trying to do this completely within the Kentico interface and to avoid writing custom code.

I'm happy to clarify if some of this question doesn't make sense.

transformation custom table

Recent Answers

Zach Perry answered on June 29, 2018 19:37 (last edited on December 10, 2019 02:31)

Does something like this work?

{% Current.GlobalObjects.CustomTables["customtable.AgencyRoster"].Items.Where("ItemGUID LIKE '" + Agency +"'").GetValue("AgencyName") |(identity)GlobalAdministrator%}

Doing this is going to run a sql query for each item in your transformation, which could kill performance. Might look into doing a repeater with a custom query.

1 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on June 29, 2018 21:19 (last edited on December 10, 2019 02:31)

When you do where - you might get more than one. You can do {%GlobalObjects.CustomTables["customtable.SampleTable"].Items.Filter(ItemGUID == 'xxx').FirstItem.GetValue("AgencyRoster")|(identity)GlobalAdministrator%}

P.S. not sure why you use 'Agency' for ItemGUID. GUID should look like c4681869-6e17-4b5a-ae26-4f2e489fe94e. GUIDs are globally unique identifiers or universally unique identifiers. They are used in computer systems all over the globe. One consists of 32 numbers broken up in to a sequence of hexadecimal digits (0-9 and a-f). GUID can not be 'Agency'. You probably meant name. You can do like in where, but if know what you getting you like doesnt make much sense, it should be equal. Secondly for like you should use %, i.e. .Where("Name LIKE 'Jo%'")

2 votesVote for this answer Mark as a Correct answer

Nick Basham answered on July 3, 2018 23:26

@Zach Perry - thanks, I tried that but couldn't get it to return anything. I wonder if I just have the syntax wrong?

@Peter Mogilnitski - I was using 'Agency' because that field is referencing a specific entry in another custom table. Reading it back, my explanation was a little unclear. Just to clarify, I have two custom tables. Custom table 1 contains information about individual agents (name, address, etc.) and Custom table 2 contains individual job listings. Each job belongs to a single agency, so I'm using the custom table item selector on Custom table 2 to reference the appropriate agency record in custom table 1. So the field 'Agency' in Custom table 2 contains the GUID for the field in Custom table 1. In my question, 'Agency' is just the field name that should return me the GUID.

I appreciate both you guys helping. I'm fairly new to Kentico, .Net and the Microsoft software stack (much more familiar with a LAMP stack) so I'm sure there's pretty basic stuff I'm missing.

0 votesVote for this answer Mark as a Correct answer