1

I am fairly new to Sharepoint, so please forgive me if this answer is readily available somewhere, because in my searching I have not encountered the same issue.

Goal:

I have a custom list with several fields, one of which I would like to use to query a SQL database and populate a simple 3-column table that will be visible whenever someone clicks on a list item.

I am unsure if this is available to do in Sharepoint since most of the questions I have seen are about importing a SQL table as a custom list or syncing a Sharepoint list with a SQL table, but nothing about making a SQL query on the view page of each custom list item. Any advice would be appreciated!

  • Hi @dak , Welcome to SE Community, Do you need to read data from SQL database and show it as a list in SharePoint? – Mohamed El-Qassas MVP Jun 1 '17 at 18:55
  • Thanks for the welcome! Not quite. I have a custom list filled with vendors and their information, however, in addition to that information I need to show a list of products we purchase from each vendor. This information changes and is kept up to date in the SQL tables maintained by our ERP. When someone clicks on a vendor from within the vendor list, I would like to query SQL with the vendor ID from the vendor they selected, and return a list and display it alongside the information stored in the custom list. I hope that explanation clears things up a bit. – dak1220 Jun 1 '17 at 19:00
0

The answer is yes but it will fairly complex. There are no OOTB options such as BDC or others.

there are several reason to explain why it needs a complex approach: it's a SharePoint online environment which is cloud hosted - it will be very difficult to call an external database and populate on list view form but still you can implement REST based services which can query and pull the information from SQL and gives the response back in JSON object. these rest based services can be called in the onload event via javascript and show in the customized list view form.

if it's a On-Premise farm environment then you would just need to implement a ASP.Net based layouts custom page with ADO.Net connection to SQL database to show the required information.

  • Is it possible to have an easier option? What if I populated a second Sharepoint list with the 3 columns I need from SQL and a 4th column indicating which list item it belongs to. Would there be some way to show only the rows of that list that pertain to the list item that I am currently viewing? I know it's a stretch, but I would very much like to avoid sinking many hours into a custom solution for what seems to be fairly simple on paper. – dak1220 Jun 2 '17 at 12:08
  • you can go with the lookup lists which is very simple but you wont have the options to integrate with the live data from SAP/SQL in your case. – kesava Jun 2 '17 at 20:01
  • From what I understand, with lookup lists, I would have to add 3 fields to represent my row to the custom list that each correspond to the lookup list? It is not the desired solution since there will be an arbitrary number of 3 field rows that I would not know the number of lookup fields to add beforehand. I hope that makes since. – dak1220 Jun 2 '17 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.