Hello,

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.