Creating conditional lookup columns in SharePoint with PowerApps
I am a fan of using lookup columns in SharePoint, because I only have to define something once instead of filling in the same information multiple times in various locations. It’s also very easy to incorporate this into your PowerApp. But if you take it to the next level and you want a lookup column to depend on another lookup column and create some sort of a conditional lookup column, you may have some struggles to configure this into your PowerApp.
In this blog, I will explain how to achieve this.
Configuring the data model in SharePoint
In this blog, I will use an example of a SharePoint list: New students (onboarding), where new students are added. In this list, the following information will be stored:
- Student name
- Faculty where the student will follow its courses
- School where the student will follow its courses
Student name is a simple Single line of text field. Faculty and School are lookup columns to the following lists:
- Faculties; This list only contains one Single line of text field: Faculty and contains the name of the faculty
- Schools; This list contains a Lookup field to the Faculties list and a Single line of text field which contains the name of the school
The data model of this solution is as follows:
I added some test data into the Faculties and Schools list so that I could test with this data:
Default behavior in SharePoint
When I want to add an item in the New students (onboarding) list, I can select the Faculty of Business and the School of Medicine. This is not what I want, because that faculty and school don’t match together. For Faculty of Business, only the School of Business should be available in the dropdown box.
PowerApps to the rescue!
To make sure you can only select the schools that are related to a specific faculty, you must use PowerApps to modify the behavior of your lookup column.
To do this, click on the Customize forms options from the Command bar of your New students (onboarding) list. This will trigger PowerApps and create a default form for you.
We only want schools to appear that are related to the selected faculty. Unfortunately we cannot filter values directly into the Schools lookup fields, because the choices in this field are based on the following expression:
Choices('New students (onboarding)'.School)These only contain the Value and the ID of the selected school. This dropdown has no knowledge of any Faculties.
The easiest way to make filtering possible is to write all schools that are related to the selected school into a collection and filter the School lookup column based on the values into that collection. The steps we need to perform:
- Adding the Schools list as datasource to our PowerApp
- When selecting a specific faculty, we need to write all related schools into a collection (overwriting all existing data in that collection)
- Filtering our School lookup choices: if one or more values from the choice option are in our collection, they need to be available. All other choice options need to be removed from the list.
Adding Schools data source
Before we can get all related schools for a selected faculty, we need to add the Schools list to our PowerApp. This can be done by going to the View tab and selecting Data sources. A window will appear on the right site where you can click + Add data source. Select the SharePoint connection. Your site should show up beneath ‘Recent sites’ if you have recently used this site within PowerApps. If not, you can manually enter the Site URL and click Go. Select the Schools list and click Connect. The data source is now added to your PowerApp.
Writing the related schools into a collection
When a faculty is selected, we need to get all related schools from our Schools list and write them into a collection. For this, we need to configure the OnChange event of the Faculty field. Because we want to overwrite all existing data into the collection, we need to use the ClearCollect() function. The expression we need to use is:
ClearCollect(
collSchools,
{
Schools: Filter(
Schools,
Faculty.Value = DataCardValue2.Selected.Value
)
}
)
| Property | Explanation |
|---|---|
| collSchools | This is our collection where all related schools for the selected faculty are written in to |
| Schools | This is the name of the column where the data is written in to |
| Filter | This is the function that filters values from a data set. The format of this function is as follows: Filter(source,logical_test) |
| Schools | This is the data set that we use in our filter. In this case, it is the Schools list we added as data source |
| Faculty.Value | This is the value we are looking for in our data set. In this case, it is the Faculty lookup field in our Schools list |
| DataCardValue2.Selected.Value | This is the selected faculty from our PowerApp |
After adding this function, we can check if it works by entering the Preview mode (by pressing F5), selecting a Faculty and closing the Preview mode. If we view the collection by clicking on the View tab and selecting Collections and clicking on the table icon in our Schools column of our collSchools collection, you can see that only the schools that are related to our selected faculty are filled.
Please note that only the first 5 items inside the collection are shown.
Filtering the School lookup column
Now that we have something that we can compare our School choices to, we can proceed by filtering our School lookup field. We need to configure the Items property of the Schools lookup field by using the Filter() action. Before we can do that, we need to Unlock the specific card by selecting the Department card and clicking on Unlock to change properties within the Advanced tab.
After that, we can fill in our expression within the Items property, which is:
Filter(
Choices('New students (onboarding)'.School),
Value in First(collSchools).Schools.Title
)
| Property | Explanation |
|---|---|
| Choices('New students (onboarding)'.Department) | This is the data set we want to filter, which are the original choices from the lookup field. You can copy this entry from the original Items property |
| Value | This is the value we want to filter, which are the original values from the lookup field as stated in the row above |
| in | This is the comparison parameter. Since we are comparing to a collection, which contains multiple items, we cannot use the = parameter. |
| First | With this expression, you only take the first item from your collection. We only have 1 item in our collection so this is what we need |
| collSchools | This is the collection that we only want the First item from |
| Schools | This is the column that we want to from our first item |
| Title | This is the field we want. If you look at the ClearCollect section of this blog, you can see that there is a Title field in the collection which contains the School |
Completed, or not?
If all steps have been configured correctly, we can now select only the Schools that are related to the selected faculty.
One thing that still isn’t exactly what I want is the following:
After you select a school and change the faculty, the school will remain selected even though the school is not related to the faculty you selected.
You can easily fix this by clearing the input from the School field when changing the Faculty field by adding the following expression to the OnChange() property of your Faculty field:
Reset(DataCardValue3)Where DataCardValue3 is the Schools lookup field in your PowerApp. After that, the Schools lookup field will be cleared after you change the faculty:
Summarizing
You should have the following expression within the OnChange property of your Faculty lookup field:
ClearCollect(
collSchools,
{
Schools: Filter(
Schools,
Unit.Value = DataCardValue2.Selected.Value
)
}
);
Reset(DataCardValue3)
and the following expression within the Items property of your Schools lookup field:
Filter(
Choices('New students (onboarding)'.Department),
Value in First(collSchools).Schools.Title
)
If you save and publish your PowerApp, you can try this within your SharePoint list and see that it works:
35 Replies to “Creating conditional lookup columns in SharePoint with PowerApps”
Hello, me again 🙂 I saw in the comments that you asked if it was a multi-select lookup. Do you have any suggestions for the code if we were to change the Faculty lookup column in the School list from single select to multi select. In the form (Student) my lookup is single select but in the Schools list it is multi-select.
I haven’t tried this for myself, but you can try to have the selected faculties into a single string value using the Concat() function. Something like: Concat(DataCardValue2.SelectedItems,Value,”;”).
You can incorporate that into the ClearCollect() function on the OnChange of your Faculty dropdown and change the equals to operator (‘=’) to the contains operator (‘in’). That should result into something like this:
ClearCollect(
collSchools,
{
Schools: Filter(
Schools,
Faculty.Value in = Concat(
DataCardValue2.SelectedItems,
Value,
“;”
)
)
}
)
Again; I don’t know for sure if this will work, but I think it will point you into the right direction
Love this post. Thank you for creating this. I am creating a simple purchase request form with this. Is there a way to sort the School Data Card. That card is my Approver list and I’d like to sort is Descending based on the value of another column.
You can just put a Sort() function around the Filter() to sort by School. Make sure to use the current value to sort on
Hi Rik,
Instead of populating school, i need to lookup a multi person field from another list and populate as default select value in current list. If it is a single person field then we pull email and displayname from other list like:
{
‘@odata.type’: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,
Claims: “i:0#.f|membership|”,
DisplayName:
}
but if it is a multi person field then how do we set the default selected value by looking up in another list? Please help.
I haven’t tried this before, but I think you should use the ForAll() function to iterate through your multi people field. For each entre found, you should append the @odata.type function as you already stated to either a string or an array variable and use that to populate the default select value.
Hi Question,
I have an question. The example works perfect for two drop downs. But, if you wanna use a conditional formatting with four drop down.
Can i use the same approch for the solution? Just make an third/fourth list, etc?
I’ve never tried it, but it should work the same. Just lookup the child value based on the parent value in a new list.
The solution “Test:” & LookUp(Schools, Title = DataCardValue5.Selected.Value, Test) works perfect, the cache was the problem 🙂
Good to hear. If you have any other problems, please don’t hesitate to contact me again!
Hi Rik,
I have a question about the solution. I have create an extra column in the list, Schools. The name of the column is for example Test. When i select the school, i want to show the info from the column Test. Normally my lookup code is like:
“Test:” & LookUp(Schools, Title = DataCardValue5.Selected.Value, Test)
But this solution creates first a collection. Do you suggest i need to make the collection wider with the new Test column?
Hi Rik, i found the problem! The tenant was not the problem but the language of the site. My teamsite is in Dutch and then is the Id with a small d. When i test the solution within a team site in English, the syntax works fine, awesome!
But, how is the Id correct? I changed it, but it still doenst work in a Dutch teamsite. What would you suggest?
Can you reproduce this?
Thanks in advance!
Hi Rik,
I just test your solution on a demo tenant and it suddenly works perfect! So, the problem is in the tenant. Now i can search for the problem and fix it.
Thankfully your solution is fine! Have a nice weekend ?
Good to hear, but weird it’s tenant related.. If you have any further questions, don’t hecitate to contact me again!
Hi Rik,
Just tried it for a second time in a new site but i got still the same error. The name isn’t valid. The identifier isn’t recognized.
The first solution works again perfectly
I really have no idea why you’re getting this message.. Maybe you can send an export of your lists (main and lookup lists) to me to see if I can reproduce it? You can send the exports to rik [at] about365 [dot] nl
Hi Rik,
Just recreate the app and still the same error. PowerApps show Id/Value after i use auto-fill in with de Faculty lookup field but still got an error.
https://imgshare.io/image/vnAoH
I can only test it with all new lists. Or is maybe the language an problem? My tenant is in English.
Very strange. All of my examples are in English too, so the syntax should be the same.
Is your lookup column a single select or multi select?
Single select. I see the that default language is Dutch, my browser is default English. I will build the setup in my demo tenant, that tenant is in English.
The main blog, the first solution works fine in my app…
Very strange indeed. I will recreate the app.
Filter(
Choices(Students.School),
Id in First(collSchools).Id
)
After choices, you typ Students, is that a new list? In the blog you start with
Filter(
Choices(‘New students (onboarding)’.Department),
Value in First(collSchools).Schools.Title
)
Yes, I recreated the solution since I changed tenants so I had to rebuild it.
I used ‘Students’ as list name instead of ‘New students (onboarding)’
Yes, it does. It’s also a lookup column
The auto-fill in shows the Id and the Value, so that’s correct. After is choose for Id, i got an error “Name isnt valid”, very strange.
https://imgshare.io/image/jxAKw
https://imgshare.io/image/jxWwe
That’s very weird indeed.. Can’t seem to think what’s wrong with it. Looks like a small bug or something.
Can you try to recreate the PowerApp?
Every thing is the same but i still got an error: “invalid argument type”
Code is the same. Last option, build the app again but i don’t get it. Your example is perfect and i still got an error.
See: https://imgshare.io/image/jxS7X
Based on your screenshot, it looks like the PowerApp doesn’t seem to know the Id attribute of your Faculty lookup in your Schools list. Are you sure it’s a lookup field?
What will auto-fill if you just enter Faculty. (without the ‘Id’)? It should return both ‘Id’ and ‘Value’.
Hi Rik,
Thanks for your help. When i add the new code, i got an error on ParenClose / Clone. Can you plz have a look, see https://imgshare.io/image/jmBDn
ClearCollect(
collSchools,
{
ID: Distinct( Schools: Filter(
Filter(
Schools,
Faculty.Id= DataCardValue2.Selected.Id
),
ID
).Result
}
);
Reset(DataCardValue3)
Code looks the same as your example?
I see a few differences:
1. The column of your collSchools is ID (all capitals) where mine is Id (only first capital). This may cause some problems in some occasions, although I don’t think it’s the case here.
2. You’ve added an extra ‘Schools: Filter(‘ after your Distinct. I don’t have this in my function. I think this is the issue, because there aren’t enough closing tags in your function now. That’s the ParenClose error you’re getting. Again, my function is:
ClearCollect(collSchools,
{
Id: Distinct(
Filter(
Schools,
Faculty.Id= DataCardValue3.Selected.Id
),
ID
).Result
}
);
Reset(DataCardValue4)
Hi Rik,
First, thank you for the very nice blog. I use it and it works nice. With a large lookup list i got duplicates so i used Distinct to filter them out. This works fine but i can’t save the data to SharePoint now. Do you have a tip how i can save my datacard to SharePoint?
Here is the code:
Distinct(
Filter(
Choices(‘New students (onboarding)’.School),
Value in First(collSchools).Schools.Title
)
Value,
)
I think I found the issue. It’s because we’re filtering on Value and not on ID in both lookup fields.
Also, when using Distinct, you will only get 1 Result column. SharePoint expects a combination of Value and ID for a lookup column, so setting the Distinct on the School Items() property will not work because you will lose the ID column.
You need to fill your collection with the unique IDs of the schools (and apply the Distinct to the OnChange of your faculty field). With that in place, you can filter the Items of your School field to that collection.
In the example below, DatacardValue3 is my Faculty dropdown and DatacardValue4 is my School dropdown:
The OnChange() of your Facilty lookup should be:
ClearCollect(collSchools,
{
Id: Distinct(
Filter(
Schools,
Faculty.Id= DataCardValue3.Selected.Id
),
ID
).Result
}
);
Reset(DataCardValue4)
And the Items() of your School lookup should be:
Filter(Choices(Students.School),
Id in First(collSchools).Id
)
With that in place, my data is saved to SharePoint
Hi,
Thanks for the perfect blog. Makes it very easy to use. I have a question. I have a lot of duplicates in my lookup list. Do you recommend to use the Distinct within the filter option?
Distinct(
Filter(
Choices(‘New students (onboarding)’.Department),
Value in First(collSchools).Schools.Title
),
School
)
Duplicates can indeed be eliminated by using Distinct(). But isn’t there an issue in your data model if you get duplicates on a conditional lookup? It should be a one on one relation there
Reset on edit form is not working. Do need to handle it separately?
What isn’t working? If you need to reset a form, you need to user ResetForm(). If you need to reset a control, you need to use Reset().
Hello, In edit mode it reset to previous value. But I would like to reset to blank value, because in edit mode user could select another item from the first dropdown. How can I do?
You should use ResetForm(), followed by NewForm( ) on the OnSuccess of your form. This way, after the form has been succesfully saved to SharePoint, the form will load in New mode instead of Edit mode