Second Day of SharePoint Connections Conference

Yesterday was the first day of the SharePoint Connections conference here in Orlando.  While most of the sessions were Microsoft presenters, they were still mostly good (they should know the product, right?).  However, my favorite presentation of the day was Robert Bogue’s presentation: Solutions Without Semicolons.  I suppose I’m really into seeing how far we can take SharePoint without having to resort to code.  Not that writing code against SharePoint is necessarily a bad thing.  It is just that coding should be a last resort, especially in an organization such as ours which has thousands of users and less than a half dozen IT staff supporting the infrastructure.  I would much rather spend my time showing people how to achieve most of what they really need on their own without having to queue up in the backlog of IT solution requests.

But that was yesterday.  Today I heard several good presentations by some of the top names in SharePoint including Asif Rehmani, Scot Hillier, and Dan Holme.  And while most of these were no-code presentations so I could see what new things I could train our users to do on their own, I did attend two of Scot’s presentations, one was a no-code approach and another very heavily into code.  I find it amusing that some people seem to feel the need for apologizing for explaining how to do some of the cool no-code solutions.

It seems that Microsoft is OK with supporting both the heavy code developers as well as the power users who just want to squeeze out every last ounce of functionality with calling the company’s programming department.  I hope that continues because there is so much you can do right out of the box (together with SharePoint Designer and InfoPath).

I, for one, recently became very interested in what you can do with external data.  External data is data that comes from an data source other than SharePoint’s SQL database, lists, or libraries.  In particular, I began playing with external  SQL data several weeks ago (if you have been following by prior blog posts, you know that).  I was glad to see several presentations on using external data at the conference.

There were also several discussions of using SharePoint in the cloud.  In fact, one presenter had his entire presentation recreated on a site at www.cloudshare.com.  Not that they are the only company offering cloud support for SharePoint.  There are many other alternative SharePoint hosting site today including another show sponsor, RackSpace.  Maybe sometime in the near future, I may do a survey of what’s available.

Hosting SharePoint in the cloud may not be such a bad idea especially considering how much trouble I had this past Saturday at Code Camp Orlando with sporatically losing connection to my SharePoint site.  Of course several people in one building had trouble with their Internet connections at Code Camp (and there were some problems at SharePoint Connections too) including one Code Camp session that they moved to a different room (I wish I’ve known that was possible).  The real frustrating part of it was that I had tested my session in the cafeteria first thing in the morning  (several buildings away from the presentation room) where I had a great wireless connection, 4 bars, while in the classroom I barely got 1 bar and it would often disappear.  I remember having problems with a wireless connection at the same location two years ago, but did not have a problem last year.  So maybe it is tied to the specific room/building.
Anyway, a couple of quick observations from today before closing for the night:

  • Use SharePoint Workspaces to take libraries and lists offline, not Outlook. Workspaces supports up to 30,000 items in a library or list.
  • Use Outlook to take External Data offline
  • Use SharePoint Workspaces’ Launchpad to quickly navigate to the sites you use most rather than going through the browser and hunting for them.
  • Use File Shares for storing large content files (libraries) rather than SharePoint’s SQL Server if you are using SharePoint 2010 and SQL Server 2008 R2.  There are some out of the box solutions (FileStream) and AvePoint has DocAve Extender which is a Free tool.
  • If you want to work with external data, you may need to look into Kerberos.  The double hop authentication to the data source will not work for multi-server production farms.  (It will work on standalone machines though.)

Opening Keynote for Dev Connections

Although SharePoint kicked off the SharePoint Connections Keynote, there was nothing really new, but then if you must remember that we are in between releases.  Therefore, the focus was on the theme of SharePoint Everywhere and specifically the movement of SharePoint to the cloud with SharePoint online.

An interesting comment was that IT workers with SharePoint skills are 11% above the average mean IT salary in IT according to Robert Half.  While the future of productivity may be in the cloud and SharePoint will soon join Office 365 and SQL Azure with an online offering, the ability to develop for the mobile platform and the cloud will not completely overshadow the enterprise deployments which remain strong and will continue to co-exist with cloud options for specific applications.

What will Microsoft’s cloud offering include?  It appears that it will be based on Office Professional Plus, Exchange Online, SharePoint Online, and Lync Online to provide applications in Unified Communications, Collaboration, Enterprise Content Management, Business Intelligence and Enterprise Search.  Office 365 will include two plans.  The Kiosk plan, which will cost less per person per month will include SharePoint Online and Exchange Online.  However, for heavier use, the Enterprise Plan will include Office Professional Plus and Lync.

If you have not checked out some of the online training, go to http://msdn.microsoft.com/trainingcourse.aspx to see both training modules and on-line hands-on labs that can help get you up to speed.

Also you might want to check the following blogs:

http://blogs.msdn.com/steve_fox

http://blogs.msdn.com/cmayo

http://blogs.msdn.com/pstubbs

The second keynote, Unlocking New Possibilities with Visual Studio by Dave Mendlen and others focused on the many OOB (Out Of Band?) additions to Visual Studio in the last 9 months.  These are all available through the online gallery on MSDN and provide everything from Productivity Power Tools to enhancement for programming new devices (Windows Phone, Kinect, etc.) to the cloud.  Also mentioned was an application prototype tool called LightSwitch to help visually develop applications.  Finally, for Visual Studio 2010 Ultimate users, you will be able to load test your application with unlimited virtual users forever at NO additional cost.

Connect to Office

When you view the Library ribbon of a library, there is a button in the Connect & Export group with the name: Connect to Office.  The tool tip description says that this options ‘adds to your shortcuts that appear when creating, opening and saving documents in Microsoft Office applications such as Word, Excel, and PowerPoint’.  On the surface, this description, while lengthy, does not really tell most of us anything useful about what this option does.  So what does it really do?

When you select the lower half of this button, you get three options:

Add to SharePoint Sites – Add a shortcut to this library in Microsoft Office.

Remove from SharePoint Sites – Remove this library from your list of shortcuts in Microsoft Office.

Manage SharePoint Sites – Modify your list of Microsoft Office shortcuts.

To find out what this really did, I started from several different libraries and selected the option to add a shortcut to this library in Microsoft Office.

Note: When I first did this, I got the following message:

Click ‘Yes’ to create your user profile site.  If you have already done this, the above message dialog will not appear.

After you have added several sites, open Microsoft, Word, Excel, or PowerPoint (2010 version) and from the File menu, select Open.  In the left navigation area, notice under favorites the entry named SharePoint Sites.  Click on this link and you will see in the detail area on the right a list of all the libraries for which you have created shortcuts.  Double click on any of these links and you will see a listing of the contents of these libraries that the current Office application can open.  To clarify, if I open a link to a library from Microsoft Word, only the Word compatible files appear in the list when I click on the shortcut to that library.  Of course, if you double-click on any of the file names, you will open that particular file.

Back in SharePoint, you can use the second option in the Connect to Office button to remove the current SharePoint library from the list of shortcuts.  If the current library is not in the list of shortcuts, a message appears briefly in the upper right corner of the screen to tell you that the library was not found.

If you are not sure which libraries you have defined as shortcuts, you should use the Manage SharePoint Sites option in the Connect to Office button.  This option shows all the current libraries included in the Office shortcuts list even if the current library is not included in the list.  The following image shows this dialog.

You can select any of the shortcuts by selecting the checkbox to the left of the shortcut name.  To delete that shortcut, click Delete from the actions across the top of the page.  To edit the link, click Edit Links.  This actions shows the following dialog that lets you not only edit the link, but to also define which group the link should be included in and even create a new group.  Unfortunately, it seems that these groupings only appear when you display the links in your My site and not in the Office tools.

Since SharePoint stores this information in your My site, you also can determine who besides yourself can view these links when they visit your profile.   How do you display these links in your profile?  Edit one of your profile pages and add the My Links web part from the Recommended Items group.

Note: In the previous figure, I’ve moved one of the links to a new group.

Finally, from this dialog, you can also add a link.  This option does not require you to first select one of the links already defined, but it does use a similar dialog to prompt for information like we saw for editing a link.

Why is this important?

Linking directly to your most commonly used libraries directly from within your Microsoft Office tool of choice can save you time from having to first open SharePoint, navigate to the site containing the library, opening the library, and then opening the file.  With these shortcuts you can open the file directly from within Microsoft Office tools.

That’s all for today.

MagicPASS Mtg – March 2011

Last Wednesday night was the 3rd meeting of the new MagicPASS SQL Server group.  MagicPASS is the south Orlando chapter of PASS that is currently being run by Kendal vanDyke.  They meet the third Wednesday of each month at Stetson University starting at about 5 PM.

Why start so early?  So they can have two full sessions in one night.  The first session this time was by Andy Warren who gave a very detailed and technical presentation on Indexes.  Some of the more interesting things he covered included:

There are really 4 types of indexes in SQL: Clustered, Non-clustered, XML, and Spatial.  Ok, that was sort of a trick question because most of us do not deal with Spatial data or even XML.

A table can only 1 clustered index because it defines the actual order of the table rows.  However SQL 2005 supports up to 249 non-clustered indexes and SQL 2008 supports up to 999.

However, what you might not know is that each of the non-clustered indexes has an additional hidden column that points back to the clustered index value.  Thus each clustered index field is repeated in each of the non-clustered indexes.  This has several implications.

First, the combination of the non-clustered fields and the clustered fields could form a covering index for some of your queries.

Second, the larger (in bytes) you clustered index, the more space all of your non-clustered indexes will use because the values have to be repeated.  This is one argument for using a unique identifier field rather than a long character or multi-column field as the clustered index key.

If your table does not have a clustered index, SQL Server generates a 8-byte RID which combines a reference to the file, page, and slot on the page where the data can be found.  However, since a unique identifier can be 4-bytes, using it instead can save you space if you have several non-clustered indexes.

Why is the size of the non-clustered index important? Or any index for that matter?  It is a trade-off between reading in as many unique index values as possible from each page read vs creating covered indexes and even the question of modifying the page fill factor so that inserts and even updates do not require splitting of pages too often.

Finally on the topic of index fragmentation, fragmentation can be bad if your TSQL execution plans rely on Index Scans to find data in the index rather than Index Seeks.  Having too high of a fill factor will require more frequent page splits to accommodate new data inserts.  Having too low of a fill factor will result in more page reads and larger indexes. 

What is the best answer?  It depends on how your plan to use your tables.  This is why it is good to get familiar with reading execution plans and using Profiler to see how users are working with the databases before you begin to fine-tune your performance.

The second presentation was by Brian Mitchell of Microsoft on Making Business Intelligence with Data Warehousing Easier by using Microsoft Appliances and Fast Track Architectures.

He basically covered three topics beginning with the HP Business Decision Appliance.  The advantage of this option is that with one SKU, a user can purchase both the hardware and software needed to create a BI solution for their company.  The hardware comes with PowerPivot, SharePoint, and SQL Server pre-loaded and configured on a HP Proliant G7 Server.  Once delivered, you can have your first BI solution up and running within a few days.

The second option is the SQL Server 2008 Fast Track Data Warehouse.  This is more of a how-to guide telling you what pieces you need and how to put it together and configure the various parts to create your BI solution.  This solution reduces the ‘pain’ of having to research what you need to buy and how to configure it.

The last option was the SQL Server 2008 R2 Parallel Data Warehouse.  This is a solution for the high end users and ships like an appliance.  However, the cost is about $2 million.  The advantage of course is that loads and operations are highly parallel optimized and therefore execute significantly faster increasing the scale and reducing the execution time of large database and BI solutions.

Anyway, that was this month.  Next month’s speaker is Jeff Garbus who will also dive deep into working with indexes and will dispel some of the myths and rules-of-thumb you may have been using.

Using External Data in Libraries

This time I will look at adding an External Content Type field to a library.  One way you could use external data with a Word document might be to supply address information from an external SQL Server data table when the user selects a person.  Some of the steps to accomplish this task you may have already done through previous topics, but I will include the steps here again for completeness.

  1. Open SharePoint Designer.
  2. Select the site you want to work in from the Recent Sites list or click the Open Site button to enter the address of your site.
  3. Select External Content Types from the left navigation
  4. If there is already a content type you can use, you can skip ahead to create the document library.  Otherwise, click External Content Type in the New group of the External Content Types ribbon.
  5. Supply a Name for the content type.  You can use the same name as the display name or provide a more user friendly display name.
  6. When working with external data in Microsoft Word, you can use the Generic List Office Item Type since we will not need to map fields to Outlook items.
  7. Use the link for External System to define the connection to the external data and define the operations needed.  If a connection already exists to the database, you can reuse it to save resources or you can define a new connection.  A SQL Connection requires the name of the server, the database, and the user authentication method.
  8. Next define the operations you need from: Create, Read Item, Update, Delete and Read List.  If you only need to access data, you may be fine with just the Read Item and Read List operation.  Be sure to define which fields to display in the picker as this will be how you will select which record to use
  9. Save and Close the definition of the External Content Type.
  10. Go to Central Administration.
  11. Open the Business Data Connectivity Service configuration page by selecting Manage Service Application from the Application Management group and clicking Business Data Connectivity Service.
  12. Add the users and/or groups that will need to use this content type by opening the Business Data Connectivity Services dropdown and selecting Set Permissions.
  13. Exit Central Administrator
  14. Close Share Designer

Next you will create the Document Library

  1. Create a standard document library with a name of your choice.
  2. Go to Library Settings which can be found in the Settings group of the Library ribbon.
  3. Add a new column of type External Data.  Provide an appropriate name for the column.  Then use the selection box to pick the External Content Type and the field from the content type that you want to display in this column.  Note you do not have to select the columns that appear in the dropdown pick list for this field.  This information was set when you defined the content type in SharePoint Designer.
  4. Select any additional fields you want to appear in the list from the External Content Type.
  5. Click OK to complete the column definition and return to the list by clicking the list name in the breadcrumbs at the top of the Library Settings page.

Upload Documents into the Library

  1. Upload documents into the library using the Upload Document option in the New group of the library’s Document ribbon.  Note that you can upload either single or multiple documents.  Let’s assume you upload a single document.
  2. Enter the document name (and path) in the Upload Document dialog or use the Browse button to locate the document.
  3. After clicking OK, the item properties dialog appears.  You may have other fields defined for this library that you can fill in, but the important one for this discussion is the external content type field.  Use the Select External Item button (to the far right of the field) to display the picker dialog.  Select an item from the picker and click OK.
  4. Click Save in the properties dialog.
  5. Notice the library now shows your uploaded document along with populated values for all of the external content type fields.

Using External Data in your Word Document – The final section of this topic looks at including external data in your Word documents created from a modified default template in a SharePoint library.

  1. Open the library used in the last section and go to Library Settings in the Settings group of the Library ribbon.
  2. In the General Settings section, select Advanced Settings.
  3. On the Advanced Settings page, select Edit Template in the Document Template area.  This action opens a blank Microsoft Word template.
  4. In the template you can add any text that you want to appear on every instance of a new document in this library.  By creating a form on this template, you can create a Word based form library.
  5. To add external data to the template, position your cursor where you want the first field from your external data to appear.  This field should be the field that you defined as a column in the library.  When you use this template to create a new document instance, this field allows you to select from the picker list for the external data.  With the cursor positioned, open Word’s Insert ribbon and expand the dropdown menu for Quick Parts.  Choose the Document Property option and select the external field that you used to define the external data column.
  6. Next add any of the additional external fields to the template.
  7. Save and close the template
  8. Return to the list view of the library and select New Document from the New group of the Documents ribbon.
  9. When the template opens as a new document, you can click the first external content field to display the two option buttons to the right of the field.  Click the Select External Item(s) button to display the pick list. It may take a few seconds to open.
  10. Select an item from the pick list and click OK.  Notice that the other external data fields populate with the corresponding values of the fields in the external content type.
  11. Save the Word document and close Word.  Returning to the library, you should now see the newly saved document along with the external data populating the newly added row.

Using External Data with Outlook 2010

To see what happens when you connect your external data to Outlook, let’s create a new external content type.  Why begin with a new external content type rather than edit an existing one?  The
best reason is that you or someone else in your organization might be using the current external content type and changing it could break another process.  Another reason is that for many external content types, you may have created several operations.  When making changes, each operation has to be reviewed and changed individually whereas creating a new external content type can generate all of the operations with a single pass through the parameters as we will see below.
In order to integrate the External Content Type with Outlook, you also need to associate your external data with an Office item type.  Outlook supports four primary item types into which you can map your external data including: Appointment, Contact, Task, and Post.  After selecting an item type, you need to map each of your external fields into one of the fields supported by that item type.  For instance, if you have a table of names along with contact information, you may want to map the external data to a Contact item type.  Let’s assume you have such a table in one of your SQL Server databases and see what steps you need to follow to display and manage the data from Outlook.

  1. Open SharePoint Designer.
  2. Open the site where you want to store the external list of the data.
  3. Select External Content Type from the left navigation menu.
  4. If you have any existing external content types, you will see their names in the main content panel, but assume you want to create a new external content type, not just modify an existing one.
  5. Select External Content Type from the New group of the External Content Type ribbon.
  6. In the External Content Type Information group, click New External content type to the right of Name and enter a name for you content type.  Although SharePoint Designer automatically copies this value to the Display Name field, you  an enter a different display name if you want.
  7. Next, select an Office Item Type (such as Contact).
  8. Since we want to use our external data with other office products like Outlook, make sure that the value for Office Sync for external list is set to Enabled.
  9. Next, click the link to the right of External System to define the connection to the external data.
  10. If you have defined previous connections, you may be able to reuse the connection.  The connection applies to the database, not to a specific table in the database, so reusing a connection could save system resources if you need to use several tables from the same external data source.  If you do not have a connection to use, you can create a new one by first identifying the data source type (such as SQL Server – other types include .NET and WCF or web services).  If you select SQL Server, you must then identify the Database Server, the Database Name, a user-friendly name for the connection, and how you want to validate the user against the data source.
  11. You can now drill down through the connection to select a specific table, view or stored procedure you want to use as the data source.  Remember if you want to modify the data, you may not be able to use a view or a stored procedure as your data source.
  12. To select a table, open the table’s folder and right-click on the table you want to use.
  13. Next, choose the operation you want to be able to perform against the data source.  If you only want users to view data, choose the New Read List and the New Read Item operations. (Both are required to get data from the external list and display it.  However, if you want to edit, delete and add records, the easiest thing to do is to select Create All Operations because  ou can define the parameters for all operations at one time and SharePoint Designer applies them appropriately to each of the operations.
  14. If you selected Create All Operations, the first page of the dialog shown next simply tells you that all operations will be created.  There is nothing you need do on this page.
  15. Clicking Next, you get to define the parameters of the operations.  These are the fields and all field properties.
  16. Deselect any fields from the table that you do not want to use.
  17. Next, identify the columns used in the external item picker.  This can be one or more columns that uniquely identify each record in the data source, but it does not have to be the primary key, especially if the primary key is a numeric ID field.  Select each field you want to use and select the checkbox: Show in Picker.
  18. Next identify any fields that have the Required and ReadOnly checkbox selected.  Deselect the Required checkbox but leave the ReadOnly checkbox selected.  If you do not do this, you will not be able to update the data when you display it.
  19. Next, use the Office Property dropdown to map each of the external data fields to one of the Outlook item fields.
  20. You might also want to change the Display Name to something that is more user-friendly.
  21. When done, you can click the Next button to go to the last page of the dialog, the Filter Parameters.
  22. You use filter parameters when reading large tables to keep the number of records returned to under 2000.  Business Data Connectivity enforces this limit unless you raise it in Central Administrator.  However, large values will degrade performance.
  23. Click Finish to complete the definition of the operations.
  24. Right click the page’s tab and select Save and right-click the tab a second time to Close the external data content type definition.
  25. The External Data Type is not ready to use yet because you have not yet defined permissions.
  26. Open Central Administrator (or ask your SharePoint Administrator) and select Manage service application in the Application Management group.
  27. Click Business Data Connectivity Service.  On the resulting page you should see your External Content Type.
  28. Use the dropdown list associated with the type name and select Set Permissions.
  29. In the dialog box, add the SharePoint users or groups you want to give rights to use this external content type.
  30. For each user or group you add, you must select from the four permissions shown.  The Edit and Execute permissions are necessary to simply use the external content type in an external list, but if you want to connect that external list to Outlook, you must select the permission: Selectable in Clients.  You can also choose whether this user can set permissions (by using the Manage Permissions option of individual items in the list.)
  31. Now open the site and create an external list from this external content type as described in last week’s post.  Open the list after creating it.
  32. With the list open, click Connect to Outlook in the Connect & Export group of the List ribbon.
  33. Some dialog boxes may appear as the Business Connectivity Service add-in starts, but eventually the data connects to Outlook and Outlook should open automatically if it is not already open.  However, I have seen a case (my machine) where Outlook does not automatically open in which case the process may still work but you have to manually open Outlook.
  34. If you open the Folder List or Contacts icons at the bottom of the left navigation of Outlook, you should see a SharePoint External List group in the Folder List Panel or you may just see the external list at the bottom of the Contacts panel (assuming you used the Contact Item Type when defining the External Content Type).
  35. You can now click on the name of your external content type to open it and then update or delete contacts from within Outlook even though you store the data in SQL Server and pass it through SharePoint using an External Content Type.

OCPS Website Receives an A+ Sunny Award

Today we were informed that OCPS received a Sunny Award from the editors of Sunshine Review, a group that reviews the content of public school and government web sites for compliance to transparency in government.  Only 13 schools in the state of FL received this award of which we were 1 of 7 to receive the A+ award.  Across all the websites in the country ranked by Sunshine Review, only 211 sites out of over 6000 received the award this year.

March meeting of MagicPASS

MagicPASS is the South Orlando SQL Server User Group.  The next meeting will be on March 16th beginning at 5 PM when Andy Warren continues is sessions on Performance.  The group will then break for some food before continuing with a presentation by Brian Mitchell.  No details were available about Brian’s talk, but if this is the Brian Mitchell from SQLPDW.com, then I’m expecting something on SQL Server Parallel Data Warehouse or perhaps something on providing strategic and operational tip on Microsoft’s SQL Server Business Intelligence stack, Analysis Services, Reporting Services, or (this would be too good to be true) SharePoint.

Meetings are held at Stetson University Center, 800 Celebration Ave. Room 109, Celebration, FL 34747.

To register to attend and to see if further details have been posted, check back at http://magicpass.eventbrite.com/?ref=enivte&invite=Nzg5MjQxL2FudG9ub21Ab2Nwcy5uZXQvMA%3D%3D%0A&utm_source=eb_email&utm_medium=email&utm_campaign=invitenew&utm_term=attend

How is that for a long link? 🙂

Features of a Custom List with External Data

Just like we looked at the limitations of a 100% External List, let’s look at the limitations of a custom list that uses as one of its columns an external data element.  Note that these observations apply no matter if you only have the one external data element or if you include additional fields from the external content type to include in your new list.  First, let’s get out of the way some things that are missing in a custom list when it includes external data.

  1. Folders are still not allowed.  I’m sticking with the theory that when beginning with an external flat file (table) that there is no easy way to map the hierarchy concept of folders.
  2. No version history.  Since SharePoint has no way to track changes to data in the backend database, version history does not make sense, no?
  3. No workflows.  I suppose this limitation also relates to the inability to track changes to data in the external data source.
  4. As with an External List, you cannot connect this custom list to Outlook.

Now let’s look at some of the things you can do with a custom list that you cannot do with the External list version of the same external content type.

  1. You can open the custom list in Datasheet mode.  True, you cannot change any of the external data fields by just typing in new values, but you can update any of the custom fields created through SharePoint.
  2. You can add more custom columns to the list and if you want, you can even edit the column that connects your custom list to the external content type to add or remove additional columns from the external data source.
  3. You can export the custom list to Excel which will include not only the custom columns, but all of the selected columns from the External Content Type.  In fact, you could link your External Content Type to a minimal custom list with just the Title column to transfer all of the data to Excel.  You can also copy the custom list as a table in Access and it too will include the external data.  However, if you link the SharePoint list to the Access table you will not be able to edit the external data fields, but you can edit the other custom list field columns.
  4. The custom list also lets you define an RSS feed for the list.  However the feed only contains the custom fields and the column used to link to the external content type.
  5. You can attach a file to each custom list item.  This is true probably because the custom list has a physical presence in SharePoint while the External List which did not support attachments did not have a physical presence in which to store that link.
  6. Item Permissions are allowed again due to a physical list presence from the custom columns of the custom list.
  7. Finally, you can modify the list item edit form by using the InfoPath button on the List ribbon.  Interestingly, when InfoPath opens, it tells you that the connecting field from the external content type is not supported and will not be available.  If you click OK, the resulting form includes all of the custom fields you added to the list and the additional columns included from the external content type even though you will not be able to edit the external content type fields.  If you decide you no longer want to use the custom InfoPath form, you must go through List Settings à Form Settings (in the General Settings group) and select the radio button: Use the default SharePoint form.  If you do not click the Delete the InfoPath Form from the server, you will start from the existing form and will need to update all of the changed fields.

Using External Content Type in a Custom List

In my earlier blog entries, you saw how to create an External Content Type to connect to a SQL Server table and how to display that data in an External List.  Did you know that you can also use that external data in a custom list?

I’m going to assume you still have your external content type and begin by going to any site in the web farm.  Remember that the external content types you define are stored in Central Administrator and work across the entire web application.

Open your site and create a new custom list (This will also work for libraries.  With your list open, click on List Settings.

In the Columns section, you see all the defined columns for the list/library.  Beneath the list are several options that you can perform by clicking on them.  The first option is: Create Column.  Clicking this option opens the New Column dialog.  For the column name, you can use any name unique to your list/library.  The important part is to select the column type: External Data.  When you select this type, the properties in the Additional Column Settings include the field External Content Type that lets you select, by clicking the second button to the right of the field from the available External Content Types you have defined so far.  Immediately beneath this property is a dropdown list of the fields from the external content type.  This field will be displayed as the value for this column.  Dropping down a little further is a checkbox to add all fields from the External Content Type to the list.  Alternately, you can use the checkboxes to the left of each field name to custom select the fields you want to include in the list.  Why do this?  When you select a record from the external data for this column, SharePoint populates the other fields choosen to appear in the list with data values from the same record.

Clicking OK at the bottom of the column properties page saves the definition of the external data column.

Now if you return to your custom list and open it, you will see that the default view includes not only the custom columns defined for the list, but also all the external columns selected from the external content type.

Go to the items ribbon and add a new item.  The default form that SharePoint builds for the list/library appears in a popup window.  You will notice that all of the standard custom fields appear in this dialog, but only the field for the external data column defined in the column definition appears.  You can directly enter a value into the external data column field directly and then click the first icon to the right of the text box to check if the external data table has a record with that value, or you can click the second button to display a list of records from the external data source.  Note that this list uses a horizontal scroll bar to let you see all the columns in the external data.  To select a record, click anywhere within it and then click the OK button.  This returns you to the New Item dialog which you can click ‘SAVE’ if you have entered all the data for this item.

When SharePoint displays your list again, you will see not only the data you just entered, but also the data for all the associated fields.

Next time, we will look at the pros and cons of using a custom list with external data and how to build a customNew Item form for this list.