Hi,

There is a new feature that was recently added to Ultimate Forms (SharePoint Online edition only at this point, but coming on-premises as well). It is called "external data connections" and it allows you to work with a wide variety of non-SharePoint data sources via interfaces integrated in SharePoint, hosted on external pages or embedded on your own non-SharePoint pages. We currently support the following data source types:

• Databases
  • MS SQL (read/write)
  • Oracle (read/write)
  • MySql (read/write)
• Web services
  • REST (read/write)
  • SOAP (currently write only)
• Documents (stored in SharePoint document libraries or via FTP)
  • Comma-delimited files (currently write only)
  • Tab-delimited files (currently write only)
  • Excel files (currently write only)
  • Word files (currently write only)
• Email (write only)

Once you create a connection, you can define forms for the connection and then either share the New form with internal and/or external users or share a data view, from which you can view and edit existing items, as well as add new ones.

In this article I will guide through the process of creating such a data connection. I will be using the sample, but fully-functional REST API that we created and are sharing with our customers. It implements a very simple contact management API, allowing you to view a list of contacts, as well as add new contacts, view, edit and delete specific contacts.

Sample REST API

The API is hosted under https://www.infowisesolutions.com/samples/api/contacts. It exposes the regular set of operations (HTTP method and URL endpoint):

• GET https://www.infowisesolutions.com/samples/api/contacts - get list of items
• GET https://www.infowisesolutions.com/samples/api/contacts/1 - get item with ID 1
• POST https://www.infowisesolutions.com/samples/api/contacts - add new item
• PUT https://www.infowisesolutions.com/samples/api/contacts/1 - update item with ID 1
• DELETE https://www.infowisesolutions.com/samples/api/contacts/1 - delete item with ID 1

Contact item itself has the following structure:

• id - unique ID and primary key of the contact. This property is read-only and should not be set.
• lastName - last name: text (up to 50 characters)
• firstName - first (given) name: text (up to 50 characters)
• dob- date of birth: date
• active - boolean (yes/no)
• units - number of installed product units: number

None of the properties are required by the API, but you can make any and all of them required on the data connection level.

Configuring External Data Connection

1. Once you open Ultimate Forms, click on External data connections under Modern forms section.
2. Click on Add new profile.
3. Enter a new title for your connection. It will be used as a page title in some scenarios, so give it a meaningful title like, such as Contacts.
4. Select REST under Type.
5. Check all forms and views under Allowed access as we want to allow both browsing existing items as well as adding new ones.
6. Enter https://www.infowisesolutions.com/samples/api/contacts under URL.
7. User name, password or headers are to be left blank as this sample API doesn't require any authentication
8. Under Columns enter the contact item properties as described above. Note the following:
   1. id column needs to be marked as key (checkmark on the left of the column name).
   2. Match the column type with the property data type as described above, for example, lastName should be mapped to Single line of text and dob should be mapped to Date and time. Incorrect mapping of column types will cause errors when interacting with the API.
   3. You can change settings of every column, giving friendly title, defining columns as required, etc.
   4. You can mark column as visible or hidden in View by clicking on the Eye icon next to column name. It doesn't affect the visibility of columns in forms.

Configuring Access Control

Access control defines where and how the data connection is exposed to users. You can use your data connection in 3 ways:

• Within a web part on a SharePoint page
• On a hosted page on our site
• Embedded on your own non-SharePoint page (such as your external website).

If you are planning to use the hosted page, check Allow hosted forms and choose if you want to expose New and/or View (list of items), choose both options. After you save your profile, you will be able to view the actual hosted page URL here as well. If you are planing to view on a SharePoint page or embed on your own web page, enter the allowed page URL.

Configuring Forms

Once you've saved your profile, you can click on Design form icon to enter Form Designer. Accept the offered option of generating a form for you, it will add all columns to the form canvas. Make any adjustments you want and publish the form.

Using Your Data Connection

Edit your connection and switch to Access Control tab. Here you can copy the page URL for the View page. Open it in a new browser tab, your data connection is now ready to be used.

External data connections are a new, exciting and easy-to-use way of interacting with your existing data and API, giving you powerful options of integration of your data sources.