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:

<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>

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”:

/* 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,'&');

	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;
}

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

10 thoughts on “SharePoint list’s or document library’s: Primary key in selected field”

  1. Thanks for the nice JavaScript!
    Unfortunately I didn’t find the way to have it work with dropdown instead of textbox. In case you know the way I would be very grateful.

    1. Hi,
      You realize that if you hook this up to a dropdown you can have no more items in the list then the options available in the dropdown?

      The script does not permit duplicate values and hence a dropdown with 20 options would restrict the total number of registrations in the list to 20.

      Alexander

  2. Hi
    I try to use this but unfortunatly it dosent work. even didint show the picture of key near “Title” .i loaded in “javascript” list ant i created “test34″ near(in same root) , then i add codes like ” and …” on cewp in editform,newform aspx . but dosent work yet.
    can you help me
    thx

  3. Hi I tried the same and followed the steps but nothing happen. Image also doesn’t appear and messages also doesnt display. Please let me now this is very urgent.

  4. Hi,

    I am trying to implement this code, but its not working for me after i have done all the steps.

    I tried to debug, but some issue was there in the PrimaryKey.js file , at the init_fields(). It is not loading the FieldInternalName.

    Please let me know your feedback.

    Awaiting your reply.

    Thanks
    Ravishankar

  5. Hey A, Hope all is well. I have a delimma. With the fabulous 40 templates being phased out with the release of SP 2010 I am losing the room reservation template, which offers the functionality to prevent double booking of resources. Of course I have turned to your site and began looking at this primary key script. I thought at first I could build a CC to add several fields, building the Unique key. but I see the script needs the field onblur event. Is there a way to check a combination of several fields for a calendar list that builds a string of the title, location, start date and length. I know it wont be a perfect solution, but is there some way to have this work on a calendar, or do you know of a solution out there I could work with?

    I have found some neat features in the 2010 release that may work, but for now I am in limbo.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.