cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lon
Level: Powered On

Cascading Dropdown - 3 lists - IT WON'T WORK

Hello everyone,

 

i'm desperate. I've watched all YT videos and felt 100 blog entries about cascading dropdown, but so far I haven't found a solution to my problem.

 

That's my scenario:
3 Lists:


Firmen(column Firmenname -> single line)
Ansprechpartner(column Firma -> Lookup -> List Companies to column Firmenname)
Tickets (column Kunde -> Lookup -> List Firmen to column Firmenname and column Ansprechpartner -> Lookup -> List Ansprechpartner to column Email-Adresse)

 

I added all three Lists as Data-Source.

 

Based on the selection of the first dropdown, the second dropdown is to be filtered.

On the Second Screenshot you can see the Filter. But it dont works. 

I tried many variations but nothing worked.

I hope you can help me!

 

1.JPG2.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Lon
Level: Powered On

Re: Cascading Dropdown - 3 lists - IT WON'T WORK

Finally i found a sollution!

Everytime i tried the Filter with the default datacard which is created by adding the datafield from sharepoint dataset. But with this datacard the filter is not working.

 

Anyway, i have added a dropdown field independently from the dataset and added following line in "Text-properties":

Filter(Ansprechpartner; Firma.Value = DataCardValueCustomer.Selected.Value)

The Filter works now. But it is said that with large datasets the filtering may not work properly.

View solution in original post

7 REPLIES 7
JRaasumaa
Level 10

Re: Cascading Dropdown - 3 lists - IT WON'T WORK


@Lon wrote:

Hello everyone,

 

i'm desperate. I've watched all YT videos and felt 100 blog entries about cascading dropdown, but so far I haven't found a solution to my problem.

 

That's my scenario:
3 Lists:


Firmen(column Firmenname -> single line)
Ansprechpartner(column Firma -> Lookup -> List Companies to column Firmenname)
Tickets (column Kunde -> Lookup -> List Firmen to column Firmenname and column Ansprechpartner -> Lookup -> List Ansprechpartner to column Email-Adresse)

 

I added all three Lists as Data-Source.

 

Based on the selection of the first dropdown, the second dropdown is to be filtered.

On the Second Screenshot you can see the Filter. But it dont works. 

I tried many variations but nothing worked.

I hope you can help me!

 

 


 

Could you simply filter the second collection using the 1st selected dropdown item instead of the datacard value? Your code shows the filter looking to DataCardValueCustomer.Selected.Id when it could simply say DropDown.Selected.Value

 

So in your second dropdown put in the "Items" property of the dropdown:

 

Filter(Ansprechpartner; Firma.Id = DropDown1.Selected.Value)

 

Then select the appropriate "Value" of the second dropdown to show the data you want displayed.

 

For example:

 

I have a collection Quotes with 2 columns, QuoteNumber and Year.

 

DropDown1 has property "Item" = Quotes and "Value" = Year

 

DropDown2 has property "Item" = Filter(Quotes,Year=Dropdown1.Selected.Value) and "Value" = QuoteNumber

 

 

 

 

Lon
Level: Powered On

Re: Cascading Dropdown - 3 lists - IT WON'T WORK

Thanks for the Answer!

 

I tested your solution but when i filter like this, it says that there are diferent data-types. On the left site its number (Firma.id) and on the right its text(DropDown1.selectec.value).

So it doesnt work.

JRaasumaa
Level 10

Re: Cascading Dropdown - 3 lists - IT WON'T WORK


@Lon wrote:

Thanks for the Answer!

 

I tested your solution but when i filter like this, it says that there are diferent data-types. On the left site its number (Firma.id) and on the right its text(DropDown1.selectec.value).

So it doesnt work.


 

You can always force the text side to be a numeric value by enclosing it with Value()

Lon
Level: Powered On

Re: Cascading Dropdown - 3 lists - IT WON'T WORK

Now i got this message: "Warning on delegation. The "filter" part of this formula may not work properly for large datasets. The data source may not be able to process the formula and may return an incomplete dataset. Your application may not return correct results or may not behave properly if the dataset is incomplete."

 

see attechments.

 

 

When i click my seccond dropdown, i get no data.
JRaasumaa
Level 10

Re: Cascading Dropdown - 3 lists - IT WON'T WORK


@Lon wrote:

Now i got this message: "Warning on delegation. The "filter" part of this formula may not work properly for large datasets. The data source may not be able to process the formula and may return an incomplete dataset. Your application may not return correct results or may not behave properly if the dataset is incomplete."

 

see attechments.

 

 

When i click my seccond dropdown, i get no data.

 

You may have to change your dropdown Item property to point to a Collection instead of using the data source directly.

 

You can setup the dropdown "On Change" property to ClearCollect distinct values from your data source using the Filter you show to gather just the data required for your dropdown list.

 

See my post linked below for an example of how to load distinct values into a collection, in this case I also collect the word "All" to allow for seeing all values. 

 

https://powerusers.microsoft.com/t5/General-Discussion/Dropdown-list-with-distinct-values-and-option...

 

Hopefully this is a good starting point for you.

Lon
Level: Powered On

Re: Cascading Dropdown - 3 lists - IT WON'T WORK

Thanks JRaasumaa!

 

i tried to add the two data "Ansprechpartner" and "Kunde" in a collection, so i have all the data i need in one collection. Then i want to filter and patch the data to the list back.

 

But it doesnt work.

This is really frustrating...

Lon
Level: Powered On

Re: Cascading Dropdown - 3 lists - IT WON'T WORK

Finally i found a sollution!

Everytime i tried the Filter with the default datacard which is created by adding the datafield from sharepoint dataset. But with this datacard the filter is not working.

 

Anyway, i have added a dropdown field independently from the dataset and added following line in "Text-properties":

Filter(Ansprechpartner; Firma.Value = DataCardValueCustomer.Selected.Value)

The Filter works now. But it is said that with large datasets the filtering may not work properly.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,878)