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

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

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:

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