How to choose the right datatype when creating external content types?

Question

assuming I want to create a SPList using external content types, in my case SQL server tables. When I create my ECT in Sharepoint Designer 2010 SQL data types are mapped to the List as follows:

• varchar(255) -> Text
• varchar(1530) -> Note
• bit -> boolean
• integer -> int

I would create my SQL table according to this information. But which SQL datatypes do I choose if my SPList should contain data types as:

• Choice
• Lookup
• User

Would it be enough to declare those as strings (varchar) in my SQL table? Also would it still be possible to set the ListItems to the values above, when creating ECT's.

Answer 1

In short you could declare them as varchar as you asked. However, BCS will create your mapped fields (for your external list through the External Content Type) based on the column types (e.g. if your database column for "Name" is varchar(50) then your ECT field "Name" will be a single line of text). Also, you won't get any built-in validation support if you default to a varchar type (e.g. you could make a column that holds a number varchar but the BCS model would not cause a validation error if you entered in "ABC").

Since the ECT field types are defined by the database columns, I'm not sure I understand your question about choice, lookup, user (since you can't add these fields to the ECT through SharePoint - they are defined by the database external system definition).

If, on the other hand, you are asking what SQL datatype you should choose to hold data similar to choice, lookup, and user, I would suggest varchar (of some maximum fixed length) for choice, int for lookup (since presumably this is lookup to a foreign key in another database table) and varchar (of some maximum fixed length) for user.

Answer 2

You need to fist decide what data you want to store in your database, then you can work on making the forms supply the required info. In the example here I'm storing a users login name in the database as a text field , using a peoplepicker to get the info and I'm storing field called Entity as a text field, using a Select box to get the value. I could also use the same technique to Store the Primary Key of the 'Entity' by changing the 'Value' in the 'Options Select'. I've used this technique for AutoComplete text boxes, jquery DatePickers and other widgets.

In a Common Library I have some common functions shared by many pages on my site:

GetDisplayFieldValueByTitle: function (title) { // gets the value of a Display field on a Display form
    var dispField = Common.GetDisplayFieldByTitle(title);
    var value = dispField[0].innerText;
    return value;
},GetDisplayFieldByTitle: function (title) { // gets  a Display field on a Display form
    var stringToFind = '<!-- FieldName="' + title + '"';
    var formFields = $('td.ms-formbody');
    var foundItem;
    $(formFields).each(function (item) {
        if (formFields[item].innerHTML.indexOf(stringToFind) > -1) {
            foundItem = $(formFields[item]);
            return false; // break out of jquery each loop
        }
    });
    if (!foundItem)
        alert("An td with a class of ms-formbody containg the text " + stringToFind + " was not found");
    return foundItem;
}, GetInputElementByTitle: function (title) { // Gets an input element given its Title (beware of issues with Titles changing for required fields
    $element = $("input[Title='" + title + "']");
    // show errors if not found
    if ($element.length === 0) {
        alert("An inpt element with a title of " + title + " was not found");
    }
    return $element;
},ReplaceInputControl: function (title, html) { // replaces an input element with some arbitrary html, preserving it's name, id, aand title 
    var control = this.GetInputElementByTitle(title);
    var id = control[0].id;
    var title = control[0].title;
    var name = control[0].name;

    var newhtml = html.replace(/%id/gi, id);
    newhtml = newhtml.replace(/%name/gi, name);
    newhtml = newhtml.replace(/%title/gi, title);
    control.replaceWith(newhtml);
},FillEntitiesSelect: function (selector, selectedValue) { // this is domain specific code that will fill my select box from a custom json call
    var $SelectControl = $(selector);
    if ($SelectControl.length !== 1) {
        alert("Thes selector passed  to FillEntitiesSelect in Common (" + selector + ") didn not return exactly one element");
    }
    if ($SelectControl.get(0).tagName !== "SELECT") {
        alert("Thes selector passed  to FillEntitiesSelect in Common (" + selector + ") didn not return a SELECT element");
    }
    $.getJSON(Common.serviceUrl.entities + "?format=json", function (data) {
        if (data.requeststatus.code === 1) {
            var statusId = SP.UI.Status.addStatus("Error retrieving entities from " + Common.serviceUrl.entities + " Error is  : " + data.requeststatus.message);
            SP.UI.Status.setStatusPriColor(statusId, 'red');
            return;
        }
        $(selector + " option").remove(); // Remove all <option> child tags.
        $SelectControl.append("<option value=''>(Select...)</option>");
        $.each(data.entities, function (index, item) {
            if (item.id === selectedValue) {
                $SelectControl.append("<option value='" + item.id + "' selected>" + item.id + "</option>");
            }
            else {
                $SelectControl.append("<option value='" + item.id + "'>" + item.id + "</option>");
            }
        });
        // fix bug in ie9 . seehttp://stackoverflow.com/questions/5908494/select-only-shows-first-char-of-selected-option
        Common.FixIe9SelectBug(selector);
    });
}, FixIe9SelectBug: function (selector) {
    // call this method after filling select box, otherwise ie9 shows only first character
    // fix bug in ie9 . seehttp://stackoverflow.com/questions/5908494/select-only-shows-first-char-of-selected-option
    $(selector).width($(selector).width());
},

With that in a shared common library, to use a peoplepicker for entering a data into a bcs text columm, add a peoplepicker somewhere on your new/edit forms and add this code in the Document ready function:

 var peopleeditor = $("span[id$='PeopleEditorRequestor']");   // get the peoplepicker you manually added to the NewForm
        var requestortd = Common.GetDisplayFieldByTitle("Requestor"); // Find textbox control representing the BCS 'People' Field
        peopleeditor.appendTo(requestortd); // Add the Peoplepicker next to the texbox 
        // hide the origional span for requestor
        $("input[title='Requestor']").parent().hide();

and then in the PreSaveAction() add this to take the value out of the peoplepicker and put it back in the textbox that will be posted to the server

var alternalteRequestor = $("div[id$='PeopleEditorRequestor_upLevelDiv']>span").attr('title'); // this gets the person selected in the poplepicker
if (alternalteRequestor) { // if there waas someone selected
    $("input[title='Requestor']").val(alternalteRequestor);// set the value of input field that will be posed back to the server
}

To use a Select (which is kind of like a lookup field) add this in the Document ready function (this example is replacing a field called Entity):

var entity = Common.GetInputElementByTitle('Entity').val(); 
Common.ReplaceInputControl('Entity', '<select style="width:100px" id="%id" name="%name" title="%title" ></select>'); 
Common.FillEntitiesSelect("select[title=Entity]", entity);  // fill the select and set the initial value as selected

You would need to create your own Common.FillXXXSelect to fill the select box with whatever values you want. Whatever gets put in the Options value will be posed back to the server as the datavalue for the column (i.e it could be a foreign key, or just the descriptor)