Answered

Dropdowns on Lookup Columns

(0) Share

Report

Posted on by 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.

Categories:

Building Power Apps

All responses (6)

Answers (3)

Sort by

WarrenBelz 156,526 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

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 44 on at

Like
a
(0)

Report
Copy link

Link copied!

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

v-siky-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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 44 on at

Like
a
(0)

Report
Copy link

Link copied!

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

BenjaminMartin 44 on at

Like
a
(0)

Report
Copy link

Link copied!

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