How to filter by another lookup column using External data lookup

Please follow this article if you would like 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.

Part A:

1. Open KWIZ Apps Settings in top right gear icon and click External data lookup. 


   


 

2. In data source drop down select SharePoint List Items.



3. 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.



4. 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. 



5. 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




6. 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.






7. 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.




8. 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={0}]&$filter=Region eq '[Region]'

9. 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:

1. Go to your list in your current site or another site and click KWIZ Apps on the list ribbon. From
    there select gear icon beside External Data feature.






2. 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.






3. 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.

4. Go back to your list and select new item. You will notice now that when I select a Region in the 
    drop down only Country's associated to that Region will display in Country drop down.