Hi team,
Hoping to understand the possibility of using the OneDrive Excel integration to check for matches against two text fields in my form.
User is provisioned a unique ID and PIN prior to being given access to the form.
Unique ID and PIN stored in OneDrive Excel
Two validations requited. Check unique ID exists in Excel and check PIN matches unique ID. Is this possible?
Hello @James_Pownall,
Welcome to the Plumsail Community!
Yes, this is possible. For this, you need to configure a data source for a Drop Down field and add some code.
The Drop Down will be used to connect to the OneDrive Excel file. Find more information here.
Select the Pin column as a Text column, and ID as a Value Column.
Then, add the code below to add validators to the fields. Note that the code only works if the ID is unique.
fd.rendered(function(){
//hide drop down field on form load
$(fd.field('DropDown1').$parent.$el).hide();
//add validator to the ID field
fd.field('ID').addValidator({
name: 'ID validator',
error: 'ID is invalid',
validate:function(value){
var data = fd.field('DropDown1').widget.dataSource.data();
//check if the ID exist
return data.some(i => i.value === value);
}
});
//add validator to the PIN field
fd.field('PIN').addValidator({
name: 'PIN validator',
error: 'PIN is invalid',
validate:function(value){
var data = fd.field('DropDown1').widget.dataSource.data();
//filter data by ID
var pin = data.filter(j => j.value === fd.field('ID').value)[0].text
//check if the PIN is valid
if(value == pin) {
return true
}
return false
}
});
});