Filter values from a dropdown in a gallery to populate another dropdown with a specific value

(0) Share

Report

Posted on by Kadd

475

Hi all,

Hope you'r doing well.

Me again, sorry, again trying to do something my beginner's level can't fight (and trust me i tried...). So back to you...who will accept to answer me and bring me a solution !! 🙂

I wrote this post once a few minutes ago and due to a loss of connection i lost the post as well so i'll try to be clear and since i couldn't manage to add pictures, i'll try to describe things as clearly as possible.

I'm still on my idea, creating a single PowerApps form linked to several SP lists...and that works pretty well so far.... BUT....I have a SharePoint list (Impact). in this list I have these fields and values

IActTxt Timeframe RawMTPD (dropdown that contains values -,C,E,V,N,I,M and NE)

Act21 2h NE

Act21 4h -

Act21 8h -

Act21 2-5d -

Act21 6-10d -

Act21 11-30d -

Act21 Not Essential -

I created a PowerApps form, I have a gallery linked to my Impact (Gallery7), with the name of my activity (TextInput11) , 7 lines, corresponding to the 7 timeframes (this is a label), and the RawMTPD (Dropdown7 - Containing the values given above), this field is hidden. Everytime i will select an activity, this field will show 6 lines with "-" and 1 line with a value. (as in the example)

Under my gallery, I have an additional dropdown (Dropdown6) which contains the same values as the RawMTPD. For this I set the "Items" property to Choices(Impact.RawMTPD) .

Now my problem is... I have another gallery (Gallery5) that contains the activities . When i select one of the activities in Gallery5, the corresponding impacts show up (this works perfectly) but in my Gallery7, I need the values of Dropdown7 to be be filtered so it sets Dropdown6 to the value that is not "-". I set the Default property of Dropdown6 this way :

LookUp(Impact,IActTxt=Gallery7.Selected.IActTxt && RawMTPD.Value<>"-", RawMTPD.Value)

the problem is this shows 2 Delegation errors , one on "&&" and the other on ".Value" saying "the highlighted part of the formula might not work correctly on large data sets" and the other one adds "...correctly with column '6.RawMTPD.Value'..." (it's a 6 that is turned the other way around...).

Would one of you , please, be able to tell me what's wrong and help me solve this ?

Thansk a lot and regards !

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Sort by

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Kadd ,
I will answer the question you raised, Choice fields (RawMTPD.Value) are multi-value (complex field types) and are not Delegable Filters.
LookUp( Impact, IActTxt=Gallery7.Selected.IActTxt && RawMTPD.Value<>"-", RawMTPD.Value )
My first suggestion would be to change the SharePoint field to a Single Line of Text and hard-code the Choices in Power Apps. I do this on any Choice field that I need to Filter/Search on.

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
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Kadd ,

How many records stored in your Impact SP List? More than 2000?

Based on the issue that you mentioned, I think you have faced Delegation issue with your formula. Firstly, the Delegation warning issue is not an error, it just means you could not delegate the data process to your data source itself, instead, you could process data locally.

In default, you could only process 500 records locally in your app, you could change this limit, so you could process 2000 records locally at most in your app.

Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

If the amount of your Impact SP List is not more than 2000, you could ignore this warning issue. For your scenario, the Choice type column (Complex type column) could only be delegated with '=' operator, it could not be delegated with ‘<>’ operator.

If the amount of your Impact SP List is more than 2000, you could consider bulk-load your Impact SP List records into a collection in your app, then use the collection as data source in your app instead of the original Impact SP List data source.

Please check and see if the following thread could help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Sort-gallery-with-multiple-fields/td-p/379730

https://powerusers.microsoft.com/t5/Building-Power-Apps/Loading-60-000-SharePoint-List-Items/m-p/448633/highlight/false#M133427

http://rezadorrani.com/index.php/2019/09/18/powerapps-delegation-with-sharepoint-as-a-data-source-with-respect-to-all-column-types/#respond

Please take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Kadd 475 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @WarrenBelz and @v-xida-msft ,

Thanks both for your answers.

@WarrenBelz : Thanks a lot for your idea, however, the problem is i need to keep a dropdown in my SP List because some people should go and do modifications on the list itself. I'm trying to prevent the possibility for one of those people to write anything they want and the way the want inside this field cause later on it goes to an excel sheet and PowerBI for statistics so it need to be predefined...hmm...

@v-xida-msft : Thanks for your idea as well. I was hoping there would be an easy solution for what i want to do but ...ARRRGGHH !! 🙂 I currently have 21 values in my test impact list (7 lines for each activity- I have 3 activities). I'm pretty busy today but I will have a look into your links asap and come back to you. I'm pretty busy today so i guess I'll do it somewhere beginning of next week.

In any case, thanks a lot to both of you !!

Regards

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Kadd ,
I will give you some more thoughts on this from my (sometimes painful) experience with what you are dealing with. If you are wanting to use SharePoint as a data source (and it is a good one used properly), consider the fact that unless you are letting users loose in Quick Edit in a Data Sheet view, all Lookup and Choice functions can be done in Power Apps, either stand-alone or in the integrated forms.
All of my fields are now plain text in SharePoint making them completely delegable filters (and I have lists with over 10000 items). I have numeric "shadow" date fields in yymmdd format and any Boolean type field that could possibly require filtering is text with Yes/No or whatever hard coded in Power Apps. I also keep "shadow" ID numeric columns in some lists.
I understand that some or all of this may not be suitable for you business requirements, but I thought it may be worth raising.
Also going forward, can you please tag whoever you want to continue the thread with as three-way conversations generally do nothing but confuse everyone.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Kadd ,

Yeah, please take a try with the solution I provided above. The Delegation issue is related to the '<>' operator within your Filter formula.

More details about supported functions/operators for SharePoint connector, please check the following article:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint

So please do not specify '<>' operator or Not function in your Filter function, otherwise, you would face Delegation warning issue with your formula.

If you want to get rid of this Delegation warning issue, as an alternative solution, you could consider bulk-load your SP List records into a collection, then use the collection as data source in your app.

Best regards,

Was this reply helpful? Yes No
Kadd 475 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-xida-msft ,

ok that might solve the delegation issue. when i put "=" instead of "<>", the issue disappears but then that doesn't solve the rest problem 🙂

I needed "<>" because i want my lookup function to go through my gallery... and find, for a specific range values, the only value, within that range where my RawMTPD column is not equal to "-" and put this result into a single dropdown outside the gallery.

So i have 7 lines for per activity, i need to filter my activities and within the 7 resulting lines, i want to find the value that is not a "-" (out of the 7 lines, 6 are "-" and 1 is a value). and it has to show that single value inside my dropdown that is outside the gallery.

Any idea how I can then achieve this ?

Thanks a lot again for your help !

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Kadd ,

Currently, the '<>' operator or Not function is not delegable in SharePoint Connector, once you use '<>' or Not function in your Filter function, you would face Delegation issue.

Currently, I afraid that there is no way to achieve your needs in PowerApps currently. As an fixed solution, please consider bulk-load your SP list records into a collection in your app, then use the collection as data source in your app. Then you could use the '<>' operator in Filter function without Delegation warning issue.

Please take a try with above alternative solution I provided above, then check if it could help in your scenario.

Best regards,

Was this reply helpful? Yes No
Kadd 475 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @WarrenBelz ,

Sorry i missed your answer.

That's interesting to know, thanks a lot but then, if I may ask, that means you somewhat force your users to do all their changes in the PowerApps form for those values , right ?

Ah yes, you're right but you both have interesting point of views...however I understand it can be confusing for readers, sorry 😞

Thanks a lot !!

Was this reply helpful? Yes No
Max44 223 on at

Like
a
(0)

Report
Copy link

Link copied!

Select “Insert”
Select “Controls” and pick the “Drop Down” option.
After the field is created, click on the new field. You will want to make sure the “Property” area is set to “Items” The function needed to create a dropdown field is as follows: [“Option1″,”Option2″,”Option3”]

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Kadd ,
"Force" is a interesting word, but yes, this is what is presented to them to interact with. They can also use SharePoint for all the lovely grouping/filtering views to see their data and (as mentioned) use the Integrated PowerApps for for editing, but there are no Data Sheet views in the Navigation.
There is however one thing I omitted that may be of interest to you. We also use MS Access (which was our Legacy system), which connects directly and very well to SharePoint Lists and almost all of the functions simply needed to be connected to a matching SharePoint list rather than their Access backend. One of the great features of Access is the DataSheet view, which looks like an Excel spreadsheet, but as frozen columns and rows where required and everything from column widths to read/write on individual fields to drop-downs based on SQL queries on other lists are available. Fields can be directly edited and simply clinking away from the record writes the data. A select group of more experienced users have access to this.
Also all new records in one of our biggest lists are inserted with SQL directly into SharePoint in batches of up to 300 items (to avoid throttling).
If you find this interesting, happy to share the details.

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