Answered

Filter dropdown control options

(0) Share

Report

Posted on by louwork

I'm building a Canvas app for users to complete weekly safety checks for pieces of equipment. There is a prescribed list of equipment which needs to be checked, which I have as a choice column in SharePoint, and a date column for the date of the check.

What I would like to do is have a dropdown control which shows pieces of equipment which are pending (i.e. there either isn't an entry on the list, or the last date of check for that piece of equipment is over a week), so that users can only select from equipment due to be checked. I've tried the following:

Filter( SafetyCheck, !IsBlank(Equipment) && ( IsBlank(DateChecked) || DateChecked<= Today() - 7 ) ).Equipment

However it does not return any options to the dropdown. I'm new to Power Apps so it's likely I'm missing something, or if there is a better way to do this I'm open to other suggestions!

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Rajkumar_M 3,747 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @louwork

Try this formula

Filter( SafetyCheck, !IsBlank(Equipment) && (IsBlank(DateChecked) || DateChecked <= Today() - 7) )

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
louwork 29 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your response, unfortunately it's still not working 😞

When I input some test data meeting the criteria of being older than 7 days into the SharePoint list it is returning blank choices into the dropdown control

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

Like (0)

Report
Copy link

Link copied!

If you get any delegation warning or error in your formula ?

Was this reply helpful? Yes No
louwork 29 on at

Like (0)

Report
Copy link

Link copied!

I have delegation warnings sorry, I really should have mentioned that!

The highlighted part the warning refers to is the &&

Was this reply helpful? Yes No
louwork 29 on at

Like (0)

Report
Copy link

Link copied!

Thanks Muhammad, it doesn't seem to recognise either instance of "Result", is there something I need to do there?

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (2)

Report
Copy link

Link copied!
Hi @louwork,

To avoid the delegation warnings we can take the following steps:

IsBlank -> use the delegable condition '= Blank()' instead

Today() - 7 -> store this within a With function to avoid non-delegable calculations within your Filter condition

Using !IsBlank() to check whether the choice is not empty is not delegable, we could use the following workarounds:

List all choice options with the equals operator (viable if you have a limited set of options)

Use nested filters to first filter with a delegable condition after which we apply the non-delegable filter on the pre-filtered dataset

We will use the Distinct() function to return all unique Equipment values and display them in the dropdown.

(1) List out the equipment options

With( //Store the value in a function scope variable {wTodayMinSeven: Today() - 7}, Distinct( Filter( SafetyCheck, (Equipment.Value = "Option1" || Equipment.Value = "Option2"), DateChecked = Blank() || DateChecked <= wTodayMinSeven ).Equipment, Equipment.Value ) )

(2) Using nested filters (partly delegable)

With( //Store the value in a function scope variable {wTodayMinSeven: Today() - 7}, Distinct( Filter( //First Filter with a delegable condition Filter( SafetyCheck, DateChecked = Blank() || DateChecked <= wTodayMinSeven ), //non-delegable filter on pre-filtered dataset !IsBlank(Equipment) ), Equipment.Value ) )

Option 2 will still give a delegation warning since it is only partly delegable - you do not have to worry about the warning. As long as the first filter returns less than your Data Row Limit (see app settings), this will method will work fine.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
louwork 29 on at

Like (1)

Report
Copy link

Link copied!

Thank you so much, option 1 has saved me! You are a star 🌟

Was this reply helpful? Yes No
aparnaa 95 on at

Like (0)

Report
Copy link

Link copied!

Thank you this helped me solve a similar requirement

Was this reply helpful? Yes No