SharePoint Extended lookup field. Part 1: Using EntityEditorWithPicker.

I’m sure that many users were faced with the necessity to enter in lookup field items from two or more lists. And I’ve got an idea to make this opportunity for SharePoint users. To my surprise I found out that it can be assembled of already existing solutions.

Custom PickerDialog and SimpleQueryControl

The developing started from reading the article Customizing EntityEditorWithPicker. If we take the example given in the article as a basis it will be easy to make our own user EntityEditorWithPicker. It results in extension of three classes: EntityEditorWithPicker, PickerDialog and SimpleQueryControl. The extension of the first class is simple. However the situation with the second and the third classes is much more interesting. As it was said before the user should be given an opportunity to select items from several lists. Looking ahead, I must specify that this control will firstly provide the opportunity to select one or another list (i.e. it will be a set of controls).

Then a click on “Browse” button of EntityEditorWithPicker control opens a PickerDialog window in which we can search items in one or another field of the selected list and choose the necessary one.

By default by clicking on “Browse” button of EntityEditorWithPicker control calls JavaScript which opens a dialogue window displaying a Picker.aspx page with specified parameters: MultiSelect, CustomProperty, EntitySeparator, DialogTitle, DialogImage, PickerDialogType (which are properties the of EntityEditorWithPicker class). The most remarkable of them is certainly CustomProperty. Via it we can transfer any string and then process its value by need in extended PickerDialog class as well as in extended SimpleQueryControl class. Both these controls are on the Picker.aspx page. In this case we should timely change CustomProperty value the of EntityEditorWithPicker control and use it in PickerDialog and SimpleQueryControl functionals. In this example in the quality of this parameter we use ID of the selected list on a SharePoint Web site and the URL for the Web site and ID of the view of the data that is contained in the selected list for filling list of fields in order to make search for list items by their values (a dropdown list with mColumnList ID in SimpleQueryControl) and forming fields in table part of PickerDialog (columnDisplayNames and columnNames properties). This parameter can be given as a string: [URL of web]; [ID of list]; [ID of view]; [Internal name of list field for displaying the selected value in the EntityEditorWithPicker text field].

So the class code inherited from PickerDialog will look like:

public class CustomLookupListPickerDialog : PickerDialog
{
   public CustomLookupListPickerDialog()
        : base(new CustomLookupListQueryControl(), new TableResultControl(),
               new CustomLookupListEntityEditor())
   { }
    protected override void OnLoad(EventArgs e)
    {
        string sData = this.Page.Request.QueryString["CustomProperty"];
        List<string> data = sData.Split(';').ToList<string>();
        ResultControl.Visible = false;
        // clear arrays of fields data
        ArrayList columnDisplayNames = ((TableResultControl)base.ResultControl).ColumnDisplayNames;
        columnDisplayNames.Clear();
        ArrayList columnNames = ((TableResultControl)base.ResultControl).ColumnNames;
        columnNames.Clear();
        ArrayList columnWidths = ((TableResultControl)base.ResultControl).ColumnWidths;
        columnWidths.Clear();
        // get SPList from the current SPWeb
        SPList listCurrent = MetaData.List_GetThis(SPContext.Current.Site.Url, data[0], data[1]);
        // get SPView
        SPView view = listCurrent.Views[new Guid(data[2])];
        // set the same width for all fields
        int width = (int)100 / view.ViewFields.Count;
        // fill in arrays of fields data
        foreach (string field in view.ViewFields)
        {
            columnDisplayNames.Add(listCurrent.Fields.GetFieldByInternalName(field).Title);
            columnNames.Add(field);
            columnWidths.Add(width.ToString() + "%");
        }
        base.OnLoad(e);
    }
}

And the class code inherited from SimpleQueryControl is given below:

public class CustomLookupListQueryControl : SimpleQueryControl
{
    SPList listCurrent;
    SPView view;
    string fldView;
    List<string> data; 
    public CustomLookupListQueryControl() { }
 
    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);
        EnsureChildControls();
        string sData = this.Page.Request.QueryString["CustomProperty"];
        // parse the string of CustomProperty parameter of Picker.aspx page
        data = sData.Split(';').ToList<string>();
        // get SPList from the current SPWeb
        listCurrent = MetaData.List_GetThis(SPContext.Current.Site.Url, data[0], data[1]);
        // "activate" the dropdown list of list's fields – otherwise the search will be made only in the first field of the list
        mColumnList.AutoPostBack = true;
        mColumnList.SelectedIndexChanged += new EventHandler(mColumnList_SelectedIndexChanged);
        // get SPView
        view = listCurrent.Views[new Guid(data[2])];
        fldView = data[3];
        // fill in the dropdown list of fields that are used in the view
        foreach (string field in view.ViewFields)
            mColumnList.Items.Add(new ListItem(listCurrent.Fields.GetFieldByInternalName(field).Title, field));
    }
 
    void mColumnList_SelectedIndexChanged(object sender, EventArgs e)
    {
        ViewState["mColumnList"] = mColumnList.SelectedValue;
    }
 
    protected override int IssueQuery(string search, string groupName, int pageIndex,
        int pageSize)
    {
        SPListItemCollection coll;
        // if the string is empty – than get all items from the list
        if (String.IsNullOrEmpty(search))
            coll = listCurrent.Items;
        else
        {
            // form a query and search strings to list data on the selected field
            SPQuery query = new SPQuery(view);
            query.Query = "<Where><Contains><FieldRef Name='" +
            mColumnList.SelectedValue + "' /><Value Type='Text'>" + search +
            "</Value></Contains></Where>";
            coll = listCurrent.GetItems(query);
        }
        // form a table and fill it in with query results
        DataTable dummyTable = new DataTable();
        foreach (string field in view.ViewFields)
            if (!dummyTable.Columns.Contains(field))
                dummyTable.Columns.Add(field);
        foreach (SPListItem li in coll)
        {
            DataRow row = dummyTable.NewRow();
            foreach (string field in view.ViewFields)
                row[field] = ((li[listCurrent.Fields.GetFieldByInternalName(field).Id] != null) ? li[listCurrent.Fields.GetFieldByInternalName(field).Id].ToString() : "");
            dummyTable.Rows.Add(row);
        }
        PickerDialog.Results = dummyTable;
        PickerDialog.ResultControl.PageSize = dummyTable.Rows.Count;
        return dummyTable.Rows.Count;
    }
 
    public override PickerEntity GetEntity(DataRow dr)
    {
        PickerEntity entity = new PickerEntity();
        entity.DisplayText = dr[fldView].ToString();
        entity.Key = dr["ID"].ToString();
        entity.Description = dr[fldView].ToString();
        entity.IsResolved = true;
        entity.EntityData.Add(dr["ID"].ToString(), dr[fldView].ToString());
        return entity;
    }
}

Here we should pay attention to GetEntity method. As a Key property of PickerEntity object we must specify a unique id of the selected list item which is a property of the list item ID. That’s why in this case the view must contain "ID" column or we can just put a data table into this field and fill it in properly.

EntityEditorWithPicker OnValueChanged event

Speaking about functional extension of the received control element we can take the challenge to process the change of the selected value in the control element of EntityEditorWithPicker. For example, to display more detailed data about the selected item in the developed control.

Here JavaScript will be helpful.

function FillMetaDataPanel(panelname, hiddenfield, ctx) {
    // panelname - data panel id of the selected list item
    // hiddenfield - string, which contains data of the view fields that are used in
    // the view specified in field settings with their internal names
    // (to get field values) and headers (to display fields)
    // ctx - ID of EntityEditorWithPicker control
    var resStr = "";
    var div = document.getElementById(panelname);
    var hidden = document.getElementById(hiddenfield);
    var sVals = hidden.value.split(';');
    if (sVals.length > 0) {
        var sTitles = sVals[3].split('^');
        var sFields = sVals[2].split('^');
        for (i = 0; i < sTitles.length; i++) {
            var listitem = GetListItem(sVals[0], sVals[1],
                document.getElementById(getSubControlID(ctx, "HiddenEntityKey")).value);
            resStr += "<tr><td Class=\"ms-vb2\">" + sTitles[i] + "</td>" +
                "<td Class=\"ms-vb2\">" +
                listitem.getElementsByTagName("z:row")[0].getAttribute("ows_" + sFields[i]) + "</td></tr>";
        }
    }
    if (resStr.length > 0) {
        div.innerHTML = "<table class=\"ms-listviewtable\">" + resStr + "</table>";
    }
    return resStr;
}

Link to this script is given in OnValueChangedClientScript property of EntityEditorWithPicker object class. GetListItem function returns data of the selected list item via calling GetListItems method for getting data of the selected list item of SharePoint Lists.asmx web-service (the example of calling SharePoint web-service in JavaScript was given here). What is HiddenEntityKey we can know from the article - Inside the SharePoint People Picker, which describes the anatomy of EntityEditorWithPicker control.

List items from lists of any webs

Finally I’d like to notice that this field type can be extended to substitution of list items from lists of any webs or work out an initial selection on list definition as given below.