cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cascading Dropdown Lists - Error with Filter Statement

Hi All, 

I am attempting to implement cascading dropdowns in my SharePoint form. Currently, I have three sharepoint lists with columns that look like this:

Product: Title

Tools: Title, Products (A lookup column to the Products Table Title values)

Test List: Title, Products (A look up column to the Prodcts Table), Tool (A lookup column to the Tools list)

 

After implementing these lists, I created a SharePoint form for the third list and added the other two lists as data sources. I've tried numerous solutions already presented here, but I continue to have reoccuring errors with the second dropdown. Currently, the first dropdown's items value is 'Choices('Test List'.Products)' and the second dropdown's value is 'Filter(Tools, Products = DataCardValue2.Selected.Value). In this instance, I get an error that says "part of the search formula cannot be evaluated due to service limitations" and that the '=' is an invalid argument type.

When I change the filter value to 'Filter(Tools, Title = DataCardValue2.Selected.Value)' I get "Part of this Search formula cannot be evaulted remotley due to service limitations". When I change the first dropdown's items value to 'Product.Title', then I get a red, error squigly under the equals sign and the .Value portion of the Filter statement. I'm lost on how to fix this problem and enable cascading dropwdowns for my SharePoint list form. Any help would be appreciated, as no solution I've found has helped. Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

Two things here Robert.

 

Filter(Tools,Products.Id = Dropdown1.Selected.ID).Title

 

That will show the Title field you want to see.

 

The blue warning is delegation. How many items do you have in your list? I bet it is more than 500.

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

6 REPLIES 6

Have you tried my video where I walk you through exactly this scenario? SharePoint LookUp Column functionality with PowerApps

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
v-monli-msft
Community Support
Community Support

Hi @Anonymous,

 

Are you trying to using the 2nd and 3rd data source in the same Form? If so, this is the reason why you have those errors. In PowerApps, one Form can only connect to one data source. This means that you cannot have other data sources in any dropdowns inside this Form.

 

To create the cascading drop downs functionality the approach is a bit different from submitting records through a Form. The "Test List" list has no knowledge of what Tools is given in what products. The Tools list is. The Tools list doesn’t has to be aware of all products. The Product list is.

 

I just find a blog has the exactly same scenario. Please follow it step-by-step to have a try. I just created a similar one and all work for me: (Follow from Cascading drop downs section)

https://www.itidea.nl/index.php/how-to-create-cascading-drop-downs-using-powerapps/

 

Regards,

Mona

 

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Shane,

 

I really like watching your PwoerApps videos and they help me out a lot generally. Unfortunately in this case, I am still receiving errors after I attempted to use the LookUp command, while trying to implement cascading dropdowns. Sepcifically, I typed the following statement "LookUp('Tools', Products = DataCardValue2.Selected.Title, Title)" into the items field of my second dropdown that the SharePoint form generated for me. I got two errors, one of which was the entire LookUp statement had an error stating that the proptery expects Table Values, but this rule prouces incompatible text values. The Title portion of the DataCardValue2.Selected.Title also came up with an error that stated it was an Invalid name. Was my syntax wrong or maybe did I implement the LookUp function incorrectly? *If its relavent, this was when my first dropdown/DataCardValue2 items value is equal to 'Product'.Title

Robert what I would do i start breaking it into baby pieces.

 

Put a label on the screen and set it to be:

 

DataCardValue2.Selected.Title

 

Do you see what you expect to see?

 

Make your dropdown Items property:

 

Tools.Products

 

Do you see what you expect to see? 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
Anonymous
Not applicable

Hi Mona,

 

The blog was very helpful to read, but unforntunatley I am still running into a problem. I created new dropdowns outside of my original form and put the first dropdown items value to Product. When I set the second dropdown items value to: Filter(Tools,Products.Id = Dropdown1.Selected.ID), I get a blue squigly under my '.Id' and the '=' portions of the statement. The error is "Suggestion: Part of this Filter Formula cannot be evaluated remotley due to service limitations. The local evaluation may produce suboptimal or partial results. If possible, please simplify the formula". Right now, I'm able to see and select white blank spaces in place of my actual values in my second dropdown. In other words, the cascading dropdowns are kind of working, but I'm not able to see the actual text values of the second drop down.

Two things here Robert.

 

Filter(Tools,Products.Id = Dropdown1.Selected.ID).Title

 

That will show the Title field you want to see.

 

The blue warning is delegation. How many items do you have in your list? I bet it is more than 500.

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

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