External Data Lookup column is part of KWizCom Modern Forms (Enterprise edition).
Use the KWizCom's External Data Lookup column to connect to and include external data from any web accessible data source in your SharePoint Online modern list forms.
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:
Creating a Lookup column that connects to an external data source is done in 2 steps:
- Create an external data source
An external data source is created at the SharePoint site-level, as described in the External Data feature documentation. - Create external data lookup column/s in your list/s
After you have your external data source/s ready, you can then create an external data lookup column, and connect it to the required external data source.
The following sections describe how to create and configure External Data Lookup columns.
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:
If the external data source to which you connect includes parameters, then you can select other form fields that their values will be sent as the parameter values to the external data source.
| IMPORTANT: If the external data source includes mandatory parameters, it will not return any value if values are not provided for these mandatory parameters. This is a good way to avoid loading all results from the external data source to your Lookup column which may result in slow UX.
Instead, make sure your external data source includes mandatory parameters. Then add to your form the filtering fields that only when updated by the end-user, the external data source will return the filtered dataset to your Lookup column. |
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.