Formula to Filter Out "Completed" Items in Gallery

(0) Share

Report

Posted on by CU10061933-0

I have an app with a gallery where I want to show only items that meet the following criteria:

- Name of logged in user is = to value in "Created By" column

-Name of logged in user is = to value in "Assigned To" column

and

- Value of "EWO Status" column is = to anything other than "Completed"

I got the first two filters to work, but when I try to add the third filter, it does not work (completed items are still showing.)

Here is my current formula:

Filter('EWO System','EWO Status'.Value <> "Completed" And 'Assigned To'.Email=varUserEmail Or 'Created By'.Email=varUserEmail)

Here is what this formula is currently getting me:

What edits do I need to make to the formula to get this to work? (EWO Status is a choice column btw)

Categories:

Using Apps Using Formulas

All responses (4)

Answers (2)

WarrenBelz 147,809 Most Valuable Professional on at

Like (0)

Report

Formula to Filter Out "Completed" Items in Gallery

@CU10061933-0,

Yes, but assuming you are using SharePoint (you did not state your data source) @MooseBot88's example is not Delegable and will mislead future readers of this post. Mine is complex only for that reason.
CU10061933-0 10 on at

Like (0)

Report

Formula to Filter Out "Completed" Items in Gallery

Thank you! @WarrenBelz's formula worked as well, but I went with @MooseBot88 because it was simpler.
Verified answer

WarrenBelz 147,809 Most Valuable Professional on at

Like (0)

Report
Formula to Filter Out "Completed" Items in Gallery

Hi @CU10061933-0,

You have two issues here - one is the requirement for And/Or seperation, but the other potential problem is that <> (not equals) is not Delegable. I will give two possibilities - if the records created by or assigning to the user will never be more than 500 (you can increase this to 2,000)

With( { _Data: Filter( 'EWO System', 'Assigned To'.Email = varUserEmail Or 'Created By'.Email = varUserEmail ) }, Filter( _Data, 'EWO Status'.Value <> "Completed" ) )

However you can make the whole filter Delegable by selecting all the other values other than "Completed" - example below

Filter( 'EWO System', 'Assigned To'.Email = varUserEmail Or 'Created By'.Email = varUserEmail, 'EWO Status'.Value = "In Progress" Or 'EWO Status'.Value = "Submitted" Or 'EWO Status'.Value = "Pending" )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee
Verified answer

MooseBot88 167 on at

Like (0)

Report
Formula to Filter Out "Completed" Items in Gallery

Try this....

Filter('EWO System','EWO Status'.Value <> "Completed" And ('Assigned To'.Email=varUserEmail Or 'Created By'.Email=varUserEmail))

I think it's where your have an And and a Or statement without separating them.

--------------------------------------------------------------------------------

If this helps, let me know. If you want to buymeacoffee.com/moosebot88