CAML Query, Form modification, Javascript, Requests, SharePoint

Validate text field input against value in another list

19 Comments

I got this request from Sonaly:

I am using MOSS.

I have two lists Machines and TRansactions.

I have 3 following textboxes in newform.aspx/editform.aspx of Transactions List.

EnqNo – Plain Text box – Required field
Date – Datetime field – Required field
MachineID – Plain Text box – Required field

Here I want to validate Machine ID from Machines List (MachineId column) before save record into transaction list

Let me know if you need more information,

Alexander Says:
Take a look at this script: Convert Singleline textfield to filtered lookup dropdown and see if this could fill your need.

The script will convert your “MachineID” to a lookup from the list “Machines”.

Alexander

Sonaly Says:
Alexandar

I dont want to use lookup field as I have 1000+ item and it is not user friendly , that is why I am thinking of using Textbox coumn and validation.

Any other advise

This example has a two list setup with only the “Title” field present in each list. You will have to edit the FieldInternalName to match your fields.

Add this code to a CEWP below the form in NewForm and EditForm in the list the user will input the value in:

<style type="text/css">
.customError{
	background-color:#FF6A6A;
}
</style>
<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();

// Attach a function to the blur event of the "Title" field
$(fields['Title']).find('input').blur(function(){
	var thisVal = $(this).val();
	validateTextAgainstList($(this));
});

function validateTextAgainstList(obj){
str = obj.val();
wsBaseUrl = L_Menu_BaseUrl + '/_vti_bin/';
// Edit the "Title" to match your FieldInternalName
var query = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+str+"</Value></Eq></Where>";
// Edit the list GUID to match your list - or use the list's displayName
	var itemCount = queryItemCount('D54206E8-5E98-4DCD-9D3D-39C321FD0792',query);
	if(itemCount==0){		
		$("input[id$='SaveItem']").attr('disabled',true);
		obj.addClass('customError');
		alert("Sorry mate, this value is not correct.");
	}else{
		// OK
		$("input[id$='SaveItem']").attr('disabled',false);
		obj.removeClass('customError');
	}
}

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>

You will have to edit the FieldRef Name=’Title’ to match your fields FieldInternalName, and the list GUID (D54206E8-5E98-4DCD-9D3D-39C321FD079) to match your list – or use the list’s displayName.

The jQuery-library is found here. 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.

Read here how to add a CEWP to the NewForm, DispForm or EditForm, how to find the list Guid of your list, and how to find the FieldInternalName of your columns.

Regards
Alexander

19 thoughts on “Validate text field input against value in another list”

  1. works perfectly, User may want to add some additional functionality. two things I thought of , something to link or display the machineIDs. Unless this is general knowledge. Or some way to link back to the source list where users can suggest new machine IDs, but they require approval before they are valid.

    What you have here, although the functionality is great, can be accomplished by a lookup field. Now if this was used in parallel with some option that forces a user to have a required field that would be kool. Say starting with 3 options that can be entered, if option 1 is entered, then field1 is required in the newofrm, same for option 2, field2 req and so on. I have a form now with 2 contacts, the user can select point of contact as thenselves, the backup or both. the problem I have is I cant make backup required, So I send them an email message if blank. but here is where the the option can specify the required fields.

    great job

  2. Hello

    I created a folder i.e. “javscripts” under document library i.e. ‘Shared Documents’ and uploaded all the related javascripts. Suddenly all the menu from left side menu is automatically hide except only 2 items are there View All Site Contents and Error.

    Then I removed ‘javscripts’ folder, but nothing happens. Then I checked Site Settings – Modified Navigation , nothing is coming under “Current Navigation”.

    Please let me know what went wrong. I am stuck here

    1. Nothing in the scripts you uploaded would ruin the quick launch. Even if they did, the error would only be visible in the page where the code is referred.

      There has to be another explanation. Could some other user have messed up the quick launch?

      Alexander

  3. First of all thanks to share intelligent code with the communities.

    I gone thru your code and understand logic. I want to implement same thing but with different sceneriao.

    we have 3 fields in transactionl ist
    i.e.
    ProductId
    sell Id ( a text field but we are storing no)
    ship Date

    I want to check following conditions

    Check entered product id is exist in transaction list or not

    if it is not exist thne alert a message (“Product is not sold “)

    if it is exist then get the maximum no of sell id where ship date is null and then display alert with sell id, and ship date.

    How can I achieve tis?

  4. This is quite cool. I am a newbie an I am a bit confused here since you are just using “Title” on both lists and whereas I am validating column “Team Manager Employee No ” against column “User Employee No” from another list. Which one is which?

  5. Hi,
    Line 13-16 is the form that the user is filling in right now. Line 22-34 is checking the “target list” for occurrences of the value.

    Alexander

    3. I’m not sure how this applies to anonymous users, but there is a setting under Site settings – Permissions – Permission Levels that reads:
      Use Remote Interfaces – Use SOAP, Web DAV, or SharePoint Designer interfaces to access the Web site.

      Take a look at that and let me know if it halps.

      Alexander

    4. Remote Interfaces was enabled, but that pointed me in the right direction. The error was with a couple lines in the interaction.js script. SOAP headers can’t be passed by anonymous users, but also aren’t required in an instance like this where it’s only performing a read. If you comment out the beforeSend for the actionHeader (around lines 34-35) the solution works perfectly. Thanks for the help!

    5. Hi,
      Glad you figured it out – i actually didn’t know about the SOAP headers and anonymous users… Thank you for the feedback!

      Alexander

  8. Hello. I tried to modify the code for the following scenario: a parent list with a lookup field. Items in the child list have a boolean field which controls their active status, which I want to validate against, e.g. the form is supposed to complain if the user picks a choice flagged as inactive in the child list.

    The code below does not work. Any idea why?

    .customError {
    BACKGROUND-COLOR: #ff6a6a
    }

    fields = init_fields();

    // Attach a function to the blur event of the “Title” field
    $(fields[‘myLookupField’]).find(‘input’).blur(function(){
    var thisVal = $(this).val();
    validateTextAgainstList($(this));
    });

    function validateTextAgainstList(obj){
    str = obj.val();
    wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
    // Edit the “Title” to match your FieldInternalName
    var query = “”+str+”True”;
    // Edit the list GUID to match your list – or use the list’s displayName
    var itemCount = queryItemCount(‘SOME_GUID’,query);
    if(itemCount==0){
    $(“input[id$=’SaveItem’]”).attr(‘disabled’,true);
    obj.addClass(‘customError’);
    alert(“Sorry mate, this value is not correct.”);
    }else{
    // OK
    $(“input[id$=’SaveItem’]”).attr(‘disabled’,false);
    obj.removeClass(‘customError’);
    }
    }

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

Leave a Reply

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