Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have the following "base" query:

   <Where>
      <And>
         <Eq>
            <FieldRef Name='Field1' />
            <Value Type='Boolean'>1</Value>
         </Eq>
            <Eq>
               <FieldRef Name='Field 2' />
               <Value Type='Text'>TextQuery</Value>
         </Eq>        
      </And>
   </Where>
   <OrderBy>
      <FieldRef Name='OrderField' Ascending='True' />
   </OrderBy>

I would like to be able to dynamically add And blocks based on whether drop down boxes have a selected value. Example:

 strQuery = "";

 if (ddlNewField.SelectedIndex != 0)
 {
   strQuery += "<And>...</And>";
 }

The strQuery value would then be concatenated somewhere in the base query.

It obviously doesn't work. After a google search it looks like blocks need to be paired with only one other value or And block. I'm currently having coders-block.

How can I accomplish this?

Thanks in advance.

edit: I can change the base query if necessary

share|improve this question

2 Answers 2

up vote 2 down vote accepted

I've done similar like

string baseQry = "<and><eq>...</eq><eq>...</eq></and>"
string finalQry = baseQry;

and then if selected index is not 0, or wherever you want to add it

finalQry = "<and>" + finalQry + "<eq>new one here</eq></and>"

And running the above code will continually build out the statement. When you're ready to use it, add the where and orderby

finalQry = "<where>" + finalQry + "</where><orderby>...</orderby>"

HTH

share|improve this answer
    
That definitely works, thanks. Now I'm trying to figure out why the query doesn't refine the gridview I'm attaching the data set to. –  David King Dec 27 '11 at 18:30
    
Almost, but this requires that baseQry exists, so requires minimum of two values. Still good start on the topic. –  Jussi Palo May 16 '12 at 9:33

There's a couple of of posts here and here but I've added support for the <In> query if more than one value for the same field is specified (hence why I don't use a Dictionary pair). My code currently only supports Text fields and doesn't support <Or>. Others have mentioned the REST API as an alternative approach.

Code - you can run this in LINQPad:

void Main()
{   
    var fieldValues = new List<FieldValue>();
    fieldValues.Add(new FieldValue("Completed", "No"));
    fieldValues.Add(new FieldValue("Completed", "Yes"));
    fieldValues.Add(new FieldValue("Alice", "1"));
    fieldValues.Add(new FieldValue("Bob", "2"));
    fieldValues.Add(new FieldValue("Charlie", "3"));

    ToCaml(fieldValues).Dump();
}

private const string CamlWhereEq = "<Eq><FieldRef Name='{0}' />{1}</Eq>";
private const string CamlWhereIn = "<In><FieldRef Name='{0}' /><Values>{1}</Values></In>";
private const string CamlValueText = "<Value Type='Text'>{0}</Value>";
private const string CamlAndOpen = "<And>{0}";
private const string CamlAndClose = "</And>";

public string ToCaml(List<FieldValue> fieldValues)
{
    string result = string.Empty;
    StringBuilder sbResult = new StringBuilder();   
    if (fieldValues.Count > 0)
    {
        if (fieldValues.Count == 1)
        {
            sbResult.AppendFormat(CamlWhereEq, fieldValues[0].Field, string.Format(CamlValueText, fieldValues[0].Value));
        }
        else        
        {
            // Check for duplicates 
            var groupedByField = from a in fieldValues
            group a by a.Field into g
            where g.Count() > 1
            select new { Key = g.Key, Item = g };

            var whereFilters = new List<string>();
            var otherFields = new List<FieldValue>();
            if (groupedByField.Count() > 0)
            {
                // Build "IN" query where multiple values specified             
                var groupedByFieldNames = groupedByField.Select(g => g.Key).ToList();                                                   
                otherFields = fieldValues.Where(a => !groupedByFieldNames.Contains(a.Field)).ToList();

                foreach (var g in groupedByField)
                {
                    StringBuilder sbFieldValues = new StringBuilder();
                    foreach (var i in g.Item)
                    {
                        sbFieldValues.AppendFormat(CamlValueText, i.Value);
                    }

                    whereFilters.Add(string.Format(CamlWhereIn, g.Key, sbFieldValues.ToString()));
                }
            }
            else            
            {
                otherFields = fieldValues;
            }

            foreach (var otherField in otherFields)
            {
                whereFilters.Add(string.Format(CamlWhereEq, otherField.Field, string.Format(CamlValueText, otherField.Value)));
            }

            if (whereFilters.Count == 1)
            {
                sbResult.AppendFormat(CamlAndOpen + CamlAndClose, whereFilters[0]);
            }
            else 
            {               
                sbResult.AppendFormat(CamlAndOpen + CamlAndClose, whereFilters[0] + whereFilters[1]);               
                for (int i = 2;i < whereFilters.Count;i++)
                {               
                    sbResult.Insert(0, string.Format(CamlAndOpen, whereFilters[i]));
                    sbResult.Append(CamlAndClose);                  
                }
            }
        }

        result = sbResult.ToString();
    }


    return result;
}

public class FieldValue
{
  public string Field { get; set; }
  public string Value { get; set; } 

  public FieldValue()
  {
  }

  public FieldValue(string field, string value)
  {
    Field = field;
    Value = value;
  }
}

Result:

<And><Eq><FieldRef Name='Charlie' /><Value Type='Text'>3</Value></Eq><And><Eq><FieldRef Name='Bob' /><Value Type='Text'>2</Value></Eq><And><In><FieldRef Name='Completed' /><Values><Value Type='Text'>No</Value><Value Type='Text'>Yes</Value></Values></In><Eq><FieldRef Name='Alice' /><Value Type='Text'>1</Value></Eq></And></And></And>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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