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

Filter Dropdown Choice Based on Status Field

Hello,

 

This should be easy.  I have a SP list (Reimbursement Request) with a text column 'Request Identifier' and a text column 'Approval Status'. 

 

On another list (Reimbursement Item) I have a 'Request Identifier' column that is a lookup based on Reimbursement Request 'Request Identifier'.  In the form for Reimbursement Item the dropdown Items formula for 'Request Identifier' is Choices('Reimbursement Request'.'Request Identifier').  This is all well and good.

 

I want to filter the Choices('Reimbursement Request'.'Request Identifier') dropdown to only the Reimbursement Request'.'Request Identifier' items that match specific 'Reimbursement Request'.'Approval Status' values.  In other words I want the 'Reimbursement Item'.'Request Identifier' dropdown to list only the 'Reimbursement Request'.'Request Identifier' values that match 'Reimbursement Request'.'Approval Status' = "Not Submitted".

 

I've tried dozens of combinations of syntax and filter functions and with no success.  This should be one of the most straight forward formulas but these layers of abstraction are counter-intuitive.

 

Thanks in advance for any help.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Re: Filter Dropdown Choice Based on Status Field

Hey @jarodpearcy 

 

Can you try out the below expression:
Filter(Choices('Reimbursement Request'.'Request Identifier'),LookUp('Reimbursement Request','Request Identifier'= Value).'Approval Status'= "Not Submitted")
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Community Support
Community Support

Re: Filter Dropdown Choice Based on Status Field

Hi @jarodpearcy ,

Do you only want to list 'Request Identifier' values whose corresponding 'Approval Status' column = "Not Submitted" within the 'Request Identifier' ComboBox in your Edit form?

 

Firstly, I think there is something wrong with the 'Request Identifier' ComboxBox formula (Choices('Reimbursement Request'.'Request Identifier')) you provided, it should be Choices('Reimbursement Item'.'Request Identifier') rather than  Choices('Reimbursement Request'.'Request Identifier').

 

I have made a test on my side, please consider take a try with the following workaround:4.JPG

Set the Items property of the ComboBox within the Training Data card to following:

Filter(
Choices('20190529_Trainings'.Training),
Value in Filter('20190529_Courses', Status="Confirmed").Title
)

On your side, you should set the Items property of the ComboBox within the 'Request Identifier' Data card to following:

Filter(
        Choices('Reimbursement Item'.'Request Identifier'), 
        Value in Filter('Reimbursement Request', 'Approval Status' = "Not Submitted").'Request Identifier'
)

Note: The 'Request Identifier' represents the LookUp column in your 'Reimbursement Item' SP List.

 

Please take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Dual Super User III
Dual Super User III

Re: Filter Dropdown Choice Based on Status Field

Hey @jarodpearcy 

 

Can you try out the below expression:
Filter(Choices('Reimbursement Request'.'Request Identifier'),LookUp('Reimbursement Request','Request Identifier'= Value).'Approval Status'= "Not Submitted")
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Community Support
Community Support

Re: Filter Dropdown Choice Based on Status Field

Hi @jarodpearcy ,

Do you only want to list 'Request Identifier' values whose corresponding 'Approval Status' column = "Not Submitted" within the 'Request Identifier' ComboBox in your Edit form?

 

Firstly, I think there is something wrong with the 'Request Identifier' ComboxBox formula (Choices('Reimbursement Request'.'Request Identifier')) you provided, it should be Choices('Reimbursement Item'.'Request Identifier') rather than  Choices('Reimbursement Request'.'Request Identifier').

 

I have made a test on my side, please consider take a try with the following workaround:4.JPG

Set the Items property of the ComboBox within the Training Data card to following:

Filter(
Choices('20190529_Trainings'.Training),
Value in Filter('20190529_Courses', Status="Confirmed").Title
)

On your side, you should set the Items property of the ComboBox within the 'Request Identifier' Data card to following:

Filter(
        Choices('Reimbursement Item'.'Request Identifier'), 
        Value in Filter('Reimbursement Request', 'Approval Status' = "Not Submitted").'Request Identifier'
)

Note: The 'Request Identifier' represents the LookUp column in your 'Reimbursement Item' SP List.

 

Please take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helper I
Helper I

Re: Filter Dropdown Choice Based on Status Field

Kris (@v-xida-msft)

 

Thanks for your reply.  You were correct on 'Reimbursement Item' being the correct source for the Choices function.  That was a mistyping when writing the question.  

The formula you provided worked perfectly.  I have to wrap my head around this style of programming through layers of abstraction to try and understand why these formulas work.  It's so much more cryptic than language (VB/C#/java) coding.

Highlighted
Helper I
Helper I

Re: Filter Dropdown Choice Based on Status Field

@yashag2255 

Your formula also worked after correcting the Choice source to 'Reimbursement Item', which was incorrect in the original posting.

 

Thanks for your effort.

Helpful resources

Announcements
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

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,186)