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:

  1. Adding the Schools list as datasource to our PowerApp
  2. When selecting a specific faculty, we need to write all related schools into a collection (overwriting all existing data in that collection)
  3. 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
        )
    }
)

PropertyExplanation
collSchoolsThis is our collection where all related schools for the selected faculty are written in to
SchoolsThis is the name of the column where the data is written in to
FilterThis is the function that filters values from a data set. The format of this function is as follows:
Filter(source,logical_test)
SchoolsThis is the data set that we use in our filter. In this case, it is the Schools list we added as data source
Faculty.ValueThis 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.ValueThis 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
)

PropertyExplanation
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
ValueThis is the value we want to filter, which are the original values from the lookup field as stated in the row above
inThis is the comparison parameter. Since we are comparing to a collection, which contains multiple items, we cannot use the = parameter.
FirstWith 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
collSchoolsThis is the collection that we only want the First item from
SchoolsThis is the column that we want to from our first item
TitleThis 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:

25 Replies to “Creating conditional lookup columns in SharePoint with PowerApps”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  10. The solution “Test:” & LookUp(Schools, Title = DataCardValue5.Selected.Value, Test) works perfect, the cache was the problem 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *