cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Create a double cascading lookup in a form

I have been playing around with powerapps and my first app is a fact!

 

To further improve it I would like to expand the form.

 

The app is meant as an order management app

 

 

I have created a couple of screens, starting with a start screen.

  1. At the start screen the user chooses the supplier
    1. The following screens should be filtered down to items linked to this supplier only
  2. On the order screen,
    1. the user have the possibility to fill in a name as text(they can also order for someone else)
    2. Choose a date from a date field
    3. Choose a category from a drop down
      1. dropdown-list1: depending on the category chosen the dropdown has to be filtered
      2. dropdown-list2: depending on the category chosen, this field has to be shown or hidden/greyed out! Only some categories requires an entry from list2.

Is this possible to do, if so I would appreciate it if someone could push me into the right direction!

I have been trying to get the first cascading lookup done for the order screen but i keep getting errors.

 

The formula I used in dropdown-list1 is: Filter(lijst1; categorie=Category_dropdown.Selected.Value)

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Create a double cascading lookup in a form

Hi

 

If you have two dropdown, you can write in such a way

1. dd-list1 = Distinct(lijst1, categorie)

- Always use DISTINCT to generate a Non-duplicating table, and show the desired FIELD (here, Categorie)

2. dd-list2 = Distinct(Filter(secondtable, categorie = dd-list1.Selected.Result), categorie)

- Then, again, using DISTINCT to generate another Table for Dropdown, with categorie = first dropdown selected value, and show only those filtered's "categorie")

 

I hope this is what you are looking for.

Thanks and have a nice day.

hpkeong
Highlighted
Regular Visitor

Re: Create a double cascading lookup in a form

Thanks for your fast reply @hpkeong.

 

I tried it right away but in my dd-list2 I get the following error:

 

"Warning: The colums produced by this rule are all nested tables and/or records, however the property expects at least some colums of simple values (such as text, or numbers)"

 

The field "categorie" is a lookup in SharePoint to the "dd-list1" list, so the references are exact the same (text).

Highlighted
Community Champion
Community Champion

Re: Create a double cascading lookup in a form

Hi

 

So ddlist1 - OK?

for dd-list2, just to share

- Distinct(Table1, Field) are used to generate table for selected field in Dropdown.

Table1 can be just a pure table or filtered table from dd-list1.

 

Example: Table1

Category           Items

A1                     aa

A1                     bb

A2                     cc

A2                     dd

A3                     ee

A3                     ff

 

ddlist1 here can be = Dropdown1.Items = Distinct(Table1, Category) and you can see 3 items in the dropdown = A1, A2, A3

then for ddlist2, Dropdown2.Items = Filter(Table1, Category = Dropdown1.Selected.Results).Items

                                                   or = Distinct(Filter(Table1, Category = Dropdown1.Selected.Results), Items).

 

Maybe I quoted incorrectly of your table name, but i hope the above sample is clear.

 

TQ

hpkeong
Highlighted
Regular Visitor

Re: Create a double cascading lookup in a form

I followed your example but I keep getting errors. It seems like Powerapps is not recognizing the loopup column I have created in the list "lijst1".

 

"lijst1" is my list with the following columns : title, category_lijst1, price, where "category_lijst1" is a lookup to the category list in SP.

 

I have used the following formulas: 

  • dropdown1 = Distinct(lijst1, categorie_lijst1)
  • dropdown2 = Distinct(Filter(lijst1, categorie_lijst1 = dropdown1.Selected.Result),Title)

On dropdown1 i get the following error: The property cannot consume the type of Tables that this rule produces, due to incompatible schemas

dropdown2 gives the invalid argument type error.

 

If I change the formula for dropdown1 to Distinct(category, Title), it shows all categories of the category list, but still dropdown 2 won't work.

 

Highlighted
Community Champion
Community Champion

Re: Create a double cascading lookup in a form

Hi

 

You mentioned "where "category_lijst1" is a lookup to the category list in SP"

OK, looks like I misunderstood you.

 

In my years of practice, I have never used PowerApps CDS (and i have no SPL) and select "LookUp" option.

Reason:

- I would prefer to use my own FREE WAY to write formula and using

Distinct, Filter and LookUp to create all those formula.

 

In your case, MAYBE because you have predefined in SPL, hence, you have to ensure it follows to the rules.

 

In such case, I am sorry that I can;t help as I have no way yo know SPL scenario. 

Hope PG will help

 

TQ

hpkeong

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,265)