marți, august 07, 2007

A Better HREF handing

After converting a list to XSLT dataview, I am having some problem with URLs inl lists, so I make it a little easy to use for editing XSLT.

To make this work, create a new list, and give it a fieldname of URL which is a link (href) type list object.

Here is a much better way to encode the a href tags:


<xsl:variable name="ThisURL" select="substring-before(@URL, ', ')" />
<xsl:variable name="ThisTitle" select="substring-after(@URL, ', ')" />
<xsl:if test="normalize-space($ThisTitle)!=''">
<div class="ms-vb2">
<A HREF="{$ThisURL}" title="{$ThisTitle}"><xsl:value-of select="$ThisTitle" /></A>
</div>
</xsl:if>


If you name the field something else like 'mylink' or something, just replace @URL in the code above with your fieldname.

Hope that help you.

joi, aprilie 26, 2007

HTML Encoder

hen using blogger, I sometimes have to put javascript into the blog post. In order to do this, I have to HTML encode it.

My favorite HTML encoder:

http://www.opinionatedgeek.com/DotNet/Tools/HTMLEncode/Encode.aspx

And just to be safe, here is my favorite URL encoder:

http://www.w3schools.com/tags/ref_urlencode.asp

Views Filtered by URL Parameter

These views are wonderful, however, when people bookmark them or stumble upon them without a URL parameter, they are nonsense.



So we use some javascript to let the user know that they have come into the page by mistake.




function getQueryVariable(variable) {
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if (pair[0] == variable) {
return pair[1];
}
}
alert('This page is for viewing a filtered report. Please return to this page with a valid filter.');
}
var IntRecordID = getQueryVariable("ID")


This will alert the user that they have come to the page without a Querystring variable and that they need to go back and rethink what they are trying to do.

miercuri, aprilie 25, 2007

Calculated Field: Quarters

The Company has a fiscal year that starts on June 1. I need a field in my list that shows what quarter and year this date falls in, so that I can sort/filter by that data instead. The Company Fiscal Quarters can be calculated.

Create a new field, calculated, and put this formula in:

=IF(MONTH([TheDate])<=6,YEAR([TheDate]),YEAR([TheDate])+1)&" Q 0"&MOD(CEILING(22+MONTH([TheDate])-5-1,3)/3,4)+1

If you are trying to do a different fiscal, change the 5 to whatever month is the last month of the quarter, and change the 6 to the first month of the quarter (november in this case):

=IF(MONTH([TheDate])<=11,YEAR([TheDate]),YEAR([TheDate])+1)&" Q 0"&MOD(CEILING(22+MONTH([TheDate])-10-1,3)/3,4)+1

You can modify it by removing the year, which will just give the quarter number (november in this case):
=MOD(CEILING(22+MONTH([TheDate])-10-1,3)/3,4)+1

And of course, you can create another field that will just give you the fiscal year (november in this case):
=IF(MONTH([TheDate])<=11,YEAR([TheDate]),YEAR([TheDate])+1)

Have Fun with this new information!

joi, aprilie 19, 2007

The Fallacy of Savings

On Sale: You can save a dollar a million times, but in the end you don't have a million dollars.

miercuri, aprilie 18, 2007

Some better Javascript for Editforms

From this discussion group:

<script language="javascript">
<!--
document.forms('OWSForm').elem­ents('urn:schemas-microsoft-co­m:office:office#
YOURFIELDNAMEHERE').disabled=t­rue;
document.forms('OWSForm').elem­ents('urn:schemas-microsoft-co­m:office:office#
YOUROTHERFIELDNAMEHERE').disab­led=true;
//-->
</script>

One caveat: If the FIRST field on the form is disabled, you'll get an error about not being able to set the focus to it.

luni, aprilie 16, 2007

How to make a blog with SP 2003

How to make a blog with SP 2003:


The basic idea is to use the historical/version control feature of the issues list to keep track of the blog and keep the comments information.


The Title, Due Date and Description are the main 'blog' parts. The title is the title, the description is the blog post, and the Due Date is the post date.


The Comments field is where the blog gets really interesting. What is actually happening is that when someone posts a blog entry, they are adding a new item to the list. When someone else (or the blogger) wants to 'add' a comment, they are not actually adding anything – they are EDITING the blog post item, and putting their own comments in!


The tricky part is working with permissions so that people do not have 'add' capabilities, nor do they have the capability to edit the title, description, or post date fields, and we only give them the ability to edit the comments field. Since SP already keeps track of versioning, each version is just another edit to the original post.


Some of the links include a 'source' query string variable. This is passed from some pages to other pages so that Share Point can pass the user back to the proper page whenever the user saves or deletes or adds or takes other actions. The value of the source variable when used in this tutorial is a URL-encoded URL pointing back to the default view page of the list you are using. You can copy and paste your URL into a tool here: http://www.w3schools.com/tags/ref_urlencode.asp which will IRL-encode your URL for you. As an example, my source is: http%3A%2F%2Fteams%2Fsites%2Fmysite%2FLists%2Fmyblog%2Fblogview%2Easpx


Making a template based on the new list:


There are many problems with making a new template from this new list. All of the modified pages will not point to the correct list or web part, and they will be broken. The broken elements will be:



  • AddComment.aspx (points to the wrong list)

  • EditForm.aspx (points to the wrong list)

  • NewForm.aspx (points to the wrong list)

  • BlogViewWComments.aspx.



The process:



  1. Create an Issues List.

  2. Rename the Due Date to 'Post Date', default is today/now.

  3. Create a new field called 'Blog', and make it a multi-text field, Required, display 15 lines, Allow rich, add to default view.

  4. Disable attachments.

  5. Change the permissions for the list:




    1. Web Designer: [REMOVE THIS]

    2. Contributor: View, Add, Edit

    3. All Users: View, Edit

    4. Blogger: Add, Edit, Delete, View




  1. Create a new view.


    1. Standard View

    2. Named "BlogViewWcomments"

    3. Public

    4. Fields: ID, Comment, Modified, Blog, modified By

    5. Sort by Modified Date, descending

    6. Filter: Show all items in this view.

    7. Group By: Title

    8. Leave the rest at default.


  2. Create two blog posts using NewForm.aspx. Create some comments for each post by editing the item and typing something into the comment field. Do this several times, and you will have several 'comments' for the post.

  3. Open BlogViewWcomments.aspx in FrontPage.


    1. Right-click the web-part, convert to XSLT.

    2. Layout the elements how you want them to appear in the final version:






      • Remove the "Current" column If it's in there (Table à Select à Column, then Press the Delete Cells button).

      • Select the text of one of the Blog cells, click 'Conditional Formatting, create, show content. Current = 'YES'.

      • Select the first row, (Table à Select à Row), insert row below (Table à Insert à Rows or Columns)

      • Drag the first comment text to the new row so that it is now underneath the blog post text.

      • Remove the now empty "Comment" column (Table à Select à Column, then Press the Delete Cells button).

      • Drag the first Modified By table (Click on the Modified By text once, then select the table it is in by clicking the TABLE tag just above your open document, sorry I don't know what that toolbar is called), cut it out and paste it into to the new row so that it is now underneath the blog post text, but above the comment text. I usually discard the presence information and just use the name text, but that is up to you.

      • Remove the now empty "Modified By" column (Table à Select à Column, then Press the Delete Cells button).

      • Drag the first Modified date text to the new row so that it is now positioned before the Modified By text.

      • Remove the now empty "Modified" date column (Table à Select à Column, then Press the Delete Cells button).

      • Put the Post Date field in. I put it in the group header, and took out the word 'Title' so that the header now reads: "+ [Date]: [Title]". Looks nice.

      • Pretty the view up a bit by putting "On" and a space before the modified date, a space between the modified date and the name of the person who modified it, and then put "Said:" and a soft-return (Shift+Enter) after the "Said:". Convert the Modified Date to a Date/Time format that looks nice (right-click format item as à Date Time).

      • Go into code view and place a DIV tag around the entire comments section (Modified by, Modified Date, comments) so that we can apply some conditional formatting.

      • Go back into design view and select the DIV tag you just put in. Hide content when "Comment" is NULL.

      • Go into code view and make it so that the conditional formatting applies to the entire TR tag, not just the DIV tag. Move the xsl:if statement from just before the DIV tag and place it just before the tr tag, and then take the /xsl:if tag and place it just after the /tr tag.

      • Pretty it up even more by adding a line on top of the comment TD (change the td tag and put Style="Border-top: 1px solid #dddddd;"). Get rid of any frivolous columns (ID). Maybe indent the blog post from the title, and indent the comments from the blog post, add a graphic like a person with a talk bubble for each comment, or whatever.

      • Change the document title. Change "<SharePoint:ListProperty Property="Title" runat="server" __Preview="BlogGeneric"/>" to "<SharePoint:ListProperty Property="Title" runat="server" __Preview="Blog View With Comments"/>".

      • Select the last row of records, and insert a new row underneath. This will give you a row under each record. In the new row, join all of the columns together (if not already). In the new cell, insert a new table, with two rows and two columns, no border, no padding, no spacing, 100% wide. You might have to tweak it in code view so that the table sits in the correct location and is repeated for each record. Populate the table with links:


        • Comments link: Make a link in the first cell of the table, and link to google or some other placeholder. Go into code view and change the link so that it is dynamic for the ID of the blog item: href="AddComment.aspx?ID={@ID}&amp;source=[URLencoded URL of the BlogView.aspx page]"

        • View All Comments link: Make a link in the second cell of the table, and link to google or some other placeholder. Go into code view and change the link so that it is dynamic for the ID of the blog item: href="BlogViewWcomments.aspx?Issue_ID={@IssueID}&amp;source=[URLencoded URL of the BlogView.aspx page]"

        • Join the two columns of the second row together. Alert Me link: Make a link in the second row of the table, and link to google or some other placeholder. This Alert Me link will provide an alert when someone comments on this SPECIFIC blog entry. Go into code view and change the link so that it is dynamic for the ID of the blog item: href="http://teams/sites/mysite/_layouts/1033/SubNew.aspx?List=%7B[LIST GUID GOES HERE]%7D&amp;ID={@ID}&source=[URLencoded URL of the BlogView.aspx page]"

        • Move the cursor down to the bottom of the list, and just outside of the web part zone, add a new table, with 1 row and 1 column. In the table, provide a link to get alerts for the blog, and to view all my comments.


          • The alert link is the same as the previous alert link, without the ID query string variable: href="http://teams/sites/mysite/_layouts/1033/SubNew.aspx?List=%7B[LIST GUID GOES HERE]%7D&amp;amp;amp;amp;amp;amp;source=[URLencoded URL of the BlogView.aspx page]"

          • The My Comments link goes to MyItems.aspx.







  1. Save the file.

  2. Save the File as "BlogView.aspx"

  3. Close "BlogView.aspx"

  4. Open "BlogViewWcomments.aspx"


    1. In code view, find <ParameterBinding Name="filterParam" Location="Postback;Connection"/> and change it to <ParameterBinding Name="filterParam" Location="Postback;Connection;QueryString(Issue_ID)"/>.

    2. In design view, Change the filter: Issue ID = [Input Parameter]

    3. Make the alert/add/view links appear only once, because the user is only looking at one blog entry: In code view, remove the "add a comment/View all comments/Alert me" link table from the comments row, and move it into the row encased by the "<xsl:if test="normalize-space(@IsCurrent) = '1'">" tag.


  5. Open BlogView.aspx in FrontPage.


    1. Change the filter to Current = YES and PostDate Less than or Equal to [Current Date].

    2. Above the comment for each post, type "Latest Comment:" and put a soft-return at the end. Make it bold too while you're at it.

    3. Change the display name to Blog View. Change "<DisplayName xmlns="http://schemas.microsoft.com/WebPart/v2/DataView">BlogViewWcomments</DisplayName>" to "<DisplayName xmlns="http://schemas.microsoft.com/WebPart/v2/DataView">BlogView</DisplayName>".

    4. Change the document title. Change "<SharePoint:ListProperty Property="Title" runat="server" __Preview="BlogGeneric"/>" to "<SharePoint:ListProperty Property="Title" runat="server" __Preview="Blog View"/>".

    5. Default: Open site in FP, navigate to the lists folder (you can just go up a folder level from where you are now, if you are editing BlogView.aspx), then right-click the bloglist, select Properties. Go to the Supporting Files Tab. Make sure default view page is BlogView.aspx.

    6. Change the sorting and grouping so that it is:






      • Post Date: Descending, not grouped

      • Title Descending, Grouped

      • Modified, Descending, not grouped





  1. Open EditForm.aspx in FrontPage.


    1. Save as "AddComment.aspx"

    2. Right-Click, customize Share Point List form.

    3. Hide all of the fields except the comments field. Do this by selecting the TD and adding Style="Display: none;" to the areas you want to hide.

    4. Add the JavaScript to delete the last comment from the comment field. See the end of this document for the code.

    5. Search for the text "onetidinfoblock3&gt;Issue ID :" and replace it with "onetidinfoblock3&gt;Blog Comment ID :".

    6. Search for the text "Issue History" and replace it with "Blog Comment History".

    7. Please note that you cannot go changing all of the occurrences of 'issue' with 'blog' because there are some functional 'issue' in the code.


  2. Open EditForm.aspx in FrontPage. Save it as "EditFormBackup.aspx"

  3. Open EditForm.aspx in FrontPage. Make all fields invisible, and make a note to 'contact the administrator to edit a blog post. Note: Workaround uses EditFormBackup.aspx or deleting a post and re-adding it. This is a security measure, albeit not very secure, from keeping people from editing blog posts.

  4. Default: Open site in FP, navigate to the lists folder (you can just go up a folder level from where you are now, if you are editing BlogView.aspx), then right-click the bloglist, select Properties. Go to the Supporting Files Tab. Make sure default edit page is AddComment.aspx.

  5. Open EditForm.aspx in FrontPage.


    1. Hide all of the fields except the Title, Blog, Post Date fields. Do this by selecting the TD and adding Style="Display: none;" to the areas you want to hide.


  6. Open NewForm.aspx in FrontPage.


    1. Hide all of the fields except the Title, Blog, Post Date fields. Do this by selecting the TD and adding Style="Display: none;" to the areas you want to hide.


  7. Open MyItems.aspx in FrontPage.


    1. In design view, Right-click the web part and change the filter. Instead of "Assigned To Equals [Current User]", change it to "Modified By Equals [Current User]"

    2. I like to change the GROUP BY to TITLE, but this is entirely up to you.

  8. Finishing Up: All pages.


    1. Open all pages and add a header: AddComment.aspx, DispForm.aspx, EditForm.aspx, NewForm.aspx, active.aspx, AllItems.aspx, BlogView.aspx, BlogViewWcomments.aspx, MyItems.aspx, find the "<placeholder…"
    2. With the pages open, go through and convert all of the views to xslt and remove the 'edit in datasheet' mode so that people cannot edit the blog entries.


  9. LAST STEP: Make any more changes you want, but each time you open/view/edit/save BlogView.aspx and BlogViewWcomments.aspx, you MUST re-check your dynamic links. Sometimes, FrontPage will substitute your dynamic links with static links, which breaks your alert, BlogViewWcomments link and your add comments link.



Header Text:

Use this as the header text, and replace the image and details for the blogger:


<table style="padding: 0; margin: 0; border: 0;">

<tr>

<td style="padding-left: 20px;text-align: left; font-face: Arial; font-size: 8pt; color: #dddddd; background-color: #ffffff;">

<p style="font-weight: bold;"><img border="0" src="person.jpg" width="78" height="89" align="left" hspace="2" vspace="2"><br />

<a href="BlogView.aspx" title="Click to return to Blog View" style="text-decoration: none;">Thoughts from our Blogger</a><br/><br/>

<a style="text-decoration: none;" title="Send email message to blogger." href="mailto:this.blogger@example.com?subject=Blog">Blogger Name</a><br>Blogger Title</p>

</td>

</tr>

</table>


JavaScript code to delete the last comment from the AddComment.aspx file:

<script>

<!--

for(i=0; i<document._ctl1.elements.length; i++)

{

if (document._ctl1.elements[i].name=='urn:schemas-microsoft-com:office:office#Comment')

{

document._ctl1.elements[i].value='';

}

}

//-->

</script>




luni, aprilie 02, 2007

Truly dynamic content SharePoint 2003

The purpose of this tutorial isn't immediately clear. What it does is that it allows you to add news articles to one list and mark them as separate content, so that you can have articles displayed on the home page, and you can have links to 'lone' articles that will only be displayed when you want them to.


If you are like me, you hate adding links to 'static' content on your Share Point 2003 site. We are going to create an announcement or news list that will display properly on your homepage, but it will also contain 'hidden' articles that are not displayed unless you provide their ID through the Querystring, and all list items are hidden except the one with that ID.


Here is a solution to THAT problem:


1. Create a news article list. Add a new column to the list, a checkbox (Y/N) and call it 'Hide'

2. Create a view that aproximates what you want.

3. Open that view in FrontPage.

4. Convert to XSLT.

5. IMPORTANT: Go into any of the cells that are in your list, and make a new table. Place ALL content into that table. Delete all webpart content that is not in that table.

6. Make it look pretty by arranging the content on the page the way you want it to look, changing fonts and all that too.

7. Go into Code view in Front Page.8. Search for "Title"

9. Look around the code for your table. The whole table will be enclosed in a tag.

10. Just before your table tag, add a div with class="ophogusshack{@Hide}" and id="article{@ID}"

11. Just after your closing table tag, add the closing div tag.

12. Step 10 and 11 enclosed each list item into a div, with a class of hidden (or not), and an id, so we can call them later.

13. Add the following css style: .ophogusshack1 { display: none; }

14. This class will hide all content that you don't want to display yet.

15. Do this: http://ophogus.blogspot.com/2006/06/filter-list-view-with-url-parameter.html

16. Add the following JavaScript to the bottom of the page in code view, but before the closing body tag:

<script><!--
if (browseris.mac && !browseris.ie5up)
{
var ms_maccssfpfixup = "/_layouts/1033/styles/owsmac.css";
document.write("<link rel='stylesheet' Type='text/css' href='" + ms_maccssfpfixup + "'>");
}

function getQueryVariable(variable) {
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if (pair[0] == variable) {
return pair[1];
}
}
//alert('Query Variable ' + variable + ' not found');
}
var IntRecordID = getQueryVariable("ID")
if (typeof(IntRecordID)!="undefined" && IntRecordID != "")
{
//alert('Displaying only article: ' + IntRecordID + ' and hiding all hidden and unhidden articles except ' + IntRecordID + '.');
promoBox();
document.getElementById('article' + IntRecordID).style.display = 'Block';
}
else
{
//alert ('Display Homepage articles, hiding all hidden articles (hide=1).');
//there is no js here, this should be expected default bahavior.
}

function getElementsByClass(searchClass,node,tag)
{
var classElements = new Array();
if ( node == null )
node = document;
if ( tag == null )
tag = '*';

var els = node.getElementsByTagName(tag);
var elsLen = els.length;
var pattern = new RegExp("(^|\\s)"+searchClass+"(\\s|$)");

for (i = 0, j = 0; i < elsLen; i++) {
if ( pattern.test(els[i].className) ) {
classElements[j] = els[i];
j++;
}
}
return classElements;
}

function promoBox(){
// this searches for all elements with the class name required
// and creates an array

var el = getElementsByClass('barrysjshack0');

// this loops thru the array and sets the style you want
for (var i=0; i < el.length; i++){
el[i].style.display = "none";
}
}

//--></script>

That's all there is to it.

vineri, martie 23, 2007

Mad Libs with SharePoint and Word

My friend Yatin Purohit said:

An easy way is to link a SharePoint Services List (say, contacts) to Access 2003 then use Access as a data source in Word 2003 for mail merge.

In Access 2003, create a new blank database (.mdb). Then in menu File -> Get External Data -> Link Tables. In the dialog box, select "Type of Files" as Windows SharePoint Services(). In the wizard that follows, Type site name and then the List name, plus other option for whole list or a view. You will see the linked table in Access where you can also make chages, queries, etc.

In Word 2003 create the base document, then select menu Tools -> Letters and Mailing -> MailMerge. Use the task pane for mail merge. Open the Mail Merge tool bar and in Data Sources dialog box, select File Type as Access and the new .mdb file. Now you are set to add fields from the list to the mailmerge document.

List storage MSSQL

From: http://developer.ebusiness-apps.com/ebakb/ebakb.asp?artid=174&catid=28


So how does SharePoint store the data for the lists? It turns out that for each site there is a seperate SQL database, containing a number of tables. The two important tables for us are the Lists and the UserData tables. The Lists table contains an entry for each list within the site (and its subwebs) and there is a column called tp_Fields that contains an XML fragment describing the schema for the list. In this schema, each field in the list is associated with a column in the UserData table. The UserData table contains a column for every possible field that can exist in a list. For example, there are 32 floating point columns, 16 datetime columns, etc. The schema in tp_Fields then associates each field in the list with a column in the UserData table. All of the data from every single list in the entire site is then kept in this UserData table.

So it is not a trivial task to write a SQL query that will retrieve all of the data for a specific SharePoint list. First we have to determine which columns in the UserData table correspond to which fields in the list. This requires getting the schema for the list from the Lists table and working out the mappings. Then we can retrieve the proper data from the UserData table, and perform our reporting on that data. To ease this process, I've created an application that goes through the Lists table and creates a seperate SQL View for each SharePoint list. Then we can perform our queries on the View for the list, and it will be as if we are accessing the list itself.

Despre mine

Timi-Ostravia, Republic of Czedvia, Bulgaria
I make web applications. The tools I use are many and far between. I have been maining to learn Ruby and Python, and Perl. These tools are all like Lego to me. They can all be make to build things and have their strengths and weakest.