none
Generate Dynamic CAML query with Comparison Operators ="Contains" and Logical Join ="Or" on All SharePoint Custom List Columns RRS feed

  • Question

  • Hi,

    I am having a list called "Users" which contains following types of columsn:

    Single line of text

    Multiple lines of text

    Choice (allow multiple selections)

    Choice (single selection drop down menu)

    Date and Time

    Multiple lines of text (Enhanced rich text)

    Yes/No (Check box)

    I want to create dynamic CAML query on all list columns with Comparison Operators ="Contains" and Logical Join ="Or".

    I have created CAML query like below and it's returning records: but not correct records

    <Query>
      <OrderBy>
        <FieldRef Name='PDate' Ascending='False' />
      </OrderBy>
      <Where>
        <Or>
          <Or>
            <Or>
              <Or>
                <Or>
                  <Or>
                    <Or>
                      <Or>
                        <Or>
                          <Or>
                            <Or>
                              <Or>
                                <Or>
                                  <Or>
                                    <Or>
                                      <Or>
                                        <Or>
                                          <Or>
                                            <Or>
                                              <Or>
                                                <Or>
                                                  <Or>
                                                    <Or>
                                                      <Or>
                                                        <Or>
                                                          <Or>
                                                            <Or>
                                                              <Or>
                                                                <Or>
                                                                  <Or>
                                                                    <Contains>
                                                                      <FieldRef Name='Title' />
                                                                      <Value  Type='Text'>adipiscing</Value>
                                                                    </Contains>
                                                                    <Contains>
                                                                      <FieldRef Name='BusinessList' />
                                                                      <Value  Type='Choice'>adipiscing</Value>
                                                                    </Contains>
                                                                  </Or>
                                                                  <Contains>
                                                                    <FieldRef Name='StrategyList' />
                                                                    <Value  Type='Choice'>adipiscing</Value>
                                                                  </Contains>
                                                                </Or>
                                                                <Contains>
                                                                  <FieldRef Name='OperationsGeneralList' />
                                                                  <Value  Type='Choice'>adipiscing</Value>
                                                                </Contains>
                                                              </Or>
                                                              <Contains>
                                                                <FieldRef Name='ConsultingList' />
                                                                <Value  Type='Choice'>adipiscing</Value>
                                                              </Contains>
                                                            </Or>
                                                            <Contains>
                                                              <FieldRef Name='DigitalList' />
                                                              <Value  Type='Choice'>adipiscing</Value>
                                                            </Contains>
                                                          </Or>
                                                          <Contains>
                                                            <FieldRef Name='Mobility' />
                                                            <Value  Type='Choice'>adipiscing</Value>
                                                          </Contains>
                                                        </Or>
                                                        <Contains>
                                                          <FieldRef Name='TechnologyLsit' />
                                                          <Value  Type='Choice'>adipiscing</Value>
                                                        </Contains>
                                                      </Or>
                                                      <Contains>
                                                        <FieldRef Name='OperationsLst' />
                                                        <Value  Type='Choice'>adipiscing</Value>
                                                      </Contains>
                                                    </Or>
                                                    <Contains>
                                                      <FieldRef Name='CorporateList' />
                                                      <Value  Type='Choice'>adipiscing</Value>
                                                    </Contains>
                                                  </Or>
                                                  <Contains>
                                                    <FieldRef Name='OGList' />
                                                    <Value  Type='Choice'>adipiscing</Value>
                                                  </Contains>
                                                </Or>
                                                <Contains>
                                                  <FieldRef Name='AnalystFirm' />
                                                  <Value  Type='Choice'>adipiscing</Value>
                                                </Contains>
                                              </Or>
                                              <Contains>
                                                <FieldRef Name='XYZ' />
                                                <Value  Type='Choice'>adipiscing</Value>
                                              </Contains>
                                            </Or>
                                            <Contains>
                                              <FieldRef Name='ERT' />
                                              <Value  Type='Choice'>adipiscing</Value>
                                            </Contains>
                                          </Or>
                                          <Contains>
                                            <FieldRef Name='HHH' />
                                            <Value  Type='Choice'>adipiscing</Value>
                                          </Contains>
                                        </Or>
                                        <Contains>
                                          <FieldRef Name='FFF' />
                                          <Value  Type='Choice'>adipiscing</Value>
                                        </Contains>
                                      </Or>
                                      <Contains>
                                        <FieldRef Name='GGG' />
                                        <Value  Type='Choice'>adipiscing</Value>
                                      </Contains>
                                    </Or>
                                    <Contains>
                                      <FieldRef Name='NNN' />
                                      <Value  Type='Text'>adipiscing</Value>
                                    </Contains>
                                  </Or>
                                  <Contains>
                                    <FieldRef Name='TTT' />
                                    <Value  Type='Text'>adipiscing</Value>
                                  </Contains>
                                </Or>
                                <Contains>
                                  <FieldRef Name='RRR' />
                                  <Value  Type='Text'>adipiscing</Value>
                                </Contains>
                              </Or>
                              <Contains>
                                <FieldRef Name='CCC' />
                                <Value  Type='Text'>adipiscing</Value>
                              </Contains>
                            </Or>
                            <Contains>
                              <FieldRef Name='CAT' />
                              <Value  Type='Text'>adipiscing</Value>
                            </Contains>
                          </Or>
                          <Contains>
                            <FieldRef Name='IGE' />
                            <Value  Type='Text'>adipiscing</Value>
                          </Contains>
                        </Or>
                        <Contains>
                          <FieldRef Name='NNNN' />
                          <Value  Type='Text'>adipiscing</Value>
                        </Contains>
                      </Or>
                      <Contains>
                        <FieldRef Name='PDate' />
                        <Value IncludeTimeValue='false' Type='DateTime'>adipiscing</Value>
                      </Contains>
                    </Or>
                    <Contains>
                      <FieldRef Name='POS' />
                      <Value  Type='Text'>adipiscing</Value>
                    </Contains>
                  </Or>
                  <Contains>
                    <FieldRef Name='QQQ' />
                    <Value  Type='Notes'>adipiscing</Value>
                  </Contains>
                </Or>
                <Contains>
                  <FieldRef Name='COOOY' />
                  <Value  Type='Notes'>adipiscing</Value>
                </Contains>
              </Or>
              <Contains>
                <FieldRef Name='PRS' />
                <Value  Type='Boolean'>adipiscing</Value>
              </Contains>
            </Or>
            <Contains>
              <FieldRef Name='RTG' />
              <Value  Type='Text'>adipiscing</Value>
            </Contains>
          </Or>
          <Contains>
            <FieldRef Name='REG' />
            <Value  Type='Choice'>adipiscing</Value>
          </Contains>
        </Or>
      </Where>
    </Query>

    Logic to create above CAML query is:

        var CamlQueryElements = [];
    
        function AddElement (searchText) {
                var objElement = new Object();
    	
                objElement.ComparisonOperators = "Contains";
                objElement.FieldName = "Title";
                objElement.FieldType = "Text";
                objElement.FieldValue = searchText;
                objElement.LogicalJoin = "Or";
                AnalystReport.CamlQuery.CamlQueryElements.push(objElement);
    
                objElement = null;
                objElement = new Object();
                objElement.ComparisonOperators = "Contains";
                objElement.FieldName = "BusinessList";
                objElement.FieldType = "Choice";
                objElement.FieldValue = searchText;
                objElement.LogicalJoin = "Or";
                AnalystReport.CamlQuery.CamlQueryElements.push(objElement);
    
                objElement = null;
                objElement = new Object();
                objElement.ComparisonOperators = "Contains";
                objElement.FieldName = "BusinessList";
                objElement.FieldType = "Choice";
                objElement.FieldValue = searchText;
                objElement.LogicalJoin = "Or";
                CamlQueryElements.push(objElement);
    
            }
    
        function GenerateQuery() {
    
            var queryJoin = "";
            var date ="";
            var query = "<{0}><FieldRef Name='{1}' /><Value {2} Type='{3}'>{4}</Value></{5}>";
             
            AddElement ("AmitKumar");
    
            if(CamlQueryElements.length >0 )
            {
                var itemCount = 0;
    
                //--Loop of column data ---::Start::----//
                for (var i=0; i<CamlQueryElements.length; i++)
                {
                    itemCount++;
    		
                    date ="";
                    if(CamlQueryElements[i].FieldType == "DateTime")
                    {
                        date = "IncludeTimeValue='false'";
                    }
    		 
                    queryJoin += "<" + CamlQueryElements[i].ComparisonOperators + "><FieldRef Name='" + CamlQueryElements[i].FieldName + "' /><Value " + date + " Type='" + CamlQueryElements[i].FieldType + "'>" + CamlQueryElements[i].FieldValue + "</Value></" +CamlQueryElements[i].ComparisonOperators + ">";
    
                    if (itemCount >= 2)
                    {
                        queryJoin = "<" + CamlQueryElements[i].LogicalJoin + ">" + queryJoin;
                        queryJoin += "</" + CamlQueryElements[i].LogicalJoin + ">" ;
                    }
    
                }
                //--Loop of column data ---::End::----//
    
                queryJoin = "<Where>" + queryJoin;
                queryJoin += "</Where>" ;
    
    		
    
            }
    
            return queryJoin;
    
        }

    if i will search for "MSDN" and it's returning records, even "MSDN" not present in any list column. If i am searching using List View search text box then no record coming for "MSDN".

    I tried with Search API :

    https://mysite.online.com/sites/TestSite1/_api/search/query?querytext=%27amit+AND+ListId:11812AAB-18AF-452B-B8F9-B15F21C89823%27

    It's working fine, but it's not returning all the columns, is there any way to get all the columns and sample to implement the paging.

    Please let me know, any pointers to resolve this issue.



    Thanks, Amit Kumar, LinkedIn Profile ** My Blog
    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful


    Thursday, April 13, 2017 3:44 PM

All replies

  • Hi Amit,

    It is not recommended to use “Contains” operation for querying fields with types other than String type. This is not so fast and accurate when querying list items.

    Please use Search REST API instead to query the list items and before that you need to make sure that a full has been run.

    To get all list columns, you can use “SelectProperties” to include the columns that you need.

    https://msdn.microsoft.com/en-us/library/office/jj163876.aspx?f=255&MSPPError=-2147217396

    About search paging, please refer to the links below:

    http://www.thesharepointguide.com/sharepoint-search-how-to-return-all-results/

    Best Regards,

    Victoria

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com



    Friday, April 14, 2017 8:07 AM
    Moderator
  • Hi Victoria,

    Thanks for sharing the information.

    I will try above suggested guidelines to implement the search and provide you the update. In the mean time, if you able to find any working sample, please share.


    Thanks, Amit Kumar, LinkedIn Profile ** My Blog
    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful

    Monday, April 17, 2017 6:05 AM
  • Hi Amit,

    You can use StartRow to implement paging for search results.

    Please refer to the link below:

    http://www.thesharepointguide.com/sharepoint-search-how-to-return-all-results/

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, April 17, 2017 6:30 AM
    Moderator
  • Hi Victoria,

    I checked the article and it's SERVER SIDE code, for same i tried using JSOM but it's not working on particular list 

        var searchText ="Amit";
        var objResults = [];
        function search()   
        {  
      
            try   
            {  
                console.log('Start');
                SP.SOD.registerSod('sp.search.js', "/_layouts/15/sp.search.js");  
      
                SP.SOD.executeFunc('sp.search.js', 'Microsoft.SharePoint.Client.Search.Query.KeywordQuery', function() {  
                    var clientContext = new SP.ClientContext.get_current(); 
                    var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(clientContext);  
                    keywordQuery.set_queryText(searchText + "+AND+ListId:47523633-0890-4AEA-9FE8-A667E5F63CC6")  
                    keywordQuery.RowLimit = 5;  
                    keywordQuery.EnableSorting = true;  
                    keywordQuery.SortList = "Title";  
                    keywordQuery.TrimDuplicates = true;  
      
                    var searchExecutor = new Microsoft.SharePoint.Client.Search.Query.SearchExecutor(clientContext);  
      
                    results = searchExecutor.executeQuery(keywordQuery);  
      
                    clientContext.executeQueryAsync(onQuerySuccess, onFailed);  
                });  
            }   
            catch (err)   
            {  
                //log error    
            }  
        }  
    
        function onQuerySuccess() { 
            objResults = [];
            objResults = results;
            console.log(results.m_value);
            for (i = 0; i < results.m_value.ResultTables[0].ResultRows.length; i++) { 
                console.log(results.m_value.ResultTables[0].ResultRows[i].Title + "**" + results.m_value.ResultTables[0].ResultRows[i].Path ) 
            } 
      
        } 
      
        function onFailed()
        {
            console.log('Query failed. Error:' + args.get_message());
        }
    
        search();

    I tried using AJAX POST to search using SEARCH API and below are my findings:

    var searchText ="Amit"; var objResults = []; var startRow = 0; var rowPerpage = 1; var rowLimit = 3; function search() { try { console.log('Start'); var searchURl = _spPageContextInfo.webAbsoluteUrl + "/_api/search/postquery";; var data = { 'request': { '__metadata' : {'type' : 'Microsoft.Office.Server.Search.REST.SearchRequest'}, 'Querytext' : searchText + ' AND ListId:47523633-0890-4AEA-9FE8-A667E5F63CC6', 'StartRow' : startRow, //'RowsPerPage' : rowPerpage 'RowLimit' : rowLimit, 'SelectProperties' : { 'results' : [ 'Title'

    , 'PublishDate'

    ,'Created' ] }, 'SortList' : { 'results' : [ { 'Property':'Created', 'Direction': '0' } ] } } }; $.ajax( { url: searchURl, type: "POST", headers: { "accept": "application/json;odata=verbose", "content-type": "application/json;odata=verbose", "X-RequestDigest": $("#__REQUESTDIGEST").val() }, data: JSON.stringify(data), success: onQuerySuccess, error: onQueryError }); } catch (err) { //log error } } function onQuerySuccess(data) { objResults = []; var query = data.d.postquery; objResults =query; console.log(objResults); var resultsCount = query.PrimaryQueryResult.RelevantResults.RowCount; for(var i = 0; i < resultsCount;i++) { var row = query.PrimaryQueryResult.RelevantResults.Table.Rows.results[i]; var taskName = row.Cells.results[3].Value; console.log(taskName); //e0793d39-0939-496d-b129-198edd916feb } } function onQueryError(error) { console.log("Error: "+ JSON.stringify(error)); } search();

    In the above code, i mentioned the select parameters with sort parameters and getting the results but value of custom column PublishDate and other custom columns, coming as NULL and also sorting on PublishDate Column giving error.

    Issues:

    >If, i will mention the select parameters and sort parameter as "Created" Descending then RowLimit is working and getting only one record with value of Created but not custom column PublishDate value (coming as null).


    >If, i will mention the select parameters and in sort parameter custom column "PublishDate" then getting error as

    Error: {"readyState":4,"responseText":"{\"error\":{\"code\":\"-1, Microsoft.Office.Server.Search.REST.SearchServiceException\",\"message\":{\"lang\":\"en-US\",\"value\":\"Invalid parameter: SortList.\"}}}","responseJSON":{"error":{"code":"-1, Microsoft.Office.Server.Search.REST.SearchServiceException","message":{"lang":"en-US","value":"Invalid parameter: SortList."}}},"status":400,"statusText":"Bad Request"}

    Please let me know, any pointers to resolve this issue.



    Thanks, Amit Kumar, LinkedIn Profile ** My Blog
    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful



    Monday, April 17, 2017 1:35 PM
  • Hi Amit,

    #1. Please also add "contenttype:item" as part of the query text.

    #2. Go to Search Service Application to check if the managed property "Created" is sortable.

    Please also test with another managed property to see what happens.

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Tuesday, April 18, 2017 11:29 AM
    Moderator
  • Hi Amit,

    I am checking to see how things are going there on this issue. Please let us know if you would like further assistance.

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, April 25, 2017 1:41 AM
    Moderator