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

Cascading dropdown using lookup values of two lists in Sharepoint

Hey there. I really would appreciate if someone could help me with an issue I'm not being able to handle in Powerapps. 

 

What I want is to select doctors based on the insurance company the user selected previsouly. 

 

I have two lists in SharePoint: 

 

Insurance, with a column [NameInsuranceCo] and some other fields like id, creation date, etc. but I'm only interested in the name of the company here).

 

Doctors, with two important columns for the case: [NameDoctor] and [NameInsuranceCo]. This second field of the list is a lookup field related to the first list and also, multiple values are allowed, as a doctor can work for several insurance companies. 

 

I created in PowerApps a {dropInsuranceCo} so the user can select the insurance company, but now I can't filter the list of doctors based on this first drop in order to show only the doctors who work for the selected company. I found several posts related to cascading filter, but none of the proposed solution works for me. (Maybe of the lookup values?) 

 

Any help will be much appreciated. 

6 REPLIES 6
Super User
Super User

Re: Cascading dropdown using lookup values of two lists in Sharepoint

Hi

 

In your case, the 1st dropdown shall be

- Dropdown1.Items = Distinct(Insurance, NameInsuranceCo)

 

ONE DOCTOR / COMPANY or COMPANIES

In your 2nd part, it should be a LABEL, as you are filtering for a RECORD

- Label = LookUp(Doctors, NameInsuranceCo = Dropdown1.Selected.Value).NameDoctor

 

Hope this helps.

hpkeong
Ancherl
Level: Powered On

Re: Cascading dropdown using lookup values of two lists in Sharepoint

Hi,@ec_correa

Accroding to your question, I design a demo to test it. I want to know if you have set Lookup data type to the [NameInsuranceCo] column of Doctors list. If you do this as above, when you set the Itms property of dropdown1 to the follow formula:

Distinct(Insurance,NameInsuranceCo)

it works well, but when you set items property of dropdown2 to the follow formula:

LookUp(Doctors,NameInsuranceCo=Dropdown1.Selected.Value).NameDoctor

something error occurs. Because the LookUp function finds the first record in a table that satisfies a formula, the above formula returns a textvalue, but the items property of dropdown2 requires table value, so the error occurs. Besides, if there are many doctors in one [NameInsuranceCo] column, the lookup function don't take effect, you need use Filter function instead of it. the correct formula of itmes property(Dropdown2) as follows:

Filter(Doctors,NameInsuranceCo=Dropdown1.Selected.Value).NameDoctor

2.PNG

the screenshot of correct display as above

 

Hope this hepls.

 

Ancherl

 

ec_correa
Level: Powered On

Re: Cascading dropdown using lookup values of two lists in Sharepoint

Hi @Ancherl. Thank you for your reply.

 

I think you’re right with your proposal, but I really can’t reproduce it. I thought it could be because I made some name changes of columns or whatever else, so, I created another lists, but still not working. Below you can find all the details of the model.

 

This is the definition of the first list for the insurance company.img1.png

 

And these are my data.

img2.png

This is the definition of the second list for the doctors.

img3.png

(“Consulta” is the name in Portuguese/Spanish for the lookup type of data in SharePoint.

 

And these are the data.

img4.png

 

As you can see, a doctor can work for one or more insurance companies.

 

The expected behaviour for the app would be:

  • When SUS is selected, all three doctors are listed.
  • When PMC is selected, Dr. Mary and Dr. Peter are listed.
  • Whan PMI is selected, only Dr. Mary is listed in the second dropdown.

 

So, coming back to PowerApps, I built the two dropdowns. First one is not a problem, but in the second one, I just can’t make it work. Please check the image below.

img5.png

 

As you can see, PowerApps is presenting an error in the formula. Thinking about the particular behaviour of lookup fields, I thought that I needed a .Value after InsuranceCompany. I did this modification, was able to surpass the error, but I still have a warning message and no data is show.

img6.png

 

I’m starting thinking this is a limitation because of the multiple values in the lookup field…

 

Thank you very much for your help!

 

Ancherl
Level: Powered On

Re: Cascading dropdown using lookup values of two lists in Sharepoint

Hi,@ec_correa

According to your statement, I have found a question of your design which I have met. The definition of the second list for the doctors of yours as below:1.PNG

 

 

 

Please pay attention to the data type of [InsuranceCompany] field, it is the Lookup data type. But the data type of [Titulo] field in InsuranceCompany is Text. The data type of [Titulo] field in InsuranceCompany as beloiw:2.PNG

 

 

 

The data type of the two fields([InsuranceCompany] field  and [Titulo] field) doesn't match. If you want to acheive your expected result, you should change the data type of [InsuranceCompany] field in Doctors list into  the same data type of [Titulo] field in Insurance Company.  The right formula as follows:

Filter(Doctors,InsuranceCompany=Dropdown1.Selected.Value).Title

Hope this helps.

 

Best regards,

Ancherl

 

 

ikatz
Level: Powered On

Re: Cascading dropdown using lookup values of two lists in Sharepoint

I think the only way to do that is without multiselect choice of field. You can filter in cascade if you change field to unique selection.

Basicly because the Sharepoint conector already only support single values in lookup fields.

https://powerapps.microsoft.com/en-us/tutorials/connection-sharepoint-online/

 

 

Functions

 

 

DropDown1.Items =  Distinct(Insurance;Title)
DropDown2.Items =  Filter(Doctors;InsuranceCompany.Value = Dropdown1.Selected.Value)

 

capture20170810141137569.pngcapture20170810141203807.png

 

 

 

 

 

 

 

 

 

.

 

 

 

Untitled Project.gif

SPRubi
Level: Powered On

Re: Cascading dropdown using lookup values of two lists in Sharepoint

had a similar requirement with 3 dropdowns - please find my solution here: http://4us2use.at/?p=235

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

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Top Community Contributors for September  2019

Top Community Contributors for September 2019 🎉🎉

Let's thank our top community contributors

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

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