Answered

How to exclude item in gallery depending on sharepoint value?

(0) Share

Report

Posted on by LanesMan

Hello,

I have a gallery which can be filtered depending on two drop down selections, a search field and I can sort the list alphabetically.

Here is the search/filter/sort bar:

For this i have written the following code (Items in the gallery):

SortByColumns(
 Filter(
 'DS PAS Dummy Shipments';
 IsBlank(Dropdown4_1.Selected.Value) || IsEmpty(Dropdown4_1.Selected.Value) || Land.Value = Dropdown4_1.Selected.Value;
 IsBlank(Dropdown4.Selected.Value) || IsEmpty(Dropdown4.Selected.Value) || Status.Value = Dropdown4.Selected.Value;
 !IsBlank(Dropdown4.Selected.Value) || !IsEmpty(Dropdown4.Selected.Value) || Status.Value = Dropdown4.Selected.Value;
 
 
 StartsWith(
 'DS Number';
 Searchbox.Text
 ) || StartsWith(
 Supplier;
 Searchbox.Text
 ) || StartsWith(
 'Center code';
 Searchbox.Text
 )
 );
 varSortColumn;
 varSortDirection
)

On the sharepoint there is a column for "status" and I would like the gallery generally to not list "closed" items.

I did not manage to do so, even with some reserch in the internet.

Any ideas?

Thank you in advance

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @LanesMan you have a delegable and non delegable option.
You could add Status.Value <> "closed" as the first parameter in your filter statement but this will not be delegable. Only use this if you don't have > 500 (or > 2000 depending on app settings) items in the SP list:

SortByColumns( Filter( 'DS PAS Dummy Shipments'; Status.Value <> "closed"; IsBlank(Dropdown4_1.Selected.Value) || IsEmpty(Dropdown4_1.Selected.Value) || Land.Value = Dropdown4_1.Selected.Value; IsBlank(Dropdown4.Selected.Value) || IsEmpty(Dropdown4.Selected.Value) || Status.Value = Dropdown4.Selected.Value; !IsBlank(Dropdown4.Selected.Value) || !IsEmpty(Dropdown4.Selected.Value) || Status.Value = Dropdown4.Selected.Value; StartsWith( 'DS Number'; Searchbox.Text ) || StartsWith( Supplier; Searchbox.Text ) || StartsWith( 'Center code'; Searchbox.Text ) ); varSortColumn; varSortDirection )

If you have a larger list and need the query to be delegable, you will have to list out the other status options and use (Status.Value = "option 1" || Status.Value = "Option 2"...) and exclude the "closed" option:

SortByColumns( Filter( 'DS PAS Dummy Shipments'; (Status.Value = "Option 1" || Status.Value = "Option 2"); IsBlank(Dropdown4_1.Selected.Value) || IsEmpty(Dropdown4_1.Selected.Value) || Land.Value = Dropdown4_1.Selected.Value; IsBlank(Dropdown4.Selected.Value) || IsEmpty(Dropdown4.Selected.Value) || Status.Value = Dropdown4.Selected.Value; !IsBlank(Dropdown4.Selected.Value) || !IsEmpty(Dropdown4.Selected.Value) || Status.Value = Dropdown4.Selected.Value; StartsWith( 'DS Number'; Searchbox.Text ) || StartsWith( Supplier; Searchbox.Text ) || StartsWith( 'Center code'; Searchbox.Text ) ); varSortColumn; varSortDirection )

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

Like
a
(0)

Report
Copy link

Link copied!

Hi @LaurensM

I've used your second suggestion and it works perfectly. Thank you very much for your support, you helped me out a lot!

Best regards