Update: new feature in Smart Import Pro - you can now use a single Sync action to automatically create or update items in your list!
Today I'm going to show you how to easily synchronize any database table with a SharePoint list. We are going to achieve the following functionality:
- DB and SharePoint columns are mapped according to our own logic (column names do not have to be the same)
- New items are automatically created in the list when they are added to the DB table
- Existing items are updated in the list when they are updated in the DB table
- BONUS! Changes in the list are updated in the DB table
We'll be using Smart Import Pro to handle importing new and existing items from the DB to the list. Smart Import Pro is capable of importing emails, DB tables (any type of DB would work) and even web services.
For sending updates back from the list to the database, we'll be using Smart Action Pro, our most versatile component. It has the ability to run stored procedure in any DB with parameters coming from a SharePoint list item.
To successfully synchronize a DB table, you would need it to have the following columns (the column names can be anything you want):
- Unique identifier - any text/number field that is unique across all items in the list.
- Created Date - date/time column containing when the item was first created. This column value should not be subsequently changed.
- Modified Date - date/time column containing when the item was last modified. When the item is first created, its Created and Modified dates should be the same.
In the SharePoint list, you would need to store the DB unique identifier in any suitable column. It's advisable to make the column indexed to improve performance. You would also need to crate suitable columns to contain DB column values without losing the data types. You can always import any DB column into a SharePoint text column.
Now let's create an import profile for our SharePoint list.
- Go to List Settings of the list.
- Click on Import settings.
- Give an easily identifiable name to your new profile.
- Specify the polling schedule, the import can be as frequent as every 15 minutes.
- Change the provider type to Database.
- Select the relevant database type.
- Enter a valid connection string, according to the database type and authentication mechanism
- Choose authentication type:
- Connection string - username/password are provided in the connection string.
- Service account - SharePoint Timer service account credentials are used.
- This account - enter username and password to be use for authentication. Username and password are going to be encrypted and stored within the profile.
- Click on Connect to display the available tables/views.
- Select the relevant table.
- Select update date column, it should be the Modified date in your DB table.
- You do not need to fill out File content and File name columns.
- You can now create your import action. We would need two import actions, one - to import new items, and the other - to update existing items.
- Select Create list item action type
- Enter an action name
- Map values to the SharePoint columns. You can use text, DB column names and functions. Use the value picker to generate valid expressions.
- Add a condition: Created Equals [Modified] to require both columns in the database item contain the same value, which means it's a new item, as only new values should be handled by the Create action.
- Save the action
- Select Update list item action type
- Enter an action name
- Map values to the SharePoint columns in the same way you did before.
- Add a condition: Create Not Equals [Modified] to only handle updated items.
- Under Item add a condition by mapping the unique identifier value stored in a SharePoint column to the relevant DB column. That will ensure that you are updating the correct SharePoint item.
- Save the action.
- Save the profile
We've know established a one-way synchronization from the database to SharePoint. If your solution requires two way synchronization, continue reading.
You would need a stored procedure in your database that updates an item with the values it receives as parameters. Next, you need to create a Smart Action Pro action to run this stored procedure whenever a list item is updated.
- Go to List Settings of your list.
- Click on Actions settings.
- If no action column exists, create one, you can use the default name Actions.
- Under Add/update action, select action type Run DB stored procedure.
- Uncheck New under Run on events leaving just Edit.
- Switch to Action Settings tab.
- Select the relevant database type
- Enter the connection string (the action will be performed under the application pool identity)
- Click Connect.
- Select the stored procedure.
- Map the procedure parameters to the column values and/or expressions. Make sure that your stored procedure accept the unique identifier as one of the parameters and uses that parameter to select a single item to be updated
- Save the action.
That's it, your two-way synchronization with an external database table is now established. Wait for up to 15 minutes
(depending on your polling schedule) for the list to be filled with the database items, start updating the items and see the changed reflected in both the database and in the list.
Today I would like to talk about the different ways in which actions in Smart Action Pro can be executed. There are 3 different types of triggers that would cause actions to run:
- List events - triggered when items are added, modified, deleted, or, in the newer versions, checked-in/out, moved or attachments added/deleted.
- Timer - based on any date column within the item (e.g. "2 days before Due Date") or daily/weekly/monthly at a predefined hour.
- Manual - by clicking on the execution column, or, in SharePoint 2010/2013 ribbon/context menu button.
You can even combine #1 and #2, having the same actions both respond to events and run on timer.
If you switch to the Advanced Settings tab of the action, you will notice that you can set an action to run synchronously. By default, actions run asynchronously; also, this setting only matters for event-based action, it does not change anything for timer-based or manual actions.
To understand what this setting means, let's explore how SharePoint updates work. When you add/edit your list item and click on Save, there are two events being triggered, one before and one after the actual database update. The Before event (such as "ItemAdding") happens before anything is actually written to the database, you even have the ability to cancel the update at this stage. The After event (such as "ItemAdded") happens after the update, so there is no way of reverting the change at this point.
When you set your action to run asynchronously (which is the default), it will continue running in the background even after the triggering form has closed (if it takes that long that is), it causes no visual delay to the user and is basically unnoticeable. So it's great for any long running operations, such as updating multiple items, creating sites or calling web services. Bear in mind though, that asynchronous action might still need to update the current item when done. Such update could be logging the execution result (which you can actually turn off, but it's on by default) or, if configured so, modify the current item. This second update can still be picked up by other actions set to respond to this event type, which could cause additional actions to run. You should plan your actions carefully not to cause unintended execution.
Synchronous actions run differently. They will actually execute before the database is updated, so if the action takes a long time, the form could appear to be stuck. Don't plan any long-running action to execute synchronously, it's not a good idea. But there is a bright side to it: because the built-in update has not actually happened yet, we can piggyback on that update, injecting any updated column values we need right into this update, so no second, action-initiated update is needed. No secondary update, no problem with unintended execution. And, as an added bonus, you have the ability to cancel the update when your action fails and even show customized error message to the users. Imagine that your action update an external DB with the same data that goes into the list. If that external DB is for some reason unavailable, you would want to prevent creating the SharePoint item as well, to keep the two in synch. Another example would be resource booking, when you want to prevent double-booking (read more here or download our Room Reservation solution that implements this approach).
So as you see, there are multiple different options that give you precise control over how and when your actions get executed, adding to the power of Smart Action Pro, the indispensable tool for any SharePoint developer.
In the latest version of Smart List Pro we've added several new features to help you customize SharePoint list forms far beyond what is possible out-of-the-box, while still avoiding using SharePoint Designer or InfoPath forms, thus keeping the forms easily maintainable without any tools.
In the past we've already introduced such features as tabbed interface, user-defined tab theming, tab descriptions, customizable column description and title location, blank rows and section titles.
In the latest version we are also adding the capability of arranging your list columns in up to 3 layout columns. The screen capture illustrates the extent to which you can customize your forms. In this example, the columns are arranged in two tabs, while each tab contains two layout columns. The list columns are then automatically arranged accordingly, with the column titles placed above the data entry controls. The column descriptions are also kept out of sight and can be opened clicking on the help icon next to the column title.
Now it's easier for the users to fill out their data, maximizing the screen space and minimizing scrolling and the best thing - all of the configuration is done directly in List Settings, using just your browser.