To create new/edit existing external data sources, click the feature's gear icon.
COMMENT: An external data source can be used in all sub-sites in the same site collection. If you want to create an external data source that will be accesible from other site collections, then you should create it in the top-level site. |
To create a new external data source click the "new datasource" button.
By clicking the button's arrow on the right you can select one of the available data source examples, which will create a pre-configured data source.
External Data Source Settings
Info Tab
In this tab you should select the external data source type and give it a name and a clear description (For the users who will later use this data source).
The available data source types are:
- Web REST API
Connects to a web service
- Excel or CSV file
Connects to an excel or CSV file that is stored on your SharePoint site
- Custom code data source
This type allows you to write your own custom code data source
Parameters tab
You can configure parameters that will be included in the data source connection.
This way you can filter the returned results at the data source level.
To add a new parameter click the "+ Add parameter" button and give it a name:
When creating a parameter there are 2 additional properties that affect its behaviour in run-time:
- Default value
This value will be used when no value is sent when connecting to the data source. It is also used when you want to test your data source. - Required
When a parameter is required, the data source will return empty results if the parameter is empty.
Check the 'Required' property when you want to make sure that no data is retrieved from the data source, unless mandatory parameters which should filter the results are provided.
Another way to filter the data source is by using a custom filter function that will run in the client browser.
Clicking the "Add custom filter function" button will open a mini-editor to edit your script:
Connection tab
The 'Connection' tab displayed properties depend on the selected connection type.
Web REST API connection properties
To connect to the web service fill-in the Url field and optionally the Body and Request headers (if required by your web service).
If you have parameters configured in the "Parameters" tab, you can insert these parameters to be included in the Url/Body of your request, by clicking the "+ insert parameter" button below each of these properties.
Then click the "Load Preview" icon
to send the request to the web service and display the returned results.
Additional Connection properties are:
- Keep results in local browser cache
Use this property to cache results in the client browser. if the web service request takes over 2 seconds to complete, the results will be cached in the client browser. - Authentication
Supports annonymous, Basic and MSAL authentication, use according to your websrevice requirements. - Items collection path
Enables setting the object path from the root of the results to the items collection.
Click the picker to select the required part/node from the retruned collection.
After you fill-in the Url and click the "Load Preview", the raw data returned from the called web service will appear side-by-side with your connection settings:
This enables you to change your connection setting and see the results side-by-side.
Excel or CSV file properties
For the "Excel or CSV file" data source type the "Connection" tab displays just the Url property:
After you select the Excel/CSV file stored in SharePoint or type it's url, you'll see additional properties:
Select the Table/Sheet/Range and click "Load Preview..." - this will load the data from the Excel/CSV file and display the results side-by-side:
Custom code data source
When selecting this type, a mini-script editor will be displayed:
Here you can write your own script to call and format the results returned from any source.
Display columns tab
Display columns enable you to format the raw data and expose it in your required format.
Example:
As you can see in the screenshot above, some of the returned raw data columns are objects that include several data entities.
Here's the returned "address" column:
The address object includes the following data items: street, city, zipcode and other items.
We might want to expose these data items as columns for consumers of this data source (such as the external data lookup column), and this can be done using display columns.
To create a new display column click the "+ Add display column" button:
The following popup will open:
The following properties need to be configured:
- Id
Unique identifier of the display column - Caption
The displayed caption - Output type
Select the type used to render the value for this column. Depending on the selected type you will see additional relevant properties. - Value type
Choose one of the following types: - Mapped value - display a selected raw data source field.
When selecting this type, type the raw data column's name in the "Mapped value" property, or use the "+ select data source field" picker to select a column from the raw data collection. - Formatted string - This type enables you to format a custom string format that includes data source field values.
- Computed value - This type enables you to write a custom script that returns the required value.