How to populate external data columns in a custom SharePoint 2010 list

Question

Hello fellow SharePointer's.

I am currently working on converting a custom solution to a more out of the box solution. I need to populate a SharePoint list with data from a SQL table which meets a particular date requirement (calibration date = to [Today]-1). I need to "query" the database daily at 4am for calibration dates matching this requirement. I have created an external list, and established a view that meets this requirement, and hardware providers can navigate to this list to view all the hardware that meets this date requirement.

The Problem:

My problem is that the hardware owners only get the read permission level to the external list, as the data owners do not want them to have the ability to modify the SQL records. I have created a sharepoint list with columns that mirror the external list, and would like to pull in the data from the external list as it refreshes with the data that meets the date requirement (Windows timer job is not an option as we are trying to eliminate the custom solution).

I would like for the sharepoint list to be to be updated with only the unique identifier (as the external data column) for each piece of hardware that meets the date requirement. From here I should be able to initiate a workflow off item creation, and use the external data column as the join data to update columns in the sharepoint list item with the corresponding data from the data source (clear as mud?). The hardware owner will then get an email notification that they have hardware to review, and they will be able to modify the SharePoint list columns with their action taken, completion date, etc. These fields do not need to write back to the database, they will just stay in the SharePoint list. Once the hardware owner "closes" the item, an email will go to the lab for notification. I have the workflow piece figured out, I just wanted to give more information on what I am trying to do with the external data column. If anyone can provide any guidance, it would be greatly appreciated :)

Answer 1

If third party solution is okay in your case, i would recommend to use Simego Data Synchronisation Studio https://www.simego.com/solutions/sharepoint-integration. It can transfer sql data to SharePoint list and can be scheduled as a task. The sql data can be retrieved by direct sql as well.

If you are using Nintex Workflows, you can create a Site workflow with action "Execute SQL" to fetch data from the sql table and store in a list. This can be scheduled as well in Nintex Worklfow scheduler.

Other solution could be to make a bridging table in sql between your actual table and SharePoint list. Add the columns to the bridging table that only Hardware guys can update. Create a sql job that syncs between actual and bridging table. The SharePoint external list will read from bridging table with ability to update columns. The workflow can't be initiated in this solution.

Hope that helps.

Answer 2

The bridging table solution

1. Create a plain sql table (bridging table) with columns exactly the same as your actual table plus the columns that Hardware guys will update.
2. Create a stored procedure and implement the data sync between your actual and bridging table. You need to use insert into bridging table if no data found otherwise use update query. Test the stored procedure.
3. Call that stored procedure in a sql job and scheduled it lets say daily.

A simple sql job script is below. Please dont use it as it is. Modify it accordingly.

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'Weekly Sales Data Backup' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'Weekly Sales Data Backup',  
    @step_name = N'Set database to read only',  
    @subsystem = N'TSQL',  
    @command = N'YOUR_STORE_PROCEDURE_NAME',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO  
EXEC dbo.sp_add_schedule  
    @schedule_name = N'RunOnce',  
    @freq_type = 1,  
    @active_start_time = 233000 ;  
USE msdb ;  
GO  
EXEC sp_attach_schedule  
    @job_name = N'Weekly Sales Data Backup',  
    @schedule_name = N'RunOnce';  
GO  
EXEC dbo.sp_add_jobserver  
    @job_name = N'Weekly Sales Data Backup';  
GO  

You can also use SQL Server Management Studio to create a job. More details on the sql jobs are at https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job

Finally use that bridging table as an external list in SharePoint with ability to update the Hardware columns. For emails, you can use insert/update trigger on bridging table.