Forums (Obsolete)

Member

sydney3511-gmail - 4/10/2012 9:27:17 AM

Display custom table data in CMSDesk from query with join lookup value

Hi all,

I am using custom table to store data. There is 1 field (CountryID) that I make as an integer to save ID from another table (countryLookup).

In the fields tab (Site Manager -> Development -> Custom Table --> (edit) --> Fields, I set it to be "SQL Query (it must return value and text fields)" and it shows the drop-down list and save Country Id correctly.

But when I go to CMSDesk or even I go the "data" tab to list all the data. The country ID shows but not the Country Name.

I know I should do join table somewhere ... but could you let me know how to put the query in.

Thank you guys.


Kentico Support	kentico_janh - 4/11/2012 8:06:53 AM RE:Display custom table data in CMSDesk from query with join lookup value Hello, Are you selecting two columns from that custom table? (one for the ID and one for the text). Here is an example: SELECT first_column, second_column FROM custom_table Best regards, Jan Hermann


Member	sydney3511-gmail - 4/16/2012 4:05:11 AM RE:Display custom table data in CMSDesk from query with join lookup value Hi Jan, Read this line please: But when I go to CMSDesk or even I go the "data" tab to list all the data. The country ID shows but not the Country Name.


Member	sydney3511-gmail - 4/16/2012 4:07:36 AM RE:Display custom table data in CMSDesk from query with join lookup value Hi Jan, I know how to do T-Query or SQL Query ... But I just want to know when you shows data of the customer table in CMSDesk, how to customize the query to show the result which is the final result after join sql statement. Thank you in adv.

Kentico Support

kentico_janh - 4/16/2012 5:28:10 AM

RE:Display custom table data in CMSDesk from query with join lookup value

Hello,

I still don't know, what do you want to achieve. If you want to have Country Name in your custom table from another table, then the field can be defined through the same SQL query as you have already used, but then you need to fill its ID column also with the name of a country:

SELECT CountryName, CountryName FROM custom_table

If you want to have only an ID in your table, but you want to display a Country Name instead in frontend, then you can write your own function in the custom table transformation, which takes an ID of your country and then returns its name.

Best regards,
Jan Hermann

Member

sydney3511-gmail - 4/16/2012 6:08:44 AM

RE:Display custom table data in CMSDesk from query with join lookup value

Hi Jan,

I have 2 custom tables.

1. custom_countrylookup
2. custom_members

In custom_countrylookup, there are 2 main fields: (1) ItemID and (2) CountryName

In custom_members, there are these fields

-- 1. MemberID
-- 2. MemberName
-- 3. MemberAddress
-- 4. MemberCountryID

When I created the field MemberCountryID in custom_members table, I select data type as Integer and I select Input as DropDownList with the query string:

"SELECT ItemID, CountryName FROM custom_countrylookup"

so when you add a new record in CMSDesk (CMSDesk -> Tools -> Custom Tables -> custom_members -> New Item) you will see a dropdown list in the form to select the country name in the custom_countrylookup.

But when it shows the data, it shows the ID of the country that I select.

I want it to show "Country Name" instead.

How to do that?

Kentico Support

kentico_janh - 4/16/2012 6:38:34 AM

RE:Display custom table data in CMSDesk from query with join lookup value

Hello,

The easiest way is to add there directly the name of a country, so please change the MemberCountryID field from integer to text type and also change your SQL query to following one:

SELECT CountryName, CountryName FROM custom_countrylookup

The custom_members table should now contain the Country Name value.

Best regards,
Jan Hermann


Member	sydney3511-gmail - 4/16/2012 6:39:52 AM RE:Display custom table data in CMSDesk from query with join lookup value Jan, I thought about it too. What if my user changed the CountryName value in the custom_countrylookup?

Kentico Support

kentico_janh - 4/16/2012 8:24:49 AM

RE:Display custom table data in CMSDesk from query with join lookup value

Hello,

That would be bad :) But there is no other option, how to display CountryName in that location (Site Manager -> Development -> Custom tables -> Data), because there is only a simple grid, which displays, what is stored in database. If you want to display those data with some modification, you would need to implement your own module, which takes data from that table, select names from the custom_countrylookup table according the CountryId column and displays them instead.

Best regards,
Jan Hermann