Utilize a SPList as a lookup.Script includes HTML and JS. List is populated by PowerShell script Field-Org-SQL-TO-SPList.ps1 #Javascript #SharePoint

SPList-REST.html

<style>


</style>

<div class="container-fluid" id="main">

<div id="target">
	<span>Select the columns you would like returned: </span> <br>
	
<br>
<input class="col-xs-1" type="checkbox" id="hospital_Number" checked>	</input>	<span class="col-xs-2" >hospital number</span><br>
<input class="col-xs-1" type="checkbox" id="Abbreviation" >		</input>	<span class="col-xs-2" >Abbreviation</span><br>
<input class="col-xs-1" type="checkbox" id="longName" >			</input>	<span class="col-xs-2" >Long Name</span><br>
<input class="col-xs-1" type="checkbox" id="shortName" >			</input>	<span class="col-xs-2" >short name</span><br>
<input class="col-xs-1" type="checkbox" id="city" checked>				</input>	<span class="col-xs-2" >city</span><br>
<input class="col-xs-1" type="checkbox" id="state" checked>				</input>	<span class="col-xs-2" >state</span><br>
<input class="col-xs-1" type="checkbox" id="division" >			</input>	<span class="col-xs-2" >division</span><br>
<input class="col-xs-1" type="checkbox" id="region" >			</input>	<span class="col-xs-2" >region</span><br>
<input class="col-xs-1" type="checkbox" id="market" checked>			</input>	<span class="col-xs-2" >market</span><br>
	<br>
<span>Please enter a hospital number</span>
<input id="hospNumDOM" value="123" type="number">
<!-- <button onclick="doTheThing()">asd</button> -->
<br>
<br>
<span id="outputTitle">Output</span>
<br>
<br>
	<table id="table">
		<tr>
			<th class="col-xs-1">Column1</th>
			<th class="col-xs-1">Column2</th>
			<th class="col-xs-1">Column3</th>
			<th class="col-xs-1">Column4</th>
			<th class="col-xs-3">Column5</th>
			<th class="col-xs-2">Column6</th>
			<th class="col-xs-1">Column7</th>
			<th class="col-xs-1">Column8</th>
			<th class="col-xs-1">Column9</th>
		</tr>
		<tr>
			<td class="col-xs-1"  id="Col1"></td>
			<td class="col-xs-1"  id="Col2"></td>
			<td class="col-xs-1"  id="Col3"></td>
			<td class="col-xs-1"  id="Col4"></td>
			<td class="col-xs-3"  id="Col5"></td>
			<td class="col-xs-2"  id="Col6"></td>
			<td class="col-xs-1"  id="Col7"></td>
			<td class="col-xs-1"  id="Col8"></td>
			<td class="col-xs-1"  id="Col9"></td>
		</tr>
	</table>
</div>
<br>
<br>
<span id="url"></span>



</div>




<script type="text/Javascript">

var columnSelect ;

function readSelectedColumns()
{
	var selectedColumns = [] ;
	var columns = "" ;
	if (jQuery("#hospital_Number").is(":checked") )
	{
		selectedColumns.push("Title");
	}
	if (jQuery("#Abbreviation").is(":checked") )
	{
		selectedColumns.push("Hospital_x0020_Abbreviation");
	}
	if (jQuery("#longName").is(":checked") )
	{
		selectedColumns.push("Hospital_x0020_Long_x0020_Name");
	}
	if (jQuery("#shortName").is(":checked") )
	{
		selectedColumns.push("Hospital_x0020_Short_x0020_Name");
	}
	if (jQuery("#city").is(":checked") )
	{
		selectedColumns.push("City");
	}
	if (jQuery("#state").is(":checked") )
	{
		selectedColumns.push("State");
	}
	if (jQuery("#division").is(":checked") )
	{
		selectedColumns.push("Hospital_x0020_Division");
	}
	if (jQuery("#region").is(":checked") )
	{
		selectedColumns.push("Hospital_x0020_Region");
	}
	if (jQuery("#market").is(":checked") )
	{
		selectedColumns.push("Area_x0020__x002f__x0020_Market");
	}
	for (var i = 0 ; i < selectedColumns.length ; i++ )
	{
		columns = columns + "," +selectedColumns[i];
	}
	//aggregate the selected columns
	columnSelect = "$select=" + columns ;
	
	//Remove the extra comma
	columnSelect = columnSelect.replace('$select=,','$select=') ;
	
	doTheThing();
}

function doTheThing()
{
	var url = "<url to site>" ;
	var listName = "fieldOrg" ;
	var hosp_DOM = "a" ;
	if (jQuery("#hospNumDOM").val().length == 3)
		{
			hosp_DOM = "0" + jQuery("#hospNumDOM").val() ;
		} 
		else if (jQuery("#hospNumDOM").val().length == 2)
		{
			hosp_DOM = "00" + jQuery("#hospNumDOM").val() ;
		}
		else
		{
		hosp_DOM  = jQuery("#hospNumDOM").val() ;
		}
	processListItems(url, listName, hosp_DOM) ;
}


function processListItems(url, listname, hosp_value) 
{
	///_api/web/lists/getbytitle('fieldOrg')/items?$select=Title,City,State&$filter=Title eq '0123'
    // Executing our items via an ajax request
    jQuery.ajax({
		url: url + "_api/web/lists/getbytitle('" + listname + "')/items?'" + columnSelect + "'&$filter=Title eq '" + hosp_value + "'" ,
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
	success: function(data){
					jQuery("#Col1").text(data.d.results[0].Title) ;
					jQuery("#Col2").text(data.d.results[0].City) ;
					jQuery("#Col3").text(data.d.results[0].State) ;
					jQuery("#Col4").text(data.d.results[0].Hospital_x0020_Abbreviation) ;
					jQuery("#Col5").text(data.d.results[0].Hospital_x0020_Long_x0020_Name) ;
					jQuery("#Col6").text(data.d.results[0].Hospital_x0020_Short_x0020_Name) ;
					jQuery("#Col7").text(data.d.results[0].Hospital_x0020_Division) ;
					jQuery("#Col8").text(data.d.results[0].Hospital_x0020_Region) ;
					jQuery("#Col9").text(data.d.results[0].Area_x0020__x002f__x0020_Market) ;
					jQuery("#url").text(url + "_api/web/lists/getbytitle('" + listname + "')/items?'" + columnSelect + "'&$filter=Title eq '" + hosp_value + "'" ) ;
        },
        error: function (data) {
            alert(data);
        }
    });
}

function applyEventListener()
{
	jQuery(":checkbox").on('focus input change',readSelectedColumns) ;
	jQuery("#hospNumDOM").on('focus input change',readSelectedColumns) ;
	readSelectedColumns();
}

_spBodyOnLoadFunctionNames.push("applyEventListener");

</script>