Archive for the ‘Lists’ Category

Setting The Default Value Of A DateTime Column

September 3rd, 2011 Abed Khooli 1 comment

This is a very brief post that addresses the calculated default value of a SharePoint 2010 column. There is a difference between a calculated column (you set the column type to: Calculated (calculation based on other columns)) and a calculated default value (additional column settings: default value). In the latter case, you can use volatile tokens like Today or Me – since they are used only once to initialize the column value. Volatile values should not be used for calculated columns.
So, if we have a column whose type is DateTime and we do need to keep both date and time, and also need to initialize it with current date (including time), the interface will not be very helpful. You can set the default value to Today’s Date but that will not get the time accurately.  It turns out that the Calculated Value option accepts the familiar Now() function. So, just select calculated value and type = NOW() as the formula. In a new form, the date and time controls will be filled automatically with current date and time (to the nearest minute).
If you have access to InfoPath and the list can be customized, you can easily set the default value for the date control (it will also use the Now() function).
Notice that the default value (or whatever is saved in the column) does not change (automatically) if you edit the item later (as opposed to using volatile functions in a calculated column).

Categories: Lists Tags: calculated default value,

Exporting Large Lists To Excel 2010

August 13th, 2011 Abed Khooli No comments

You try to export a large list (say over 100000 items) to Microsoft Excel 2010 and guess what? Only the first 52000 rows (plus the header) are exported. You also get a warning that reads “you do not have permission to view the entire list because it is larger than the list view threshold enforced by the administrator.” However, using Excel 2007, you can export the whole list without any complaint (a test was done on a little over 100000 items). Office 2010 is integrated with SharePoint 2010 more than 2007 is and this is one downside of this integration – or is it? Consider performance implications if several users start exporting large lists to Excel (may be security issues as well – taking data away easily).
The warning message resonates very well with the resource throttling in SharePoint 2010 except that the default threshold for a user is 5000 items only. In fact, the fix is to adjust resource throttling.
From Central Admin – application management – manage web applications, highlight the specific web applications and from the web application’s general settings (ribbon), select resource throttling and adjust the limit (for end user or for auditors and administrators) or enable and specify a time window for large queries. Export the list and reset the throttling back when done.

Categories: Lists Tags: 52000 rows exported, Export to excel 2010, Resource throttling

How To Display Tasks Assigned Directly And Through A Group

August 4th, 2011 Abed Khooli No comments

Let’s say we have a traditional SharePoint 2010 Tasks list. The Assigned To field allows both users and groups. Tasks are being assigned both on individual level and on group level. How do you filter your tasks? SharePoint has several views for the Tasks list and two of them are relevant: My Tasks and By My Groups. The first is obvious and the second refers to the group membership of the current user. So, the answer is in the combination of the two.
An easy trick would be to create a web part page and add two web parts to it – one based on the My Tasks view (edit web part, select view) and the other based on the By My Groups view. You stack them on top of each other and that should work.
What we will do in this short article is actually combine them in one view. I created a view (in the browser) based on the By My Groups – actually exact copy. Then I headed to SharePoint Designer and opened the view for editing. I also opened the My Tasks view and switched both to code view. The focus is on the QUERY part. All you need to do is use an Or operator and copy the criteria from the My Tasks view.
The image shows the final query in context (code view of a copy of By My Groups view).
You can see, in the inset, that I have my tasks grouped (expandable and collapsible) whether they were assigned explicitly or implicitly (as part of a group I am a member of).

Categories: Lists, SharePoint Designer 2010, Views Tags: group tasks, my tasks

The SPXSLT Project And How To Turn Announcements Into Marquee

June 11th, 2011 Abed Khooli 2 comments

Like several other posts, this one was inspired by a comment on my earlier post (Horizontal News Ticker Using SharePoint 2010 CQWP) and a question on SharePoint Forums about limiting the number of characters in the summary view of an announcements list. Usually the announcements Body field is rich text and if you try substring($thisNode/@Body,1,n) where n is the number of characters to display, you will end up with an html tag that may not get closed within the character limit. So, you need to strip the html tags first and this functionality is not available in SharePoint Designer.
Luckily, Marc Anderson, who maintains the SPXSLT project at Codeplex, had a ready xsl template which limits the Body to the first n words, which is even better. You should check Marc’s work and the SPXSLT project as it could save you plenty of time.
In a dataview web part, you can customize one item or the entire view (which could easily inject several hundred lines of XSL when you switch to code view). This is usually the same xsl that serves several types of lists. Sometimes, you only need a few lines to get your data in the rights format. Marc’s template is under 80 lines (including empty lines) and does even more than the standard one.
What you do (and this is for lists of type announcements) is to open the page that has the web part in SharePoint Designer, have the web part selected and from List View Tools (ribbon), Design tab, Customize XSLT (customize Entire View – click the drop down arrow to get there). Switch to code view and the block of code should be selected. Select the portion of code between and (several hundred lines) and delete it. You will use your own instead. You could use the one from Marc’s post as is, but here, we will do something different. The actual announcements view is rendered as table cells and we would like to make use of the liScroll Jquery plugin and turn our announcements linked titles into a news ticker or marquee.
In this case, we don’t need the html manipulation or even the word count. I based my template on Marc’s but removed all table related markup, used and kept the Title field only. I also gave the

    element the class name dfwp-list for no specific reason other than matching the class used by the CQWP (so earlier instructions work without modification). The rest is the same as outlined in the Horizontal News Ticker post.
    Note: The @FileRef will not actually link to the item display form (this is how it reads in the original). To fix it, try to view one item from a standard announcements list and code the url accordingly. It should be something like: http://current-web-address/_layouts/listform.aspx?PageType=4&ListId=list-GUID&ID={@ID}&ContentTypeID=0x010400B0AC1F599DF7584FA331A0991AB8E6ED
    You need to enter your own values for current web address and list-guid (without the braces!)
    One more note: if you add your announcements as a dataform instead of xsltview, you can use the design group (dataview tools) and select a bullet list. In this case, you can link titles to the display form directly from the UI.

Categories: CEWP, DataView, Lists, SharePoint Designer 2010 Tags: , XSLT

List Of All Lists In A Web Using ListOfLists DataSourceMode

June 8th, 2011 Abed Khooli No comments

I wrote earlier about using a SOAP service to get an inventory of lists within a certain site. This post uses a different approach and can also be filtered for a particular type of list. So, if you need to view all task lists or calendars or document libraries or surveys or even external lists based on the __spBaseTemplate property.
The approach is fairly simple. Create a web part page in the relevant site and insert an empty data view (insert – data view – empty data view). You will get a link to select a datasource and you can select any list (in this example, the Links list was selected – random selection). You have a few fields selected in the data source details pane to the right, so you just select the “Insert selected fields as …” and choose Multiple Item View from the drop down (this is in the data source details pane near the top, select view/data source from the ribbon if the datasource is not visible). You should see some rows if the selected list is not empty.
Now, switch to code view (bottom left of the status bar) and locate the List” fragment of the code and change List to ListOfLists and switch back to design view.
You may notice something strange: a grid with several rows and no data inside. That’s ok. We’ll fix soon. While the dataform web part is selected, go to Add/Remove columns from the ribbon (data view tools – options tab). Remove the select fields (right side) and select a few of those starting with 2 underscores (__). Enlarge the image of the example to get a few to start with (I did not rename them for illustration, you should). In the Paging option (next to add/remove columns), select Display all items. That should give you a list of all lists (libraries included) within the site.
if you want just a specific type of list, click Filter (ribbon options) and then from the filter dialog, check add XSLT filtering and click Edit to add your filter. For example, use [@__spBaseTemplate='ExternalList'] to get only external lists.
You can adjust to dataform view, link lists or conditional format as you need, including the title of the web part which is named after your selected list by default. Notice that this works within one site (by default, the site you created the page in).

Categories: Data Form, Libraries, Lists, SharePoint Designer 2010 Tags: ListOfLists