cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenjaminMartin
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
Drrickryp
Super User II
Super User II

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

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

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
WarrenBelz
Super User III
Super User III

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.

Drrickryp
Super User II
Super User II

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

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

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

@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)

 

 

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (27,722)