Retrieving SharePoint List Data

by Ronak Patel, in SharePoint Automation, posted 2/11/11

SharePoint List

SharePoint List is like a database table which has columns and rows. A SharePoint List is a collection of similar items that contains columns or fields that define the item data or metadata schema. Each item stored in a List shares the same schema.  Querying of data in lists can be done with an XML-based query language schema called CAML, a query schema that offers basic querying operations such as filtering, sorting and grouping of data.

I have created a sample List “Employee” with some raw data. We will use this list for our test case. The List has three employees whose EmployeeId ranges from 30 to 45.

SharePoint List

 

Step 1

Drag SharePoint action and select “Create Session” activity.

 

The user has to provide the SharePoint site URL and proper credentials to connect to the SharePoint server.

The user can optionally provide the Http Proxy settings also.

SharePoint create session

Step 2

 

Drag the SharePoint action and select the “Get list items” activity. Using the default CAML query this will retrieve the first 25 rows from the list. If there are more than 25 rows then it will send another request to the SharePoint server to fetch the next batch. This way the client and server can deal with small chunks of data efficiently. To complete this step, provide the valid dataset name.

SharePoint List Item

 

Step 3

Drag the SharePoint action and select the “End session” activity.

Task:

   <AMSHAREPOINT ACTIVITY="create_session" SITE="http://server/my/personal/Team" AUTHTYPE="Basic" USERNAME="administrator" PASSWORD="password" DOMAIN="winserv08r2" />

   <AMSHAREPOINT ACTIVITY="get_listitems" LIST="Employee" RESULTDATASET="test" PROVIDER="session_based" />

   <AMSHAREPOINT ACTIVITY="end_session" />

How to retrieve only those employees whose employeeId is above 30?

Use SharePoint activity “Get list items” to get the dataset of all employees in the List. Use the custom CAML query to retrieve only those employees whose employeeId is greater than 30. In our case this should return only Joy and Smith.

CAML Query:

<View><Query><Where><Gt><FieldRef  Name="EmployeeId" />

<Value Type="Counter">30</Value></Gt></Where></Query> </View>

Task:

   <AMSHAREPOINT ACTIVITY="create_session" SITE="http://server/my/personal/Team" AUTHTYPE="Basic" USERNAME="administrator" PASSWORD="password" DOMAIN="winserv08r2" />

   <AMSHAREPOINT ACTIVITY="get_listitems" LIST="Employee" RESULTDATASET="test" PROVIDER="session_based">&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Gt&gt;&lt;FieldRef Name="EmployeeId" /&gt;&lt;Value Type="Counter"&gt;30&lt;/Value&gt;&lt;/Gt&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;</AMSHAREPOINT>

<AMSHAREPOINT ACTIVITY="end_session" />

Conclusion

AutoMate is a powerful software platform that primarily focuses on supplying a complete, all-in-one system for the development, deployment, management and automation of simple to elaborate business and IT processes. Its intuitive, drag-and-drop based graphical interface provides businesses of all sizes with a true rapid-development platform for streamlining business operations executed on the desktop that requires no programming knowledge to use and maintain. With the addition of 40 different activities related to SharePoint automation, integrating SharePoint and its data into an automation routine becomes effortless. The user can achieve uploading/downloading SharePoint documents or retrieving/adding list-items to SharePoint list with simple drag and drop steps.