Answered

Filter a choice list items based on a if condition on another column in the same list

(1) Share

Report

Posted on by kundup

Hi, I am new to power app.
I have a SharePoint list which two choice columns (one column name is "Product teams" (let's say choices are A, B, C, D) and another column name is "Archive?" (let's say choices are Yes and No).

Product Teams (Choice)	Archive? (Choice)
A	No
B	Yes
B	No
C	No

If my "Archive?" column choice is "Yes" at least once for a particular product team, then that corresponding product team item (ie B here) will fall off from "Product team" choice list. (ie, B will not be shown as a choice to the user in the user form).

What should be the formula and where should I put it?

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

Verified answer

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @kundup,

If both columns are choice columns (without multi selection), you could filter the list by Archive = Yes and then only display the Product Teams choices that are not in this filtered list. Adjust the code to your specific case where necessary (list name & column names):

With( { //Temp store all unique product teams with the archive value 'Yes' choiceValYes: Distinct( Filter( LISTNAME, Archive.Value = "Yes" ), 'Product Teams'.Value ) }, //Only display the choice options not contained in that filtered list Filter( Choices(LISTNAME.'Product Teams'), !(Value in choiceValYes) ) )

The code above could be used in the Items property of a dropdown.

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!
kundup 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey, where should I put this code? Or which property should I update in power app? Archive column is not visible to the user in the form but product team is a datacard.
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@kundup,

You will have to put this code in the Items property of the Product Teams' DataCardValue (the dropdown where you only want to see the 'No' product teams). You might have to unlock the datacard first by clicking on that particular card, navigating to the property panel on the right, 'advanced' and select unlock datacard.

I hope this helps!
kundup 25 on at

Like
a
(1)

Report
Copy link

Link copied!

This is awesome! Thank you so much.
kundup 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey @LaurensM , I am facing another issue:

I have this below formula in "Update" Property of 'Product Teams_Data Card' which allows me to enter the choices manually and if its not in the list, it will add to the dropdown list. But after applying the above code, that has stopped working- I am not able to enter the values manually.

If(
IsBlank(DataCardValue11.Selected),
{Value: DataCardValue11.SearchText},
DataCardValue11.Selected
)
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

I tested this on my end and I could still add new choice options by typing them out manually and submitting the form. Normally this functionality should still work, since the datasource for that dropdown is still the Choices(LISTNAME.'Product Team') as it was before - just filtered this time:

Are you receiving an error when trying to add a new choice type? Keep in mind, if Archive is 'Yes' by default. These options will not show in the list because we filter them out.
kundup 25 on at

Like
a
(0)

Report
Copy link

Link copied!

The default value of Archive is "No". Its giving me no option to type..I have the 'allow manual entry' check box ticked in SharePoint column for Product team. Its directly giving me the drop-down value to choose.
LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

This might be because the isSearchable property of the dropdown is set to false. Could you select the DataCardValue and double check the IsSearchable property? This should be set to true.
kundup 25 on at

Like
a
(0)

Report
Copy link

Link copied!

This works now. Thanks a lot.