Filter Dropdown Choice Based on Status Field

[jarodpearcy]

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.

[yashag2255 · Accepted solution]

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!

[v-xida-msft · Accepted solution]

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:

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,

[jarodpearcy]

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]

@yashag2255 

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

Thanks for your effort.