External Data Connections in SharePoint

Introduction

If you've used Modern forms in Ultimate Forms for SharePoint before, you know how powerful, yet easy-to-use they are. The sophisticated, responsive forms allow precise control over layout and look & feel, as well as advanced permissions and rules. Basically, you are able to implement a great deal of your business directly in the form. There might literally be no need need for any backend workflows.

Until now, we've always closely tied the forms to the underlying SharePoint infrastructure. You could only create a form based on a SharePoint list or document library. The data can only come from and return to only that list or document library library.

That is, until we figured you might want to leverage the forms to interact with data in other applications as well. This makes Ultimate Forms the most versatile tool in your organization.

Introducing Data Connections

So we added External data connections to Ultimate Forms. Using these connections, you can set up a form that submits data into a variety of non-SharePoint applications. You can host the forms inside and outside SharePoint.

And we made sure to preserve the same familiar user interface. This way both your developers and your user will have the same familiar, intuitive experience.

But we are not limiting it to forms. Using our Rollup web part, you can also display the external data right inside SharePoint web part pages. We made sure the look and feel is similar to SharePoint list. You also have the same set of features at your disposal, such as paging, sorting and filtering.

All in all, your users will see the external data as identical to SharePoint lists. They can then work with it in an integrated familiar environment. They work with external data in SharePoint sites as it if it were native SharePoint data.

Note that data connection are only present in the SharePoint Online (Microsoft 365) version of Ultimate Forms. If you are on premises, the only option available to you at this point is External lookups. These look like regular SharePoint lookup columns, but draw their information from external data sources.

Connection types

Data connections support a wide variety of external data sources and protocols:

• External Databases: Microsoft SQL, Oracle and MySql. You connect by simply providing a valid connection string and you get a database connection in SharePoint sites. If you work with information inside your network, you need to configure exception rules for your firewall. You can find our IP addresses here.
• Salesforce - you are able to work with both production and test orgs, as well as switch between the two. This is the easiest way to integrate Salesforce in SharePoint.
• REST APIs and SOAP web services - reading, adding, modifying and deleting items. You can provide you API key in the connection headers.
• Documents: you can generate or update Word and Excel documents, as well as comma and tab delimited files. The system will then automatically upload them to SharePoint / OneDrive, FTP, Dropbox and Box. With Excel and text files, the system will append new submission to the existing file.
• Email - you can send form data as an email (Exchange Online, Exchange on-premises and SMTP protocols are supported)

Security

The forms themselves have no knowledge of how to connect to your database or email server. No form does not and cannot load the connection settings, key or credentials. We safely store that information on our servers. The form submits to our servers only.

The servers then verify that the submission. They will perform a series of check to ensure the submission is valid:

• Check that the information is coming from a human (no bots allowed!).
• Check if the submission type is in the list of authorized operations. For example, your connection might only allow new item submission and not editing of existing items.
• Ensure the submission is coming from an authorized URL.

Connection settings

The actual connection settings depend on the connection type you decided to use. For instance, when you use a SQL server, you specify the .NET connection string. If you're not sure how to create a connection string, please refer to this site.

Other connection type might require a different set of settings. For instance, to connect to Salesforce, you first create an application within Salesforce itself and authorize CORS. Once the application is ready, you use its Consumer key and Consumer secret to establish a secure connection.

Once you've entered the connection properties, click on Connect. The system will connect to the data source and display a list of available tables or objects. You then select the table or object you plan to work with.

The system will display the columns of the selected table or object. You can perform additional configurations on them:

• Define the column format (for example, you might want to display a text column as a choice of values instead of free form entry).
• Rename the column (database column often have cryptic names)
• Define additional settings specific to the column type, such as maximum/minimum values for numeric columns

Note that some data connection types, such as Email or Text files, do not provide a list of columns. Here you will just create your own columns directly in Ultimate Forms.

Once you've completed configuring your connection and column settings, you can save your new connection profile. You can now click on Design form inside the data connection list. You will proceed directly into Form Designer to visually design your form.

Form design

We are using the same Form Designer you are already familiar with, with some minor changes. For example, you cannot select the content type for the form (because, well, there are no content type outside SharePoint). But in general, the experience will be almost exactly the same.

As in SharePoint, you can create tabbed interface, add permission rules and style your form with themes. The resulting form can look identical to any regular SharePoint form that you built in the past.

Publish your form

The form can be hosted in one of 3 ways (or all of them together):

1. In a web part on a web part page in SharePoint
2. Embedded on your own page (say your external web site) - we provide a short script snippet to copy and paste inside your page.
3. Hosted on our page - this is the easiest option for those quick solutions you might need to come up with.
4. On Microsoft Teams tabs

If you chose option 1 or 2, you need to specify where you will place the form. Make sure to include the page URLs within Allowed internal page addresses under Access control of the connection. It ensures that the form author maintains complete control over where her form can execute. 

For option #3, you need to allow access for anonymous external users, also under Access control. Once you save the connection, the system will assign a unique ID to it. In the same location you will be able to copy the form URL.

Form web part

You can place the form on any web part page by including Infowise Form or Infowise Rollup web part. In the web part settings, specify the connection ID that you copied from the settings page. Again, don't forget to include the page URL under Allowed internal page addresses, otherwise it won't load.

Print and Import

External data connection are valid source for Print and Import components of Ultimate Forms. You can create a print template based on the data connection. You can then print the information, convert to PDF (including fillable PDF) or generate a Microsoft Word / Excel documents.

With Import profiles, you can automate data import into the external data source. You can both create new entries and update existing entries. For example, you can enter information coming from emails directly into MS SQL tables. And you can also add condition and perform value adjustments using functions.

Conclusion

External data connections let you create a modern and easy-to-use form interface within SharePoint and outside it. This works for many data stores, programming interfaces, and applications that lack such an interface. By doing this, you can use your existing tools and enhance the user experience, achieving seamless integration.