Skip Ribbon Commands
Skip to main content
Sign In

Customer Service: 1-855-594-9266

canada flag

External Data Lookup

External Data Lookup


Feature Overview

External Data Lookup is part of KWizCom Modern Forms (Enterprise edition).

Use the KWizCom's External Data Lookup to connect to and include external data from any web accessible data source in your SharePoint Online modern list forms.

This feature includes 2 parts:
  • External data sources
    An External data source is configured at the site/site-collection level and incliudes all settings required to connect to a web service and retrieve defined data.

  • External Data Lookup
    You can add this Lookup column to your modern lists by connecting it to an external data source.

Example:
We have our products' data stored in 'AdventureWorks' Azure SQL DB.
The data is stored in 2 database tables: Products and Product Category.
Using the KWizCom External Data Lookup we can add Category, Sub-Category and Product cascaded Lookup columns to KWizCom modern list forms, connected to these SQL database tables:
The following sections describe how to configure External Datasources and External Data Lookup Columns.

 

Creating External Data Sources

When you install the KWizCom Modern Apps in your site, make sure to install the "External Data" feature:
To create new/edit existing external data sources, click the feature's gear icon.
COMMENT:
An external data source can be used in forms on the same site. If you want to be able to use an external data source from other sites (in the same site collection), 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 resulst 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

When connecting to a data source such as Web REST API the returned as raw data.
In the follo
wing example you can see the raw data returned from https://jsonplaceholder.typicode.com/users web service:
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.
 

Creating External Data Lookup Column

After you have your External data source/s ready, you can now create External Data Lookup column in your modern KWizCom form, and connect it to the required data source.
To create a new External Data Lookup column click the "KWizCom Apps" toolbar button in your modern list view:
Then click the External Data icon to open the External Data settings page:
In the External Data settings page type the new Lookup column name, and then select a data source and the display column you want to connect to:
Click "Save" to save the new External Data Lookup column settings.

External Data Lookup Implementation Details

The External Data Lookup column is implemented as a loosely-coupled lookup.
So how does it work?
Behind the scenes it is implemented as a SharePoint Choice column.
When user selects a lookup option in New/Edit form and saves the item - the entire remote item is saved in a hidden JSON column. 
There are several cases that should be considered:
1. If the data source does not work (server down), the lookup will show an error and allow you to try again. You will NOT lose your saved value if you edit the item.
2. If the item you selected does not exist anymore, but a new/different item exist with the same display value - your item will now have that item selected and update the JSON field to match that new item.
3. If there are multiple items with the same display value - the first item will be loaded and displayed in the External Data dropdown/grid.
Saving the entire remote lookup data item in the External Data column enables using this field by other applications like office or workflows, without expecting that endpoint to need to connect to the data source itself.
 

Created at 1/26/2022 8:12 AM by Nimrod Geva (UTC-05:00) Eastern Time (US and Canada)
Last modified at 3/6/2022 9:57 AM by Nimrod Geva (UTC-05:00) Eastern Time (US and Canada)
Total Views: 469

Tags

Article Type: User-guide
Recent Discussions
There are no items to show in this view.