Learn how to to filter by another column using External data lookup.
For my example I am filtering Region to Country. In this article I will create two data sources: one for Region and one for Country.
This guide will walk you through creating cascading dropdowns in KWizCom Forms using the External Data Lookup column.
Part A: Setup Data Sources
Expand to see the setup
1. Click the gear icon in the top-right corner and select KWIZ Apps Settings from the dropdown menu.
2. In KWIZ Apps list settings page, click on a gear icon besides External Data Connector
3. In Datasource type drop down select web REST API and then SharePoint List Items.
4. For the first data source I will connect to the my SharePoint List which contains my region data. In the Parameters tab: enter your siteUrl and listTitle.
5. In the Connection tab, you will see the siteURL that you are connecting to which includes columns that you are retrieving from your list. Enter the internal column names that you want to retrieve.
- In my example, I am retrieving the Region and Country columns.
6. In the Display columns tab, add the column you have just added within the Url.
- For my example I have added Region column. For caption enter your column name and for Mapped value enter you column name again than click OK and Save.
7. For your second data source (SharePoint List items), enter your siteURL and listTitle. If you are connecting to the same list you can copy the data source from External data main menu.
Rename the data source to Country.
8. From there add an additional parameter which will be the column name from your first data source.
- In my example I am using my Region column from my first data source connection. Make this a Required parameter.
9. In the Connection tab, you will want add a filter statement which enables the filtering between two columns. enter the filter statement right after the square bracket ]. In my example i have entered my Region column name where Region equals Region.
[siteUrl]/_api/web/lists/getByTitle('[listTitle]')/items?$select=Id,Title,FileLeafRef,Modified,Created,Author,Region,Country,City/UserName,Author/Title,Editor/UserName,Editor/Title&$expand=Author,Editor[itemLimit??&$top=]**&$filter=Region eq '[Region]'
10. In the Display columns tab, enter your second column that you would like to retrieve.
- For my example I have added Country column. For caption enter your column name and for Mapped value enter you column name again than click OK and Save.
Part B: Create columns and link the data sources
Expand to see the setup
1. Click on KWIZ Apps in the ribbon to open the KWIZ Apps settings page for the list.
2. Click on a gear icon besides External Data Connector
3. Create a new External data column and name it Region as per your first external data source in Part A.
In the External data source drop down select Region and in Display Column drop down select Region. After that please click Apply at the top of settings.
4. Create another column and name it Country as per your second data source in Part A.
In the External data source drop down select Country and in Display Column drop down select Country.
Scroll down and you will see the Data Source Parameters. In the Set parameter [Region] value to enter Region in both drop downs. Click Save at the top of settings.
Results
Go back to your list and select New item. You will notice that when selecting a Region in the drop-down, only Country's associated to that Region will display in Country drop-down.
