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.
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 :)