Developer forum

Forum » CMS - Standard features » SQL based Dropdown field in Item

SQL based Dropdown field in Item

E-mail notifications
Adrian Ursu
Reply
Posted on 15/02/2016 18:18:39

Hi Guys,

Has anyone used the option to populate an ItemType dropdown field using an SQL statement?

The official answer from support is that this functionality does not support all kinds of SQL statements but just simple ones. In my test I tried a simple Select.. From but the Interface control is returned empty.

However, in the ItemType definition it seems to be executed correctly.

Thank you,
Adrian


Replies

 
Nicolai Høeg Pedersen
Posted on 15/02/2016 19:40:24
Reply

Can you please post the SQL?

 
Adrian Ursu
Posted on 15/02/2016 20:07:50
Reply

Hi Nicolai,

Sure.

Initially I have used: Select Distinct [Brand] From [EcomProducts[ Where [ProductDefaultShopID] ='SHOP2'

After the response from Service Desk, I have decided to Simplify it and reduced it to : Select [Brand] From [EcomProducts]

I got the same outcome

Here is a video with my reported problem: http://screencast.com/t/kpuHXYi0c4u

Thanks,

Adrian

 
Nicolai Høeg Pedersen
Posted on 15/02/2016 20:10:15
Reply

A select box etc. requires a key and a value, so you might need a SQL that contains both a key and a value. So it has to return 2 columns.

 
Adrian Ursu
Posted on 15/02/2016 20:29:33
Reply

I have selected "Brand" for both.

The interface allows me to do that.This should not be a limitation.

However, I can try and change the Select to: Select [Brand] as Key, [Brand] as Value from [EcomProducts] if you think this will solve it.

 

 
Adrian Ursu
Posted on 15/02/2016 20:40:34
Reply

Hi Nicolai,

I have tried it and it works.

It would have saved all of us a lot of trouble if Support Desk could indicate this from the very beginning.

Thanks a lot for your solution, and I would suggest adding some warnings in the interface or include this tip in the documentation.

Thanks,

Adrian

 
Nicolai Høeg Pedersen
Posted on 15/02/2016 22:56:34
Reply

Glad it helped, and sorry the Service desk did not know.

Will consider the warning.

Thanks, Nicolai

 

You must be logged in to post in the forum