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
Microsoft
Microsoft

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
Microsoft
Microsoft

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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (69,845)