0

I have a string 'listOfUsers' which is in the format user1;user2;user3;user4;user5;user6;

This string could have any number of users.

I would like to build a CAML query which will find all those users.

The query to generate will look like this:

<View>
    <Query>
        <Where>
            <Or>
                <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user1</Value></Eq>
                <Or>
                    <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user2</Value></Eq>
                    <Or>    
                        <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user3</Value></Eq>
                        <Or>    
                            <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user4</Value></Eq>
                            <Or>    
                                <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user5</Value></Eq>
                                <Eq><FieldRef Name='onPremisesSamAccountName'></FieldRef><Value Type='Text'>user6</Value></Eq>
                            </Or>
                        </Or>
                    </Or>
                </Or>
            </Or>
        </Where>
    </Query>
</View>

But I am unsure how to build it in JavaScript where I take a string of users and generate the CAML query.

3
  • 1
    Are you using plain JavaScript in SPFx web part? Are you comfortable with typescript example? Feb 27, 2023 at 13:14
  • Yes whatever you have would be appreciated :) thanks Feb 27, 2023 at 13:15
  • Check code given in my answer below. Let me know if it works for you. Feb 27, 2023 at 13:22

2 Answers 2

1

I have created helper functions to dynamically generate filter conditions to use in CAML query. check this:

Helper functions:

  //Construct filter string required to fetch data from list using CAML
  public createFilterString(filterSelection: string[], fieldName: string): string[] {
    let filterStringCollection: string[] = [];
    filterSelection.map((aItem) => {
      filterStringCollection.push(
        `<Eq>
            <FieldRef Name="` + fieldName + `" />
            <Value Type="Text">` + aItem + `</Value>
          </Eq>`
      );
    });
    return filterStringCollection;
  }

//Append operator to filter string
  public getFilterStringByOperator(filterCollection: string[], operator: string): string {
    let filterString = "";
    if (filterCollection.length === 1)
      filterString += filterCollection[0];
    else if (filterCollection.length === 2)
      filterString += `<` + operator + `>` + filterCollection[0] + filterCollection[1] + `</` + operator + `>`;
    else {
      for (let i = 0; i < filterCollection.length; i++) {
        if (i == 0)
          continue;

        if (i == 1) {
          filterString += `<` + operator + `>` + filterCollection[0] + filterCollection[1] + `</` + operator + `>`;
          continue;
        }

        filterString = `<` + operator + `>` + filterString + filterCollection[i] + `</` + operator + `>`;
      }
    }
    return filterString;
  }

You can use those like:

let userEmailsStr: string = "user1;user2;user3;user4;user5;user6";
let userEmailsArr: string[] = userEmailsStr.split(";");

let arrFilterString = createFilterString(userEmailsArr, "onPremisesSamAccountName");
let filterString = getFilterStringByOperator(arrFilterString, "Or");

let camlQuery = `<View><Query><Where>` + filterString + `</Where></Query></View>`;
6
1

I created a library (SharePointPlus) that permits to deal with SharePoint API. Part of this lib, I have a function to parse a "SQL-like" string to CAML.

The function is hosted there and it's called $SP().parse.

For example, you could write $SP().parse('onPremisesSamAccountName = "user1" OR onPremisesSamAccountName = "user2"'); and it will return '<Or><Eq><FieldRef Name="onPremisesSamAccountName" /><Value Type="Text">user1</Value></Eq><Eq><FieldRef Name="onPremisesSamAccountName" /><Value Type="Text">user2</Value></Eq></Or>'

It's handy because you can write complex SQL-like sentences and get the CAML equivalent.

You probably don't want to install my whole lib, so below it's the extracted part that you need:

function _cleanString(str) {
  return str.replace(/&(?!amp;|lt;|gt;)/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");
}

function parse(q, escapeChar) {
  // schema: https://docs.microsoft.com/en-us/sharepoint/dev/schema/query-schema
  var queryString = q.replace(/(\s+)?(=|~=|<=|>=|~<>|<>|<|>| LIKE | IN )(\s+)?/g,"$2").replace(/""|''/g,"Null").replace(/==/g,"="); // remove unnecessary white space & replace '
  // the Null doesn't work with IN, so we need to move it outside
  if (/\w+ IN \[([^[]+,)?Null,?/.test(queryString)) {
    // eslint-disable-next-line
    queryString = queryString.replace(/(\w+) IN \[([^\[]+,)?Null(,[^\]]+)?\]/g,"($1 = Null OR $&)") // eslint-disable-next-line
                             .replace(/\[([^\[]+,)?Null(,[^\]]+)?\]/g,"[$1$2]")
                             .replace(/(\[),|(,),|,(\])/g,"$1$2$3");
  }

  var factory = [];
  escapeChar = (escapeChar===false ? false : true)
  var limitMax = queryString.length;
  var closeOperator="", closeTag = "", ignoreNextChar=false;
  var lastField = "";
  var parenthesis = {open:0};
  var lookupId = false;
  for (var i=0; i < queryString.length; i++) {
    var letter = queryString.charAt(i);
    switch (letter) {
      case "(": // find the deepest (
        var start = i;
        var openedApos=false;
        while (queryString.charAt(i) == "(" && i < limitMax) { i++; parenthesis.open++; }

        // find the corresponding )
        while (parenthesis.open>0 && i < limitMax) {
          i++;
          // if there is a ' opened then ignore the ) until the next '
          var charAtI = queryString.charAt(i);
          if (charAtI=="\\") ignoreNextChar=true; // when we have a backslash \ then ignore the next char
          else if (!ignoreNextChar && (charAtI=="'" || charAtI=='"')) openedApos=!openedApos;
          else if (!ignoreNextChar && charAtI=="(" && !openedApos) parenthesis.open++;
          else if (!ignoreNextChar && charAtI==")" && !openedApos) parenthesis.open--;
          else ignoreNextChar=false;
        }

        var lastIndex = factory.length-1;

        // concat with the first index
        if (lastIndex>=0) {
          if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
          factory[0] += parse(queryString.substring(start+1, i));
          if (closeOperator != "") factory[0] += "</"+closeOperator+">";
          closeOperator = "";
        } else {
          factory[0] = parse(queryString.substring(start+1, i));
        }
        break;
      case "[": // for operator IN
        var start = i; // eslint-disable-line
        var openedApos=false; // eslint-disable-line
        // find the corresponding ]
        while (i < limitMax) {
          i++;
          // if there is a ' opened then ignore the ) until the next '
          var charAtI = queryString.charAt(i); // eslint-disable-line
          if (charAtI=="\\") ignoreNextChar=true; // when we have a backslash \then ignore the next char
          else if (!ignoreNextChar && (charAtI=="'" || charAtI=='"')) openedApos=!openedApos;
          else if (!ignoreNextChar && !openedApos && charAtI=="]") break;
          else ignoreNextChar=false;
        }

        var lastIndex = factory.length-1; // eslint-disable-line
        var arrIn = JSON.parse('[' + queryString.substring(start+1, i) + ']');
        // we want to detect the type for the values
        var typeIn = "Text";
        switch(typeof arrIn[0]) {
          case "number": typeIn = "Number"; break;
          default: {
            // check if it starts with ~ and then it's a number -- lookupid
            if (arrIn[0].charAt(0) === "~" && typeof (arrIn[0].slice(1)*1) === "number") {
              typeIn = "Integer";
              // change all array values
              arrIn.forEach(function(e,i) { arrIn[i]=e.slice(1) })
            }
          }
        }
        factory[lastIndex] += '<FieldRef Name="'+lastField+'" '+(typeIn==="Integer"?'LookupId="True"':'')+' /><Values><Value Type="'+typeIn+'">' + arrIn.join('</Value><Value Type="'+typeIn+'">') + '</Value></Values>' + closeTag;
        lastField = "";
        closeTag = "";
        // concat with the first index
        if (lastIndex>0) {
          if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
          factory[0] += factory[lastIndex];
          if (closeOperator != "") factory[0] += "</"+closeOperator+">";
          delete(factory[lastIndex]);
          closeOperator = "";
        }
        break;
      case ">":  // look at the operand
      case "<":
        i++;
        if (queryString.charAt(i) == "=") { // >= or <=
          factory.push("<"+(letter==">"?"G":"L")+"eq>");
          closeTag = "</"+(letter==">"?"G":"L")+"eq>";
        } else if (letter == "<" && queryString.charAt(i) == ">") { // <>
          factory.push("<Neq>");
          closeTag = "</Neq>";
        } else {
          i--;
          factory.push("<"+(letter==">"?"G":"L")+"t>");
          closeTag = "</"+(letter==">"?"G":"L")+"t>";
        }
        break;
      case "~": // special operator '~=' and '~<>' for lookup
        if (queryString.charAt(i+1) == "=" || (queryString.charAt(i+1) === '<' && queryString.charAt(i+2) === '>')) {
          lookupId=true;
        }
        break;
      case "=":
        factory.push("<Eq>");
        closeTag = "</Eq>";
        break;
      case " ": // check if it's AND or OR
        if (queryString.substring(i,i+5).toUpperCase() == " AND ") {
          // add the open tag in the array
          closeOperator = "And";
          i+=4;
        }
        else if (queryString.substring(i,i+4).toUpperCase() == " OR ") {
          // add the open tag in the array
          closeOperator = "Or";
          i+=3;
        }
        else if (queryString.slice(i,i+6).toUpperCase() == " LIKE ") {
          i+=5;
          factory.push("<Contains>");
          closeTag = "</Contains>";
        }
        else if (queryString.slice(i,i+4).toUpperCase() == " IN ") {
          i+=3;
          factory.push("<In>");
          closeTag = "</In>";
        }
        else lastField += letter;
        break;
      case '"': // look now for the next "
      case "'":
        var apos = letter;
        var word = "", other="";
        while ((letter = queryString.charAt(++i)) != apos && i < limitMax) {
          if (letter === "\\" && queryString.charAt(i+1) === apos) letter = queryString.charAt(++i);
          word+=letter;
        }
        lastIndex = factory.length-1;
        factory[lastIndex] += '<FieldRef Name="'+lastField+'" '+(word=="[Me]"?'LookupId="True" ':'')+'/>';
        lastField = "";
        var type = "Text"; //(isNaN(word) ? "Text" : "Number"); // check the type
        // check automatically if it's a DateTime
        if (/\d{4}-\d\d?-\d\d?((T| )\d{2}:\d{2}:\d{2})?/.test(word)) {
          type="DateTime";
          // check if we want to evaluate the TIME also
          if (/\d{4}-\d\d?-\d\d?((T| )\d{2}:\d{2}:\d{2})/.test(word)) other=' IncludeTimeValue="TRUE"';
        }
        if (escapeChar) word = _cleanString(word);
        // special words ([Today] and [Me])
        if (word === "[Me]") {
          word = '<UserID Type="Integer" />';
          type = "Integer";
        } else if (word.slice(0,6) == "[Today") {
          type="DateTime";
          // find the offset if defined
          word = '<Today OffsetDays="'+(1*word.slice(6,-1))+'" />';
        }

        factory[lastIndex] += '<Value Type="'+type+'"'+other+'>'+word+'</Value>';
        factory[lastIndex] += closeTag;
        closeTag = "";
        // concat with the first index
        if (lastIndex>0) {
          if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
          factory[0] += factory[lastIndex];
          if (closeOperator != "") factory[0] += "</"+closeOperator+">";
          delete(factory[lastIndex]);
          closeOperator = "";
        }
        break;
      case "0": case "1": case "2": case "3": case "4": case "5": case "6": case "7": case "8": case "9":
        if (closeTag != "") { // it's the value
          var value = letter;
          while (!isNaN(letter = queryString.charAt(++i)) && i < limitMax) value+=""+letter;
          lastIndex = factory.length-1;
          factory[lastIndex] += '<FieldRef Name="'+lastField+'"'+(lookupId?' LookupId="True"':'')+' />';
          lastField = "";
          factory[lastIndex] += '<Value Type="'+(lookupId?"Integer":"Number")+'">'+value.replace(/ $/,"")+'</Value>';
          factory[lastIndex] += closeTag;
          closeTag = "";
          // concat with the first index
          if (lastIndex>0) {
            if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
            factory[0] += factory[lastIndex];
            if (closeOperator != "") factory[0] += "</"+closeOperator+">";
            delete(factory[lastIndex]);
            closeOperator = "";
          }
          i-=2;
          break;
        }
      default: // eslint-disable-line
        if (closeTag == "") lastField += letter;
        else if (letter.toLowerCase() == "n" && queryString.substring(i,i+4).toLowerCase() == "null") { // if we have NULL as the value
          lastIndex = factory.length-1;
          if (closeTag == "</Neq>") { // <>
            factory[lastIndex] = "<IsNotNull>";
            closeTag = "</IsNotNull>";
          } else if (closeTag == "</Eq>") { // =
            factory[lastIndex] = "<IsNull>";
            closeTag = "</IsNull>";
          }
          i+=3;
          factory[lastIndex] += '<FieldRef Name="'+lastField+'" />';
          lastField = "";
          factory[lastIndex] += closeTag;
          closeTag = "";
          // concat with the first index
          if (lastIndex>0) {
            if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
            factory[0] += factory[lastIndex];
            if (closeOperator != "") factory[0] += "</"+closeOperator+">";
            delete(factory[lastIndex]);
            closeOperator = "";
          }
        }
        else if ((letter.toLowerCase() === "t" && queryString.substring(i,i+4).toLowerCase() === "true") || (letter.toLowerCase() === "f" && queryString.substring(i,i+5).toLowerCase() === "false")) { // when we have TRUE/FALSE as the value
          lastIndex = factory.length-1;
          i+=3;
          if (letter.toLowerCase() === "f") i++;
          factory[lastIndex] += '<FieldRef Name="'+lastField+'" /><Value Type="Boolean">'+(letter.toLowerCase() === "t"?1:0)+'</Value>';
          lastField = "";
          factory[lastIndex] += closeTag;
          closeTag = "";
          // concat with the first index
          if (lastIndex>0) {
            if (closeOperator != "") factory[0] = "<"+closeOperator+">"+factory[0];
            factory[0] += factory[lastIndex];
            if (closeOperator != "") factory[0] += "</"+closeOperator+">";
            delete(factory[lastIndex]);
            closeOperator = "";
          }
        }
    }
  }
  return factory.join("");
}

Now, if you have an array of users, you could do:

var users = [ "user1", "user2", "user3", "user4" ];
parse(users.map(function(user) { return 'onPremisesSamAccountName = "'+user+'"'}).join(" AND "));

You can also complexify if you need to have an "OR", or to compare with some other columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.