Allow Nulls Column Not Allowing Modification of SharePoint 2010 ECT

I had created an external content type from an events table in my database. Two of the columns, end_day and start_day, were datetime value types and were set to NOT NULL. I had no problems creating the ECT like this. However, when I later added some columns to the events table and needed them to show up in the “create” operation on the ECT, I couldn’t save the changes. I got the following error:

Data source element end_day is of data type system.datetime, the mapped item end_day is of data type system.nullable
Data source element start_day is of data type system.datetime, the mapped item start_day is of data type system.nullable

To fix this, I just set the columns in the events table to allow nulls.

I don’t understand why I was able to create the initial ECT with columns that did not all null but not able to modify. That’s for another day.

Case Project: Displaying Login Activity in a SharePoint External Content Type Chart

Graphs/Charts are fancy. They are a great way to display statistics from your external content to your users. People love to keep track of things like sex, ethnicity, etc and they would like to have a pleasant way of seeing these results. (e.g. Show me the trends of white males compared to black males that have applied for this program since its inception.) SharePoint to the rescue!

Ok, so what I want to do is show a bar graph of daily logins to an application. Each time a user logins to the external application, a row is inserted into a database table with some information about the login, one item being the login time. SharePoint has a built in web part called “charts” that allow you to easily display pie charts, line graphs, bar graphs, etc with any of your external content data.

What I need to do is convert a table like so

into charts like the following: (daily, monthly and yearly counts)

Really the only “hard” part is creating an external content type from the login table that the charts will be able to read.   I created three views, one for each chart:

Daily Counts:


-- Convert the datetime value into mm/dd/yyyy format
-- sum(1) - For each mach in the group add one
-- COUNT(id) I have to have some sort of primary key for SharePoint, otherwise I can't create the external content type
select CONVERT(VARCHAR(10), submitted, 101) as [Submitted], sum(1) as [Count], COUNT(id) as [id]
from login_tracker
-- Only get the last 7 days
where submitted >= DATEADD(day, DATEDIFF(day, 6, GETDATE()), 0)
-- Group together each row that has the same mm/dd/yyyy
group by CONVERT(VARCHAR(10), submitted, 101)

Monthly Counts:


-- Now we're converting the datetime stamp to 1 - 12.
select month(CONVERT(VARCHAR(10), submitted, 101)) as [monthnum], DATENAME(month, CONVERT(VARCHAR(10), submitted, 101)) as [Submitted], sum(1) as [Count], COUNT(id) as [id]
from login_tracker
-- Only get data from the current year.
where submitted >= year(GETDATE())
group by DATENAME(month, CONVERT(VARCHAR(10), submitted, 101)), month(CONVERT(VARCHAR(10), submitted, 101))

Yearly Logins:


-- Convert datetime to yyyy format (e.g. 2012)
select DATENAME(year, CONVERT(VARCHAR(10), submitted, 101)) as [Submitted], sum(1) as [Count], COUNT(id) as [id]
from login_tracker
group by DATENAME(year, CONVERT(VARCHAR(10), submitted, 101))

From here you just make an ECT from each view and connect it to a chart!

How to Open Edit Form from Sharepoint BCS Profile Page

So I made a profile page that lists all of my external content types data along with all of it’s association data. Now I want to put an action on the profile page to edit the item.

Creating ECT Action

  1. Central Administration -> Manage service apps -> BDCS
  2. Right click on your ECT and click “Add Action”
  3. Make an action with the URL pointing to the list’s Edit Form: http//mysite/lists/EditForm.aspx?id={0}
  4. Add the tables primary key as a parameter.   (There is no BDC idenitity avaiable for you to use!!! So this won’t initially work.)

Once you have your action, go ahead and add it to your profile page.  When you click it, you’ll get an error.  This is because instead of going to http://mysite/list/EditForm.aspx?ID=__bgc88883333 its going to http://mysite/list/EditForm.aspx?id=444.

What we need to do to fix this is convert the SQL table id into the BDC id.

Converting SQL Id to BDC Id

  1. Edit your list’s EditForm.aspx page and add the following code:

    <%@ Register Tagprefix="SharePoint" Namespace="Microsoft.Office.Server.ApplicationRegistry.Infrastructure" Assembly="Microsoft.SharePoint.Portal, Version=, Culture=neutral, PublicKeyToken=" %>

  2. Make sure you allow SharePoint to run your inline code by adding the following to C:inetpubwwwrootwssVirtualDirectories80web.config

Now if you go back to your items profile page and click the Edit Action you made, everything should work!!

Thanks to http://tihomirignatov.blogspot.com/2012/05/open-edit-form-of-external-list-from.html for getting me started.

Extract List Column Data From Sharepoint 2010 ows_MetaInfo

All of the list attributes (columns) can be found in the ows_MetaInfo attribute. This is just a huge string packed with information that you can parse to extract. So far I’ve been able to gather the following about this data:

  1. Keys that start with “vti_*****” are predefined list attributes (e.g. vti_title, vti_author, vti_modifiedby, etc)
  2. Keys that start with “_dlc_****” contain data about the resource itself (e.g _dlc_DocIdUrl, _dlc_DocIdItemGuid, etc)
  3. Keys that are the exact name of an attribute are columns that you added to the list after creation. (e.g. My Custom Cost)

To extract any of this data I use the following to regular expressions functions:


// First get the key / value you want. In this instance I want the custom "title" column I added to the list.
preg_match('/(?=vti_title).*/', $result->getAttribute("ows_MetaInfo"), $match); // Returns "vti_title:RW|my title is this: what?"

// Extract the value
$title = preg_replace('/w*:w{2}|/', '', $match[0]); // Returns "my title is this: what?"

Displaying and Downloading Sharepoint Document Libraries Using PHP

Objective

Create a PHP based web page that creates a list of all documents in a SharePoint 2010 Document Library. Each item in this list is a link to the actual document. When a user clicks the link, the document is downloaded from SharePoint’s database and displayed to the user.

Background

Sharepoint 2010 is fantastic. If you don’t use it, look into it. (Drupal isn’t bad either). While i’ve been migrating a custom coded CMS to Sharepoint 2010, I keep running across interesting issues. My latest was that I needed to let users submit PDF documents to the system and then have those documents display on the website.

With a traditional PHP website, piece of cake: Save document to server, list link to file, user clicks link.

SharePoint however stores all of the documents into a database that is only understandable by SharePoint…

Prep

Make sure you have done the following:

  1. Enabled Basic Authentication on the SharePoint server
  2. Enabled the openssl, curl and soap PHP extensions on your web server
  3. Downloaded a copy of the Lists services to your web site. (Go to https://sharepointsite/subsite_if_one/_vti_bin/Lists.asmx?WSDL)

Now on to the coding!

Steps

Here is my main loop that fetches the document list and creates links to the document on the page:


$results = getDocumentList({GUID});
$load = false;
$out = '

    ';
    //Fetching the elements values. Specify more attributes as necessary
    foreach($results as $result){

    // Get a custom column in my list.
    $title = getMetaItem($result, 'vti_title');

    // Get the document url and guid of document
    $docURL = getMetaItem($result, '_dlc_DocIdUrl');
    $docURL = preg_split('/w*|/', $docURL);
    $docURL = explode(",", $docURL[0]); // Parse the full url, doc_id

    $out .= "

  • {$title}
  • ";

    if(isset($_GET['id']) && trim($docURL[1]) == $_GET['id']){
    $load = true;
    $url = "https://usportal.aaalliance.ucf.edu/" . rawurlencode(preg_replace('/(^[0-9]*).{2}/', '', $result->getAttribute("ows_FileRef")));
    }
    }
    $out .= '

';

// If requesting document
//preg_match('/(dw)*-(dw)*-(dw)/', $_GET['id'])){
if($load){
openDocument($url);
}
else echo $out;

My function to get all of the documents in a specified library:

/*
* @param $listName string this is the GUID of your library. To find it go to your library through the sharepoint website. Then click on "library settings". Once there look at the URL. It should be: https://sharepointsite/subsite/_layouts/listedit.aspx?List={E2EAE561-D43R-4F8B-AC4D-146BEB66DBD4}

Use what List= is (including the {})
*/
function getDocumentList($listName, $config = array())
{
$authParams = getAuth(); // returns username:password

/* A string that contains either the display name or the GUID for the list.
* It is recommended that you use the GUID, which must be surrounded by curly
* braces ({}).
*/
$rowLimit = '150';

/* Local path to the Lists.asmx WSDL file (localhost). You must first download
* it manually from your SharePoint site (which should be available at
* yoursharepointsite.com/subsite/_vti_bin/Lists.asmx?WSDL)
*/
$wsdl = $_SERVER['DOCUMENT_ROOT']."/Lists.xml";

//Creating the SOAP client and initializing the GetListItems method parameters
$soapClient = new SoapClient($wsdl, $authParams);
$params = array('listName' => $listName,
'rowLimit' => $rowLimit,
'viewFields' => ""
);

//Calling the GetListItems Web Service
$rawXMLresponse = null;
try{
$rawXMLresponse = $soapClient->GetListItems($params)->GetListItemsResult->any;
}
catch(SoapFault $fault){
//var_dump($fault);
echo 'Fault code: '.$fault->faultcode;
echo 'Fault string: '.$fault->faultstring;
}

//Loading the XML result into parsable DOM elements
$dom = new DOMDocument();
$dom->loadXML($rawXMLresponse);

$results = $dom->getElementsByTagNameNS("#RowsetSchema", "*");
unset($soapClient);

return $results;
}

My function to parse ows_MetaInfo

/*
* Parse ows_MetaInfo for a value
*/
function getMetaItem($result, $key)
{
$match = array();
preg_match('/(?='.$key.').*/', $result->getAttribute("ows_MetaInfo"), $match);
return preg_replace('/w*:w{2}|/', '', $match[0]);
}

Finally, open the document with by it’s URL


function openDocument($url)
{
$authParams = getAuth();

// Get the file name.
$filename = substr(strrchr(rawurldecode($url), '/'), 1);
header("Content-Disposition: attachment; filename=".str_replace(' ', '', (urlencode($filename))));
header("Content-type: application/octet-stream");
header("Content-Transfer-Encoding: binary");

$ch = curl_init();

curl_setopt($ch, CURLOPT_BINARYTRANSFER, true);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_USERPWD, $authParams['login'] . ":" . $authParams['password']);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
//curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2); // Not working for me
//curl_setopt($ch, CURLOPT_CAINFO, getcwd() . "/../data/CAcerts/my.pem");

$response = curl_exec($ch);
echo $response;
curl_close($ch);
}

Oh how I love making systems talk to systems…

Thank you to http://davidsit.wordpress.com/2010/02/23/reading-a-sharepoint-list-with-php/ for getting me started on how to read sharepoint lists.

Creating A Workflow From an External List in SharePoint 2010

You can’t.

Yeah, sucky, but… there are ways around that. If you Google for a while you’ll see some theories, I’ve got my own though that I’ve actually implemented. (This won’t work for all use cases.).

Ok, so basically I needed to do the following:

1) Create a PHP form that users could submit an application through.
2) Display this information on a SharePoint site with a “Pending” state.
3) Allow users with access to this information to change the state of the application in SharePoint.
4) Depending on what the state changes to, email a certain user.

Most of this is pretty straight forward, the only real hurdles were a) providing a selection of state options and b) starting the “workflow”

(Later I’ll try to add some actual screen shots of what I did)

Providing a select of state options

Since you aren’t using SP workflows, you need to have a column in your database table to hold the state of the workflow. Since columns can only contain one value, you need to create a way of letting a user choose among a variety of predefined options to save to the column. Use InfoPath!

InfoPath allows you to attached a DropDown or Radio to a field. So just add your predefined options to the attached DropDown or Radio and then set your field to read only. Now when a user goes to edit the application, they have a list of “states” to choose from.

Starting the “Workflow”

Now all you need to do is listen for the state change with a database update trigger! This isn’t the most elegant way, but there’s nothing elegant about external list workflows in SharePoint! On the bright side, it’s separated from SP so it can be used with other systems.

My trigger just listens for updates and checks to see what the “state” column is going to be changed to. Various emails are sent based on the new “states” value.

And that is how I do External List Workflows in SharePoint 2010.

Creating an External List From Related Tables In SharePoint 2010 – Part 1

This is going to show you how to create a listing of related database content in SharePoint. Two great resources that help me do this are:

First things first: Create a SQL Server View of Related Content

The database that I will be working with has 3 tables; Person, Jobs and Education.

Populated with

insert into Playground.dbo.Person (first_name, last_name) values('brian', 'strickland');
insert into Playground.dbo.Person (first_name, last_name) values('joe', 'smith');
insert into Playground.dbo.Person (first_name, last_name) values('jane', 'doe');

insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(3, 'Babysitter', '1/1/2002', '3/23/2002');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(3, 'Cook', '1/1/2003', '2/3/2004');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(3, 'Host', '2/4/2004', '7/2/2005');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(3, 'Dancer', '1/1/2002', '3/23/2002');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(3, 'Singer', '1/1/2002', '3/23/2002');

insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(2, 'Researcher', '1/1/2002', '3/23/2003');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(2, 'Unemployed', '1/1/2004', '3/23/2004');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(2, 'Student', '1/1/2005', '3/23/2005');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(2, 'Unemployed', '1/1/2006', '3/23/2007');

insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(1, 'Bum', '1/1/2003', '3/23/2011');
insert into Playground.dbo.Jobs (person, title, start_date, end_date) values(1, 'Homeless', '2/29/2012', '4/1/2012');

insert into Playground.dbo.Education (person, name, degree) values(1, 'BCC', 'AA');
insert into Playground.dbo.Education (person, name, degree) values(1, 'UCF', 'CS');
insert into Playground.dbo.Education (person, name, degree) values(2, 'BCC', 'AA');
insert into Playground.dbo.Education (person, name, degree) values(3, 'BCC', 'AA');
insert into Playground.dbo.Education (person, name, degree) values(3, 'UCF', 'Art');

We will create the view so that returns a list of all rows in Person with each of it’s related Education and Jobs data by choosing the following columns.

The problem with this, is that I don’t want to display 17 rows to my SharePoint users.  I only need to show them the 3 employees and merge the name, degree, etc fields.  To do this I need to write a SQL function for each of my related tables that will combined the related row data into a single column.

The way I go about doing this is that I create a function for each related table that I need to concatenate into one column and replace that select column in my sql view to use that function.  For example the default sql view is


SELECT dbo.Person.id, dbo.Person.first_name, dbo.Person.last_name, dbo.Education.name, dbo.Education.degree, dbo.Jobs.start_date, dbo.Jobs.title
FROM dbo.Person
INNER JOIN dbo.Education ON dbo.Person.id = dbo.Education.person
INNER JOIN dbo.Jobs ON dbo.Person.id = dbo.Jobs.person

So what I want to do is replace all of the dbo.Education.* with a function call that will concatenate all of the Education data for a single person and a function call that will concatenate all of the dbo.Jobs.* data for a single person.

To create a single string for the Education data create the following scalar function:


CREATE FUNCTION [dbo].[fnGetEducation] (@person INT)

RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @list VARCHAR(8000)
SELECT @list =(

SELECT name + ', ' + degree + '; '
FROM Education
WHERE person = @person
AND name IS NOT NULL -- only care about the name
ORDER BY name
FOR XML PATH('')
)

RETURN LEFT(@list,(LEN(@list) -1))
END

To create a single string for the Jobs data create the following scalar function:


CREATE FUNCTION [dbo].[fnGetJobs] (@person INT)

RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @list VARCHAR(8000)
SELECT @list =(

SELECT title + ', ' + CONVERT(nvarchar(30), [start_date], 126) + ' - ' + CONVERT(nvarchar(30), end_date, 126) + '; '
FROM Jobs
WHERE person = @person
AND title IS NOT NULL
ORDER BY title
FOR XML PATH('')
)

RETURN LEFT(@list,(LEN(@list) -1))
END

Once you’ve done that ALTER the view by replacing


dbo.Education.name, dbo.Education.degree

with


(select dbo.fnGetEducation(dbo.Person.id)) as [Education]

and


dbo.Jobs.start_date, dbo.Jobs.title, dbo.Jobs.end_date

with


(select dbo.fnGetJobs(dbo.Person.id)) as Jobs

Now if you execute your view query you’ll get

Now you have a view that SharePoint can build external lists from.  In Part II I will describe how to do this.