Dropdowns on Lookup Columns

[BenjaminMartin]

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.

[WarrenBelz]

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 · Accepted solution]

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

[BenjaminMartin]

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

[v-siky-msft · Accepted solution]

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

[BenjaminMartin]

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

[BenjaminMartin · Accepted solution]

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