Category Archives: CAML Query

SharePoint list’s or document library’s: Primary key in selected field

05.01.2010 Small update to hide the “OK-button” if the “keyIsNotUniqueCount” is greater than 0.
25.11.2009 Small update to the script for placing the image correctly when used with the headings script.

In this article I will give you a solution for adding “primary key support” to a list or a document library with JavaScript.

This will work with fields of type “Single line of text”, “Multiple lines of text (Plain text)” and “Number”. Note that if you activate this feature for a field of type “Number”, it strips off all other than numbers 0-9. No spaces, commas or periods are allowed. This is done to avoid conflicts based on different formats used in different language settings.

You can enable this feature for more than one field in a list or document library.

Note: This does only work in NewForm and EditForm, not if edited in DataSheet.

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.3.2.min. If you download another version, be sure to update the script reference in the sourcecode.

The sourcecode for the file “PrimaryKey.js” is found below.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

The last file in this screen-shot is the Right click and select Save picture as. Right click on the image and select “Save picture as”.

Add a CEWP below your list-form in NewForm and EditForm.
Add this code:
[javascript]
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript" src="/test/English/Javascript/PrimaryKey.js"></script>
<script type="text/javascript">
// Enables "Primary key" on the "Title-field"
ensurePrimaryKey(‘Title’,”,’/test/English/Javascript/PrimaryKey.gif’,”);
</script>
[/javascript]

Your “Title-field” will look like this:
IMG

The check is attached to the “blur” event, and triggers when you removes focus on the field. If you try to add a “non-unique-value” to that field, you end up with this warning, and cannot save the list item:
IMG

If used with a field of type “Number”, the code strips away spaces, commas and periods as described above.

Parameters explained:

  • FieldInternalName: FieldInternalName of the field to add this feature to
  • PrimaryKeyViolationWarning: [optional] The text shown under the field if the key is not unique.
  • keyImageSrc: [optional] Image source for the “key.image” added before the field label. If not supplied it default’s to “/_layouts/images/prf16.gif”.
  • PrimaryKeyHoverImageText: [optional] Description text if hovered over the “key-image”.

Sourcecode for the file “PrimaryKey.js”:
[javascript]
/* Primary key for lists or Document libraries
* ———————————————
* Created by Alexander Bautz
* alexander.bautz@gmail.com
* https://spjsblog.com
* v1.1
* LastMod: 05.01.2010
* ———————————————
* Include reference to:
* jquery – http://jquery.com
* interaction.js – http://spjslib.codeplex.com/
* stringBuffer.js – http://spjslib.codeplex.com/
* ———————————————
* Call from a CEWP below the list form in NewForm orEditForm like this:
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript" src="/test/English/Javascript/PrimaryKey.js"></script>
<script type="text/javascript">
// Enables primary key for the "Title-field"
ensurePrimaryKey(‘Title’,’This field’s value has to be unique for this list.’,’/test/English/Javascript/PrimaryKey.gif’,’Primary key is enabled for this field’);
</script>
*/

if(typeof(fields)==’undefined’)fields = init_fields();
function ensurePrimaryKey(FieldInternalName,PrimaryKeyViolationWarning,keyImageSrc,PrimaryKeyHoverImageText){
if(fields[FieldInternalName]!=undefined){
listDisplayName = $("h2.ms-pagetitle a").text();
keyIsNotUniqueCount = 0;
// If PrimaryKeyViolationWarning is not defined, default to this text
if(PrimaryKeyViolationWarning==undefined || PrimaryKeyViolationWarning==”){
var PrimaryKeyViolationWarning = "This field’s value has to be unique for this list.";
}
// If PrimaryKeyHoverImageText is not defined, default to this text
if(PrimaryKeyHoverImageText==undefined || PrimaryKeyHoverImageText==”){
var PrimaryKeyHoverImageText = "Primary key is enabled for this field";
}
if(keyImageSrc==undefined || keyImageSrc==”){
keyImageSrc = "/_layouts/images/prf16.gif";
}
// If number – trim off all spaces, commas and periods
if($(fields[FieldInternalName]).html().match(‘SPFieldNumber’)!=null){
$(fields[FieldInternalName]).find(‘:input’).keyup(function(e){
var currVal = $(this).val();
$(this).val(currVal.replace(/[^0-9]/g,”));
});
}

// Determine where to put the image – if used with the Heading-script
if($(fields[FieldInternalName]).find("h3:has(‘div’)").length>0){
var insertImageHere = ‘h3 div’;
}else{
var insertImageHere = ‘h3’;
}

// Add blur function
$(fields[FieldInternalName]).find(‘:input’).blur(function(){
// Check if input value is unique in this list
checkPrimaryKey(FieldInternalName,$(this),PrimaryKeyViolationWarning);
})
// Add key image before label
.parents(‘tr:first’).find(insertImageHere)
.prepend("<img title=’" + PrimaryKeyHoverImageText + "’ src=’" + keyImageSrc + "’>");
}
}

function checkPrimaryKey(FIName,keyField,warningText){
wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
var ItemId = getIdFromFormAction(); // Returns 0 if NewForm
var keyFieldVal = keyField.val().replace(/&/g,’&amp;’);

if(ItemId==0){ // NewForm
var query = "<Where><Eq><FieldRef Name=’" + FIName + "’/><Value Type=’Text’>" + keyFieldVal + "</Value></Eq></Where>";
}else{ // EditForm – skip current ID
var query = "<Where><And><Eq><FieldRef Name=’" + FIName + "’/><Value Type=’Text’>" + keyFieldVal + "</Value></Eq>" +
"<Neq><FieldRef Name=’ID’/><Value Type=’Counter’>" + ItemId + "</Value></Neq></And></Where>";
}
var res = queryItems(listDisplayName,query,[‘ID’]);
if(res.count==-1){
alert("An error occured in the query:n" + query);
}else if(res.count>0){
keyIsNotUniqueCount = keyIsNotUniqueCount + 1;
keyField.parents(‘td:first’).find(".customPrimaryKeyAlert").remove();
keyField.parents(‘td:first’).append("<div class=’customPrimaryKeyAlert’ style=’color:red’>" + warningText + "</div>");
}else{
keyField.parents(‘td:first’).find(".customPrimaryKeyAlert").remove();
if(keyIsNotUniqueCount>0){
keyIsNotUniqueCount = keyIsNotUniqueCount – 1;
}
}
// Hide button
if(keyIsNotUniqueCount>0){
$("input[id$=’SaveItem’]").hide();
}else{
$("input[id$=’SaveItem’]").show();
}
}

// This function gets the list item ID from the form’s action-attribute
function getIdFromFormAction(){
var action = $("#aspnetForm").attr(‘action’);
var IdCheck = action.substring(action.indexOf(‘?’)).indexOf(‘ID=’);
if(IdCheck==1){
var end = action.indexOf(‘&’);
if(action.indexOf(‘&’)<0){
id = action.substring(action.indexOf(‘ID=’)+3);
}else{
id = action.substring(action.indexOf(‘ID=’)+3,end);
}
}else{
id = 0; // Called from NewForm returns 0
}
return id;
}

function init_fields(){
var res = {};
$("td.ms-formbody").each(function(){
if($(this).html().indexOf(‘FieldInternalName="’)<0) return;
var start = $(this).html().indexOf(‘FieldInternalName="’)+19;
var stopp = $(this).html().indexOf(‘FieldType="’)-7;
var nm = $(this).html().substring(start,stopp);
res[nm] = this.parentNode;
});
return res;
}
[/javascript]

Save as “PrimaryKey.js” and upload to the “script-library” as shown above.

This is not a “guaranteed” primary key, as it does not work in datasheet-editing, and I’m sure someone finds a way to trick it. I’m nevertheless confident that someone can make use of this code.

Let me know if something is unclear.

Regards
Alexander

Add HTML mouseover tooltip

28.12.2009: Updated the code in line 38 and 41 and added “stop(true,true)” to prevent animation from looping when mouse is rapidly hovered in and out.


In a previous post i described how to add a custom tool-tip on mouse-over a SharePoint field. This was using the “title-property” of a DOM element and therefore limited to plain text only.

Here is a method for adding HTML tool-tip

It uses a standard “Custom list” as a repository for the mouse-over tool-tip’s. This list can be used for multiple lists.

The tool-tip can look like this (but is unlimited as it uses HTML):
IMG

IMG

IMG

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.3.2.min. If you download another version, be sure to update the script reference in the sourcecode.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

The sourcecode for “HTML_style_ToolTip.js” is provided below.

Create the repository like this:
IMG

IMG

IMG

Add a CEWP below your NewForm and EditForm list-form (and DispForm if you like) like this:
IMG

With this code:
[javascript]
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript" src="/test/English/Javascript/HTML_style_ToolTip.js"></script>
<script type="text/javascript">
toolTip(‘MyTestList’,’MouseOverTooltipResources’);
</script>
[/javascript]

Sourcecode for the file “HTML_style_ToolTip.js”:
[javascript]
/* HTML style ToolTip
* ———————————————
* Created by Alexander Bautz
* alexander.bautz@gmail.com
* https://spjsblog.com
* v1.1
* LastMod: 28.12.2009
* ———————————————
* Include reference to:
* jquery // jquery.com
* interaction.js // http://spjslib.codeplex.com/
* stringBuffer.js // http://spjslib.codeplex.com/
* ———————————————
* Call from a CEWP below the list form in NewForm, DispForm or EditForm like this:
* toolTip(ListnameOrToken,ResourcesListName)
*
* Parameters:
* ListnameOrToken = Identifier for the list – to distinguish between lists with similar FieldInternalName
* ResourcesListName = ListName or Guid of the tool-tip repository
*/

fields = init_fields();

function toolTip(ListnameOrToken,ResourcesListName){
tooltip = init_tooltip(ListnameOrToken,ResourcesListName);
$.each(tooltip,function(idx,item){
var split = item.split(‘|’);
var fieldName = split[0];
var displayText = split[1];
var toolTip = split[2];
if(fields[fieldName]!=undefined){
$(fields[fieldName]).find(‘td.ms-formbody’).prepend("<div class=’customMouseOverTooltip’ style=’float:right;cursor:hand’>&nbsp;" +
displayText + "&nbsp;<div style=’padding:3px;display:none;’>" + toolTip + "</div></div>");
}
});

$(".customMouseOverTooltip").hover(function(){
$(this).find(‘div:first’).css({‘position’:’absolute’,’background-color’:’f8f8ff’,’border’:’1px silver solid’}).stop(true,true).fadeIn(350)
},
function(){
$(this).find(‘div:first’).stop(true,true).fadeOut(150);
});
}

function init_tooltip(ConsumerListName,ProviderListName){
wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
var query = "<Where><Eq><FieldRef Name=’Identifier’ /><Value Type=’Text’>" + ConsumerListName + "</Value></Eq></Where>";
var res = queryItems(ProviderListName,query,[‘Title’,’DisplayText’,’ToolTip’]);

tooltip = [];
if(res.count==-1){
alert("An error occured in the query:n" + query + "nnContact an administrator");
}else if(res.count>0){
$.each(res.items,function(idx,item){
if(item[‘ToolTip’]!=null){
var title = item[‘Title’];
var val = item[‘ToolTip’];
var DisplayText = item[‘DisplayText’];
tooltip.push(title + "|" + DisplayText + "|" + val);
}
});
}
return tooltip;
}

function init_fields(){
var res = {};
$("td.ms-formbody").each(function(){
if($(this).html().indexOf(‘FieldInternalName="’)<0) return;
var start = $(this).html().indexOf(‘FieldInternalName="’)+19;
var stopp = $(this).html().indexOf(‘FieldType="’)-7;
var nm = $(this).html().substring(start,stopp);
res[nm] = this.parentNode;
});
return res;
}
[/javascript]
Save this as “HTML_style_ToolTip.js” and upload to your scriptlibrary as shown above.

Enjoy!

Regards
Alexander

Get variables in a script from another list

This one is a follow-up on the two previous posts on manipulating form labels, but the technique can be used for many other scenarios.

I will show you how to use entries from another list as “resources” for a script by querying the “resources-list” for variables to use in the script.

Because i use the same scenario as in the two previous posts, you must read them before proceeding with this one.

The array i used to populate the labels for each option in the choice list is now moved out in another list, and we “ask for it” with a CAML-query to the web-service lists.asmx.

Create a new list like this:
IMG
These are all the actual “FieldInternalNames” (Fields get their “FieldInternalName” first time created – any later modification of the name affects only their “DisplayName”).

The NewForm looks like this:
IMG

The fields are used like this:

  • ListGuid: This is the list Guid of the list using this resource.
  • Title: This is the FieldInternalName of the column to use the resource in.
  • Val: This is the actual value to return.
  • Description: This is used to describe the use of this “resource”.

Here is the form filled for our need in this example:
IMG

You must refer two more scripts like this:
IMG

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

You will need the list GUID of your “resources-list” and the list you will insert the resources in. Replace the one in the script (resourcesListGuid – line 34 in the script) and the one in the picture above and the one in line 13 with your own!

I have made the “resources-list” more complex than i needed for this example, but by adding the “ListGuid” column i prepare the list so that it can be used by many different lists without having to think about using unique names for the columns in the different lists.

The script is now modified like this:
[javascript]
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript">
fields = init_fields();

var myNewLabel = "<br><div>Here is some custom text added by adressing the formlabel with jQuery!</div>" +
"<br><div>You can insert images to:<br><img src=’/test/English/Shared%20Documents/jQuery_img.jpg’ border=’0′></div>"

$(fields[‘MyChoice’]).find(".ms-formlabel h3").after(myNewLabel);

// Lookup the array values from another list and split it to create an array
arrMyChoice = getExternalResources(‘3264E665-228B-4A08-970C-4CE5E871A002′,’MyChoice’).split(‘,’);

// Call the script that inserts the descriptions
descriptionBeforeChoice(‘MyChoice’,arrMyChoice,300);

function descriptionBeforeChoice(FieldInternalName,arrName,widthOfCustomLabel){
$(fields[FieldInternalName]).find(".ms-formbody").find(":checkbox").each(function(idx){
// Add alternating style to make it easier to follow the lines in the form
var trClass = ”;
if(idx%2==0){
trClass = ‘ms-alternatingstrong’;
}
$(this).before("<span style=’display:inline-block;width:" + widthOfCustomLabel + ";white-space:nowrap’>" + arrName[idx] + "</span>")
.parent().css({‘white-space’:’nowrap’})
.parents(‘tr:first’).addClass(trClass);
});
}

function getExternalResources(ListGuid,ElementTitle){
// ListGuid can be swapped with DisplayName
// ElementTitle is here the FieldInternalName of the field to add the resources to
var resourcesListGuid = "{7008EA8E-623D-4F80-A70C-35C0F71E5FB0}"; // GUID of the "resources-list" – can be swapped with DisplayName
var query = "<Where><And><Eq><FieldRef Name=’ListGuid’ /><Value Type=’Text’>" + ListGuid + "</Value></Eq>" +
"<Eq><FieldRef Name=’Title’ /><Value Type=’Text’>" + ElementTitle + "</Value></Eq></And></Where>";
wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
var res = queryItems(resourcesListGuid,query,[‘Val’],1);
if(res.count==-1){
alert("An error occured in the query:n" + query);
}else{
return res.items[0][‘Val’];
}
}

function init_fields(){
var res = {};
$("td.ms-formbody").each(function(){
if($(this).html().indexOf(‘FieldInternalName="’)<0) return;
var start = $(this).html().indexOf(‘FieldInternalName="’)+19;
var stopp = $(this).html().indexOf(‘FieldType="’)-7;
var nm = $(this).html().substring(start,stopp);
res[nm] = this.parentNode;
});
return res;
}
</script>
[/javascript]

The end result looks like in the previous post:
IMG

As always – ask if something is unclear!
Alexander

Show Workflow history in Workflow status column in list view

In this post i will show you how to display the workflow history log in the workflow status column instead of just “In Progress” or “Completed”.

The default behavior is like this:
IMG

The status is “Completed”, but what did it do? A click on “Completed” brings up the workflow history log:
IMG

I think that is to complicated!

How about we display it like this:
IMG
Of course it is fully clickable, and takes you right to the good old workflow status page.

Here’s how it’s done

The list:
IMG

The workflow:
IMG
IMG

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):
IMG
The code for the file “WorkflowHistoryInListView.js” is supplied below.

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.3.2.min. If you download another version, be sure to update the script reference in the sourcecode.

The scripts “interaction.js” and stringBuffer.js” is created by Erucy and published on codeplex – you can find them here.

Add a CEWP below your ListView like this:
IMG

With this code:
[javascript]
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript">
WfListGuid = "{49293528-0c47-4150-b70c-77e876548d12}";
</script>
<script type="text/javascript" src="/test/English/Javascript/WorkflowHistoryInListView.js"></script>
[/javascript]

You could also use WfListGuid = “Workflow History”; for a english language site – but remember that the displayname of your workflow history list will change depending of your site’s language. I personally always use list GUID.

Note! The workflow history list is only present in a site if there is at least one workflow configured. If it’s not present in your site – it will be created with your first workflow. All subsites has it’s own workflow history list.

The list GUID for your Workflow history list is found like this:
Browse to your list by its “displayname” – Workflow History for a english site (it’s hidden from browser’s and do not display in “View all site content”). When in doubt – use SharePoint designer to get the name of your list among the other lists in the “Lists-folder”.

When you have browsed to the list – right click and view source. Search for “listName” and you will find it like this:
ctx.listName = “{49293528-0C47-4150-B70C-77E876548D12}”;

Here’s the sourcecode for the file “WorkflowHistoryInListView.js”:
[javascript]
/* Display the "Workflow History" instead of just "In Progress" or "Completed" in the Workflow-status column
* ———————————————
* Created by Alexander Bautz
* alexander.bautz@gmail.com
* https://spjsblog.com
* Version: 1.0
* LastMod: 01.10.2009
* ———————————————
*/

listGuid = ctx.listName; // SharePoint provides this

WriteLogg();
function WriteLogg(){
if(typeof(wfListObj)=="undefined")wfListObj = getWfLogg(); // If not already createt – build an "object" containing all WF-history for the current list
$(‘a[href*="WrkStat.aspx"]’).each(function(){ // Find all a-tags that have a href containing WfkStat.aspx
if($(this).text()!=”){
var wfGuidRaw = $(this).attr(‘href’); // Get the href
var wfGuid = unescape(wfGuidRaw.substring(wfGuidRaw.lastIndexOf(‘WorkflowInstanceID=’)+19)).toLowerCase(); // Get the GUID of the current WF from the href
var wfLogg = wfListObj[wfGuid] // Get the actual history from the WF-object by "asking" for the log on this list items GUID
if(wfLogg!=undefined){ // If the workflow history isn’t empty
wfLogg = wfLogg.split(‘|’).join(‘<br>’); // Separate the lines
$(this).html(wfLogg); // Set the clickable part of the a-tag to the actual history from the log
}
}
});
}

function getWfLogg(){
wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’; // Set the path to the webservice "lists.asmx"
var query = "<Where><Eq><FieldRef Name=’List’ /><Value Type=’Text’>" + listGuid + "</Value></Eq></Where>"; // Get all WF-history for the current list
var res = queryItems(WfListGuid,query,[‘ID’,’WorkflowInstance’,’Description’]);
obj = {};
if(res.count == -1){
alert("An error occured in the query:n" + query); // On error
}else{
$.each(res.items,function(idx,item){
if(item[‘Description’]!=null){
// Is there history already logged on this GUID?
if(obj[item[‘WorkflowInstance’]]==undefined){
// No
obj[item[‘WorkflowInstance’]] = item[‘Description’];
}else{
// Yes – add to it so that all log on current WF-run is displayed together
obj[item[‘WorkflowInstance’]] = obj[item[‘WorkflowInstance’]] + "|" + item[‘Description’];
}
}
});
return obj; // Return object containing all WF-history on current list
}
}

// Attaches a call to the function to the "expand grouped elements function" for it to function in grouped listview’s
function ExpGroupRenderData(htmlToRender, groupName, isLoaded){
var tbody=document.getElementById("tbod"+groupName+"_");
var wrapDiv=document.createElement("DIV");
wrapDiv.innerHTML="<TABLE><TBODY id="tbod"+groupName+"_" isLoaded=""+isLoaded+"">"+htmlToRender+"</TBODY></TABLE>";
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
WriteLogg(); // Call the script – the rest of the function "ExpGroupRenderData" is a unmodified SharePoint function from the file "BFORM.JS"
}
[/javascript]

That’s it!

Feel free to ask if something is unclear.
Alexander

Convert Singleline textfield to filtered lookup dropdown

08.04.2011 A small makeover to get rid of some extra script references and to add compatibility to all major browsers.


This one is related to my post on Cascading dropdowns, but is used to convert one column of type “Single line of text” or a column of type “Hyperlink or Picture” to a filtered dropdown.

You can populate this dropdown from any list in current site, sub site or parent site – as long as the user has read access to the list holding the information. The lookup can be against all elements – or filtered by any metadata in the source list item – like an “active/inactive” – Yes/No Checkbox-column.

In your list – add a column of type “Single line of text”, with a nice “FieldInternalName” (a name without spaces and special characters) – you can edit the column name as soon as the column is created to get a readable “DisplayName”. It is this newly created “Single line of text-column” that is to be converted to a dropdown. by this script. In this example I have used the “Title-column” as the field to convert.

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). Make sure all users have read access to that folder.

Download the file “dropdownFromTextOrHyperlinkField.js” from here

Upload it to the selected folder.

Add a CEWP below your NewForm list-form (and EditForm if you like) like this:

IMG
With this code:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/dropdownFromTextOrHyperlinkField.js"></script>
<script type="text/javascript">
fields = init_fields_v2();

singleFilteredDropdown('{1b128964-7075-491a-be7b-55c40d94714b}',L_Menu_BaseUrl,'Title','Active','Boolean','Eq','1',false,'DropdownNr1','<select>','Title',true,false,false);

</script>

Please note that the call to “init_fields_v2()” has changed from “init_fieldInternalName()” from earlier versions. You have to change the “src” to the file “dropdownFromTextOrHyperlinkField.js” to match your local path.

The list that is source for my lookup has a listGuid of {1b128964-7075-491a-be7b-55c40d94714b}, a Yes/No column named Active, and the Title column which holds the value to populate the dropdown.

IMG

The source list is located in the same site as the target list – hence the variable L_Menu_BaseUrl which SharePoint provides for us.

You should end up with a result like this:
IMG
Here some of the elements in the sourcelist is set to “inactive”:
IMG

To get a hyperlink back to the selected element – use a column of type “Hyperlink or Picture” – with hyperlink format, and change the parameter “showAsURL” to true.

Please ask if something is unclear.

Alexander

Cascading dropdowns

14.06.2010 A new version of this script is released Cascading dropdowns in SharePoint text fields – populated by lookup in another list (version 2)

I recommend using the new version.


Updated 14.08.2009
This script “converts” singleline textfields to lookup-dropdowns. One dropdown filteres the next. You can have 2-5 connected dropdowns in each “collection”. You can have as many “collections” as you want in each listform.

The source of the cascading dropdowns is a standard custom list containing the information to populate each of the dropdowns. This list can reside in the current site, the rootsite or in any subsite as long as users has access to it.

The script uses the original textfield to hold the value selected, and “spawns” a dropdown to take it’s place. In editform (or on refresh of the form on validation) the script updates the dropdown to hold the correct value – read from the original textfield.

If you open in editform and the original value selected in no longer a valid choice (the value is deleted from the source list), the dropdown and the corresponding hidden textfield is cleared.

When changing a “parent” dropdown, the children is cleared for the previously selected values and repopulated with the new choices.

To use this script you have to reference jQuery (tested with jquery-1.3.2.min.js) and these scripts:
* SPAPI_Core.js
* SPAPI_Lists.js
* SPAPI_dspsts.js
* Made by Darren Johnstone http://darrenjohnstone.net
download the scripts here. This download contains more scripts than you need to refer – only the scripts named over is used.

How to use:
Make a sourceList to hold the selectable values to fill your dropdowns.
Img
Create the fields like this:
Img

The calculated column (_Lookup) that concatenates all these columns is the field the script uses to find matching values to populate the next dropdown.

Find the ListGuid of your SourceList like this:
Img
You need it in the function call later.

Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a subsite named “test” with a subsite named “English” with a document library named “Javascript”):
Img

In the consumer list (the list where your dropdowns is supposed to be), add corresponding columns (standard SharePoint single line textfields) for all but the calculated field. The final “product” looks like this:
Img

Insert a CEWP below the listform in NewForm and EditForm and add reference to the scripts like this:
Img
The source looks like this (copy the source below):
Img
The listGuid of your sourceList is used here

Sourcecode:[sourcecode lang=”javascript”]
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/SPAPI_Core.js"></script>
<script type="text/javascript" src="/test/English/Javascript/SPAPI_Lists.js"></script>
<script type="text/javascript" src="/test/English/Javascript/SPAPI_dspsts.js"></script>
<script type="text/javascript" src="/test/English/Javascript/CascadingDropDowns.js"></script>

<script type="text/javascript">
/*
* providerArr = Array of FieldInternalNames of the fields in the source-list.
* consumerArr = Array of FieldInternalNames of the fields in the list where the dropdowns will be created.
* dropDownIdPrefix = Unique prefix if there are more then one instance of cascading dropdowns in one list.
* lookupSourceCalculatedField = FieldInternalName of the calculated column in the source-list that concatenates all lookup values in one string – separated by "delimiter".
* lookupSourceListGuid = GUID of the source-list.
* dropDownDefaultvalue = The default value of dropdowns that are not empty – ex. <select>.
* numberOfDropdowns = Number of levels of cascading dropdowns – (2-5).
* delimiter = Separator character between the values of the calculated column in "lookupSourceCalculatedField". Must be the same as the delimiter used in the "_Lookup" in the sourceList.
* sourceListURL = relative URL to site/subsite. If root site use only two apostrophes representing a blank string – like this: ”.
* debug = true or false – if true: the textfield that holds the dropdownvalue is visible.
*/
// You don’t have to use the same names in providerArr and consumerArr, but you have to use the FieldInternalName and not the Displayname.
providerArr = [‘Title’,’Model’,’Color’,’Year’,’Milage’];
consumerArr = [‘Title’,’Model’,’Color’,’Year’,’Milage’];
cascadingDropDowns(providerArr,consumerArr,’CascNr1′,’_Lookup’,'{83eb224b-03fa-4a8b-b493-80253373a962}’,'<Select>’,5,’ – ‘,true,’/test/English’,false);
// For description of all the parameters see the sourcecode.
</script>
[/javascript]
Be sure to use the correct path to your script in the reference – mine is located at “/test/English/Javascript”.

How to find the FieldInternalName? – Click here

Get the sourcecode for “CascadingDropDowns.js” here and save it as CascadingDropDowns.js. Make sure you get only the script and not other text – copy manually – do not use Ctrl+A.

Upload in the document library with the other scripts needed – as shown above.

If you need more info to setup this script, feel free to ask.

PS. I have other scripts for single filtered lookup’s both for for obtaining value only, and for link to list item or to document. Ask for it and i will post it.

Regards
Alexander