javascriptsharepointcaml

Sharepoint: Lookup against a large list with CAML Query

I have a SharePoint List (list_dls) which has all the Email addresses. The list has a single column with Field name "Title". I need to validate that the Item in the "LookupField" is contained in the 'list_dls'. The list has over 5000 emails, hence I will need to do a CAML query. The script does not seem to find the item. Any ideas to get it to work would be really appreciated.

Here is the code.

<input type='button' value='get Lists' onclick="PopulateLookupField();"/>
<p id="demo"></p>
<script language="javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script language="javascript" type="text/javascript">

var LookupField = 'lnam@xyz.com';

function PopulateLookupField() 
{
    var clientContext = new SP.ClientContext.get_current();
    var LookupList = clientContext.get_web().get_lists().getByTitle('list_dls');
    var camlQuery_list = new SP.CamlQuery();
    camlQuery_list.ViewXml = @"<View Scope='RecursiveAll'><ViewFields><FieldRef Name='ID'/><FieldRef Name='Title'/></ViewFields><RowLimit Paged='TRUE'>5000</RowLimit></View>";
    LookupList<ListItem> allListItems = new LookupList<ListItem>();
    do
    {
        var listItemCollection = LookupList.GetItems(camlQuery_list);
        clientContext.Load(listItemCollection);
        clientContext.ExecuteQuery();
        allListItems.AddRange(listItemCollection);
        var ItemFound = listItemCollection.Where(item => item.FieldValues["Title"].ToString().Contains(LookupField); 
        if(ItemFound !== null){break;}
        camlQuery_list.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;
    } while (camlQuery_list.ListItemCollectionPosition != null);
    clientContext.load(ItemFound);
    alert("ItemFound = " + ItemFound);
}
</script>

Solution

  • First, go to the SharePoint list "list_dls" and create a new index for Title column (maybe is already done)

    https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0

    Also, you can search immediately with caml query in a list

    camlQuery_list.ViewXml = $"<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>{LookupField}</Value></Eq></Where></Query></View>";

ListItemCollection collection = LookupList.GetItems(camlQuery_list);

context.Load(collection);
context.ExecuteQuery();

if (collection.Count > 0)
{
    var yourItem = collection[0];
}