cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Dropdowns on Lookup Columns

Hi Everyone,

I have 1 SP List that I export data to from a database. I need to create 2 dropdowns in my app based off that list and have it output to another list. For example, choose location in first dropdown then choose from the projects under that location after, then submit the form.

I created 2 lookup columns in the new SP list connected to the export but I get delegation errors. 

 

I'd also like to filter the results in the app so it only shows the entries the current user has made.

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Dropdowns on Lookup Columns

Hi @BenjaminMartin 

From your description, it sounds like you want cascading dropdowns based on the database.  This is done by filtering the Items of the second dropdown based on the selection in the first and using Distinct to remove duplicates.  The example on the left a list of imaginary contacts filtered by state and city but is analogous to your location and projects list.cascading.png  The formulas for the control's Items properties are shown in blue beneath each control. A second list could be populated by submitting the results to another list based on the selections in the dropdowns and the gallery with the Patch() function on a button on the screen with its OnSelect property as follows:  Patch(SPlist, Defaults(SPlist), {state:Dropdown1.Selected.Result, city:Dropdown2.Selected.Result, company: Gallery1.Selected.company, email:Gallery1.Selected.email, etc.})

View solution in original post

Highlighted
Community Support
Community Support

Re: Dropdowns on Lookup Columns

Hi @BenjaminMartin ,

 

Have your issue been resolved? If not, happy to help further.

 

If you'd like the Gallery to only show entries for the current user instead of everyone's entry, please add one more logic criterion in Filter formula in Items property:

Filter('SP list', 'Created By'.DisplayName = varUser.FullName)

 

Set(varUser, User()) // save User info into variable in App.OnStart property

 

In your Patch function, you need to reference the control value using dot notation, rather than the control itself.

 

Patch('Capex Hours Reporting', Defaults('Capex Hours Reporting'), {Facility_31:ddMarket.Selected.Result}, {Project_title:ddProject.Selected.Result},{pay_period:ddpay_period.Selected.ColumnName},{hours:Value(texthours.Text)},{close_project:closetoggle.Value})

 

Hope this helps

Sik

View solution in original post

Highlighted
Helper I
Helper I

Re: Dropdowns on Lookup Columns

I got it! This ended up working....

 

SortByColumns(Filter([@'Capex Hours Reporting'],varUser.FullName = 'Created By'.DisplayName, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Dropdowns on Lookup Columns

Hi @BenjaminMartin ,

You cannot Filter on Lookup columns and they are not Delegable in other functions, hence the warning.

Do they need to be Lookup columns (you can do the same Lookup in Power Apps by connecting to the relevant lists).

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Super User III
Super User III

Re: Dropdowns on Lookup Columns

Hi @BenjaminMartin 

From your description, it sounds like you want cascading dropdowns based on the database.  This is done by filtering the Items of the second dropdown based on the selection in the first and using Distinct to remove duplicates.  The example on the left a list of imaginary contacts filtered by state and city but is analogous to your location and projects list.cascading.png  The formulas for the control's Items properties are shown in blue beneath each control. A second list could be populated by submitting the results to another list based on the selections in the dropdowns and the gallery with the Patch() function on a button on the screen with its OnSelect property as follows:  Patch(SPlist, Defaults(SPlist), {state:Dropdown1.Selected.Result, city:Dropdown2.Selected.Result, company: Gallery1.Selected.company, email:Gallery1.Selected.email, etc.})

View solution in original post

Highlighted
Helper I
Helper I

Re: Dropdowns on Lookup Columns

@Drrickryp thank you!

 

I got everything working except for the last few columns of the patch function. Here's what I have:

 

Patch('Capex Hours Reporting', Defaults('Capex Hours Reporting'), {Facility_31:ddMarket.Selected.Result}, {Project_title:ddProject.Selected.Result},{pay_period:ddpay_period},{hours:texthours},{close_project:closetoggle})

 

-pay_period is a number dropdown

-texthours is a text field where the user enters the number

-closetoggle is a yes/no toggle

 

When I try Selected.Value or Selected.Result they don't work

 

I'd also like for the Gallery to only show entries for the current user instead of everyone's entry. 

Highlighted
Community Support
Community Support

Re: Dropdowns on Lookup Columns

Hi @BenjaminMartin ,

 

Have your issue been resolved? If not, happy to help further.

 

If you'd like the Gallery to only show entries for the current user instead of everyone's entry, please add one more logic criterion in Filter formula in Items property:

Filter('SP list', 'Created By'.DisplayName = varUser.FullName)

 

Set(varUser, User()) // save User info into variable in App.OnStart property

 

In your Patch function, you need to reference the control value using dot notation, rather than the control itself.

 

Patch('Capex Hours Reporting', Defaults('Capex Hours Reporting'), {Facility_31:ddMarket.Selected.Result}, {Project_title:ddProject.Selected.Result},{pay_period:ddpay_period.Selected.ColumnName},{hours:Value(texthours.Text)},{close_project:closetoggle.Value})

 

Hope this helps

Sik

View solution in original post

Highlighted
Helper I
Helper I

Re: Dropdowns on Lookup Columns

@v-siky-msft  Thank you. I was able to get the patch function to work as needed. I've tried adding the filter for the current user to the items property of my gallery. If I add that filter by itself it works but when I add it to what's there with a , or ; it gives an error.

 

This is what is there currently:

 

SortByColumns(Filter([@'Capex Hours Reporting'], StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending)

 

 

Highlighted
Helper I
Helper I

Re: Dropdowns on Lookup Columns

I got it! This ended up working....

 

SortByColumns(Filter([@'Capex Hours Reporting'],varUser.FullName = 'Created By'.DisplayName, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,501)