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

How to delegate Filter()

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

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz Community Champion
Community Champion

Re: How to delegate Filter()

Hi @efroseroc ,

This thread is getting way beyond original question (How to delegate filter) which has been solved.

These threads need to be indexed properly so users can find them.

Can you please accept this one on the appropriate post that solved it and start a new thread called something "Setting Default values off hidden controls" and tag me in it - I will respond.

 

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.

View solution in original post

15 REPLIES 15
WarrenBelz Community Champion
Community Champion

Re: How to delegate Filter()

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

  1. What is the Items property of DataCardValue11
  2. What type of field is Zone in List2
efroseroc Helper I
Helper I

Re: How to delegate Filter()

Hi @WarrenBelz , thank you for your help.

 

1. The items property is : Choices(List1.Zone)

2.   In list1 : Zone: Choice.

      In list2: Zone: Text.

 

 

WarrenBelz Community Champion
Community Champion

Re: How to delegate Filter()

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.

 

efroseroc Helper I
Helper I

Re: How to delegate Filter()

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

 

 

 

WarrenBelz Community Champion
Community Champion

Re: How to delegate Filter()

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

  1. What is the order of the field input/filtering (from 1 - 4)
  2. What type of controls are they?
  3. What is the current Items property of each?
  4. Some seem to be Card values, please confirm they are controls.

I may have a few more queries depending on the answers to this

 

 

efroseroc Helper I
Helper I

Re: How to delegate Filter()

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 

WarrenBelz Community Champion
Community Champion

Re: How to delegate Filter()

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.

efroseroc Helper I
Helper I

Re: How to delegate Filter()

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. 

 

 

WarrenBelz Community Champion
Community Champion

Re: How to delegate Filter()

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.

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,708)