Home - Laura Rogers @WonderLaura

8/29/2010

External Content Types - SQL Authentication, NO Kerberos

It’s so easy, even I can do it! In SharePoint 2010, Business Connectivity Services (BCS) is what used to be the BDC in SharePoint 2007. With the BCS, you create External Content Types (ECT) that let you define a connection to a database or web service that is external to SharePoint.

The actual steps of the creation of the External Content Type are actually the most intuitive of this whole process. It’s the security / authentication setup that is the most complicated. There are several levels and options for giving access to the data you connect to. First of all, you can go here to go through those steps of creating the ECT, here’s a MSDN page:

How to: Create an External Content Type Based on a SQL Server Table

Also, here’s a good Introduction to Business Connectivity Services.

I’m no BCS guru like Brett Lonsdale (@brettlonsdale), but I’m going to explain some very high level information about the authentication stuff, and then show you the steps for a scenario where we will use a single SQL account to impersonate when we access the external data from SharePoint.

Database Level
- You have the option of passing the “User’s Identity” to the external data source. This means that each user who will be accessing the ECT data will need to have direct access to that back end database, such as a SQL database. Also, when the external data is on a separate server than SharePoint (most likely), than you’ll need to implement Kerberos authentication on your farm because of the double hop issue.
- You can also use impersonation, which means that you can give a single account access to the external database, and simple impersonate that account when accessing the data.
ECT Level

In Business Data Connectivity Service Application in Central Admin, you use the permissions buttons in the ribbon to set permission on individual objects or set the default and propagate it to all objects.

SharePoint List Permissions

Once external content types are created, you can create SharePoint external lists from them, from within SharePoint Designer. Once the list has been created, you can set permissions on it just like any other SharePoint list.

Here are the steps to take your external content and make it available in SharePoint, and make it accessible to the desired group of people. I have to admit that these steps are high level in some places, for example I’m not going to talk you through database permissions in SQL. Also, SQL needs to be in mixed mode if you’re going to be using a SQL account as opposed to a Windows account. This post is about how to impersonate a SQL account, but the Windows account steps are pretty similar.

What to know ahead of time:

Which SQL account are you going to use, to impersonate.
Which group of domain users will you be giving access to the data? In this example, I’m using “Domain users” which is everyone, but you don’t have to give access that liberally.

In SQL:
Give the SQL account the appropriate level of access to the database that will be used in the SharePoint External Content type. So whatever permission you give this account, will be the effective permissions of the group (domain users in this case).
In SharePoint Designer 2010:
Create the External Content Type. The authentication type can be initially set as “User’s Identity”. This will be changed later. Create an External List also, if you so desire. With this, users will be able to edit the database data directly.
In Central Administration, Open the Business Data Connectivity service application.
Check the box next to the External Content Type that you just created, and click the Set Object Permissions button.
Give the appropriate group access, such as Domain Users = Edit. Again, this part depends on the situation and your preferences.

In testing I like to use the practice of granting permissions liberally at first, just to make sure everything’s working, and then go back through and start taking things away.
In Central Administration, Open the Secure Store service application.
If this is the first time that secure store has been used, you will need to generate a new key before the first secure store item can be created. You will be prompted to create a password.

Click the New button in the ribbon at the top left.

Target Application ID - make up something that is relevant to the name of the SQL server you’ll be authenticating to, and the type of authentication, such as ServerASQL

Display Name - can be something like “ServerA SQL Group”, which is descriptive as to what this target application is going to be used for.

Contact Email - the email address of the target application’s primary contact person

Target Application Type - Pick Group. This is so that a whole group of users can impersonate the one SQL account. If individual was selected, it would only be a one to one mapping of one person impersonating one SQL account.

Click NEXT.
For the first field name, call it SQL Account, and pick “User Name”. For the second field name, call it SQL password and pick “Password”. Click NEXT.
Target Application Administrators - pick the account of the person who is responsible for this target application.

Members - This is the group that you want to be able to impersonate the SQL account. Pick domain users if you would like everyone in the company to have access to the data. The security permissions were given to the SQL account at step one in this process will dictate what type of access this group will have.
Click OK.
Back on the main screen of the Secure Store service application, click the drop-down box on the name of the item that you just created. Click Set Credentials:
Type the name of the SQL account that you want to use, and the password. Click OK.
In SharePoint Designer
Open the external content type that was created at step two.
Click the Edit Connection Properties button in the ribbon.
Change Authentication Mode to “Impersonate Custom Identity”.
In the Secure Store Application ID box, type the ID that you created at step 4b above.
Click OK and save the external content type.

Now, when the external list is created from the external content type, all domain users will have access to it.

Technorati Tags: SharePoint 2010,Business Connectivity Services,External Content Type,SQL

Posted at 3:54 PM by Laura Rogers | Category: BCS | Permalink | Email this Post | Comments (0)

8/9/2010

InfoPath User Roles in Browser-Based Forms – Another Way (Part 2 of 2)

In my previous post, I showed you the concept of creating a web service data connection to receive info about the currently logged in user. I showed how you can use information about the current user’s memberships, in order to mimic the user roles functionality. In this post, I’ll show how you can use a similar role functionality, but without having to use any groups at all. No distribution lists from AD, and no SharePoint groups. You’re just going to look at properties of the current user, and perform actions in the form accordingly. I’m going to use the same example that I used in the previous post, of a form that is filled out by end users, but that IT dept users will open to fill out more fields just for them.

Hopefully, your Active Directory profile information is accurate. If it is then this solution will be perfect for you. This method does not use SharePoint groups, but properties of the current user’s profile. This information comes from User Profiles and Properties in Central Administration, which in many companies is imported from Active Directory. We will create a form open rule that looks at the Department field in the current user’s profile.

You’re going to use the “Department” of the current logged in user, in order to determine if the current user is in the IT department. If they ARE, then they will get to see the “IT Dept” view of the form when they open it.

A data connection to the UserProfileService web service will be created again, but this time with a different operation selected.

Create a new data connection in the InfoPath form, to receive data. The source is a web service. (In SharePoint 2010, pick SOAP web service.) Click NEXT.
Type the URL of the userprofileservice.asmx file. This is at the root of your SharePoint site, under the _vti_bin folder. Click Next.
For the operation, click GetUserProfileByName, and click NEXT.
On the Parameter screen, just leave the default and click NEXT.
Leave “store a copy of the data…” unchecked, and click NEXT.
Leave “Automatically retrieve data when form is opened CHECKED, and click FINISH.
Next, a field will need to exist, in order to temporarily store the department name of whomever the currently logged in user is. This is not to be confused with the name of the department that you may have captured when the person originally filled out the form. This new field’s value will actually change each time a different person opens the form.
Create a new text field in the InfoPath form. Name it CurrentUserDepartment.
In the properties of this new field, click the little function button for the default value. fx
You’re going to set up this field so that it defaults to the current user’s department name. On the Insert Formula screen, click Insert Field or Group…
Pick GetUserProfileByName as the data source, click the name of the Value field, but don’t click OK yet. Click the Filter Data button.
Click the Add button, so we can add one filter.
Pick Name is equal to Department, and click OK on the four different screens.
This is what your CurrentUserDepartment field’s default value will look like now. Be sure to LEAVE the check box checked, for “Refresh value when formula is recalculated”. Yes, you do want it to be recalculated each time someone new opens the form. Click OK.

Note that you don’t have to actually display this field on the form at all. It can just exist in your list of data source fields. But for testing purposes during form creation, you’ll want to see this.
Now, when someone opens my form, I want the form to automatically switch to the IT view if the user’s department contains “IT”.
- In InfoPath 2010, click the Data tab in the ribbon, and click the Form Load button, which will bring up the Rules pane on the right.
- In InfoPath 2007, click the Tools menu and choose Form Options. Click the Rules button on the Open and Save section.
Define the rule:
The condition is: Click the first drop-down box (the field name). Select the name of the CurrentUserDepartment field. Select Contains from the second drop-down. In the third drop-down, choose Type Text… and type IT.

Unfortunately, this screen is case sensitive. So, if a user’s department name is not of the same case, this rule won’t apply.
Click to add the Action. The action will be just to switch views, switch to the IT Dept view.
Now try it out. Publish the form. If the text “IT” exists in the department name of your user profile, the form will now automatically switch to the IT Dept view when it opens.

BONUS extra stuff:

The field that we created in this example is used to simply capture the current user’s department name. If you’d like to create the fields that capture the form originator’s pertinent information like department, phone, job title, etc, you can create text boxes that default to each of those values. But when you do this, be sure to UNcheck the “Refresh value when formula is recalculated” check box. This is because you want the information to initially be captured when the first person fills out the form, but you don’t want it to change after that.
Also, the CurrentUserDepartment is going to be re-populated each time a different person opens the form, but you may want to do something extra incase the current user’s department field is blank. You can build in an action as part of the submit button, to set CurrentUserDepartment field to blank, that way you ensure that no one will incorrectly see information that does not pertain to them.

Here are a few references that pertain to some of the topics addressed in these last 2 posts of mine:

Auto-Pop Life with InfoPath (Part 1 of 2) – Lori Gowin

InfoPath – User Roles in Browser-Enabled Forms Using AD Groups – Clayton Cobb

Find out About Site Users within Microsoft Access (Screencast) - Me

Technorati Tags: InfoPath,SharePoint,UserProfileService,web service

Posted at 8:54 PM by Laura Rogers | Category: InfoPath | Permalink | Email this Post | Comments (0)

8/8/2010

InfoPath User Roles in Browser-Based Forms- Another Way (Part 1 of 2)

This blog post applies to both InfoPath 2007 and 2010, and both SharePoint 2007 and 2010, in MOSS and SharePoint Server 2010 (not WSS or Foundation).

There’s a concept in InfoPath called “User Roles”. You can set up these roles, and when a person opens the form, the form knows if they belong to a certain role, and you can set up certain objects to hide or show in the form based on the user’s role, or switch to a view based on a role, etc. Each role that you set up can be defined by specific user names, Active Directory groups, or a person’s name from within the form, like this:

UNFORTUNATELY, there are a couple of limitations of this functionality in InfoPath.

User roles are NOT available when creating browser-based forms. Therefore, if your form absolutely requires these, then all users who ever fill out this form will need to have the InfoPath software installed on their computers. This is a huge limitation in a lot of cases.
User roles cannot be created based on SharePoint Groups, only AD groups.

There’s a blog post out there, that describes a a way to get around this limitation, and emulate user roles in a browser based form. BUT this method entails manually keeping up a separate SharePoint list where you define all of the users and their roles.

Today, I’m going to tell you about another way of using the currently logged in user’s information instead of roles, and this can be used in a browser-based form. This can be used with SharePoint groups, where the built in user roles cannot. The trick is a built in SharePoint web service called the user profile service. Lori Gowin blogged a while back about how to use this web service in order to auto-populate text boxes in any SharePoint form, with information about the current logged in user. My method entails using that same web service. Clarification: This doesn’t refer to ANY SharePoint group, this is only the “members” group that exists on each SharePoint site. There is another web service that can be used, UserGroup.asmx, in order to get the info about SharePoint groups in general, on a site.

There are a couple of very useful operations in the UserProfileService.asmx web service, so I’ll show you a couple of options (2 parts to this blog). In this example, you have a change request form. Site visitors fill out the form, and after they’ve filled it out, people in the IT department will have additional fields to fill in. We don’t want non-IT staff to see these additional fields, though. We will create another view in the form. This view will contain all of the original fields that were filled in by the originator, plus the additional IT fields. When the form is opened, we want it to automatically switch to this IT view when the logged in user is an IT person. The prerequisite is that there is a SharePoint group on your site called IT Group, and you are a member of it. Also, you’ll need a form with 2 views, one of them called IT Dept.

Create a new data connection in the InfoPath form, to receive data. The source is a web service. (In SharePoint 2010, pick SOAP web service.) Click NEXT.
Type the URL of the userprofileservice.asmx file. This is at the root of your SharePoint site, under the _vti_bin folder. Click Next.
For the operation, click GetUserMemberships, and click NEXT.
On the Parameter screen, just leave the default and click NEXT.
Leave “store a copy of the data…” unchecked, and click NEXT.
Leave “Automatically retrieve data when form is opened CHECKED, and click FINISH.
Now, when someone opens my form, I want the form to automatically switch to the IT view if the user is in a SharePoint group called “IT Group”.
- In InfoPath 2010, click the Data tab in the ribbon, and click the Form Load button, which will bring up the Rules pane on the right.
- In InfoPath 2007, click the Tools menu and choose Form Options. Click the Rules button on the Open and Save section.
Define the rule as so:
The condition is: Click the first drop-down box (the field name). Click “Select Field or Group”. In the drop-down for the data source, select GetUserMemberships, which was the first data connection that you created. Expand several levels and select the DisplayName field. Click OK.
For the value, click “type text” and type IT Group, as so. Click OK.
Click to add the Action. The action will be just to switch views.
The action is just to switch views, so you’re done with the form open rules now.
Now try it out. Publish the form. If you’re a member of the IT Group (SharePoint group), the form will now automatically switch to the IT Dept view when it opens.

Stay tuned for part 2 of this blog post, for another way to go about accomplishing something similar with this same web service.

Testing: This GetUserMemberships operation returns a list of all groups that the current user is a member of, SharePoint and Active Directory. In order to see exactly what is returned from this operation, here’s how you can do this. In the data source pane on the right side of InfoPath, click the Main drop-down box at the top of it and change the data source to GetUserMemberships. This will show all the fields. Expand several levels until you get to the one called MembershipData. Drag this whole group of fields onto your form, as a repeating table. Preview your form. This will show you a list of all the groups that you are a member of. You may need to increase the width of the Display name field, so that you can see all the values. You’ll also notice that there is a field called Source that shows whether each one is a SharePoint group (“SharePointSite”) or a Distribution list.

Technorati Tags: InfoPath 2010,InfoPath,SharePoint,forms,web service

Posted at 1:05 PM by Laura Rogers | Category: MS Office | Permalink | Email this Post | Comments (3)

7/29/2010

SharePoint Technology Conference is Coming Up

I absolutely can’t wait for SPTechCon in Boston this October! My co-workers will be there, and we will all be in the SharePoint911 booth there! This conference is not only wonderful because of the many hour-long sessions, but there is a whole day of “101” sessions that are more intense half-day or full-day workshops. Oh, and when you go to their website, there’s a great video on the left side of the homepage with 3 minutes of highlights from the last conference.

The following is the list of the five sessions that I’ll be teaching, and each has a link to add to your own calendar:

W8-AM: SharePoint 2010 and Office 2010 Integration

Wednesday, October 20, 2010: 8:30AM – noon Add to my calendar
Me & Mark Miller
This beginner-level session will help end users, project managers and information workers to discover the best ways to tackle your daily work with the 2010 Microsoft Office system. Tips include how to use Outlook to keep all your SharePoint lists and libraries within easy reach, and how to display library metadata within Word documents. From offline document editing and version comparisons, to reporting on SharePoint library data, this session is packed with tricks you can use to increase your SharePoint productivity. Think about integration from an end user’s perspective and come learn topics, such as how to make it simple to save files directly to SharePoint and work more efficiently. The demonstration will feature a company policy management system, and will feature Access custom lists, content types, and quick parts. Many of the integration points are available in both the 2007 and the 2010 suite of products. Integration improvements in SharePoint and Office 2010 will also be highlighted, such as those in InfoPath Forms Services, Visio Services and Access Services.

W8-PM: Creating Custom Business Solutions

Wednesday, October 20, 2010: 1:30PM – 5PM Add to my calendar
Some of the most commonly asked questions that SharePoint site managers and end users ask are around the need to create custom business solutions without having to be a developer. In this workshop, Laura Rogers will teach you how to use Data View/Data Form Web parts in SharePoint Designer 2010. Not only will this workshop cover Data View Web part fundamentals, but you will also learn specific examples of ways to put these skills into play. It is a best practice to learn what can be done out of the box in SharePoint before delving into custom code. This workshop will be presented using SharePoint 2010, but the topics that will be covered can be accomplished in either 2007 or 2010. Laura will highlight the new user interface in SharePoint Designer 2010 and the new ways to do those old familiar tasks.
What will you learn in this workshop?
- Fundamentals and best practices associated with creating data view Web parts, such as conditional formatting and hyperlinks
- Creating a merged list of multiple document libraries
- Creating a change control system mash-up
- Creating a joined view of two different lists
- Creating a list/library permissions dashboard

This session is designed from power users, project managers, business analysts and developers.

107: SharePoint 2010 Out-of-the-Box Web Parts

Thursday, October 21, 2010: 9:30AM – 10:45AM Add to my calendar
This session will demonstrate just how flexible and useful the out-of-the-box Web parts are in SharePoint Server 2010. There will be an overview of many of these Web parts, along with demonstrations of several new Web parts and some that have been improved or changed from MOSS 2007. Best practices and use cases of individual Web parts will be discussed. Some of the Web parts that will be covered are filter, content rollup, people, chart, Excel Web access and media. Attendees will also be given a demonstration on customizing data views using the new SharePoint Designer 2010. It helps developers know what can be done out of the box before writing code, and it’s also useful for end users to know the full extent of SharePoint’s Web parts.

506: The Power of Content Types

Friday, October 22, 2010: 8:30 – 9:45 Add to my calendar
In SharePoint, the concept of content types seems a bit ambiguous to most people, which becomes a negative factor when it comes to actual use of this feature. In this session, Laura Rogers will teach you what you need to know about content types to be able to implement them in your company. You may have heard that content types can be used in order to upload document templates so that they display as choices for users on a library’s “New” button. Yes, that can be done… but wait, there’s so much more! In conjunction with site columns, content types can be utilized to create custom business solutions. Take your lists and libraries to the next level without having to do any custom development. In this session, demonstrations will be done to take you through common scenarios, and show how to use the power of content types in SharePoint. Many of these concepts carry over from SharePoint 2007 to 2010, but new 2010 capabilities will be highlighted.

806: SharePoint Designer 2010 Workflows: A Case Study

Friday, October 22, 2010: 2:30 – 3:45 Add to my calendar
In this session, learn how to create custom workflows in SharePoint 2010, using SharePoint Designer 2010. The example used will be a travel-request system in SharePoint, with multiple levels of approval. Attendees will learn how much the approval workflow process has been improved in SharePoint 2010 compared to the same workflow in 2007, how to involve multiple SharePoint lists in a workflow, and how InfoPath is highly integrated into the SharePoint Designer workflow and the assigned approval tasks. Laura will walk through the creation of the entire workflow, and the new product capabilities, workflow customizations, and troubleshooting steps will be discussed along the way.

Technorati Tags: conference,workflows,content types,data view web parts,office integration

Posted at 1:27 PM by Laura Rogers | Category: conference | Permalink | Email this Post | Comments (0)

7/20/2010

The Best Practices Conference 2010 in DC

The SharePoint Best Practices Conference is coming up next month in Washington, DC (Reston, VA, specifically). This is definitely one to look forward to.

Here are the sessions that I’ll be presenting at the conference:

Data View Web Part Best Practices - Old and New

Audience: Business Analyst, Power User, Administrator
Session Level: 200
In order to create custom business solutions in SharePoint, data view web parts are often created using SharePoint Designer. These data views are commonly used in order to take list and library views to a higher level of customization, without having to write code. In SharePoint 2010, these web parts have been drastically improved. In this session, you will learn how the data view web part best practices have changed in SharePoint 2010, which has made them not only more pervasive, but more accessible. You will learn old and new best practices related to list and library web parts, compared to views of other types of data sources.

Add event to my calendar

Form Approval Process - Best Practices

Audience: Business Analysts, Project Managers, Information Workers, Power Users
Session Level: 100
In your company, forms are everywhere, and they usually have some type of workflow associated with them. Approval workflows are a very common request in SharePoint, and there are several different ways to go about creating this process. SharePoint Designer and InfoPath are common tools that are used to not only create the business form, but to run it through an approval. Depending on the business requirements, there are different best practices involved. In this session, attendees will learn how SharePoint 2010 and InfoPath 2010 can be leveraged the most efficiently to create an approval process that is best for the specific project. Improvements from 2007 to 2010 will also be discussed.

Add event to my calendar

See you all there!

Technorati Tags: BPC10,workflows,data view web parts,best practices,sharepoint designer

Posted at 8:31 PM by Laura Rogers | Category: conference | Permalink | Email this Post | Comments (0)

7/2/2010

The Text Filter Web Part – Without Having To Filter Exact Text

This applies to both SharePoint 2007 and 2010. In MOSS Enterprise, and the 2010 version of Enterprise. There is an out-of-box web part called the Text Filter Web Part. Basically, when you put this web part on a page, and put a list or library web part on the same page, you then create a web part connection that sends the text typed in the box as a filter to one of the columns in the web part, like this:

ch5[38]

The name Molly Clark had to be typed in exactly. So, if you typed “Molly”, this record would not come up.

People use the text filter web part when they just want to search a single column in a list or library. Otherwise, you’d simply use the “Search” box at the top of the screen, choose “This List” or “This Site” and perform SharePoint searches that way.

One more note before I get into today’s solution:

If you’re making use of site columns in your lists, there’s a setting where you can specifically select which columns you do NOT want to be searchable on the site. In Site Settings, click Searchable Columns. Put check boxes in the NoCrawl column for the ones that shouldn’t be searchable.
ch5[40]

Here’s how to set up the text filter so that any partial word searches will work

One problem: with this solution, I don’t think there’s a way to make the list automatically display all items by default before doing your filter.

So again, this involves a data view web part… and that “parameters” concept that I keep raving about.

Go to a web part page, open it up in SharePoint Designer, and insert a text filter web part and a data view web part of any list or library.
Click to select your list web part, click the Data View menu, and choose Parameters.
Create a new parameter. In the name column, it doesn’t matter what it’s called, let’s just call it FilterParam. In the Parameter Source drop-down box, choose Query String.
The Query String Variable needs to be the exact name of the column that you’d like to filter.
Click the Data View menu, and choose Filter.

The Field Name needs to be the name of the column you’re going to filter, and HERE’S THE TRICK: Form the Comparison, choose CONTAINS. For the Value, choose the name of the parameter that you created at step 3. Click OK.
Right click on the Text Filter Web Part, and choose Web Part Properties.
The Filter Name box is required. Make something up. If it’s going to be used to search the title field, you might want to call it something intuitive like “Search Title”. There’s also a box in the Advanced Filter Options, to control the width in pixels. Type in a width if you wouldn’t like the web part stretching across the entire page. Click OK.
Right click on the text filter web part, and choose Web Part Connections.
Choose Send Filter Values to. Click Next.
Connect to a web part on this page. Click Next.
Target web part: choose the name of your list. This part is important: The target action needs to be Get Parameters From. Click Next.
Click the name of the text filter on the left, and the name of your new parameter on the right. Click Next.
Click Finish and save the web part page.

Try it out. In the text filter, you can type any part of any word and hit enter. It doesn’t take Boolean expressions or anything fancy like that, but it’s much more useful than having to type the text exactly. Again, this is a solution that I just whipped up today, and can’t tell if there’s any way to make the web part show all items until you want to filter it. Nothing obvious. I guess if you need to have the whole list displayed, you can just insert a third web part on the page, as a full view of the list.

Technorati Tags: sharepoint,search,filter,web parts

Posted at 5:04 PM by Laura Rogers | Category: Web Parts | Permalink | Email this Post | Comments (11)

6/29/2010

Display a Sub-Site List on a Top Level Site

In SharePoint 2007, it was possible to display any list or library from one site on another site in the same site collection, but to accomplish it usually entailed digging into SharePoint Designer and creating a data view web part. There was that little button when designing a data view web part, called “Connect to another Library”, which let you create a web part on one site, based off data in another site.

In SharePoint 2010, it is easy to put list or library data from a sub-site on a top level site in the same site collection, and it doesn’t entail creating any special web parts or breaking any pages off of the site definition! Here’s how:

Open the browser and go to the list or library that you want to ultimately display on another site.
Click the Library tab in the ribbon, and click the orange Edit Library button, to open it up in SharePoint Designer 2010.
In the Views section on the right, click on the name of ANY view, to open it up. Yes any. It doesn’t matter which view you pick here. Don’t worry, you won’t be modifying this page.
Put the cursor inside the content section of the list, so that the set of List View Tools tabs are displayed at the top.
Click the Web Part tab. In the Save Web Part section, there are two options:
- To Site Gallery – This option is used if you want to have the ability to insert this list onto any site page in the whole site collection.
- To File – This allows you to save the .WEBPART file on your computer, so that you can import that file onto any page in the site collection.
Click To File, and save it to your desktop. The following question pops up:

Do you want this Web Part to always show list data from the current web site? If you select No, the web part will show data from the list at the path ___, relative to the site in which it is used.

It is important to understand what this means. Click Yes to save this exact list. Click No, if you want this web part to be relative. So for example, if this is a list called Announcements, this web part would be used to display any list with the name Announcements that exists on the site that this web part is imported on. In this example, it’s a document library called Shared Documents.
Click Yes.
Close SharePoint Designer.
Go to the other site, on which you would like to display this list. Click to Add a web part, and click the little Upload button below all of the categories of web parts. Upload the web part and add it to the page.

This is where the AWESOMENESS became apparent to me. Now a default view of the sub-site’s list will be displayed AND you can go into the web part properties tool pane and change the view. YES, the familiar Selected View drop-down and Edit the Current view button are both available… even though the list you’re viewing exists on another site!

BUG alert 1: Unfortunately, the functionality described in this blog post only works when you’re viewing a sub-site’s information on a top level site. This doesn’t work the other way around (top level list on a sub-site) because when you try to import the web part on the page you get an error.

BUG alert 2: When importing any web part on a page, the upload functionality is a bit funky in 2010. Sometimes you have to upload the web part twice before it will stick, sometimes some other site list gets added to the page, and every time you have to click to insert a web part at least twice. The first time to upload it (then the list of web parts goes away), then the second time to add that web part to the page.

Technorati Tags: sharepoint 2010,web parts,sharepoint designer,data view web parts

Posted at 12:59 PM by Laura Rogers | Category: Web Parts | Permalink | Email this Post | Comments (4)

6/26/2010

Phone Number Validation in SharePoint 2007

Yes, you can do ANY kind of validation such as phone numbers, email addresses, social security numbers, zip codes, whatever. If you’re really nerdy, you can even validate IP addresses. I just thought I’d catch your attention with the title, since phone numbers are one of the most frequent types of data that people need to validate. This is all done using regular expressions, with NO programming involved!

What is validation? It means that when you have a form with a box for someone to type their telephone number, you want to make sure they type an actual full phone number, in the right format.

Currently in SharePoint 2007, you can make a field required, and you can set it to a certain type of data such as a number or date. On the other hand, in SharePoint 2010, validation is now a built in option each time you create a new column in any list or library.

Guess what… this solution entails creating a data view web part!

In this example, I have 3 fields I want to validate:

Email Address
Business Phone
Social Security Number.

In a previous screencast, I showed you how to Customize Form Pages in SharePoint (Screencast), so this is the prerequisite for doing the validation. Once you’ve created a contact list, added a social security number text column, and opened the site in SharePoint designer and created the custom form, these are the steps you take to add the validation. This will need to be done on the NewForm AND the EditForm of your list.

In your custom form, click to select the Email Address field, and click the little chevron at the top right corner of it, like this:
In the Format As drop-down box, click to change the format to “Text Box”.
Click to select the text box, right-click it and click Properties, so that the Tag Properties toolpane will show on the left side of the screen.
Scroll to the very bottom of the tag properties pane to find the ID tag, and ~~rename it to EmailTextBox, like this~~: Correction! Don’t rename the text box. You’ll just have to remember which one is which.
Click the Insert menu at the top of the screen, select SharePoint Controls, and click on More SharePoint Controls. This will put a new toolpane on the right side of the screen, called Toolbox. Click to expand the ASP.NET Controls, and the Validation section in there:
In the form, put the cursor next to the email address text box, on the right of it. Double click the control called the RegularExpressionValidator to add it to the page. Then, double-click the RequiredFieldValidator to add it also.

I know what you’re thinking. If I’ve already set it up as a required field when I created the column in SharePoint, why would I need to make it required again? When you change a field to a text box in the form, it doesn’t recognize the SharePoint required field info at all.
Click to select the red RegularExpressionValidator, and then take a look at that Tag Properties tool pane on the left again. In the Behavior section, in ControlToValidate, select the ~~EmailTextBox~~ default name of that text box, which will be something like ff20.
Do the same thing with the RequiredFieldValidator. Set its ControlToValidate as the ~~EmailTextBox~~ default name of the text box.
You only need to add the RequiredFieldValidator if the field needs to be required. Otherwise you can leave it off.
Select the RegularExpressionValidator again, because now it’s time to set up the regular expression. In the Tag Properties pane’s Appearance section, set the Error Message to say:
Not a valid email address
In the Behavior section of the Tag Properties, go to the ValidationExpression box, and click the ellipsis button to see this, and select “Internet e-mail address” and click OK:

Here’s what the Tag Properties pane looks like when the Regular Expression Validator is selected:

THIS IS SO COOL. I know you want to, go ahead and try this one out, so ahead and save the page and hit F12 to preview in the browser. Try typing incorrect and correct email addresses, and you’ll see that when it’s not a real email address, the red error comes up.
Time do set up the validation for the other two fields. Repeat steps 1 through 10 for the social security number and phone number, but KEEP READING BECAUSE THERE’S A TRICK TO THESE OTHER TWO.
For the ControlToValidate box for each control, be sure to select the appropriate one (of the above three). The ErrorMessage obviously needs to be different for each field also.

SO HERE’S THE TRICKY PART THAT FRUSTRATED THE HELL OUT OF ME. In the regular expression editor, when you select the “U.S. Social Security number” or the “U.S. Phone Number” patterns, they (and others) JUST DON’T WORK, so here’s how to fix them…

First of all, this is the pattern for a social security number:

It’s 3 digits, then a hyphen, then two, a hyphen, then four, like 765-76-9900. See in the pattern in the box above, the numbers with the curly brackets {} around them represent the number of numbers in a row. The problem is that SHAREPOINT DOESN’T LIKE THE CURLY BRACKETS. So, any time you see a pattern that has curly brackets in it, you have to modify it. So the above pattern for a SSN would need to be modified to be:
\d\d\d-\d\d-\d\d\d\d

Same goes for the phone number. Here’s what the modified phone number expression looks like:

This is the finished version of the NewForm. Again, you’ll need to do the exact same thing on the EditForm. I’ve typed a couple of them incorrectly, so each shows me an error message and the form can’t be submitted until they’re correct.

Here's my associated screencast, showing this process: Validate Email Addresses and Phone Numbers in SharePoint 2007 (Screencast)

In all my frustration figuring this out, here are the good reference sites that I came across, regarding regular expression patterns, in case you want to create your own:

Regular-Expressions.info

How To: Use Regular Expressions to Constrain Input in ASP.NET

Regular Expression Library

Technorati Tags: SharePoint,validation,MOSS,data view web parts,regular expressions

Posted at 3:50 PM by Laura Rogers | Category: Web Parts | Permalink | Email this Post | Comments (14)

6/25/2010

SharePoint 2010 Related Lists (Part 4 of 4)

Yes, the title of this post is a little confusing, especially if you read the other 3 part, but let me explain. The first three parts of this series were all about the Query String URL Filter, and how to create mashups that display multiple related lists on one page. Lists have a lot more relational capabilities now, which is seen everywhere from lookup columns to workflows. So, since we’re talking about mashups and related lists, I thought I’d show you the Related Lists button in SharePoint 2010, which lets you quickly throw a few web parts on a page, and automatically connects them. So, another title for this blog could be “how to do the stuff from the other 3 posts… in SharePoint 2010”

In this example in SharePoint 2010, there is a main projects list with several lists with related items that lookup to the projects list. Let’s create some simple ones quickly, just to try it out.

Custom list on the site called Projects. To try it out, all you need is a Title field.
Create a new Issues list on the site. Create a new required lookup column in the list, called ProjectID, and make it look up to the Project’s Title field.
Create a new Project Tasks list, and also add a required ProjectID field that looks up to the Project list’s Title field.
Create a few items in each list, with some tasks and some issues associated with each project name.
Go back to the Projects list. Click to create a new view, name it Mashup.
On the new view, click Site Actions and choose Edit Page.
CHECK IT OUT!!! There’s a button in here to Insert Related List!!!
Click the Insert Related List button, and the drop-down will show the Project Tasks list and the Issues list. Insert each one on the page.
On the Page tab in the ribbon, click Stop Editing. This is what the page looks like now:

DONE. WOW, it’s going to be so easy now for end users to be able to create their own related list views, without even having to know how to create the web part connections!

How did it work? Since the Issues and Project Task lists contained Lookup columns to the Project list, SharePoint automatically knew to consider them “related lists”. If a list has no other lists that look up TO IT, then the related lists button will be grayed out.

One more thing… yes, the Related Lists button does exist in SharePoint Designer 2010 also, and not just in the browser.

Technorati Tags: SharePoint 2010,web parts,web part connections,related lists,projects,SharePoint Designer

Posted at 5:36 PM by Laura Rogers | Category: Web Parts | Permalink | Email this Post | Comments (5)

6/12/2010

The Query String URL & Data View Web Parts (Part 3 of 4)

In the last two posts of this series, I showed you how to use the MOSS Enterprise out of box web part called the Query String (URL) Filter. This post will show you how to accomplish the same thing, but without MOSS. This entails using the data view web part. I’ll use the same example of a project details page. The query string will be used to pass the project ID to each of the data view web parts on a page.

Even if you DO have MOSS, another good reason to use data view web parts for mash-ups, is that redundant column that you’re connecting to. You know how when you’re creating web part connections, the only columns available for the connection are the ones that are being displayed in the web part? When data view web parts are used, ALL list fields are available in the connection, not just the displayed ones!

Here’s how to configure each web part in SharePoint Designer, to use a Query String:

Create a blank web part page, and open it in SharePoint Designer. Insert a data view web part, we’ll just start with the issues list. Again, this is using the same project details example, so please read my last post about it.
In the Data View menu in the toolbar, click Parameters.
There will already be one parameter. Add a new Query String parameter, called “ProjParam”, like this:
In the Data View menu in the toolbar, click Filter.
Create a filter where the ProjID field is equal to the new parameter [ProjParam]:
Save the page. Make sure there are a couple of items in the list, each with a ProjID associated with them. Open the web part page in the browser and test the query string.

The trick is that now you use the URL in order to pass the project number to this web part. This is supposed to be a project mash-up, so add all of the associated lists to the page as data view web parts, and repeat steps two through five on each one.

The URL to the page will now end with the page name.aspx?Project=2

That red number is unique to each project. In my previous post, I showed how to create the workflow that generates that project details URL for each new project. This screenshot shows that this list is automatically filtered by issues that have a ProjID field equal to one, because of the URL.

ch7[11]

In summary, here’s a brief comparison:

Query String (URL) Filter Web Part

Out of box web part
No SharePoint Designer needed
MOSS Enterprise version needed

Data View Web Part With Query String

SharePoint Designer data view web part creation needed
More complex setup
Can be done in just WSS, without MOSS

Note that you can still create all data view web parts, and still use the Query String URL Filter Web Part. This combination method will let you get rid of all of the redundant columns, in this case the ProjID… with the ease of use of the out of box query string URL filter web part.

Technorati Tags: SharePoint Designer,MOSS,Web Parts,data view web parts,Query string url

Posted at 7:34 PM by Laura Rogers | Category: Web Parts | Permalink | Email this Post | Comments (1)

RSS Feed

1 - 10

Laura Rogers @WonderLaura