1

I am new to SharePoint and InfoPath or SharePoint designer. I am trying to do the following, could not able to find solution yet. read so many blogs.

  1. Have an external SQL server database.
  2. I want to create a InfoPath form or SharePoint designer form by connecting to external DB.
  3. I want to be able to upload this form into SharePoint.

I want this form to be able to retrieve data and submit data to and form external database. I don't need to store anything on SharePoint database. Basically, trying to use SharePoint Server as a web server.

Is this possible? if so, please help me. Please reference links or videos.

Thanks,

  • Look up and use BCS (Business Connectivity Services). There's plenty of articles on how to use SharePoint Designer to set up a connection to an external database to use CRUD operations through SharePoint, using SharePoint forms. – Mike May 6 '16 at 19:20
2

Here's a very short overview of what you need to do assuming you have set up SP server already.

  1. Create the columns in your external database if you haven't already. This is important as changes to the db schema later have a problematic effect on external content types (ECT) in SP.

  2. In Sharepoint Designer (SPD) create a new ECT where you will be asked to add a name for it and a display name and point to the url of the db server. Once you have done that you will see a list of tables in that server. Find the table you want to connect to, right click and select 'create all operations' . You will then see a modal that allows you to pick an identifier (usually ID) and which column to display in a picker (if this particular ect was being used as a look up). You can also exclude columns in the db from being accessed by the ECT. Once you have completed the 3 steps in the modal hit Finish.

If you do not have any foreign key constraints in the table then you can hit save on the ECT and skip step 3.

  1. If you have any foreign key constraints in the db then you will need to set up an association in the ECT. Go back to the tree view of the db and again right click on the table you are pointing at and this time select 'association'. Now you will need to select the table to which the foreign key points but since you can point directly at the external db you need to have previously created an ECT for each of the tables that are lookups, then in the drop down below select the column in the ECT that the association applies to. Hit next and then (for reasons unknown to me) in the next screen you again select the column to be associated and tick the map to identifier.

Once the ECT is created you need to create a list from it. look in the top ribbon whilst in the summary view of the ECT you should see Create List. Alternatively if you look in the list of ECTs, highlight the ECT and then hit External List in the ribbon.

Once the list is created, go to lists and library's, click on the new list to go into it and there you should see in the ribbon 'Infopath Form'.

BE AWARE once you have created your form and published it, any changes to the table in the db or to the ECT (that are not just adding filters) will be a real PITA especially for lookup fields. There are ways around it but that's outside of this question. Also any lookup fields do not like to be copy/cut and pasted, you can only drag them otherwise they lose their properties and don't work as expected.

Apologies for no images or if I've got any steps slightly incorrect as I'm completing this from memory on my phone waiting for a train after a day of creating ECTs.

1

If you need to add data connection for external database you can refer Add a data connection to a Microsoft Access database. In this article only for Microsoft Access but you can similarly add for other databases.

Please let me know if it enough for you.

Thanks

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.