Hello,
when I'm adding a new item in my sharepoint list, I want to check if this record already exist inside and if it's already exist then I want to stop the insert and appear a warning message.
How I can realize this?
Thank you.
Dear @stefano.mazzi,
Do you want to check the title or some other field for duplicates? You can always use this setting for a column to prevent duplicate values:
Hi @Nikita_Kurguzov ,
thank you for your supporto.
I forgot to indicate an important detail.
I need to check if the the record already exist based on more fields.
So I need to use a JS with before save function I think.
Could you help me to have the structure of the JavaScript?
Thank you.
Dear @stefano.mazzi,
Sure, we can, but can you tell us more? How many fields? What's their type - text, lookup, etc.?
Should they all be unique or do you only want to detect when all of these fields match? Let us know what exactly, and we'll try to help with the code.
I would recommend not running it in beforeSave, but instead to replace the Save button function with a check, which would trigger save only if your condition is met, and will give error otherwise.
I have lookup, choice, text and number fields.
If all of these fields match then I don't have to save new data but I want a warning message popup.
Below an example of my code:
if (fd.field('Record_x0020_Type').value == 'CTYPE') {
const r = sp.web.lists.getByTitle("PR Matrix").getItemsByCAMLQuery({
ViewXml: '<View><Query><Where><And><Eq><FieldRef Name="Record_x0020_Type" /><Value Type="Choice">CTYPE</Value></Eq> <Eq><FieldRef Name="Company" /><Value Type="Text">IT1</Value></Eq><Eq><FieldRef Name="Company_x0020_Type" /><Value Type="Text">1</Value></Eq></And></Where></Query></View>',
});
console.log("Record found", r);
}
Thank you.
Hi @Nikita_Kurguzov
I have problem to run getItemsByCAMLQuery (async) and fd.validators.push (sync), so I enter everytime in loop.
My goal is that I have to save new data only if CAML query find nothing inside the sharepoint list, otherwise if the record already exist I want a warning message.
How I can achive this?
fd.validators.push({
name: 'MyCustomValidator',
error: "Record must be unique in order to insert as new",
validate: function(value) {
isFormValid = false;
validated = false;
checkElement();
if(validated){
console.log("form validated, return formvalid", isFormValid);
return isFormValid;
}
console.log("form not Validated");
return false;
}
});
function checkElement(){
var RecordType=fd.field('Record_x0020_Type').value;
var Company=fd.field('Company').value;
var CompanyType=fd.field('Company_x0020_Type').value;
console.log("RecordType",RecordType);
console.log("Company",Company.LookupValue);
console.log("CompanyType",CompanyType);
//var View="<View><Query><Where><And><Eq><FieldRef Name='Record_x0020_Type' /><Value Type='Choice'>"+RecordType+"</Value></Eq><And><Eq><FieldRef Name='Company' /><Value Type='Lookup'>"+Company.LookupValue+"</Value></Eq><Eq><FieldRef Name='Company_x0020_Type' /><Value Type='Text'>"+CompanyType+"</Value></Eq></And></And></Where></Query></View>"
//console.log("View",View);
// DEBUG
if (RecordType == 'CTYPE') {
const ExistingItem = sp.web.lists.getByTitle("PR Matrix").getItemsByCAMLQuery({
ViewXml: "<View><Query><Where><And><Eq><FieldRef Name='Record_x0020_Type' /><Value Type='Choice'>"+RecordType+"</Value></Eq><And><Eq><FieldRef Name='Company' /><Value Type='Lookup'>"+Company.LookupValue+"</Value></Eq><Eq><FieldRef Name='Company_x0020_Type' /><Value Type='Text'>"+CompanyType+"</Value></Eq></And></And></Where></Query></View>",
}).then(result => {
console.log("Record Found", result);
var length=result.length
console.log("ExistingItem Length", length);
if (length > 0) {
console.log("Returning false");
isFormValid = false;
validated = true
}
else{
console.log("Returning true");
isFormValid = true;
validated = true;
}
});
}
else{
isFormValid = true;
validated = true;
}
}Dear @stefano.mazzi,
You can try something like this:
var itemExists = false;
fd.spRendered(function(){
//change click function
fd.toolbar.buttons[0].click = function(){
var filter = "Company_x0020_Type eq '" + fd.field('Company_x0020_Type').value + "'";
filter += "and Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Company/Id eq " + fd.field('Company').value.LookupId;
pnp.sp.web.lists.getByTitle("PR Matrix").items.filter(filter).getAll().then(function(items){
if(items.length > 0){
itemExists = true;
fd.isValid;
}
else{
itemExists = false;
fd.save();
}
});
}
fd.validators.push({
name: 'ItemExistsValidator',
error: "Item already exists",
validate: function(value) {
return !itemExists;
}
});
});@Nikita_Kurguzov Seems to works great!
Now I have to insert last step.
The CAML query must be different based on the field Record Type
if (fd.field('Record_x0020_Type').value == 'CTYPE') {
}
else if (fd.field('Record_x0020_Type').value == 'APP') {
}
else if (fd.field('Record_x0020_Type').value == 'GOA') {
}
Here the CAML query parameters.
###############
#### CTYPE ####
###############
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='Record_x0020_Type' />
<Value Type='Choice'>CTYPE</Value>
</Eq>
<And>
<Eq>
<FieldRef Name='Company' />
<Value Type='Lookup'>IT1</Value>
</Eq>
<Eq>
<FieldRef Name='Company_x0020_Type' />
<Value Type='Text'>1</Value>
</Eq>
</And>
</And>
</Where>
</Query>
###############
##### APP #####
###############
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='Record_x0020_Type' />
<Value Type='Choice'>APP</Value>
</Eq>
<And>
<Eq>
<FieldRef Name='Company' />
<Value Type='Lookup'>IT1</Value>
</Eq>
<Eq>
<FieldRef Name='Cost_x0020_Centre_x0020_Code' />
<Value Type='Lookup'>1100</Value>
</Eq>
</And>
</And>
</Where>
</Query>
###############
##### GOA #####
###############
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='Record_x0020_Type' />
<Value Type='Choice'>APP</Value>
</Eq>
<And>
<Eq>
<FieldRef Name='Company' />
<Value Type='Lookup'>IT1</Value>
</Eq>
<And>
<Eq>
<FieldRef Name='Executive_x0020_Role' />
<Value Type='Lookup'>CEO</Value>
</Eq>
<And>
<Eq>
<FieldRef Name='Investment_x0020_Type' />
<Value Type='Lookup'>IT</Value>
</Eq>
<Eq>
<FieldRef Name='Budget' />
<Value Type='Boolean'>True</Value>
</Eq>
</And>
</And>
</And>
</And>
</Where>
</Query>
How I can optimize the script?
Thank you very much for your support.
@Nikita_Kurguzov
Could be something like this?
var itemExists = false;
fd.spRendered(function () {
function ShowFieldsByRecordType() {
// Show field Record Type Empty
$(fd.field('Record_x0020_Type').$parent.$el).show();
//Hide others fields
$(fd.field('Company').$parent.$el).hide();
$(fd.field('Company_x0020_Type').$parent.$el).hide();
$(fd.field('Cost_x0020_Centre_x0020_Code').$parent.$el).hide();
$(fd.field('Manager_x0020_Cost_x0020_Centre').$parent.$el).hide();
$(fd.field('Director_x0020_Cost_x0020_Centre').$parent.$el).hide();
$(fd.field('Site_x0020_Head_x002d_Plant_x002').$parent.$el).hide();
$(fd.field('Executive').$parent.$el).hide();
$(fd.field('CEO').$parent.$el).hide();
$(fd.field('Group_x0020_Purchase_x0020_Manag').$parent.$el).hide();
$(fd.field('Local_x0020_Purchase_x0020_Manag').$parent.$el).hide();
$(fd.field('Executive_x0020_Role').$parent.$el).hide();
$(fd.field('Investment_x0020_Type').$parent.$el).hide();
$(fd.field('Budget').$parent.$el).hide();
$(fd.field('Amount_x0020_Boundary').$parent.$el).hide();
if (fd.field('Record_x0020_Type').value == 'CTYPE') {
// Show field CTYPE
$(fd.field('Company').$parent.$el).show();
fd.field('Company').required = true;
$(fd.field('Company_x0020_Type').$parent.$el).show();
fd.field('Company_x0020_Type').required = true;
}
else if (fd.field('Record_x0020_Type').value == 'APP') {
// Show field APP
$(fd.field('Company').$parent.$el).show();
fd.field('Company').required = true;
$(fd.field('Cost_x0020_Centre_x0020_Code').$parent.$el).show();
fd.field('Cost_x0020_Centre_x0020_Code').required = true;
$(fd.field('Manager_x0020_Cost_x0020_Centre').$parent.$el).show();
fd.field('Manager_x0020_Cost_x0020_Centre').required = true;
$(fd.field('Director_x0020_Cost_x0020_Centre').$parent.$el).show();
fd.field('Director_x0020_Cost_x0020_Centre').required = true;
$(fd.field('Site_x0020_Head_x002d_Plant_x002').$parent.$el).show();
fd.field('Site_x0020_Head_x002d_Plant_x002').required = true;
$(fd.field('Executive').$parent.$el).show();
fd.field('Executive').required = true;
$(fd.field('Executive_x0020_Role').$parent.$el).show();
fd.field('Executive_x0020_Role').required = true;
$(fd.field('CEO').$parent.$el).show();
fd.field('CEO').required = true;
$(fd.field('Group_x0020_Purchase_x0020_Manag').$parent.$el).show();
fd.field('Group_x0020_Purchase_x0020_Manag').required = true;
$(fd.field('Local_x0020_Purchase_x0020_Manag').$parent.$el).show();
fd.field('Local_x0020_Purchase_x0020_Manag').required = true;
}
else if (fd.field('Record_x0020_Type').value == 'GOA') {
// Show field GOA
$(fd.field('Company').$parent.$el).show();
fd.field('Company').required = true;
$(fd.field('Executive_x0020_Role').$parent.$el).show();
fd.field('Executive_x0020_Role').required = true;
$(fd.field('Investment_x0020_Type').$parent.$el).show();
fd.field('Investment_x0020_Type').required = true;
$(fd.field('Budget').$parent.$el).show();
fd.field('Budget').required = false;
$(fd.field('Amount_x0020_Boundary').$parent.$el).show();
fd.field('Amount_x0020_Boundary').required = true;
}
}
// Calling ShowFieldsByRecordType when the Record Type value changes
fd.field('Record_x0020_Type').$on('change', ShowFieldsByRecordType);
// Calling ShowFieldsByRecordType on form loading
ShowFieldsByRecordType();
});
fd.spRendered(function(){
//change click function
fd.toolbar.buttons[0].click = function(){
if (fd.field('Record_x0020_Type').value == 'CTYPE') {
var filter = "Company_x0020_Type eq '" + fd.field('Company_x0020_Type').value + "'";
filter += "and Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Company/Id eq " + fd.field('Company').value.LookupId;
}
else if (fd.field('Record_x0020_Type').value == 'APP') {
var filter = "Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Company/Id eq '" + fd.field('Company').value.LookupId + "'";
filter += "and Cost_x0020_Centre_x0020_Code eq " + fd.field('Cost_x0020_Centre_x0020_Code').value.LookupId;
}
else if (fd.field('Record_x0020_Type').value == 'GOA') {
var filter = "Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Budget eq '" + fd.field('Budget').value + "'";
filter += "and Company/Id eq '" + fd.field('Company').value.LookupId + "'";
filter += "and Executive_x0020_Role eq '" + fd.field('Executive_x0020_Role').value.LookupId + "'";
filter += "and Investment_x0020_Type eq " + fd.field('Investment_x0020_Type').value.LookupId;
}
pnp.sp.web.lists.getByTitle("PR Matrix").items.filter(filter).getAll().then(function(items){
if(items.length > 0){
itemExists = true;
fd.isValid;
}
else{
itemExists = false;
fd.save();
}
});
}
fd.validators.push({
name: 'ItemExistsValidator',
error: "Item already exists",
validate: function(value) {
return !itemExists;
}
});
});Dear @stefano.mazzi,
Sure, but I would recommend initializing the filter variable outside first:
fd.spRendered(function(){
//change click function
fd.toolbar.buttons[0].click = function(){
var filter = '';
if (fd.field('Record_x0020_Type').value == 'CTYPE') {
filter = "Company_x0020_Type eq '" + fd.field('Company_x0020_Type').value + "'";
filter += "and Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Company/Id eq " + fd.field('Company').value.LookupId;
}
else if (fd.field('Record_x0020_Type').value == 'APP') {
filter = "Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Company/Id eq '" + fd.field('Company').value.LookupId + "'";
filter += "and Cost_x0020_Centre_x0020_Code eq " + fd.field('Cost_x0020_Centre_x0020_Code').value.LookupId;
}
else if (fd.field('Record_x0020_Type').value == 'GOA') {
filter = "Record_x0020_Type eq '" + fd.field('Record_x0020_Type').value + "'";
filter += "and Budget eq '" + fd.field('Budget').value + "'";
filter += "and Company/Id eq '" + fd.field('Company').value.LookupId + "'";
filter += "and Executive_x0020_Role eq '" + fd.field('Executive_x0020_Role').value.LookupId + "'";
filter += "and Investment_x0020_Type eq " + fd.field('Investment_x0020_Type').value.LookupId;
}
pnp.sp.web.lists.getByTitle("PR Matrix").items.filter(filter).getAll().then(function(items){
if(items.length > 0){
itemExists = true;
fd.isValid;
}
else{
itemExists = false;
fd.save();
}
});
}
fd.validators.push({
name: 'ItemExistsValidator',
error: "Item already exists",
validate: function(value) {
return !itemExists;
}
});
});