Using a CAML Query to Retrieve Information from SharePoint

Instructional Goals

• Demonstrate how to retrieve data from SharePoint using the CAML Query AgileShape.

Example Process: Retrieve Expenses

Problem Statement

Display a custom, ad hoc report of employee expenses.

Business Requirements

• Display a report of employee expenses.
• Expenses are stored as items in a SharePoint list.

Prerequisites

• A SharePoint list, named ExpenseList, with the following columns set up to record employee expenses:

  SharePoint Column
  EmployeeID
  Department
  ExpenseDate
  ExpenseType
  Amount

• An InfoPath form with the following 2 views:
  • Find Expenses - Fields to enter the following search criteria:

    InfoPath Field
    EmployeeID
    Department
    ExpenseDate
    ExpenseType
    Amount

  • View Expenses - Display the expense information returned by the search in a repeating table.
• To access your SharePoint list, you must log on to SharePoint.

Navigation

1. In AgilePoint Envision, open a process template.
2. Navigate to the SharePoint stencil.
3. On the SharePoint stencil, drag the CAML Query AgileShape, and drop it on the process template.
4. To view the entire list of properties, in the Design Tasks pane, click Show Properties.
5. On the CAML Query Configuration field, click the Ellipses button.

Instructions

On the CAML Query Builder Configuration window, do the following to set up a CAML query to retrieve employee expenses from the SharePoint list according to the options in the Find Expenses form. These instructions provide sample values based on the example process.

1. On the List and View Fields tab, complete the following fields:

   Field Name	Value
   Site Url	http://demo3:8000/
   List Name	ExpenseList
   Configured View Fields	Use the right arrow button to move the following fields from the Possible View Fields list to the Configured View Fields list: EmployeeID ExpenseDate ExpenseType Department Amount

2. On the Where Clause tab, build your query using the dialog box, or simply copy and paste code that represents your query in the text area. This example uses the following clause:

   <Where>
   <And>
   <Eq>
   <FieldRef Name='EmployeeID'   />
   <Value Type='Text' >${/my:ExpenseDetails/my:EmployeeID}</Value>
   </Eq>
   <And>
   <Eq>
   <FieldRef Name='Department'   />
   <Value Type='Text' >${/my:ExpenseDetails/my:Department}</Value>
   </Eq>
   <And>
   <Eq>
   <FieldRef Name='ExpenseType'   />
   <Value Type='Text' >${/my:ExpenseDetails/my:ExpenseType}</Value>
   </Eq>
   <And>
   <Geq>
   <FieldRef Name='ExpenseDate'  IncludeTimeValue='TRUE' />
   <Value Type='DateTime' IncludeTimeValue='TRUE'>${/my:ExpenseDetails/my:FromDate}</Value>
   </Geq>
   <Leq>
   <FieldRef Name='ExpenseDate'  IncludeTimeValue='TRUE' />
   <Value Type='DateTime' IncludeTimeValue='TRUE'>${/my:ExpenseDetails/my:To}</Value>
   </Leq>
   </And>
   </And>
   </And>
   </And>
   </Where>
   <OrderBy>
   <FieldRef Name='ExpenseDate' Ascending='True' />
   </OrderBy>

3. On the Grouping tab, complete the following fields:

   Field Name	Value
   Order By - Columns	ExpenseDate
   Order By - Sort Order	Ascending

4. On the Output Mapping tab, select Map CAML Query Response to AgilePoint Schema.
5. Click the Ellipses button.
6. On the Schema Mapper Window, map the columns in your SharePoint list to the schema elements on your InfoPath form.

   SharePoint Column	InfoPath Field
   EmployeeID	EmployeeID
   Department	Department
   ExpenseDate	ExpenseDate
   ExpenseType	ExpenseType
   Amount	Amount

7. To test your query, on the Test Query tab, click Execute Query. Resolve any errors that occur, and continue testing until the query executes successfully.