Synchronizing DB tables with SharePoint lists
Vladi Gubler
Vladi Gubler
July 25, 2013 | Products

Hello,

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.

 

Loading...

Add your comment

Comments are not designed to replace support calls. If you have a specific issue with one of our products, please send an email to support@infowisesolutions.com to open a support ticket.

UltimateForms

Build powerful business applications in SharePoint using only your browser.
100% No-Code Solution

It's never been easier, to create, innovate and share, all you need is your web browser!

Cost-effective

Address business process pain points immediately. Save time and money.

Fantastic Support Team

Facing difficulties installing the application? Contact our fantastic support team.

support@infowisesolutions.com

Related Topics

Infowise logo
© 2005-2021 Infowise Solutions Ltd
Microsoft partner logo
GSA Schedule

What is right for you?

Online Trial

Not ready to install yet? Create a trial site in our environment
  • Full control of the site and its settings
  • Optionally pre-install one of our business solutions
  • Site provisioned automatically
  • Expires after 30 days
Register Now

Installation

Install in your own environment, on Microsoft 365 and on premises
  • Start working with your real users and data
  • Install on SharePoint Online or on premises
  • Register for 30 day trial
  • Trial can be converted to paid license
Request a Live Demo

Book an appointment for a one-on-one with an ULTIMATEforms expert trainer.

Test drive the awesome power of ULTIMATEforms

Learn how to quickly and easily turn time-consuming business processes into automated, efficient workflows.

Have a strong start

Only thirty minutes of well-coached time can translate into great future savings in time and money. So, sit back and enjoy the ride.

It's Free

Learn how to address business process pain points immediately. Save time and money.

Request a Live Demo

Book an appointment for a one-on-one with an ULTIMATEforms expert trainer.

Test drive the awesome power of ULTIMATEforms

Learn how to quickly and easily turn time-consuming business processes into automated, efficient workflows.

Have a strong start

Only thirty minutes of well-coached time can translate into great future savings in time and money. So, sit back and enjoy the ride.

It's Free

Learn how to address business process pain points immediately. Save time and money.