SharePoint 2010 External Content Types & SPServices

Nov 19, 2010 at 3:27 PM

Since I do not know exactly how the web services work for SP, I am wondering if they spservices plugin can be used on external data fields.

I have not had any luck getting it to work. If this is possible, please let me know. I will post any progress I find.

Coordinator
Nov 19, 2010 at 3:39 PM

I have not done any testing with external Content Types, so I'm really not sure. What specifically are you trying to accomplish?

M.

Nov 19, 2010 at 3:43 PM

We want to add the ability to display related information from the external data type. However, the control is not a dropdown, rather a lookup/check field.

The column is not recognized by name when the script runs. I am not sure if this is due to the different form control, or to it being an external datatype.

I also have no idea whether this script would even work on anything other than a dropdown, as I have not yet tried that.

Thanks for your response!

Coordinator
Nov 19, 2010 at 3:47 PM
Edited Nov 19, 2010 at 3:47 PM

Read the docs. ;+)  SPDisplayRelatedInfo is written to work with dropdowns. You may be able to adapt it to your situation, but it'll take some work. If you do, I'd be interested in seeing what you come up with!

M.

Nov 19, 2010 at 3:53 PM

OK, thanks. But the error I get is "column not found on page" - does this happen if it is not a dropdown? or would this be due to it being an external type only?

If it is just the dropdown issue, I may work with it - the external type may prove to take too long for our current project.

By the way, the multiSelectColumn script works in SP 2010!

 

Coordinator
Nov 19, 2010 at 3:59 PM

Yeah, I just looked and perhaps the wording on that message could be better. I'll think about how to make it clearer.

Do you mean SPSetMultiSelectSizes?

M.

Nov 19, 2010 at 4:18 PM

yep, SPSetMultiSelectSizes works fine in sp 2010 (have only tested foundation so far).

But does the error message refer to it not being a dropdown, or that it can't reach the data?

I was hoping the external data type could be accessed through sharepoint's web services once it was pulled in, but am thinking it isn't and a web service would have to actually be set up on the external db - but that is not a possibility at the moment.

Coordinator
Nov 19, 2010 at 4:32 PM

The error message means that the column cannot be found as a dropdown on the form, whether it's a simple, complex, or multi-select "dropdown".

Like I said, I haven't done any testing with the Web Services and SharePoint 2010 external Content Types, so at this point you're on your own. I'm assuming that youre trying to use GetListItems or UpdateListItems?

M.

Nov 19, 2010 at 4:37 PM

Yes, also delete, but I am not the DBA, so this will wait for another day.

I am also having an issue getting the SPRequireUnique function to work - seems to work on the form when testing, but in practice, the popup does not havve the same behavior as the full page form. Odd.

Coordinator
Nov 19, 2010 at 4:46 PM

Deletes would be with UpdateListItems as well.

I'm not sure that I understand the issue you're having with SPRequireUnique.

M.

Nov 19, 2010 at 4:49 PM

SPRequireUnique does not work in the popup. It works as a regular page only. Is this something you have run across before?

Coordinator
Nov 19, 2010 at 4:51 PM

If you look at the docs for that function, you'll see right at the top that I have not tested it with SharePoint 2010.

M.

Nov 19, 2010 at 5:11 PM

yes, I see that, just wanted you to know my findings and see if it's an error on my end.

Nov 19, 2010 at 5:16 PM

also, autocomplete works, and works with external data types by pulling from that list.

Nov 19, 2010 at 6:31 PM

a quick fix to SPLookupAddNew to allow you te specify an after update page, or image. This also helps keep the user from "getting lost" in SharePoint 2010.

I used a simple html page uploaded to Site Pages, a sharepoint web part page, and an image path link. This only works with an address on the sharepoint site itself - normal permissions rules apply.

	
 
 
 
 // Function which provides a link on a Lookup column for the user to follow
 // which allows them to add a new value to the Lookup list.
 // Based on http://blog.mastykarz.nl/extending-lookup-fields-add-new-item-option/
 // by Waldek Mastykarz
 // altered for SharePoint 2010 by Emerson Bruce
 $.fn.SPServices.SPLookupAddNewCustom = function(options) {
  var opt = $.extend({}, {
   lookupColumn: "",    // The display name of the Lookup column
   promptText: "Add new {0}",  // Text to use as prompt + column name
   completefunc: null,    // Function to call on completion of rendering the change.
   closeMessageUrl: "",            // appears after update - page, image path, html document path, etc.
   debug: false     // If true, show error messages;if false, run silent
  }, options);
  // Find the lookup column's select (dropdown)
  var lookupSelect = new dropdownCtl(opt.lookupColumn);
  if(lookupSelect.Obj.html() == null && opt.debug) { errBox("SPServices.SPLookupAddNew", "lookupColumn: " + opt.lookupColumn, "Column not found on page");return;}
  var newUrl = "";
  var lookupListUrl = "";
  var lookupColumnStaticName = "";
  // Use GetList for the current list to determine the details for the Lookup column
  $().SPServices({
   operation: "GetList",
   async: false,
   listName: $().SPServices.SPListNameFromUrl(),
   completefunc: function (xData, Status) {
    $(xData.responseXML).find("Field").each(function() {
     if($(this).attr("DisplayName") == opt.lookupColumn) {
      lookupColumnStaticName = $(this).attr("StaticName");
      // Use GetList for the Lookup column's list to determine the list's URL
      $().SPServices({
       operation: "GetList",
       async: false,
       listName: $(this).attr("List"),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find("List").each(function() {
         lookupListUrl = $(this).attr("WebFullUrl");
         // Need to handle when list is in the root site
         lookupListUrl = lookupListUrl != SLASH ? lookupListUrl + SLASH : lookupListUrl;
        });
       }
      });
      // Get the NewItem form for the Lookup column's list
      $().SPServices({
       operation: "GetFormCollection",
       async: false,
       listName: $(this).attr("List"),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find("Form").each(function() {
         if($(this).attr("Type") == "NewForm") newUrl = $(this).attr("Url");
        });
       }
      });
      // Stop looking;we're done
      return false;
     }
    });
   }
  });
  if(lookupListUrl.length == 0 && opt.debug) {
   errBox("SPServices.SPLookupAddNew",
    "lookupColumn: " + opt.lookupColumn,
    "This column does not appear to be a lookup column");
   return;
  }
  if(newUrl.length > 0) {
   // Build the link to the Lookup column's list enclosed in a div with the id="SPLookupAddNew_" + lookupColumnStaticName
   newLink = "<div id='SPLookupAddNew_" + lookupColumnStaticName + "'><a href='" + lookupListUrl + newUrl + "?Source=" + escapeUrl(opt.closeMessageUrl) + "'>" + opt.promptText.replace(/\{0\}/g, opt.lookupColumn) + "</a></div>";
   // Append the link to the Lookup columns's formbody table cell
   $(lookupSelect.Obj).parents("td.ms-formbody").append(newLink);
  } else if(opt.debug) {
   errBox("SPServices.SPLookupAddNew",
    "lookupColumn: " + opt.lookupColumn,
    "NewForm cannot be found");
   return;
  }
  // If present, call completefunc when all else is done
  if(opt.completefunc != null) opt.completefunc();
 };
 
 
 
Nov 19, 2010 at 9:13 PM

any idea why I am getting a space instead of a %20 on the following?

var simplelink = thisSite + lookupListUrl + newUrl + "?Source=" + escapeUrl(opt.closeMessageUrl);

Coordinator
Nov 19, 2010 at 9:17 PM

Yeah, you're using my escapeUrl() function instead of JavaScript's escape(). I probably should reinventory those functions to see what's there that ought to be cleaned up.

M.

Coordinator
Nov 19, 2010 at 9:23 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Nov 19, 2010 at 9:42 PM

the "escapeURL" part works fine. It's actually the "lookupListURL" that is returning a space.

If I escape it, it breaks the code. ???

 

Coordinator
Nov 19, 2010 at 9:50 PM

I wouldn't think that it would break the code. I'm using the lookupListUrl to build up the link to the NewForm in the lookup source list, but it's in quotes and you shouldn'e need to escape it.  Or am I missing something?

BTW, thanks for the idea with the closeMessageUrl above. I think that's a keeper, and I'm going to test it out.

M.

Coordinator
Nov 19, 2010 at 9:52 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Nov 22, 2010 at 3:14 PM

I was just trying everything ^_^

I am using a list called "Regions List" as my add new list - for some reason, I am getting a spaces in the address for "regions list" instead of getting "regions_x0020_list".

Here is the code I was working with:

// Build the link to the Lookup column's list enclosed in a div with the id="SPLookupAddNew_" + lookupColumnStaticName
   var thisSite = $().SPServices.SPGetCurrentSite();
   var simplelink = thisSite + lookupListUrl + newUrl + "?Source=" + escapeUrl(opt.closeMessageUrl);
   alert(simplelink);

Any thoughts on why I am not getting this to work correctly? In the original code, there did not seem to be an issue with this.

Emerson

Nov 22, 2010 at 3:17 PM

sorry, meant "regions%20List"

Nov 22, 2010 at 6:41 PM

A better version of the SPLookupAddNew for SharePoint 2010 which leverages the SP popup functionality.

 

There is now some unneccesary code, but I have left it for now as this does work and I don't want to break anything


 // Function which provides a link on a Lookup column for the user to follow
 // which allows them to add a new value to the Lookup list.
 // Based on http://blog.mastykarz.nl/extending-lookup-fields-add-new-item-option/
 // by Waldek Mastykarz
 // altered for SharePoint 2010 by Emerson Bruce
 $.fn.SPServices.SPLookupAddNew2010 = function(options) {

  var opt = $.extend({}, {
   lookupColumn: "",    // The display name of the Lookup column
   promptText: "Add new {0}",  // Text to use as prompt + column name
   completefunc: null,    // Function to call on completion of rendering the change.
   debug: false     // If true, show error messages;if false, run silent
  }, options);

  // Find the lookup column's select (dropdown)
  var lookupSelect = new dropdownCtl(opt.lookupColumn);
  if(lookupSelect.Obj.html() == null && opt.debug) { errBox("SPServices.SPLookupAddNew", "lookupColumn: " + opt.lookupColumn, "Column not found on page");return;}

  var newUrl = "";
  var lookupListUrl = "";
  var lookupColumnStaticName = "";
  var lookupListID ="";
  // Use GetList for the current list to determine the details for the Lookup column
  $().SPServices({
   operation: "GetList",
   async: false,
   listName: $().SPServices.SPListNameFromUrl(),
   completefunc: function (xData, Status) {
    $(xData.responseXML).find("Field").each(function() {
     if($(this).attr("DisplayName") == opt.lookupColumn) {
      lookupColumnStaticName = $(this).attr("StaticName");
      
      // Use GetList for the Lookup column's list to determine the list's URL
      $().SPServices({
       operation: "GetList",
       async: false,
       listName: $(this).attr("List"),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find("List").each(function() {
         lookupListUrl = $(this).attr("WebFullUrl");
         lookupListID = $(this).attr("ID");
         //trim curly braces
         lookupListID = lookupListID.replace(/{|}/g,"");
         // Need to handle when list is in the root site
         lookupListUrl = lookupListUrl != SLASH ? lookupListUrl + SLASH : lookupListUrl;
        });
       }
      });
      // Get the NewItem form for the Lookup column's list
      $().SPServices({
       operation: "GetFormCollection",
       async: false,
       listName: $(this).attr("List"),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find("Form").each(function() {
         if($(this).attr("Type") == "NewForm") newUrl = $(this).attr("Url");
        });
       }
      });
      // Stop looking;we're done
      return false;
     }
    });
   }
  });

  if(lookupListUrl.length == 0 && opt.debug) {
   errBox("SPServices.SPLookupAddNew",
    "lookupColumn: " + opt.lookupColumn,
    "This column does not appear to be a lookup column");
   return;
  }
  if(newUrl.length > 0) {
   // Build the link to the Lookup column's list enclosed in a div with the id="SPLookupAddNew_" + lookupColumnStaticName
 newLink = "<div id='SPLookupAddNew_" + lookupColumnStaticName + "'><a class='ms-addnew' id='idHomePageNewItem' onclick='javascript:NewItem2(event, \"http://dev-foundation:36452/_layouts/listform.aspx?PageType=8&ListId={" + lookupListID + "}&RootFolder=\");javascript:return false;' target='_self' href='' >" + opt.promptText.replace(/\{0\}/g, opt.lookupColumn) + "</a></div>";
   
   
   // Append the link to the Lookup columns's formbody table cell
   $(lookupSelect.Obj).parents("td.ms-formbody").append(newLink);
  } else if(opt.debug) {
   errBox("SPServices.SPLookupAddNew",
    "lookupColumn: " + opt.lookupColumn,
    "NewForm cannot be found");
   return;
  }
  // If present, call completefunc when all else is done
  if(opt.completefunc != null) opt.completefunc();
 };
 

Nov 22, 2010 at 7:48 PM

Cleaned up version (removed hard coded address:

 
 
 // Function which provides a link on a Lookup column for the user to follow
 // which allows them to add a new value to the Lookup list.
 // Based on http://blog.mastykarz.nl/extending-lookup-fields-add-new-item-option/
 // by Waldek Mastykarz
 // altered for SharePoint 2010 by Emerson Bruce
 $.fn.SPServices.SPLookupAddNewCustom = function(options) {

  var opt = $.extend({}, {
   lookupColumn: "",    // The display name of the Lookup column
   promptText: "Add new {0}",  // Text to use as prompt + column name
   completefunc: null,    // Function to call on completion of rendering the change.
   closeMessageUrl: "",            // appears after update - page, image path, html document path, etc.
   debug: false     // If true, show error messages;if false, run silent
  }, options);

  // Find the lookup column's select (dropdown)
  var lookupSelect = new dropdownCtl(opt.lookupColumn);
  if(lookupSelect.Obj.html() == null && opt.debug) { errBox("SPServices.SPLookupAddNew", "lookupColumn: " + opt.lookupColumn, "Column not found on page");return;}

  var newUrl = "";
  var lookupListUrl = "";
  var lookupColumnStaticName = "";
  var lookupListID ="";
  // Use GetList for the current list to determine the details for the Lookup column
  $().SPServices({
   operation: "GetList",
   async: false,
   listName: $().SPServices.SPListNameFromUrl(),
   completefunc: function (xData, Status) {
    $(xData.responseXML).find("Field").each(function() {
     if($(this).attr("DisplayName") == opt.lookupColumn) {
      lookupColumnStaticName = $(this).attr("StaticName");
      
      // Use GetList for the Lookup column's list to determine the list's URL
      $().SPServices({
       operation: "GetList",
       async: false,
       listName: $(this).attr("List"),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find("List").each(function() {
         lookupListUrl = $(this).attr("WebFullUrl");
         lookupListID = $(this).attr("ID");
         //trim curly braces
         lookupListID = lookupListID.replace(/{|}/g,"");
         // Need to handle when list is in the root site
         lookupListUrl = lookupListUrl != SLASH ? lookupListUrl + SLASH : lookupListUrl;
        });
       }
      });
      // Get the NewItem form for the Lookup column's list
      $().SPServices({
       operation: "GetFormCollection",
       async: false,
       listName: $(this).attr("List"),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find("Form").each(function() {
         if($(this).attr("Type") == "NewForm") newUrl = $(this).attr("Url");
        });
       }
      });
      // Stop looking;we're done
      return false;
     }
    });
   }
  });

  if(lookupListUrl.length == 0 && opt.debug) {
   errBox("SPServices.SPLookupAddNew",
    "lookupColumn: " + opt.lookupColumn,
    "This column does not appear to be a lookup column");
   return;
  }
  if(newUrl.length > 0) {
   // Build the link to the Lookup column's list enclosed in a div with the id="SPLookupAddNew_" + lookupColumnStaticName

   newLink = "<div id='SPLookupAddNew_" + lookupColumnStaticName + "'><a class='ms-addnew' id='idHomePageNewItem' onclick='javascript:NewItem2(event, \"" + lookupListUrl + "_layouts/listform.aspx?PageType=8&ListId={" + lookupListID + "}&RootFolder=\");javascript:return false;' target='_self' href='' >" + opt.promptText.replace(/\{0\}/g, opt.lookupColumn) + "</a></div>";
   alert(newLink);
   
   // Append the link to the Lookup columns's formbody table cell
   $(lookupSelect.Obj).parents("td.ms-formbody").append(newLink);
  } else if(opt.debug) {
   errBox("SPServices.SPLookupAddNew",
    "lookupColumn: " + opt.lookupColumn,
    "NewForm cannot be found");
   return;
  }
  // If present, call completefunc when all else is done
  if(opt.completefunc != null) opt.completefunc();
 };
 

Nov 29, 2010 at 2:40 PM

Final version - removed the line to trim curly braces and just removed the braces from the string ^_^ Duh

// Function which provides a link on a Lookup column for the user to follow
// which allows them to add a new value to the Lookup list.
// Based on http://blog.mastykarz.nl/extending-lookup-fields-add-new-item-option/
// by Waldek Mastykarz
// altered for SharePoint 2010 by Emerson Bruce
$.fn.SPServices.SPLookupAddNew2010 = function(options) {

  var opt = $.extend({}, {
   lookupColumn: “”,    // The display name of the Lookup column
   promptText: “Add new {0}”,  // Text to use as prompt + column name
   completefunc: null,    // Function to call on completion of rendering the change.
   debug: false     // If true, show error messages;if false, run silent
  }, options);

  // Find the lookup column’s select (dropdown)
  var lookupSelect = new dropdownCtl(opt.lookupColumn);
  if(lookupSelect.Obj.html() == null && opt.debug) { errBox(“SPServices.SPLookupAddNew”, “lookupColumn: ” + opt.lookupColumn, “Column not found on page”);return;}

  var newUrl = “”;
  var lookupListUrl = “”;
  var lookupColumnStaticName = “”;
var lookupListID =”";
  // Use GetList for the current list to determine the details for the Lookup column
  $().SPServices({
   operation: “GetList”,
   async: false,
   listName: $().SPServices.SPListNameFromUrl(),
   completefunc: function (xData, Status) {
    $(xData.responseXML).find(“Field”).each(function() {
     if($(this).attr(“DisplayName”) == opt.lookupColumn) {
      lookupColumnStaticName = $(this).attr(“StaticName”);
      // Use GetList for the Lookup column’s list to determine the list’s URL
      $().SPServices({
       operation: “GetList”,
       async: false,
       listName: $(this).attr(“List”),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find(“List”).each(function() {
         lookupListUrl = $(this).attr(“WebFullUrl”);
         lookupListID = $(this).attr(“ID”); 

         // Need to handle when list is in the root site
         lookupListUrl = lookupListUrl != SLASH ? lookupListUrl + SLASH : lookupListUrl;
        });
       }
      });
      // Get the NewItem form for the Lookup column’s list
      $().SPServices({
       operation: “GetFormCollection”,
       async: false,
       listName: $(this).attr(“List”),
       completefunc: function (xData, Status) {
        $(xData.responseXML).find(“Form”).each(function() {
         if($(this).attr(“Type”) == “NewForm”) newUrl = $(this).attr(“Url”);
        });
       }
      });
      // Stop looking;we’re done
      return false;
     }
    });
   }
  });

  if(lookupListUrl.length == 0 && opt.debug) {
   errBox(“SPServices.SPLookupAddNew”,
    “lookupColumn: ” + opt.lookupColumn,
    “This column does not appear to be a lookup column”);
   return;
  }
  if(newUrl.length > 0) {
   // Build the link to the Lookup column’s list enclosed in a div with the id=”SPLookupAddNew_” + lookupColumnStaticName
newLink = “<div id=’SPLookupAddNew_” + lookupColumnStaticName + “‘> <a class=’ms-addnew’ id=’idHomePageNewItem’ onclick=’javascript:NewItem2(event, \”" + lookupListUrl + “_layouts/listform.aspx?PageType=8&ListId=” + lookupListID + “&RootFolder=\”);javascript:return false;’ target=’_self’ href=” >” + opt.promptText.replace(/\{0\}/g, opt.lookupColumn) + “</a> </div>”;

   // Append the link to the Lookup columns’s formbody table cell
   $(lookupSelect.Obj).parents(“td.ms-formbody”).append(newLink);
  } else if(opt.debug) {
   errBox(“SPServices.SPLookupAddNew”,
    “lookupColumn: ” + opt.lookupColumn,
    “NewForm cannot be found”);
   return;
  }
  // If present, call completefunc when all else is done
  if(opt.completefunc != null) opt.completefunc();
};

Coordinator
Dec 2, 2010 at 5:32 AM

Thanks a lot for sticking with this and posting your results! I've got this on my list to look at...

M.