In this post I'm going to show you how easy it can be to integrate an external application (using any kind of database or even just web services) with SharePoint lists, linking, exporting and importing data to and from a non-SharePoint application.
My demo illustrates the following capabilities:
- Link an external item to SharePoint - we'll be selecting a customer from an external database table and entering sales data for that customer in a SharePoint list
- Exporting data to external DB item - we will then update the SQL table with the amount of the last sale
- Importing data back into SharePoint - changes made to the SQL table are imported into a log list located in SharePoint
I'll be using a SharePoint Foundation site with two custom lists: Sales and Imported Sales. I'll be also using a table in an external MS SQL database called Clients (contains: ClientID (int), Client Name (nvarchar), Phone (nvarchar), Email (nvarchar), LastSale (currency), LastUpdate (datetime)).
Link External Item
We'll be using LOB Item Link Field, it looks more or less like a lookup column, but gets its data from an external source: MS SQL, Oracle, MySQL, web services and so on. We are adding a column of this type to our Sales list, we also add Amount (currency column). The settings for the LOB Item Link Field are as follows:
We are basically providing the connection string and selecting a table and its column. We also chose to place each column into a separate SharePoint column (the additional columns are generated automatically).
That's it, now when you go to register a new sale, we can select a client from the DB:
Once saved, the values go right into SharePoint's columns:
Export to External DB
Once the item is saved, it's going to update the DB item with the latest sale amount. We use Smart Action Pro to execute a stored procedure in the SQL database to update the table row. The stored procedure accept SharePoint column values as parameters:
The actions runs automatically, whenever we add a new item to the Sales list. The stored procedure updates both the LastSale and LastUpdate columns:
Update Clients SET LastSale=@LastSale, LastUpdate=GETDATE() WHERE ClientID=@ClientID
Our sale amount is now registered in the SQL table.
Import into SharePoint
Our next goal is import the latest changes to the Clients table into Imported Sales list in SharePoint, demonstrating the ability to automatically import data. We'll be using Smart Import Pro, our component that can import emails, DB or web service items into SharePoint. A great advantages of this product compared to the alternatives (such as email-enabled lists in SharePoint) are that it can import into any type of list or document library and it can not only create new items, but also update existing items, based on the new data.
We'll set up an import profile. It will run every 15 minutes and import latest changes in our Clients table into Imported Sales list. That's why we needed LastUpdate column in our SQL table, we'll be using it to figure out which items have been updated.
We've completed the whole cycle of updates now, we have information coming to SharePoints, updates pushed back into SQL DB and come back to SharePoint, without any custom development!
You can now set approval status and check-out status programmatically using Smart Action Pro, as part of your business process automation. Using Update list item and Create list item action types, you can set the values of the special pseudo-columns:
- Approved - yes/no. Sets the approval status to either Approved (yes) or Rejected (no)
- Check-in (in document libraries only) - yes/no. Sets the check-in status to either Checked-in (yes) or Checked-out (no)
Yes/no value allows for a wide variety of possible inputs, for instance, in English, you can use words such as yes, true, ok, approve, affirmative and so on. The built-in support for French, German and Spanish provides similar terms. You can also map to any yes/no column by either selecting it in the value builder or simply typing in column name surrounded by square brackets (e.g. [Approve Order]).
The Update list items action can update multiple items (based on filter), located in any list of the current site collection. As with any action, it can be executed in response to events (such as items added, modified or deleted), based on timer (such as approve automatically items one week after creation) or manually, using execution columns, context menus or ribbon button (the latter two are not available in SharePoint 2007).
You can also set approval and check-in comments by setting textual values or expressions to the relevant fields.
The current approval status can also be used in conditions and filter, in this case it expects a status name in the local language of the site, such as Approved, Rejected or Pending. For instance, you can use it to send notifications to item supervisors if an item has not be approved within the predefined time period. For the similar purpose with check-in stauts use Check Out To field (which holds the user to whom the document is current checked out), for instance, check that Check Out To field Equals blank value to make sure the document is not currently checked out.
These new capabilities give you even more power and control to successfully transform your SharePoint server into a real application development platform.
Associated Tasks Field is a great help when you want to use your SharePoint as an application development platform. Simply put: it makes SharePoint relational. You can create related tasks, items or documents directly from the parent item, which can then be viewed, monitored or updated from any Display or Edit form of the parent item and any list view of the parent item's list. You can easily manage tasks related to your project, add repeating sections to your forms or upload and assign documents to your RFPs.
You can manage your related items in two ways:
- Simply create a lookup column in the child list, pointing to the parent list. It will filled out automatically and used to manage the relationship
- Use one of the provided Associated content types. By using a set of hidden relationship columns, the connection is created and maintained in a completely transparent fashion. The advantages of this method include the ability to create multiple different Associated Items columns in the same list (think separate corrective and preventive actions) and also the ability to create related items for a parent item that is still in the middle of its creation process (such as add line items to an invoice that hasn't been saved yet).
Associated Items Summary field type works together with the Associated Items field to calculated summaries based on the related items:
- Count the number on related items (e.g. how many lines are in the current invoice or how many tasks belong to the current project)
- Calculate sum, average or even standard deviation of any number/currency column in the related items (such as, invoice total or average support ticket handling time per client).
- Concatenate text values into one, separated by commas.
The value is calculated automatically, updated each time related items are added, modified or deleted and is stored within the parent item itself.
Example of settings:
You can even calculated summary of only specific child items using filters, such as the number of open tasks (count tasks where Status does not equal Completed). And using Smart List Pro validation rules, you can use these values in input validations, such as prevent a project from being closed when there are still open tasks.
In many cases we need to assign new or existing documents to the list items we are currently working on, be it project, task or event. Generally, we use the handy Attachments feature built-in into SharePoint. But what if the document is already stored in one of the document libraries and you do not want to create a copy? Or you need to have the complete set of metadata, permissions or version management, it's just not available with attachments.
Document Link Field is a useful SharePoint column type that can really help here. You can use it to select and link multiple documents to the current item or even upload new documents into a library of your choosing.
The latest version even allows you to dynamically determine the source (or target) library, by selecting or filling out another column in your list item. For instance, your documents are stored in three document libraries: Sales, Finance and Research. By selecting a choice field Department, containing the same 3 vlaues, you open the file picker of Document Link Field directly inside the relevant library.
Here we select the library, next, when we click on Add Link, the picker opens the correct library
Select a document and click OK:
Save the items, you can see your item and its document links directly in the list view: