Cascading Drop-Downs and SharePoint integration

[JillH]

Hi all

I have a robust PowerApps form that allows associates at my company to request projects, then others to assign them to a variety of associates. It's a SharePoint-integrated PowerApp.

I need to expand usage to more teams. What I need to do is create a list of teams (Team 1, Team 2, etc), then a secondary cascading drop-down of Project Types that populate depending on what Team was selected in the previous drop-down.

In my "sandbox" area, I've done just that using instructions from a video from Shane Young and an excel table uploaded to SharePoint & brought in as a data source.

Code for the second drop-down looks like this:

Distinct(Filter(TestProjectTypes,Title=DropDownRole.SelectedText.Value),'Project Type')

Works great.

But now, I'm trying to figure out how to integrate this all into my actual App. Can I use the Excel file to drive the selections then collect the answer to drop it in a field in SharePoint? Do I need to have the Teams and Project Types as a Lookup List in SharePoint and is that possible do the drop-downs in the same manor? A third option I'm not thinking of? 

In all this, I need to retain the ability to change the Team and/or Project list name upon occasion and not have it change the historical values. That however, could be more of a SharePoint question than a PowerApps one...

TIA!

[EricLott]

In my opinion, I would scrap the Excel file and create a SharePoint List that associates the Projects to the Teams i.e. a list for projects that have the columns ProjectID, ProjectName, ProjectTeamID, etc...

Then have a teams list with a team Id and a Team name. (unless you're referring to actual Microsoft Teams, which you can integrate via Flow with a GetTeam sort of function)

In PowerApps, you can now associate the teams and the Projects in the same manor you have above, just using your Lists instead of the Excel file.

[JillH]

Thanks!

I created a list with Team & Project Type, added those two fields as lookups to my primary list, and created a test app directly from the SharePoint "new/edit" window. I added the Team dropdown, edited items with the code below, and it's not working:

Distinct(JillTestBITimeList,WhatIsYourRole)

I added .value at the end, no dice.

Original Code, which gives duplicates:

Choices(JillTestBITimeList.WhatIsYourRole)

 Is there a way to combine Distinct and Choices? Some other methodology or syntax?

Thanks!

Jill

[v-siky-msft]

Hi @JillH ,

It seems that WhatIsYourRole column is LookUp type, please try this:

Distinct(Choices(JillTestBITimeList.WhatIsYourRole),Value)

Best regards,

Sik

[JillH · Accepted solution]

The solution I found was via this video - a different way of thinking about the whole thing that actually gives me some unexpected flexibility. 

https://www.youtube.com/watch?v=43ekj5MlNJU