Posts Tagged ‘SharePoint How-to’

Filter List Items by Date in SharePoint Dataview

I had a scenario where I needed to filter a SharePoint 2010 discussion list to only show discussions that had been updated within the last 60 days.  My Discussion list is inserted on a page using a DataViewWebPart (DVWP).  So, to filter the discussion list as required, select the DVWP, and then create a filter, like so:

 

Next we need to tweak the code of the filter so that it will be "Greater Than Or Equal to Today, minus 60."

In SharePoint Designer Code view, locate the following bit of code that resides towards the end of the DVWP:

...
selectcommand="<View><Query><OrderBy><FieldRef Name="DiscussionLastUpdated"
Ascending="TRUE"/></OrderBy><Where><Leq><FieldRef Name="
DiscussionLastUpdated"/><Value Type="
DateTime"><Today/></Value></Leq></Where></Query></View>"
...

Before we tweak this code, we can safely convert it as follows:

...
selectcommand="<View><Query>
<OrderBy><FieldRef Name='DiscussionLastUpdated' Ascending='TRUE'/></OrderBy>
<Where><Leq><FieldRef Name='DiscussionLastUpdated'/><Value Type='DateTime'><Today /></Value>
</Leq></Where></Query></View>" 
...

And finally, we simply change that code to the following to include OffsetDays (scroll to the right and look at the Today tag):

...
selectcommand="<View><Query>
<OrderBy><FieldRef Name='DiscussionLastUpdated' Ascending='TRUE'/></OrderBy>
<Where><Geq><FieldRef Name='DiscussionLastUpdated'/><Value Type='DateTime'><Today OffsetDays='-60'/></Value>
</Geq></Where></Query></View>" 
...

The list should now be filtered to only show discussions that have been updated within the past 60 days.  If you re-check the Filter settings for the list, it should look like so:


 

 

Reference blogs.msdn.com

Filtering SharePoint Form Data using InfoPath

The goal here is to create a SharePoint form that contains several fields that will be filtered based off previous selections in the form. In the example that follows starting with a Category, only specifc Vendors will be available, likewise for Topics available for selected Vendor, and so on for DurationDescription, and Instructor. What we want to do is filter the available options in the form as selections are being made.

Here is what the final result will look like:

 

Setup SharePoint Lists

So lets get to it. Start with 2 sharePoint lists, my example will use the following:

  • Training Catalog
  • Training Request

The Training Catalog list contains the raw list of available trainings and uses the following columns:

  • Category
  • Vendor
  • Topic (Rename the title field to Topic)
  • Duration
  • Description
  • Instructor

Note: these columns are all single line of text. Lookups or other column types may work, but are untested.

The Training Request list contains the exact same columns as above. Other fields, such as "When" can be included as well, but have no impact on this tutorial either way.

Note: These columns MUST be created as single line of text.

Open SharePoint Form in InfoPath

Open the Training Request list form:

  • List Tools > List > Customize Form, or
  • List Settings > Form Settings > Modify the existing InfoPath form > OK.

 

 

After the form loads in InfoPath, select the first colum (Category) and right click it > Change Control > Drop-Down List Box.
Do the same for Vendor, and Topic.  Filtered data can only be applied to specific Control Types. Drop down fields are ideal for our use case here.
And we will only need to filter data on these first 3 fields, the rest can remain as Text Box controls.

Create InfoPath Data Source

Field: Category

Right Click Category again and select Drop-Down List Box Properties.
Select Get Choice from an external data source, and to the right of Data Source select Add…

  1. In the Data Connection Wizard Create a new connection to: Receive data > Next.
  2. Select the source of your data: SharePoint library or list > Next.
  3. SharePoint site details: the location of the SharePoint site should be prepopulated > Next.
  4. Select a list of library: Training Catalog > Next.
  5. Select Fields: Category, Vendor, Topic, Description, Duration, Instructor, and any other fields you might have included in your Training Catalog list.
  6. Sort by: Categoy > Next > Next > Name your new Data Connection and select > Finish.

Back in the Drop-Down List Box Properties dialog box, the Data source field should now point to your new data connection, in my case named Training Catalog. 

  • To the right of the the Entries field, click on the Select XPath button.
  • In the Select a Field or Group dialoag box, select d:SharePointListItem_RW and press OK.

Note: The Category column does not need to be filtered as it is the first field in our form.

  • Back in the Drop-Down List Box Properties dialog box set the Value and Display name fields both to d:Category.
  • And for good measure, check the Show only entries with unique display names and click OK.

Setup Some InfoPath Filters

Field: Vendor

  • Right click Vendor field and select Get choices from an external data source and set the Data source to Training Catalog.
  • Click the Select XPath button to right of the Entries field.
  • In the Select a Field or Group dialoag box, select d:SharePointListItem_RW.

 

Select the Filter Data… button in the Select a Field or Group dialog box and In the Filter Data dialog box click the Add… button:

  • In the first drop down field choose select a field or group.
  • The Data source should be set to Training Catalog (Secondary). Choose "Show Advanced view" if you cant see the Data source.
  • Under d:SharePointListItem_RW select Category and then press OK.
  • The middle drop down qualifier should be set to "is equal to".
  • In the third drop down field choose select a field or group, select the Show advanced view, and set the Data Source to Main.
  • Then, under the my:SharePointListItem_RW group select Category and click OK four times to close out the various dialog boxes.
  • Finally, set the Value and Display name fields both to d:Vendor and check the Show only entries with unique display names.

Field: Topic

  • Right click Topic field and select Get choices from an external data source and set the Data source to Training Catalog.
  • Click the Select XPath button to right of the Entries field.
  • In the Select a Field or Group dialoag box, select d:SharePointListItem_RW.

Select the Filter Data… button in the Select a Field or Group dialog box and In the Filter Data dialog box click the Add… button:

First Condition

  • In the first drop down field choose select a field or group.
  • The Data source should be set to Training Catalog (Secondary). Choose "Show Advanced view" if you cant see the Data source.
  • Under d:SharePointListItem_RW select Category and then press OK.
  • The middle drop down qualifier should be set to "is equal to".
  • In the third drop down field choose select a field or group, select the Show advanced view, and set the Data Source to Main.
  • Then, under the my:SharePointListItem_RW group select Category and then select the And button to add another condition.

Second Condition

  • In the first drop down field choose select a field or group.
  • The Data source should be set to Training Catalog (Secondary).
  • Under d:SharePointListItem_RW select Vendor and then press OK.
  • The middle drop down qualifier should be set to "is equal to".
  • In the third drop down field choose select a field or group, select the Show advanced view, and set the Data Source to Main.
  • Then, under the my:SharePointListItem_RW group select Vendor and click OK four times to close out the various dialog boxes.
  • Finally, set the Value and Display name fields both to d:Topic and check the Show only entries with unique display names.

 

If you preview the form in InfoPath, the first 2nd and 3rd fields should now only display relevant data based on the 1st and 2nd field filtering. Close the preview when you're finished.

For the final 3 fields, there are no "choices" to make. And thus there will be no more fields to setup filters on.
We simply want to populate the Duration, Description, and Instructor fields.

Set Field Values using InfoPath Actions

  1. With the Topic field selected, choose Home from the top menu and select Manage Rules. In the Rules Pane, name your new Rule.
  2. Under Condition: a default rule should be created "None – Rule runs when field changes" – leave it at that.
  3. Select the Add button under Rule Type: Action and choose "Set a fields value".

 

We will need to define 3 actions, one for each of the remaining fields (Duration, Description, Instructor).

In the Rule Details dialog box:

  • Set the Field to (Main) Duration by clicking the XPath button (remember to "Show Advanced View" to select Main data source).
  • Set the Value by clicking the fx button, select Insert Field or Group… and choose (Training Catalog (Secondary)) Duration field.
  • Before you hit OK, additionally select Filter Data > Add to setup some filter conditions.

In the Specify Filter Conditions dialog box, do as we did before and set your conditions for Domain, Vendor, and this time include a Topic condition as well.

The Specify Filter Conditions should be as follows:

  • (Training Catalog (Secondary)) Category "is equal to" (Main) Category "and"
  • (Training Catalog (Secondary)) Vendor "is equal to" (Main) Vendor "and"
  • (Training Catalog (Secondary)) Topic "is equal to" (Main) Topic

Select OK > OK > OK > OK to close out all the dialog boxes.
And finally, the Value box should look like this: Duration[Category = Category and Vendor = Vendor and Topic = Topic]
Click OK.

Repeat these steps for Description and Instructor by adding 2 additional actions to the Rule created on the Topic field. If you preview the form in InfoPath now, the first 3 fields will be filtered, and the final 3 fields will be populated based on the preceeding 3 fields.

And that's it!!!  

SharePoint Workflow Emails Using HTML

I've been bashing my head against the wall trying to understand why custom HTML used in a SharePoint Workflow "Send an Email" Action looked so awful when delivered.  In short, there seemed to be many more carriage returns, or new lines, than there should have been.  I was also having issues getting table background colors to render, and forcing externally referenced images to show.  

Inserting Custom HTML into Workflow Emails

Well, the good and bad news was that the answer was right under my nose the entire time.  Here's how I tracked down the last 2 issues mentioned above.  To add a background color to a table, insert an image, or implement any other custom HTML, you need to insert the raw HTML into the Advanced Properties window of the Send An Email action (reference).  Simply highlight the Email Action in your workflow and from the Top Ribbon select Advanced Properties > Body > and "…".  From there, you can insert HTML directly into the body of your email.  And it works perfectly, unless you are forgetting one simple thing.

"Optimizing" Workflow Emails 

SharePoint likes to handle HTML very "differently" then most applications.  That goes for all other Microsoft products frankly, and HTML in Workflow emails is no exception.  The key point to remember is that Workflow Emails containing HTML are expecting everything to be in one long string (reference).  No line breaks or carriage returns between any of your tags can be present.  Technically, this does shave a few hundred bytes (or maybe even a KB) off the document size, and could thus be considered "optimized."  However, the main issue here is that we are already jumping through so many hoops when working with the SPD String Builder (i.e. the Body editor of an email):  Copy/Paste is not possible, for instance, unless all "Lookups" have been removed.  By forcing HTML to be one long string we now introduce another issue where the HTML becomes nearly unreadable to human eyes should troubleshooting or future modifications be required.

Just to verify that indeed SharePoint expects HTML to be one long string, we can do the following.  Create a new "Send an Email" action and include some generic text, without using html.  Something like this:

If we then use the steps above to access the Advanced Properties of the Send an Email action, we will see exactly what has occurred behind the scenes:

Thus, we need to follow suit if we wish for our emails to render even remotely close to what our markup dictates. Seriously though, I assumed the DTD for SPD 2010 was HTML 4.0, but HTML 3.2??? OMG! These days, most modern Email Clients will accept at least XHTML 4.01 Transitional, and that is the markup DTD that I am now using for all my SPD 2010 Workflow emails.

Final Note on SPD 2010 "Optimization"

Back in the days of SPD 2007, it was really easy to Optimize HTML for pages that were not connected to a SharePoint site.  You could just go File > New > HTML Page, paste in your HTML, and Optimize it.  This really elevated SPD from being strictly a SharePoint tool to a handy Webmaster tool overall.  The Optimize functionality still exists in SPD 2010 – its buried in the menu Ribbon under Spelling > Optimize HTML > Remove Whitespace > HTML all whitespace.  However, by the nature of SPD 2010, this feature can only be used on a page that "lives" in a SharePoint site.  So in theory you could create a document library that contains workflow HTML templates, in a human readable form, and whenever you need to Copy/Paste the HTML into a workflow, just temporarily run the Optimize HTML function against your page, Copy/Paste the "Optimized" HTML into a workflow, and close the HTML page in SPD without saving it.  While this isn't a terrible idea, it does seem counter-intuitive and clunky in some ways, but maybe it's just me being difficult.

Removing Sealed Site Columns from SharePoint Lists

You may come across an issue where you need to remove a site column from a Site Content Type associated with a list, which of course is not possible using the native GUI (no remove button).

Instead you need to use the Sharepoint Management Shell to remove the offending field.

  1. Logon to the SharePoint Server.
  2. Run Sharepoint Management Shell as Administrator
  3. swap out the following with your information and run it in Sharepoint Management Shell.
$web = Get-SPWeb http://host.domain/sites/sitename
$list = $web.Lists["List Name Here"]
$field = $list.Fields["Field Name Here"]
$field.AllowDeletion = “true”
$field.Sealed = “false”
$field.Delete()
$list.Update()
$web.Dispose()

If you are having issues, you can try double checking some alternate names of your field using the following:

$web = Get-SPWeb http://host.domain/sites/sitenamehere
$web.AvailableFields.SchemaXml | Out-File c:\temp\fields.txt

You will be looking for the correlating Field ID of your field name.  For example, if im looking for "Description", id find this string:

<Field ID="{34A72E09-3CA6-4931-B2E3-F81C40BB87BD}" Name="RoutingRuleDescription" StaticName="RoutingRuleDescription" SourceID="http://schemas.microsoft.com/sharepoint/v3" Group="Document and Record Management Columns" DisplayName="Description" Type="Text" Required="TRUE" CanToggleHidden="TRUE" Sealed="TRUE" MaxLength="255" ShowInDisplayForm="FALSE" Version="3" Description="Provide a description." />

 

 

Return top