Cascading dropdown using lookup values of two lists in Sharepoint

[ec_correa]

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. 

[hpkeong]

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.

[Ancherl]

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

the screenshot of correct display as above

Hope this hepls.

Ancherl

[ec_correa]

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.

And these are my data.

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

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

And these are the data.

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.

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.

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]

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:

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:

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]

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)

.

[SPRubi]

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