cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Davy1
Regular Visitor

Filter choices

Hi Experts,

 

I am creating an embedded canvas app on Opportunity for Opportunity products. The embedded can vas app lists all the opportunity products for the opportunity and i have added a edit form to edit the opportunity product as well.

We have a custom lookup for opportunity products called color. Its being pull from an entity called Parameter Value that has all the colors(blue, black etc), this entity also has a lookup to another entity called Parameter master that has the value called "color".

Why do we have a setup like this? Parameter value not only has colors(blue, black etc) but also sizes(10,11,12 etc), style(Smooth, matte, gloss) and other properties and the Parameter Master has values such as "Color" and "Height", "Length", "Finish" etc

 

The  datacard I have on the edit form has the formula as

Choices([@'Opportunity Products'].as_Color)

 

but it lists all everything in parameter value(blue, black, smooth, matte, gloss, 10, 11, 12). I am trying to figure out how to filter it only to show colors?

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

Hi @Davy1 

Do your data structure inside two tables like below?

v-albai-msft_0-1614321193028.png

v-albai-msft_1-1614321193031.png

If yes, I think it is hard to achieve your purpose, since data between your two tables do not have a relationship(or if you already have a relationship based on the lookup field, you can refer to below second solution).

As a workaround, if you don’t have so many choices, you can try to use static choices inside your formula. For example, using below formula for your combo box control:

Filter(Choices([@opportunities].as_Color), Name = "blue" || Name = "black")

v-albai-msft_2-1614321193035.png

Besides, if you still want to use the datasource in the formula, you can try to add another field in your Parameter Value table to distinguish these data. Like below(my BelongsTo field is a lookup column to

Parameter master table):

v-albai-msft_3-1614321193036.png

v-albai-msft_4-1614321193038.png

Then using this formula:  

 Filter('Parameter Values',BelongsTo.Name = "Color") 

v-albai-msft_5-1614321193040.png

v-albai-msft_6-1614321193041.png

Best Regards,

Allen

View solution in original post

4 REPLIES 4
Davy1
Regular Visitor

Can anyone help me please, is this something that cannot be achieved? like going 2 levels into a record to set filters

v-albai-msft
Community Support
Community Support

Hi @Davy1 

Do your data structure inside two tables like below?

v-albai-msft_0-1614321193028.png

v-albai-msft_1-1614321193031.png

If yes, I think it is hard to achieve your purpose, since data between your two tables do not have a relationship(or if you already have a relationship based on the lookup field, you can refer to below second solution).

As a workaround, if you don’t have so many choices, you can try to use static choices inside your formula. For example, using below formula for your combo box control:

Filter(Choices([@opportunities].as_Color), Name = "blue" || Name = "black")

v-albai-msft_2-1614321193035.png

Besides, if you still want to use the datasource in the formula, you can try to add another field in your Parameter Value table to distinguish these data. Like below(my BelongsTo field is a lookup column to

Parameter master table):

v-albai-msft_3-1614321193036.png

v-albai-msft_4-1614321193038.png

Then using this formula:  

 Filter('Parameter Values',BelongsTo.Name = "Color") 

v-albai-msft_5-1614321193040.png

v-albai-msft_6-1614321193041.png

Best Regards,

Allen

View solution in original post

Allen, 

 

Thank you very much for your efforts here in actually trying to replicate the scenario to help me understand the solution. It works, finally after almost 2 weeks of battling with it. I am also grateful that someone from Microsoft is trying to help me.

 

My scenario is the second one where the both the parameter value and the parameter master are related, so I am using your formula there, and it works really well.

 

Filter('Parameter Values',BelongsTo.Name = "Color")

 

The last problem I have on this is, I am loosing the search box for the dropdown when i use the above formula and I have more than 70 colors so scrolling though them becomes tedious. The search box came up when i had choices formula but there I am don't know how to filter like how we did here. Kindly help me on this please.

 

What i mean is, with the current setup when i click on the dropdown it shows this with a scroll bar and i have to scroll to find it.

Davy1_0-1614353267887.png

When i had the choices formula there was a search box when i clicked like below but i could not apply the filter there.

 

Davy1_1-1614353523923.png

 

 

I would like to have the search box when we click on the dropdown with the filters applied.

Thank you so much for your response and help in Advance.

Davy1
Regular Visitor

I just found out there is a property called Issearchable, i enabled it and it works now. Thanks again for all your help.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,072)