Answered

Filtering a lookup using Dynamics CRM as a data source

(0) Share

Report

Posted on by TonySuslovich

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (24)

Answers (1)

Sort by

PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
TonySuslovich 75 on at

Like
a
(0)

Report
Copy link

Link copied!
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!

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
TonySuslovich 75 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
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

If this is the one that worked:

Was this reply helpful? Yes No
TonySuslovich 75 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
TonySuslovich 75 on at

Like
a
(0)

Report
Copy link

Link copied!
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) )

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
TonySuslovich 75 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Hardit,

The display name of the 1st optionset that is bound to OpportunityTypeDataCardValue control on the form is 'Opportunity Type'
The display name of the 2nd optionset that is bound to ProjectProcurementDataCardValue control on the form is 'Project Procurement'
I need to filter ProjectProcurementDataCardValue dropdown control depending on the value selected in the OpportunityTypeDataCardValue dropdown control. Both attributes are defined as optionsets in the actual schema.

Was this reply helpful? Yes No