Showing posts with label CAML Query. Show all posts
Showing posts with label CAML Query. Show all posts

Tuesday, November 29, 2016

CAML, get all list items where text field contains a value?

Server Object Model:


query.Query = "<Where><Contains><FieldRef Name="Name"/><Value Type="Text">" + "YourValue" + "/Value></Contains></Where>";




Client Object Model:

"<Query><Where><Contains><FieldRef Name="Name"/><Value Type="Text">" + "YourValue" + "/Value></Contains></Where></Query>";

CAML query filter for HTML BR tag

<Value Type="Note"><!CDATA[<BR>]]></Value>

<Query>
   <Where>
      <Contains>
         <FieldRef Name='title_id ' />
         <Value Type='Computed'>o</Value>
      </Contains>
   </Where>
</Query>

CAML: Filter URL field on description

In CAML query, 'complex' fields which store multiple values, only the 'default' value is available to query - by this I mean what would be available when SPField.GetFieldValueAsText() 

How to build a CAML query with where clause?

We can SPUserField as SPLookupField

<Query> 
     <Where> 
         <And> 
              <Eq> 
                  <FieldRef Name='UserField'/> 
                  <Value Type='Lookup'>" + username + "</Value> 
              </Eq> 
              <Eq> 
                   <FieldRef Name='UserField' LookupId='True' /> 
                   <Value Type='Lookup'>" + ID + "</Value> 
              </Eq>
         </And> 
     </Where> 
 </Query> 

How to download the U2U CAML query Builder for SharePoint 2013?

CAML Designer

The one that works is this URL: http://camldesigner.blob.core.windows.net/offlinepackage/CamlDesigner2013.zip

The link comes from Karine Bosch's Blog. CAML Designer is the successor of CAML Builder, and from experience I can tell you this tool is much better and accurate. I've tried the both.

Edit: Looks like the CAML Designer now is a subscription application, but it is present on GitHub. https://github.com/chrisdee/Tools/tree/master/SharePoint/CamlDesigner

CAML Builder

But if you want the previous tool, the page to turn to is http://www.u2u.be/Software where you have the download at http://www.u2u.be/Downloads/U2U.SharePoint.CQB2010.zip which contains the wsp.

The CamlBuilderSetup.msi-file is located here: https://docs.google.com/file/d/0B1H-kV-5gwStUlF1OEdjWWJZNXc/edit?pli=1




U2U CAML Query Builder is now available again and updated for SharePoint 2013:

http://www.u2u.be/Software

Wednesday, March 2, 2016

How to retrieving next 7 Days Due Date Tasks from SharePoint List

C# Coding:


DateTime strdate = (DateTime)DateTime.Today.AddDays(7);
            string strgetDate = strdate.Year + "-" + strdate.Month + "-" + strdate.Date.Day;

SPWeb currentWeb = SPContext.Current.Web;
            SPList lst = currentWeb.Lists["Projects"];
            SPQuery sQuery = new SPQuery();
            sQuery.Query = "<Where><Geq><FieldRef Name='DueDate' /><Value Type='DateTime'>"+strgetDate+"</Value></Geq></Where><OrderBy><FieldRef Name='DueDate' /></OrderBy>";
            SPListItemCollection myColl = lst.GetItems(sQuery);
            if (myColl.Count > 0)
            {
                grdBind.DataSource = myColl.GetDataTable();
                grdBind.DataBind();
            }

Tuesday, February 9, 2016

Datetime CAML query not working

<Where>

  <Leq>
   <FieldRef Name ='" + internalEndDate + "'/>
   <Value Type ='DateTime'>" + System.DateTime.Now.AddDays(7) + "</Value>
  </Leq>


 </Where>

Current Date
<Where>

  <Leq>
   <FieldRef Name ='" + internalEndDate + "'/>
   <Value Type ='DateTime'>" + System.DateTime.Now+ "</Value>
  </Leq>


 </Where>