Hi Plumsail!
I have a requirement where I need to check if a value exists in another field, specifically a lookup field and if it exists, then I want to return true. If not, then I want to return false with an error message displaying that the value does not exist.
As you can see from the image below, I have two Cost Centre fields. The 'Cost_Centre' field is a Lookup field and 'Cost Centre -testing' is a single line text field.
I will hide the Lookup field using CSS but keep the testing field visible for users to enter a valid Cost centre code. I want this field to lookup against the 'Cost_Centre' field and if a value is found, then the field should return true, otherwise it should return false.
The use-case for this is to add a visual cue for users so they are aware that the Cost-Centre that they have entered is in fact a valid one.
[UPDATE]
I have created this code but I'm not sure how to test the condition with the Lookup field..
fd.spRendered(function() {
fd.field('Cost_centre_test').validators.push({
name: '',
error: 'Sorry, this Cost Centre does not exist.',
validate: function(value) {
if(fd.field('Cost_centre_test').value = fd.field('Cost_Centre').LookupValue){
//Applies Colour to the field if Correct input
$($(fd.field('Assignment_Number').$el).find('input')[0]).attr('style', 'border-color: #6CEE5A; box-shadow: 0 0 5px #6CEE5A;');
return true; // Validation succeded
}
// You land here if it did not succeed
this.error = 'Sorry, this Cost Centre does not exist.';
//Applies Colour to the field if Incorrect input
$($(fd.field('Cost_centre_test').$el).find('input')[0]).attr('style', 'border-color:#ff0000; box-shadow: 0 0 5px #ff0000; Color:#ff0000;');
return false;
}
});
});
Dear @DryChips,
You can check if the value exists or not without the Lookup field, it won't be of much help here. You'll need to send a pnpjs request to the source list, and filter values from the list with Cost_Centre value, and if there are any items returned - then it exists.
Something similar is described here, though it overwrites the save button functionality instead - Sharepoint List : How to check if data already exist - #7 by Nikita_Kurguzov
You can write a similar code to work on value change in Cost_Centre field.
1 Like
Ahhh thanks Nikita, sound tricky to pull off. I'll see what I can do and get back to you.
Hi Nikita,
this is what I have created. It doesn't work but I think I'm on the right track..
fd.spRendered(function(){
//Queries Cost Centre Column in SP list
pnp.sp.web.lists.getByTitle("Org Hierarchy").items.filter(Cost Centre).getAll().then(function(items){
if (fd.field('Cost_centre_test').value = fd.field('Cost_Centre').items){
$($(fd.field('Cost_centre_test').$el).find('input')[0]).attr('style', 'border-color: #6CEE5A; box-shadow: 0 0 5px #6CEE5A;');
return true;
}
// You land here if it did not succeed
this.error = 'Sorry, this Cost Centre does not exist.';
//Applies Colour to the field if Incorrect input
$($(fd.field('Cost_centre_test').$el).find('input')[0]).attr('style', 'border-color:#ff0000; box-shadow: 0 0 5px #ff0000; Color:#ff0000;');
return false;
});
});
Dear @DryChips,
Close, but not quite. Try it like this:
var ccexists = false;
fd.spRendered(function(){
fd.validators.push({
name: 'CostCentreExistsValidator',
error: "Cost Centre doesn't exist",
validate: function() {
return ccexists;
}
});
fd.field('Cost_centre_test').$on('change', function(value){
validateCC(value);
});
});
function validateCC(value){
var filter = "Title eq '" + value + "'";
pnp.sp.web.lists.getByTitle("Org Hierarchy").items.filter(filter).getAll().then(function(items){
if(items.length > 0){
ccexists = true;
}
else{
ccexists = false;
fd.isValid;
}
});
}
1 Like
Hi Nikita,
thanks so much!
Could you explain what needs to be changed in the code above? Do I need to reference anything in the form or SharePoint list?
The code is validating the cost centre -testing field but it doesn't recognise the cost centre value that I have put in the field. It spits back an error 'Cost Centre doesn't exist' when it clearly does. 
Dear @DryChips,
At the very least, you need to change the filter to match the column's Internal Name, this is looking up the Title column:
var filter = "Title eq '" + value + "'";
Oh I see, so it should look like so:
var filter = "Cost Centre eq '" + value + "'";
var filter = "Cost Centre eq '" + CostCentre + "'";
var filter = "Cost Centre";
I have tested these three and it still doesn't recognise the Cost Centre code.
Dear @DryChips,
The column name cannot contain a space in it. You can check the actual Internal Name of the column by going to the List Settings > Cost Centre column and copying its name from URL:
It would be something like this:
var filter = "CostCentre eq '" + value + "'";
1 Like
Oh I see, here is what it looks like on my end:
var filter = "Cost_x0020_Centre eq '" + value + "'";
Is this correct?
Dear @DryChips.
Yes, the filter looks correct. If it doesn't work - some other things might need to be adjusted. Check browser's console for errors.
1 Like
Oh wow, its worked! Thank you so much Nikita! You're the best! 
1 Like
Hi Nikita, sorry to keep bothering you.
I wanted to ask, is it possible to make a message appear below the field to say "Cost-Centre not found" after the user has entered the number. Currently, the code is set to trigger when it is empty or change the field red via CSS when the value doesn't exist. I think it would be a nice a message of some sort during that cycle.
Here is my updated code:
//This code will validate the cost-centre code to a SP list
var ccexists = false;
fd.spRendered(function(){
fd.field('Cost_Centre').validators.push({
name: '',
error: "Please enter a 7 digit valid Cost Centre Code",
validate: function() {
return ccexists;
}
});
fd.field('Cost_Centre').$on('change', function(value){
validateCC(value);
});
});
//This function is doing a Lookup to the Org Hierarchy SharePoint list
function validateCC(value){
var filter = "Cost_x0020_Centre eq '" + value + "'";
pnp.sp.web.lists.getByTitle("OrgHierarchy").items.filter(filter).getAll().then(function(items){
if(items.length > 0){
$($(fd.field('Cost_Centre').$el).find('input')[0]).attr('style', 'border-color: #6CEE5A; box-shadow: 0 0 5px #6CEE5A;');
ccexists = true;
}
else{
$($(fd.field('Cost_Centre').$el).find('input')[0]).attr('style', 'border-color:#ff0000; box-shadow: 0 0 5px #ff0000; Color:#ff0000;');
ccexists = false;
}
});
};
Dear @DryChips,
You can try to validate form after setting ccexists as false:
$($(fd.field('Cost_Centre').$el).find('input')[0]).attr('style', 'border-color:#ff0000; box-shadow: 0 0 5px #ff0000; Color:#ff0000;');
ccexists = false;
fd.isValid;
1 Like
Hi Nikita,
I noticed that the lookup field is a little slow in retrieving the departments. I have to type the value a little slower than usual in order for it find the departments under the Cost Centre.
Most users who will interact with the forms will either copy and paste a Cost Centre value or type the value a little quicker. The lookup field will sometimes return false and users will come back to us complaining that they can't find their department and stuff.
How can I ensure that the lookup field works as efficiently as possible?
I read somewhere in this forum where you mentioned placing the code inside the .ready event but I'm not sure where to add this in my code...
//This function is doing a Lookup to the Org Hierarchy SharePoint list
function validateCC(value){
//validate the entered value
if ((/^[A-Za-z]\d{6}$/).test(value)) {
var filter = "Cost_x0020_Centre eq '" + value + "'";
pnp.sp.web.lists.getByTitle("OrgHierarchy").items.filter(filter).getAll().then(function(items){
if(items.length > 0){
$($(fd.field('Cost_Centre').$el).find('input')[0]).attr('style', 'border-color: #6CEE5A; box-shadow: 0 0 5px #6CEE5A;');
ccexists = true;
}
});
}
else{
$($(fd.field('Cost_Centre').$el).find('input')[0]).attr('style', 'border-color:#ff0000; box-shadow: 0 0 5px #ff0000; Color:#ff0000;');
ccexists = false;
}
};
//This code will auto-populate all the Org Hierarchy fields v2
function populateFields() {
//get CostCenter
var costCentre = fd.field('Cost_Centre').value;
//get departments
var orgL7 = fd.field('Department').value.LookupValue;
//make sure both values are selected
if(costCentre && orgL7) {
var filter = encodeURIComponent("Cost_x0020_Centre eq '" + costCentre + "' and Org_x0020_L7 eq '" + orgL7 + "'");
//filter list and get item values
pnp.sp.web.lists.getByTitle('OrgHierarchy').items.filter(filter).get().then(function(item){
fd.field('Speciality').value = item[0].Id;
fd.field('Directorate').value = item[0].Id;
fd.field('Division').value = item[0].Id;
fd.field('Hospital').value = item[0].Id;
});
}
}
//call function on field change
fd.field('Cost_Centre').$on('change', populateFields);
fd.field('Department').$on('change', populateFields);
Hello @DryChips,
How do you filter Departments by Cost Center? I don't see any code related to that.
Please share how do you apply the filtration for the lookup field.
Does the lookup field point to the large list?
Hi Margarita,
This is the code that does the filtering:
//This code will auto-populate all the Org Hierarchy fields v2
function populateFields() {
//get CostCenter
var costCentre = fd.field('Cost_Centre').value;
//get departments
var orgL7 = fd.field('Department').value.LookupValue;
//make sure both values are selected
if(costCentre && orgL7) {
var filter = encodeURIComponent("Cost_x0020_Centre eq '" + costCentre + "' and Org_x0020_L7 eq '" + orgL7 + "'");
//filter list and get item values
pnp.sp.web.lists.getByTitle('OrgHierarchy').items.filter(filter).get().then(function(item){
fd.field('Speciality').value = item[0].Id;
fd.field('Directorate').value = item[0].Id;
fd.field('Division').value = item[0].Id;
fd.field('Hospital').value = item[0].Id;
});
}
}
Hello @DryChips,
Thank you for clarification!
PnP request need time for getting the list data, especially from large lists. You can add the Cost Center input validation before getting executing the PnP request, the same way you in the validateCC() function.
function populateFields() {
//get CostCenter
var costCentre = fd.field('Cost_Centre').value;
//get departments
var orgL7 = fd.field('Department').value.LookupValue;
//make sure both values are selected and the cost center is in valid format
if((/^[A-Za-z]\d{6}$/).test(costCentre) && orgL7) {
var filter = encodeURIComponent("Cost_x0020_Centre eq '" + costCentre + "' and Org_x0020_L7 eq '" + orgL7 + "'");
//filter list and get item values
pnp.sp.web.lists.getByTitle('OrgHierarchy').items.filter(filter).get().then(function(item){
fd.field('Speciality').value = item[0].Id;
fd.field('Directorate').value = item[0].Id;
fd.field('Division').value = item[0].Id;
fd.field('Hospital').value = item[0].Id;
});
}
}
