cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbjb247
Helper II
Helper II

Formula for delegable filter, show all items with several criteria, EXCEPT if...

Hi, 

 

First time using a Sharepoint List, and getting my head around the delegable queries.  

 

The records are all peoples names & addresses.  

 

The Gallery should:

  • Filter the addresses down to six postcodes/same as US zipcodes (this works), postcodes are stored in the datacard values
  • EXCEPT if the person has been blacklisted, there is a column with a simple 'Y' or 'N', updated on another screen with the patch function, "Y" in the column if they're blacklisted, blank cell or "N" if they're not blacklisted.

This is the gallery items code for part 1 (this works)

SortByColumns(Filter([@AppCandidateData],

StartsWith('Postcode', DataCardValue41_3.Text)

||StartsWith('Postcode', DataCardValue43_2.Text)

||StartsWith('Postcode', DataCardValue44_6.Text)

||StartsWith('Postcode', DataCardValue45_5.Text)

||StartsWith('Postcode', DataCardValue46_4.Text)

||StartsWith('Postcode', DataCardValue47_3.Text)),

"Post", If(SortDescending1, Descending, Ascending))

 

This is the change I've made below in black is to ask for it to only show people who aren't blacklisted in the gallery, but it seems to just show everyone from the six postcodes in the gallery, exactly the same as the green formula above.

SortByColumns(Filter([@AppCandidateData],

StartsWith('Postcode', DataCardValue41_3.Text)

||StartsWith('Postcode', DataCardValue43_2.Text)

||StartsWith('Postcode', DataCardValue44_6.Text)

||StartsWith('Postcode', DataCardValue45_5.Text)

||StartsWith('Postcode', DataCardValue46_4.Text)

||StartsWith('Postcode', DataCardValue47_3.Text) && Or(Blacklisted=" ",Blacklisted="N"),

"Post", If(SortDescending1, Descending, Ascending))

 

Any help would be ,much appreciated 🙂 thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Hi, I think I might have got this to work with your help!

 

I've had to add a comma and a parenthesis, also swap out the ISBLANK for ="" as it was giving me a delegation issue, but with the alterations, I've dropped it into the app and it seems to work so far.  Please could you let me know if what I've updated to looks below ok to you?  It's handling quite a lot of data so it's not easy to see quickly if everything is working as it should, but, so far so good I think(?)!

 

Thank you so much!

 

SortByColumns(
Filter(
Filter(
[@AppCandidateData],Or(
IsBlank(
Blacklisted=""
),Blacklisted="N"
)
),
Or(StartsWith('Postcode', DataCardValue41_3.Text),
StartsWith('Postcode', DataCardValue43_2.Text),
StartsWith('Postcode', DataCardValue44_6.Text),
StartsWith('Postcode', DataCardValue45_5.Text),
StartsWith('Postcode', DataCardValue46_4.Text),
StartsWith('Postcode', DataCardValue47_3.Text)
)),
"Post",
If(SortDescending1, Descending, Ascending))

View solution in original post

4 REPLIES 4
Drrickryp
Super User
Super User

Hi @jbjb247 , 

There may be a better way to do this but I would first filter the dataset to remove blanks and "N" responses to the Blacklisted column first and then filter the results by the post codes.

 

 

SortByColumns(
 Filter(
   Filter(
    [@AppCandidateData],Or(
                            IsBlank(
                                      Blacklisted
                                     ),Blacklisted="N"
                             )
          )
Or(StartsWith('Postcode', DataCardValue41_3.Text),
StartsWith('Postcode', DataCardValue43_2.Text),
StartsWith('Postcode', DataCardValue44_6.Text),
StartsWith('Postcode', DataCardValue45_5.Text),
StartsWith('Postcode', DataCardValue46_4.Text),
StartsWith('Postcode', DataCardValue47_3.Text)
     ),
       "Post",
       If(SortDescending1, Descending, Ascending))

 

 

This seems awkward to me but I think I have all the parentheses and commas correct. Hopefully it will work.  One quick question, do you think that after filtering by the post codes or by the blacklist, you will have more than 2k records?  If not, you could create a collection from the filtered list from one of the filters (post codes or Blacklist) and then filter that collection by the other one. 

Hi, I think I might have got this to work with your help!

 

I've had to add a comma and a parenthesis, also swap out the ISBLANK for ="" as it was giving me a delegation issue, but with the alterations, I've dropped it into the app and it seems to work so far.  Please could you let me know if what I've updated to looks below ok to you?  It's handling quite a lot of data so it's not easy to see quickly if everything is working as it should, but, so far so good I think(?)!

 

Thank you so much!

 

SortByColumns(
Filter(
Filter(
[@AppCandidateData],Or(
IsBlank(
Blacklisted=""
),Blacklisted="N"
)
),
Or(StartsWith('Postcode', DataCardValue41_3.Text),
StartsWith('Postcode', DataCardValue43_2.Text),
StartsWith('Postcode', DataCardValue44_6.Text),
StartsWith('Postcode', DataCardValue45_5.Text),
StartsWith('Postcode', DataCardValue46_4.Text),
StartsWith('Postcode', DataCardValue47_3.Text)
)),
"Post",
If(SortDescending1, Descending, Ascending))

View solution in original post

@jbjb247 

Looks good to me.418TGXcvGaL._AC_.jpg

 

Haha!!

 

Thank you!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,794)