SharePoint: CAML Query - AND

I was ANDing three SharePoint fields in CAML Query based on the regular logic (place And between all three fields) and was getting errors with absolutely no clue that what possibaly could be wrong.
Here is the right way...thanks to Google:

Target: field1 && field2 && field3
Correct CAML:

<where>
  <and>
    <and>
      <eq>
        <fieldref name="field1">
        <value type="Text">val1</value>
      </fieldref></eq>
      <eq>
        <fieldref name="field2">
        <value type="Text">val2</value>
      </fieldref></eq>
    </and>
    <eq>
      <fieldref name="field3">
      <value type="Text">val3</value>
    </fieldref>
   </eq>
  </and>
</where>

Happy CAMLing!

Date Comparison in XSL

Since XSL in SharePoint Designer does not have a very good support for date comparisons. I Googled for at least more than half of the day for a decent date comparison and came across this post as a solution by ma man Kotendra. However, this post assumes that date is provided in ISO format (YYYMMDD). This was not my case…so I Googled bit more and bit more and little bit more :o) and here is the solution.

Below is the formula (XSLT Filtering) to compare dates in SharePoint Designer using XSL:

[
number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(@Open_x0020_Date),1033,1),1033,'yyyyMMdd'))
>=
number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($varFromDate),1033,1),1033,'yyyyMMdd'))
]

Above formula will convert date 5/31/2009 into 20093105 and treat it as a number to compare with another number converted from date.

Thanks!

Implementing Search in a SharePoint List

There are times when we want to search in a SharePoint list only, not in the whole portal. I ran into this requirement in my current project and implemented the search on a list using SharePoint Designer. I didn’t want to create a WebPart and with some research I was able to do it in SharePoint Designer. In this post I will explain what exactly I did.

To implement the search I created following two pages:

1. Search.aspx (This page will take input parameters)
2. SearchResults.aspx (This page will display results in DataView Control and filter the data based on the input parameters provided in the Search.aspx)

Let’s start the implementation:

1. I will not go into the details of Search.aspx page. All you can have on the Search.aspx page is a text box and a submit button. Clicking the button will redirect the use to SearchResults.aspx with the parameters attached into the Query String. Our final URL will look like:

http://www.domain.com/sites/MySite/ListName/SearchResults.aspx?skill=Sharepoint

Yup, I am searching for people in my list who have SharePoint skills.

2. Open the SearchResults.aspx in SharePoint Designer and insert the DataView Control. I applied Master Page so those pages don’t look like an alien :o).
3. Assign the DataSource of the list and at this point DataView should display all the items in the list.
4. In the designer, select <WebPartPages:DataFormWebPart> and open the Common Data View Task window and choose Parameters.

5. Create a Parameter varTechnicalSkill and Select Query String from Parameter Source drop down and provide the Query String Variable which is skill in our case.

6. Again, open the Common Data View Task window and choose Filter this time.
7. Select Add XSLT Filtering and hit Edit… button. Don’t get confuse with the varExperience. It’s just another variable and has absolutely nothing to do with our example.

8. In the Advance Condition window, in Edit the XPath expression provide the following formula for search:

[contains(@Skill,$varTechnicalSkill)]

Contains is a XSL function
@Skill is the column in the list
$varTechnicalSkill is the parameter which will get populated by the Query String skill.

Hit OK and then OK again.

9. If you view the source then you should be able to see the following XSL code added to the <XSL:tamplate>:

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[contains(@Tech_x0020_Skill_x0020__x0023_1,$varTechnicalSkill)]"/>

10. Save the SearchResults.aspx page and that’s it. We are good to use the search functionality.

SharePoint: Accessing XSL variables in JavaScript

As we all know for a successful SharePoint project we need SharePoint knowledge, development skills, ability to google and prayers…lots of them.

During my current project I was developing a report which will generate a letter and the body of that letter will be generated by the data stored in Rich Text field in a SharePoint list. Now, in my case that body was a predefined template of the letter and I need to replace few keywords in the letter body using JavaScript. To achieve all this I need to access the Body variable (carrying the letter template) into the javaScript function to play with it.

Following are the steps to access I took to access XSL variable into my JavaScript function:

1. Open the website in SharePoint Designer.
2. Create an empty page Report.aspx.
3. Insert DataForm Webpart by selecting Insert-> SharePoint Controls -> DataView.
4. Hide all the columns and your WebPart should look like the following:

5. Also, you should be able to see the variable @Body in the source code window under <datafields> tag. Both are highlighted in the above image.
6. Now add a hidden field in the page under XSL tag and assign its value to @Body variable. Since, @Body variable is accessible in SharePoint generated XSL only. I added hidden field under one of the <xsl:template> tags.



7. Now you have got the value of @Body into the hidden field and you can access the field’s value easily with the following line of javascript, I called this line at the end of the HTML <body> tag:

var strBody = document.getElementById('txtBody').value;

8. Thats it.