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?

[WesleyE]

@WarrenBelz Where exactly? I copied what you posted, but I don't see where it's missing.

[WarrenBelz]

@WesleyE ,

My code was missing a comma (it happens when you free-type in Notepad) - I would have thought it would be easy to find.

[WesleyE]

@WarrenBelz I see you updated your previous post with that missing comma, but I still have the same error. Is there another one missing somewhere? This is what I currently have:

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
   )
)

[WarrenBelz]

@WesleyE ,

Take a moment to look at the bracket alignment and numbers. I was giving you more a structure rather than something to copy and paste.

[WesleyE]

@WarrenBelz Thanks a million! Your code seems to work and has all I need. I couldn't figure this out myself so thanks so much!

[WesleyE]

Actually @WarrenBelz I found a little bug during my testing. I thought I had tested all scenarios but I might have been a little too excited, hehe.

If you don't own any DLs, it'll display ALL DLs and the search functionality works, so this is awesome!

However, if you own a DL, it'll display that DL, but the search functionality no longer works.. You can only search for the items you own, but no longer search for ALL other DLs you don't own.

What I need is; by default it should display the DL that person owns, which is what it currently does using the OwnerEmail = User().Email, but next to that I need to be able to use the search bar and search for any other items in my datasource.

I think currently the search is stuck because of this "&&". It will still search the datasource, but only look for items where OwnerEmail = User().Email? What I need is - when using the search bar - ignore this piece: 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
      )

[WarrenBelz]

@WesleyE ,

I must admit your requirements are a little confusing to me. You can put || (or) instead of && (and) at that spot, but then you would need to get rid of the Len() condition on the search box in the bottom part and remove "" from the Default of the box. The other option is to go the "very long" way and stipulate and conditions for each scenario (you might have a go at this if the above does not work)

[WesleyE]

@WarrenBelz I think my requirements are "simple", it's just that it doesn't seem to easily code in PowerApps. Or my explanation kinda sucks and it sounds more complicated than it actually is? If so, I am sorry for that.

In PowerShell I can make this work in a minute using 3 if statements, but in PowerApps it unfortunately doesn't seem to work this way.

This is what I want:

• IF OwnerEmail matches User().Email), then display those items. The person using the app should also be able to search for anything in my datasource using the search bar. If the search bar is empty, it should default back to this view: OwnerEmail matches User().Email
• IF OwnerEmail doesn't match User().Email), then display ALL items in my datasource. If the search bar is being used, then display those items. If search bar is empty, default back to all items in my datasource.

Unfortunately, I can't make it work using ||, removing the LEN and remove the "" from the default of the search bar.

My previous code was working and is pretty simple:

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

But adding another view (when "OwnerEmail doesn't match User().Email" then display all items) seems difficult/complex.

Basically there are two scenarios of people using my app. Depending on the scenario it should give them a different view:

• For people that are owner of an item in my datasource (OwnerEmail matches User().Email) - it should display the items they own but they should also be able to search for any other item using the search bar. If the search bar is empty it should default back to the items they own.
• For people that are NOT owner of an item (OwnerEmail doesn't match User().Email) - it should display ALL items in my datasource. They should also be able to search for any other item using the search bar. If the search bar is empty it should default back to ALL items.

[WesleyE · Accepted solution]

I think I have fixed it using this code:

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

[WarrenBelz]

@WesleyE ,

Yep - pretty close to the direction I was heading