sharepoint data entry validation

Question

I want my data entry to not be on a list of exempted entries. I want to use a list of entries. But am open to suggestion Tested in excel by placing exceptions in a column. Extracted to sharepoint a small table to create a library of entries

error message I enter reads "This Job Code is exempt from this process"

in sharepoint validation i enter this formula... IF([Job Code]=[Exceptions],"true","False") which isnt what i want however;

[exceptions] after i save the formula converts to this..

IF([Job Code]=Exceptions,"true","False")

this didnt work correctly so i reversed the outcomes IF([Job Code]=[exceptions],"False","True") which is the result i want, if the number exists in the exceptions collumn i dont want them to be able to save!

In excel i used this formula...

=ISNUMBER(MATCH(A9,Table1[Exceptions],0)) 

this does exactly what i want...but i want this to happen in sharepoint and not validate the entry...

i have tried other formulas with no luck, i keep getting a syntax error when saving... examples are...

=IF(SUMPRODUCT(--(A2:F2=A3)),TRUE,FALSE) this works but i dont know how to convert the single cell and range/array into sharepoint Any help is greatly appreciated!

Answer 1

Unfortunately, the Validation Setting formula is only applied to specific fields on the same ROW.

Check The Supported and Unsupported Columns In SharePoint Calculated Column Formula

Therefore, you can't search for column values and validate against these values in the SharePoint validation setting or calculated field like Excel formula!

Answer 2

We need using some code to achieve it. The following code for your reference, add the code into script editor web part into new/edit form page.

<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
function PreSaveAction() { 
    var jobCode = $("input[title='Job Code']").val();

    if(isJobCodeExempt(jobCode)){
        $("input[title='Job Code']").after('<br/><span class="ms-formvalidation ms-csrformvalidation" id="Error_JobCode"><span role="alert">This Job Code is exempt from this process.<br></span></span>'); 
        return false;
    }   
    return true;
}
function isJobCodeExempt(jobCode){
    var flag=false;
    var listID=_spPageContextInfo.pageListId.replace("{","").replace("}","");
    $.ajax({
        url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists(guid'"+listID+"')/items?$filter=Exceptions eq "+jobCode,
        type: "GET",
        async:false,
        headers: {
            "Accept": "application/json;odata=verbose",
        },
        success: function (data) {
            if(data.d.results.length>0){                 
                flag=true;
            }

        },
        error: function (data) {
            //alert("Error");
        }
    });
    return flag;
}
</script>