cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filtering a lookup using Dynamics CRM as a data source

Hi all,

 

I have a very basic question but am struggling to find an answer. I'm trying to build a very simple power app using Dynamics CRM as a data source. I dropped 2 lookup fields on the form (Country and State). I want to filter State based on the country selected. For example, if I select USA as a country I want to display only US states in the state lookup. If I select Canada as a country, I want to only display Canada provinces in the state lookup. State entity has a lookup field to Country entity of course. Doing this directly in Dynamics CRM is very easy (just 2-3 clicks) but I need to be able to do something like this in PowerApp.

 

Another question I have that may be related to this one is...if I have an optionset field and another optionset and I also want to display some values in the 2nd optionset depending on the value selected in the 1st one, how do I do that? For example, the 2nd optionset has 10 possible values in it but I only want to display 3 of them if Value A is selected on the 1st optionset and I only want to select 6 of them if Value B is selected on the 1st optionset. 

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filtering a lookup using Dynamics CRM as a data source

Ok I am going to share my example and hopefully that will help you extrapolate that to your use case. 

 

I have two dropdowns: 

1. I have 2 option sets: App Type that has 2 values - Model Drive App, Canvas App and Live (Power Apps) which also has 2 values - Yes and No

2. Dropdown11 with an Items property 

Choices('App Type')

2. Dropdown12 with an Items property

Switch(
    Dropdown11.Selected.Value,
    'App Type'.'Canvas App',
    Filter(
        Choices('Live (Power Apps)'),
        Text(Value) = "Yes"
    ),
    'App Type'.'Model Drive App',
    Filter(
        Choices('Live (Power Apps)'),
        Text(Value) = "No"
    )
)

So what this should do is when the 1st dropdown is set to Canvas App, the 2nd dropdown should only show Yes as an option. Similarly when the 1st dropdown is set to Model Drive App, the 2nd dropdown should only show No as an option. 

 

This is the behavior: 

ConditionalOptionSet.gif

 

Let me know if this helps. If needed, I can do a screen share with you, it will be way faster that way. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

 

View solution in original post

24 REPLIES 24
Highlighted
Super User
Super User

Re: Filtering a lookup using Dynamics CRM as a data source

What is the current property of the Country and State dropdowns? 

 

You should be able to achieve the filtering of States dropdown with something like this: 

Filter(States, Country = CountryDropdown.Selected) or 

Filter(States, Country.Name = CountryDropdown.Selected.Name)

 

A similar approach should work for your option set. Only diff will be you will have to specify all the mappings since there are no lookups within option sets. This can be achieved with something like this (this isn't exact and we might have to back and forth to solve this):

If(OptionSet1Dropdown.Selected = OptionSet1.ValueA, Filter(Choices(OptionSet2), Value = 1 || Value = 2 || Value = 3), If(OptionSet1Dropdown.Selected = OptionSet1.ValueB, Filter(Choices(OptionSet2), Value = 4 || Value = 5 | | Value = 6 || Value = 7 || Value = 8 || Value = 9 || Value = 10))

 

Let me know if this works.

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Helper I
Helper I

Re: Filtering a lookup using Dynamics CRM as a data source

Hi Hardit, thank you for your reply. 

 

I need to fetch GUID from the Country combo-box. It only displays the country name.

 

I have the formula to filter States combo-box as following:

 

Filter('World States', Country = CountryDataCard.Selected.'Country ID')

 

'World States' is the entity where all of the states live

Country is the display name of the field in 'World States' entity that references the GUID for the Country entity which is the data source for the Country combo-box.  When I hover over 'Country ID' part of the formula, it tells me that the data type is GUID which is what I want, but it highlights an error underneath of equal sign saying 'Invalid argument type

 

I will check out your solution for filtering optionset next.

 

Thank you!

Highlighted
Super User
Super User

Re: Filtering a lookup using Dynamics CRM as a data source

That's because the one on the left of the = sign is a record and on the right is a GUID. Without testing it out, there are 2 options for you: 

Filter('World States', Country.'Country ID' = CountryDataCard.Selected.'Country ID')

OR

Filter('World States', Country = Filter(Countries, 'Country ID' = CountryDataCard.Selected.'Country ID'))

 

Let me know if either of these work for you. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Helper I
Helper I

Re: Filtering a lookup using Dynamics CRM as a data source

Hi again Hardit, yes your first suggestion worked! For the life of me I cannot understand the left part of the equation. Why are we comparing the value in the country drop-down to the value in the Country entity? Very strange, but it worked 🙂

 

Thanks again and I'll report on my findings with the optionset dependencies shortly, but is there a way to use something like CASE statement to make it more readable?

Highlighted
Super User
Super User

Re: Filtering a lookup using Dynamics CRM as a data source

If this is the one that worked: 

Filter('World States', Country.'Country ID' = CountryDataCard.Selected.'Country ID')

then the explanation is that the left side of the equation is the Country lookup field in the World States entity. Since this is a lookup field, it is referencing a record from the Country entity. Since we want to compare the ID of the record to the ID of the Country selected in the dropdown, that's why he reference ID on both sides. 

 

This should also work: 

Filter('World States', Country = CountryDataCard.Selected)

 

For your option set, you can use the Switch function with something like this: 

Switch(
OptionSet1Dropdown.Selected, 
OptionSet1.ValueA, 
Filter(Choices(OptionSet2), Value = 1 || Value = 2 || Value = 3), 
OptionSet1.ValueB, 
Filter(Choices(OptionSet2), Value = 4 || Value = 5 | | Value = 6 || Value = 7 || Value = 8 || Value = 9 || Value = 10)
)

You can find more info on this function here

 

Let me know your results with the option set. 

 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

 

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

Highlighted
Helper I
Helper I

Re: Filtering a lookup using Dynamics CRM as a data source

So in essence you're referencing the field from the World States entity by qualifying Country entity and then the actual attribute from the World States entity? Interesting.

 

As for setting up optionset dependencies, I'm still trying to understand when to use the label or when to use the actual value. I'm attaching a screenshot with the errors I'm getting.

 

 
Highlighted
Super User
Super User

Re: Filtering a lookup using Dynamics CRM as a data source

Can you show me the error messages?

At the minimum, you will have to replace the data card references inside the Chouces function with the name of the 2nd option set.

Let me know what happens then.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com
Highlighted
Helper I
Helper I

Re: Filtering a lookup using Dynamics CRM as a data source

I cannot get a clean screenshot with errors because they overlap the formula. Name of the 2nd optionset on the form is ProjectProcurementDataCardValue and the name of the 1st optionset is OpportunityTypeDataCardValue. I need to filter ProjectProcurementDataCardValue  based on the selected value in OpportunityTypeDataCardValue. I have a feeling that I'm confused in which case to use the actual Display Name from the schema and in which case to use datacard name from the UI form. Here's the formula I have (you can see on the screenshot, everything is highlighted in red). 'New Opportunity' and 'Existing Opportunity' and the labels from the field bound to OpportunityTypeDataCardValue drop down and 100000000, 100000001, 100000002, 100000004 and the  values for the field bound to the ProjectProcurementDataCardValue  dropdown.

Switch(
OpportunityTypeDataCardValue.Selected,
'New Opportunity',
Filer(Choices(ProjectProcurementDataCardValue), Value = 100000000 || Value = 100000001),
'Existing Opportunity',
Filer(Choices(ProjectProcurementDataCardValue), Value = 100000002 || Value = 100000003)
)

 

Highlighted
Super User
Super User

Re: Filtering a lookup using Dynamics CRM as a data source

The name of the option sets is not the data card value names. Those are names of the controls themselves i.e. dropdowns in your case.

That's why the first reference in the Switch function references the first dropdown's name as we want to filter the 2nd dropdown based on the value of the first dropdown.

Subsequently in the Switch funtion (for every case), you need to use the name if the option set itself. If you dont know what that is, look at the Items property of the 2nd dropdown. Let me know what that is and we should be able to solve it.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

Helpful resources

Announcements
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,793)