Hi,

What is the data type of column in List B?

You can simply you can insert a calculated column next to your text box and set formula like 

If(lookup('List B','List B Column Name',textboxNamedControl,'List B Column Name') == '',false,true)

EX:

And add a validation rule for text box like 

Or 

You can insert a javascript code in the cutom javascript section on form settings and call this function on a button click.

It will display the alert if entered text box value present in List B column.

function findDuplicateItems()
{
var RequestNumber = NWF$('#'+textVal).val();
var clientContext = new SP.ClientContext.get_current();
var oList = clientContext.get_web().get_lists().getByTitle('Test Request Number');
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml("<View><Query><where><Eq><FieldRef Name='Title'/><value Type='Text'>"+RequestNumber+"</value></Eq></Where></Query></View>");
window.collListItem = oList.getItems(camlQuery);
clientContext.load(collListItem);
clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
}
function onQuerySucceeded(sender, args)
{
if(collListItem)
{
var title = '';
var RequestNumber = NWF$('#'+textVal).val();

var listItemEnumerator = collListItem.getEnumerator();
while (listItemEnumerator.moveNext())
{
var oListItem = listItemEnumerator.get_current();
title = oListItem.get_item('Title');
if(title == RequestNumber)
{
alert('duplicate exists');
}
}
}
}
function onQueryFailed(sender, args)
{
alert('Request failed.');
}

Good day Lakshmi,

Thank you very much for the provided information. Just a quick one regarding the javascript which i think will be the solution to my problem.

How would i amend it for my specific needs? I dont know javascript at all.

Do I replace the "RequestNumber" with the Client Details List name / Column name

function findDuplicateItems()

{

var RequestNumber= NWF$('#'+textVal).val();

var clientContext = new SP.ClientContext.get_current();

var oList = clientContext.get_web().get_lists().getByTitle('Title');

var camlQuery = new SP.CamlQuery(); camlQuery.set_viewXml("<View><Query><where><Eq><FieldRef Name='Title'/><value Type='Text'>"+RequestNumber+"</value></Eq></Where></Query></View>");

window.collListItem = oList.getItems(camlQuery);

clientContext.load(collListItem);

clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

}

function onQuerySucceeded(sender, args)

{

if(collListItem)

{

var title = '';

var ClientDetails = NWF$('#'+textVal).val();

var listItemEnumerator = collListItem.getEnumerator();

while (listItemEnumerator.moveNext())

{

var oListItem = listItemEnumerator.get_current();

title = oListItem.get_item('Title');

if(title == ASC Client Lookup)

{

alert('A duplicate ASC Client Name exists');

}

}

}

}

function onQueryFailed(sender, args)

{

alert(Please specify a different Client Name.');

}

 My lists are configured as follow:

• Client Details

• Client Details -  Form

• ASC Client Names - List

function findDuplicateItems()

{

var titleVal = NWF$('#'+titleVal).val();

var clientContext = new SP.ClientContext.get_current();

var oList = clientContext.get_web().get_lists().getByTitle('ASC Client Names');

var camlQuery = new SP.CamlQuery(); camlQuery.set_viewXml("<View><Query><where><Eq><FieldRef Name='Title'/><value Type='Text'>"+titleVal+"</value></Eq></Where></Query></View>");

window.collListItem = oList.getItems(camlQuery);

clientContext.load(collListItem);

clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

}

function onQuerySucceeded(sender, args)

{

if(collListItem)

{

var title = '';

var titleVal = NWF$('#'+titleVal).val();

var listItemEnumerator = collListItem.getEnumerator();

while (listItemEnumerator.moveNext())

{

var oListItem = listItemEnumerator.get_current();

title = oListItem.get_item('Title');

if(title == titleVal)

{

alert('A duplicate ASC Client Name exists');

return false;

}

}

}

}

function onQueryFailed(sender, args)

{

alert(Please specify a different Client Name.');

}

And insert this code in form settings and for 'Title' field settings name Client ID variable as 'textVal'

Add below in save button client click.

would you mind to explain your setup with two lists where second one (List holds just client names?

if you made 'Client Name' unique in field's setting ('Enforce unique values' option) in List A, you wouldn't need List B at all, and likewise you wouldn't need at all any special validation and complex scripting. Uniqueness would be enforced by sharepoint itself.

Good day Marian,

List B (ASC Client Names) is part of the same site and currently holds information (text values) which is extracted from a SQL table.

I understand and agree with you that I can remove the 'Enforce unique values' option form the "Title" column in List A (Client Details) as the value entered here will be detirmined by the names already part of List B.

All I require is a lookup from the "Title" column in the "Client Details" List to the "Client Name" column in the "ASC Client Names" List and provide me with a message advising that the value I have entered in the "Title" column is already part of the "ASC Client Names" List if true.

1. straight to your question/problem:

I'd recommend to use lookup() function as mentioned by Lakshmi Narayana C‌ in his first option.

you do not need to complicate things with javascript at all for what you need.

assume you have following fields

listA - NewClientName connected to NewClientNameNamedControl form control

ListB - ClientName

then your validation formula might look like

IsNullOrEmpty(lookup('ListB','ClientName',NewClientNameNamedControl,'ClientName'))‍‍

2. just for my curiosity...

do you upload new clients from sharepoint back to SQL database? which system of these two is "the master" for maintaining client's data? can both insert new clients?

to be honest, I do not know details of our setup, but "feel in bones" several potential (serious) problems