cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ecb21
Level: Power Up

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
Super User
Super User

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
ecb21
Level: Power Up

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).

Super User
Super User

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
ecb21
Level: Power Up

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.

 

Super User
Super User

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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 418 members 5,069 guests
Please welcome our newest community members: