If statement gallery items

[WesleyE]

Hi all,

Hope you can help on the following;

My PowerApp can be used by everyone in our organization and it will display them all Distribution Lists we have out there.

The Gallery Items is currently set to:

If(IsBlank(SearchInput.Text),
Sort(Filter(DATASOURCE, OwnerEmail=User().Email), DisplayName, SortOrder.Ascending),
Sort(Filter(DATASOURCE, StartsWith(DisplayName,SearchInput.Text) || StartsWith(Manager,SearchInput.Text)), DisplayName, SortOrder.Ascending)
)

This is working fine and the way it currently work is:

• If the searchbar in my PowerApp is empty, it'll search for all Distribution Lists owned by that person using this piece of code:

Sort(Filter(DATASOURCE, OwnerEmail=User().Email), DisplayName, SortOrder.Ascending)​

• If the searchbar is not empty, it'll search my datasource for whatever was entered in the searchbar, using this piece of code:

Sort(Filter(DATASOURCE, StartsWith(DisplayName,SearchInput.Text) || StartsWith(Manager,SearchInput.Text)), DisplayName, SortOrder.Ascending)​

Some users don't own any Distribution Lists. What I'd like to add to my if statement is the following:

If column "OwnerEmail", in my datasource is empty and the searchbar is empty as well then display all Items in my datasource (so basically remove all the filtering I use today).

The end goal would be 3 different filterings for my Gallery Items:

1. If column "OwnerEmail" in my datasource is not empty, but the searchbar is empty, do this: 
   Sort(Filter(DATASOURCE, OwnerEmail=User().Email), DisplayName, SortOrder.Ascending)
2. If the Searchbar in my Powerapp is not empty, do this: 
   Sort(Filter(DATASOURCE, StartsWith(DisplayName,SearchInput.Text) || StartsWith(Manager,SearchInput.Text)), DisplayName, SortOrder.Ascending)
3. (NEW to add) If OwnerEmail in my datasource is empty and the searchbar is empty, do this: Display ALL items of my datasource. Example code: 
   Sort(DATASOURCE, DisplayName, SortOrder.Ascending)

How do I make this work?

[WarrenBelz]

HI @WesleyE ,

Set the Default of SearchInput to "" (empty string) and try this

Sort(
   Filter(
      DATASOURCE,
      (
         OwnerEmail = Blank() ||
         OwnerEmail = User().Email
      ) &&
      (
         StartsWith(
            DisplayName,
            SearchInput.Text
         ) || 
         StartsWith(
            Manager,
            SearchInput.Text
         )
      )
   ), 
   DisplayName
)

Please click Accept as solution 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 Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[WesleyE]

Thanks for your quick help, but I'm still seeing some issues:

Using your code, now by default the app is displaying all Distribution Lists.

I do own a Distribution List, so the app should only show the one Distribution List I own by default, unless I start using the search bar.

For people not owning any Distribution Lists, the code is working fine as it is showing them all Distribution Lists.

The searchbar has an issue too. I can search for other Distribution Lists, but I can no longer search for other managers, unless I put my own name in the search bar.

[WarrenBelz]

@WesleyE ,

There is no reason I can see why the search box should not work, however I am struggling a bit with the logic you want on OwnerMail. Do you want to display all Items if there is nothing at all in the list matching the user's email, but if at least one item exists matching, display only those items. If so, this is going to get a bit complex.

[WesleyE]

@WarrenBelz 

I figured it was going to be a bit complex, that's why I am here asking the experts, haha!

I think you understood what I am trying to achieve with the user's matching items, etc.

I hope this makes sense what I'd like to show by default to the users using the app:

1. If column OwnerEmail in my datasource is NOT empty (meaning we have a match for at least one item) only display the item(s) of this person. I can do that today using this code: Sort(Filter(DATASOURCE, OwnerEmail=User().Email), DisplayName)
2. (THIS IS THE NEW THING TO ADD) If column OwnerEmail in my datasource is empty (meaning we don't have a match), display ALL items of my DATASOURCE.
3. If the searchbar is being used, ignore 1 & 2 and display what's being searched for, I can do that today using this code: Sort(Filter(DATASOURCE, StartsWith(DisplayName,SearchInput.Text) || StartsWith(Manager,SearchInput.Text)), DisplayName)

We can make this work if I am able to use 3 IF statements, I don't know if I can?

That would look like this:

IF OwnerEmail matches User().Email), then display those items.

IF OwnerEmail doesn't match User().Email), then display ALL items in my datasource.

IF SearchBar being used, search for those items in my datasource.

[WarrenBelz]

@WesleyE ,

Actually not too bad when I thought it through properly

With(
   {
      _Matches:
      Filter(
         DATASOURCE,
         OwnerEmail = User().Email
      )
   },
   Sort(
      Filter(
         DATASOURCE,
         (CountRows(_Matches) = 0 || OwnerEmail = User().Email) &&
         (
            StartsWith(
               DisplayName,
               SearchInput.Text
            ) || 
            StartsWith(
               Manager,
               SearchInput.Text
            )
         )
      ), 
      DisplayName
   )
)

Please click Accept as solution 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 Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[WesleyE]

I'm sorry @WarrenBelz, using this code it won't load any items. The search bar doesn't work too, but I think that is because it doesn't load any items at all initially.

On this line:  (CountRows(_Matches) = 0 || OwnerEmail = User().Email) && - it's saying "The filter part of this formula might not work correctly on large data sets"

The SharePoint List that I'm using as a datasource contains about 4800 items.

Screenshot attached.

[WarrenBelz · Accepted solution]

@WesleyE ,

I assume you set the Default of the search bar to "" as I noted, The below is the "long way" and not very elegant, but should work.

With(
   {
      _Matches:
      Filter(
         DATASOURCE,
         OwnerEmail = User().Email
      )
   },
   If(
      CountRows(_Matches) > 0,
      Sort(
         Filter(
            DATASOURCE,
            OwnerEmail = User().Email &&
            (
               Len(SearchInput.Text) = 0 ||
               (
                  StartsWith(
                     DisplayName,
                     SearchInput.Text
                  ) || 
                  StartsWith(
                     Manager,
                     SearchInput.Text
                  )
               )
            )
         ), 
         DisplayName
      ),
      Sort(
         Filter(
            DATASOURCE,
            (
               Len(SearchInput.Text) = 0 ||
               (
                  StartsWith(
                     DisplayName,
                     SearchInput.Text
                  ) || 
                  StartsWith(
                     Manager,
                     SearchInput.Text
                  )
               )
            )
         ), 
         DisplayName
      )
   )
)

Please click Accept as solution 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 Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[WesleyE]

@WarrenBelz yep that SearchInput text field is set to "".

Using this code I get an error saying the IF statement has some invalid arguments.

[WarrenBelz]

@WesleyE ,

You are missing a comma