Answered

Drop down filter used choices

(0) Share

Report

Posted on by SimpleUser

Hello,

i'm creating a staff performance app, i have a drop down in a form which is for selecting/ displaying the performance year i.e. 24/25, 25/26 etc. This info is taken from a sharepoint choice column 'Performance Year'.

I need to be able to filter the selectable items in a new form based on if they are available. I.e. if there is a record for 24/25 then the drop down will filter that out and only show 25/26, 26/27 and so on.

Struggling to find a solution that works for me i have the below which works if i have an item selected in the gallery (in 'items' of drop down), however for a new item nothing will be selected so i need it to go and look at the SP list column and return available values. My SP list is called 'Staff Reviews'.

If(varFormMode=FormMode.New,Filter(Choices([@'Staff Reviews'].'Performance Year'),Not(Value in ThisItem.'Performance Year'.Value)),Choices([@'Staff Reviews'].'Performance Year'))

Help appreciated

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

Plz try this function formula!

If(
varFormMode = FormMode.New,
Filter(
Choices('Staff Reviews'.'Performance Year'),
!IsMatch(
ThisItem.'Performance Year'.Value,
Value
)
),
Choices('Staff Reviews'.'Performance Year')
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

Was this reply helpful? Yes No
SimpleUser 78 on at

Like (0)

Report
Copy link

Link copied!

Hi,

this gives me an error 'Regular expressions must be constant values' against Value in IsMatch format

Was this reply helpful? Yes No
Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!

You can try using the Text function instead of the Value function.

If(
varFormMode = FormMode.New,
Filter(
Choices('Staff Reviews'.'Performance Year'),
!IsMatch(
ThisItem.'Performance Year'.Value,
Text(Value)
)
),
Choices('Staff Reviews'.'Performance Year')
)

Thanks!

Was this reply helpful? Yes No
SimpleUser 78 on at

Like (0)

Report
Copy link

Link copied!

same error unfortunately, is this because there will be no item selected as it's a new record? so 'thisitem' is empty?

Was this reply helpful? Yes No
Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!

Try this

If( varFormMode = FormMode.New,
If( IsBlank(ThisItem),
{}, // return an empty list if ThisItem is empty
Filter( Choices('Staff Reviews'.'Performance Year'),
!IsMatch( ThisItem.'Performance Year'.Value, Text(Value) ) ) ),
Choices('Staff Reviews'.'Performance Year') )

Was this reply helpful? Yes No
SimpleUser 78 on at

Like (0)

Report
Copy link

Link copied!

hi, same error, with an additional one, 'the property on this control expects Table values, the rule produces void values which areincompatible' this is linked to the datacardvalue of the drop down im trying to put the list in

thank you for your help

Was this reply helpful? Yes No
Verified answer

SimpleUser 78 on at

Like (0)

Report
Copy link

Link copied!

I have worked it out, not sure if it's the most efficient way of doing it, i created two collections. One with the available choices from the choice column, one filtering the users used options.

I then created a third collection to filter out the ones that had been used with the below

ClearCollect(colMergedYears,ShowColumns(Filter(AddColumns(colYearOptions,"Used",If(IsBlank(LookUp(colUsedYears,'Performance Year'.Value=colYearOptions[@Value])),true,false)),Used=true),"Value"))

then colMergedYears in the item of the drop down.

Was this reply helpful? Yes No