If you don’t want the context menu to show up in your list
You need to modify the XSLT template (site/lists/list_name/read_list_name.aspx). Before I could view the XSLT content I had to drag part of the grid… and remove the following
<xsl:attribute name="height">100%</xsl:attribute>
<xsl:attribute name="onmouseover">OnChildItem(this)</xsl:attribute>
</xsl:if>
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:
- How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services
- Concatenate Values From Multiple Rows Into One Column
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(‘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
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:
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:
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
with
and
with
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.
I had a friend running Windows Vista and whenever she tried to close the gadgets, her computer would freeze. The fix to this was pretty simple:
1) Go to “%localappdata%\Microsoft\Windows Sidebar”
2) Copy “Settings.ini” to somewhere else
3) Delete “Settings.ini”
4) Log off and log back on.
require_once(‘Zend/Validate/Abstract.php’);
class Custom_Zend_Validate_MaxWordCount extends Zend_Validate_Abstract
{
const INVALID = ‘tooManyWords’;
protected $_max;
public function __construct($max = 100, $subject)
{
$this->_max = $max;
$this->_messageTemplates = array(
self::INVALID => "$subject has more than $max words."
);
}
public function isValid($value)
{
if(count(explode(‘ ‘, preg_replace(‘/\s+/’, ‘ ‘,$value))) > $this->_max){
$this->_error(self::INVALID);
return false;
}
return true;
}
}
I develop with a development (my computer), staging (internal remote server) and production (public remote server) environment and most of my application code is not a sibling to the websites public code. (This is not ideal, but I won’t get into why it’s like this.)
I develop my projects using SVN so after I’ve committed my final changes from my development environment to the SVN server, I have to manually export from the SVN server to the staging server. This is prone to accidental exporting to the wrong directory (especially since our sites live in two different places, due to the issue I’m not mentioning.) So I needed a more automated way to do this. Ant was the way.
Below is roughly the Ant build file that each of my projects use. Basically what is does is 1) run all of the unit tests, 2) if the unit tests pass, export the latest revision from the SVN server to the Staging server.
<description>Build My Project Applications</description>
<target name="build">
<!– Run all unit tests –>
<exec dir="${basedir}/tests/" executable="C:\PHP\phpunit.bat" failonerror="true">
</exec>
<!– Export to stage –>
<taskdef name="svn" classname="org.tigris.subversion.svnant.SvnTask">
<classpath>
<fileset dir="C:\Program Files\Eclipse\plugins">
<include name="**/svn*.jar"/>
</fileset>
</classpath>
</taskdef>
<echo message="Exporting application files from svn repository:" />
<input message="Please enter svn repo username:" addproperty="svn.username" />
<input message="Please enter svn repo password:" addproperty="svn.password" />
<svn username="${svn.username}" password="${svn.password}">
<export force="true" srcUrl="svn://svn.server/my.website.com/trunk" revision="HEAD" destPath="\\stage.server\area\away\from\website\my.website.com" />
</svn>
</target>
</project>