SQL table to external content type (or types) - Suggestions on how I might do this

Question

I have an SQL table that has 11 columns. I need to use what is in each column as a separate, independent drop down in a list item. The columns and rows aren't related, there just grouped into one table as an extract from an oracle database.

So for instance, a list item would have 11 drop downs in which each drop down would have a selected value but they aren't related.

Do I need to make 11 different external lists that are columns in a list item? I need to perform this using either SharePoint designer and/or Javascript.

Thanks for your consideration.

Answer 1

I think what you might be able to do is create External Data Columns. I've never done it before myself, so take all this with a grain of salt, but it seems like there is a way to create a list column and have it connected to a single field of an external content type.

What you would need to do would be

• have your external content type set up and available on the site
• go to the list (all tutorials I've found say to create the columns on the list itself, not as a site column, and indeed when I went to Site Columns to create a new one, the "External Data" was not an option, but if I went into a list and tried to create a new column there, it was)
• create 11 external data columns, each one showing only the field you want for that column

I'm not sure what the controls actually look like for those types of fields, so you may not get drop-downs.

There's a couple tutorials here and here, and of course you can find many more online.

If you absolutely must have drop-down controls, something I would try might be to create the external content type, create a single external list, and then create lookup fields that lookup to the individual columns on the external list. I have no idea if that would actually work, though.