Integrating SharePoint with external database application

Introduction:

Integrating SharePoint with external databases and applications can streamline data management and automate business processes. By leveraging SharePoint’s capabilities for linking, exporting, and importing data, it becomes possible to synchronize information between different systems without complex custom development.

This post demonstrates how to seamlessly connect SharePoint with an external database using Ultimate Forms: External Data Lookup, Ultimate Forms: Actions, and Ultimate Forms: Import.

Key Capabilities Demonstrated:

1. Link an external item to SharePoint – Selecting a customer from an external database table and entering sales data for that customer in a SharePoint list.
2. Exporting data to external DB item – Updating the SQL table with the amount of the last sale.
3. Importing data back into SharePoint – Changes made to the SQL table are imported into a log list located in SharePoint.

For this demonstration, we will use a SharePoint site with two custom lists: Sales and Imported Sales. Additionally, an external MS SQL database table called Clients will store customer details with the following fields:

• ClientID (int)
• Client Name (nvarchar)
• Phone (nvarchar)
• Email (nvarchar)
• LastSale (currency)
• LastUpdate (datetime)

Linking External Items:

To integrate SharePoint with an external data source, we will use Ultimate Forms: External Data Lookup. This feature functions similarly to a standard lookup column but connects to external sources such as MS SQL, Oracle, MySQL, or web services.

We will add an External Data Lookup column to our Sales list, along with an Amount column (currency type). The settings for the External Data Lookup field include:

• Defining the connection string to the external database.
• Selecting the appropriate table and column.
• Configuring automatic mapping of database columns to SharePoint fields.

Once configured, users can select a client from the external database when registering a new sale in SharePoint. This ensures data consistency across systems.

Exporting Data to an External Database:

When a new sale is recorded in SharePoint, we need to update the corresponding client record in the SQL database with the latest sale amount. This is achieved using Ultimate Forms: Actions, which enables executing SQL stored procedures directly from SharePoint.

A stored procedure is triggered automatically when a new sale is recorded. The procedure updates 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.

This ensures that the external database remains synchronized with sales recorded in SharePoint.

Additional benefits of Ultimate Forms: Actions include:

• The ability to trigger updates based on multiple conditions.
• Sending confirmation emails after each successful database update.
• Logging all executed actions for auditing purposes.

Importing Data into SharePoint:

The next step is to import updated client records from the SQL database back into the Imported Sales list in SharePoint. This is done using Ultimate Forms: Import, a tool that allows importing data from external databases, emails, and web services into SharePoint lists or document libraries.

To automate the import process:

1. We create an import profile in Ultimate Forms: Import.
2. The profile runs every 15 minutes to retrieve the latest changes from the Clients table.
3. The LastUpdate column in the SQL table is used to identify updated records.
4. New or modified records are added to the Imported Sales list in SharePoint.

Best Practices for SharePoint-Database Integration:

To maximize the efficiency and reliability of SharePoint database integrations, consider these best practices:

1. Optimize Queries – When configuring External Data Lookup, avoid retrieving unnecessary columns to improve performance.
2. Implement Security Controls – Ensure that only authorized users have access to update or modify external data sources.
3. Monitor Performance – Regularly review execution logs and optimize scheduled tasks.
4. Use Conditional Logic – Implement business rules that determine when and how data should be exported or imported.
5. Regularly Backup Data – Schedule backups for both SharePoint and the external database to prevent data loss.

Conclusion:

With Ultimate Forms, integrating SharePoint with external databases is simple and requires no custom development. We have successfully:

• Linked SharePoint to an external SQL database.
• Automatically exported sales data from SharePoint to an external system.
• Imported updated data back into SharePoint, ensuring real-time synchronization.

By leveraging Ultimate Forms: External Data Lookup, Ultimate Forms: Actions, and Ultimate Forms: Import, organizations can enhance their SharePoint functionality and streamline data integration processes.

For more details, check out the Ultimate Forms documentation and see how you can improve your SharePoint environment today!