cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jarodpearcy
Level: Powered On

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
Dual Super User
Dual Super User

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

Community Support Team
Community Support Team

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
Dual Super User
Dual Super User

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

Community Support Team
Community Support Team

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

jarodpearcy
Level: Powered On

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.

jarodpearcy
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,103)