How to delegate Filter()

(0) Share

Report

Posted on by efroseroc

152

Hello everyone, I´m new to Power Apps and I can't find a workaround for the following problem:

I´ve made a edit form based on a sharepoint list, let´s call it list1, this list as of now has 3000 records.

In a combobox I have this formula:

Item: Filter(List2; Zone= DataCardValue11.Selected.Value )

The idea of this formula is that based on the selected value on another combobox, bring items located in a sharepoint list (List2), this list has less than 50 records and will stay that way.

For example, if you pick Europe (Zone) , the combobox will show only european cities. When you are in "developer mode" it works, it only shows european cites

This formula shows a delegation warning, consequently when you submit the form as an user the field is empty. Even though the user picked valid options.

Is there a way to use another function other than filter to solve this issue?

Any help is greatly appreciated and thanks in advance!

Best Regards

Mike

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
Hi @efroseroc ,
You should not have delegation issues on that structure - I just tested a drop down on a 6000 item list and it filtered without any issues.
So two questions
What is the Items property of DataCardValue11
What type of field is Zone in List2

Was this reply helpful? Yes No
efroseroc 152 on at

Like (0)

Report

Hi @WarrenBelz , thank you for your help.

1. The items property is : Choices(List1.Zone)
2. In list1 : Zone: Choice.
In list2: Zone: Text.

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
Hi @efroseroc ,
Firstly, you should not have to do this as text searches are Delegable and the selection output from a Choice field is Text, but try this. On the OnChange of DataCardValue11 put

UpdateContext({vChoice:DataCardValue11.Selected.Value})

then in the Items of your second combo put

Filter(List2;Zone=vChoice)

and see if this works. This is not necessarily the final solution - I am just trying to identify the issue.

Was this reply helpful? Yes No
efroseroc 152 on at

Like (0)

Report

Hi @WarrenBelz
I've done as you asked and I have the same Issue, the user can use the boxes as intended but when you view the record itself fields are missing even though they are required.

Let me explain a little more:

The fields are: Zone(DataCard11), Location(DataCard8), base(DataCard6) and Country (Dropdown1)

So if you pick Europe, you can choose GER, and lastly Berlin
The idea is that Country is automatically selected in this case, Germany

DataCard6: items : Filter(List3; BASE = DataCardValue8.Selected.Location)

Where List3 has the connection between Location and Base and has few records.

Basically the same situation with Country.

When you check the record the fields that are missing are Location and Base; Country picks the first option in the list, Argentina

Maybe there is even a better way without that many lists, This app was handed to me and this is the way it was designed and I have little experience with Power Apps so I decided to leave it that way.

Thank you very much for your efforts

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
Hi @efroseroc ,
My first post was only about the delegation issue, which I assumed was your problem. I have not looked at anything else.
You have four levels of Filter -Zone, Country, Base and Location in DataCard11, DropDown1, DataCard6 and DataCard8 There are a few things I need to know
What is the order of the field input/filtering (from 1 - 4)
What type of controls are they?
What is the current Items property of each?
Some seem to be Card values, please confirm they are controls.
I may have a few more queries depending on the answers to this

Was this reply helpful? Yes No
efroseroc 152 on at

Like (0)

Report

Hi @WarrenBelz,

I think is easier to rename lists 2,3,4 to Location, Base and Country respectively.
1. The order is;
1. Zone -> ComboBox: Datacardvalue1.Items: Choices('List1'.Zone)

2. Location -> ComboBox: Datacardvalue8. Items: Filter( Location; ZoneL = vChoice )

3. Base -> ComboBox: DataCardValue6. Items: Filter(Base; LocationBase = DataCardValue8.Selected.BaseL)
LocationBase is a field in Base, and BaseL is a field in Location

4. Country -> DropDown: DropDown1: Items: Filter(List4; Country= DataCardValue8.Selected.CountryL)
Country is a field in Country and CountryL is a field in Location

So the user chooses between Zone( Asia, Europe, NA), Then location, let´s say Europe ( Munich, paris, madrid..) ,Then Base, in this case Munich, ( Deutsch Museum, Munich stadtmuseum....)
Country will be hidden from the user and automatically selected.
Thank you again.
Best regards
@efroseroc

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
Thanks @efroseroc ,
To get my mind around this - the first ComboBox DataCardValue1 for Zone is a Choices field in List1
Choices('List1'.Zone)
I assume this also sets the variable vChoice at OnChange
You then look for the locations in Combo Box Datacardvalue8 in a List called Location in the Field ZoneL based on the result of Zone choice above. In the box, you show field BaseL from the filtered items
Filter( Location; ZoneL = vChoice )
You then look for the Base in ComboBox DataCardValue6 in a list called Base in Field LocationBase based on Location above
Filter( Base; LocationBase = DataCardValue8.Selected.BaseL )
So going back a level, when you filter Location to get BaseL, CountryL will also be in that data set, so the Country should be available as
Lookup( Location; ZoneL = vChoice; CountryL )

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.

Was this reply helpful? Yes No
efroseroc 152 on at

Like (0)

Report

Thanks again @WarrenBelz ,
I apologise I made a little mistake in the previous message,
The correct settings for Country are:

4. Country -> DropDown: DropDown1: Items: Filter(Country; CountryName= DataCardValue8.Selected.CountryL)

Is basically the same but may be of help, at the moment this formula isn´t working.

All your assertions were correct, except the last one regarding the country, we have two issues here:
1. The result is a text, and the formula expects a table value.
2. vchoice is a zone and a zone can have many countries, so it doesn't select accurately, the country should be selected based on location or base.

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report

Thanks @efroseroc ,
If I read this correctly, you are trying to get a value from “higher up” in the query chain where it uniqueness has not been established?
I am not sure about the “table vale” reference, but before I continue the exploration of you data structure, would you consider simply putting Country in your Base table as CountryB and then it would be very easy to get.

Was this reply helpful? Yes No
efroseroc 152 on at

Like (0)

Report

Hi @WarrenBelz

I was giving it some thought and I believe it's better to simplify the problem.

I've created a new List called List.Base it has everything we need, you will notice is very similar to List1, and all fields are lines of text.
Of course the picture of List1 I post here is very basic compared to the real one, but it should help us to see clearly. On the other hand List.Base is exactly the same fields wise.

The form in the app is based on List1, let's imagine the app is made for cashiers all around the globe. What we want is for them to enter the date, value and the base the sale is made. The other fields (Location, country and zone) will be filled automatically and hidden from the user.

For this we have 3 ComboBoxes:
1. For Base we have, DataCardValue6 with items: Choices('List1'.BASE)
2. For Location we have, DataCardValue8 with items: Filter(List.Base;Base=DataCardValue6.Selected.Value).Location
3.For Country we have, DataCardValue8 with items: Filter(List.Base;Base=DataCardValue6.Selected.Value).Country
4. For Zone we have, DataCardValue8 with items: Filter(List.Base;Base=DataCardValue6.Selected.Value).Zone

When you test it it works, if you choose Germany it doesn't show NAM for example, same with all the other fields.
But when you check List1 for the record you just made, only Base is correct, location is empty even though is required, Country and Zone are incongruent values (For example NYC in EUR).

I hope this new approach is useful, thank you

LISTBASE.png

list1.png

Was this reply helpful? Yes No