cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NSimpraga
Helper I
Helper I

Searching by Department field substring

Hi all,

my company has a specific hierarchy layout so our Department field has the following layout :

Service AreaName (SquadName, SquadronName) 

 

So basically every employee has a service area, a squad and squadron assigned.

For example - Employee A is in service area1 and squad1 squadron1. Employee B can be in the same squad1 and squadron1 but be in a different service area. Every squadron has one or more squads.

 

What my goal is, is to develop an app in which you can enter a Squad, Squadron or Service Area name and get all the employees which belong to the respective squad/squadron/SA.

 

I am not sure how to proceed on this, I think I need to do a substring search on the Department field or a search with wildcards but I don't know if and how that is supported. Any advice and pointing in the right direction is appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @NSimpraga :

Do you want to get a list of employees by entering their (squad/squadron/service area)?

Can you tell me :

What Is the data type of " Department " field? Text? Or?

I assume the date type of " Department " is Text.

Ive made a test for your reference:

Case1: Filter the records by a keyword.

Step1:add a TextInput(TextInput) control and clear its default property:

Step2:add a Gallery control and set its items property to:

 

Search(Test1, TextInput.Text," Department")             /*Test1 is my datasource*/

 

3.PNG

In this case, users can search for records by entering a keyword in the textinput control. ( As the picture shows)

Case2: Filter the records with squad,squadron,service area respectively

Step1:add three TextInput(Area,Squad,Squadron) controls and clear their default property:

Step2:add a Gallery control and set its items property to:

 

 

Filter(
    Test1,                                                      /*Test1 is my datasource*/
    Area.Text in Match(
        Department,
        "\S*\("
    ).FullMatch,
    Squad.Text in Match(
        Department,
        "\S*\,"
    ).FullMatch,
    Squadron.Text in Match(
        Department,
        "\S*\)"
    ).FullMatch
)

 

 

2.PNG3.PNG

In this case, users can match keywords in a specified position(area/squad/squadron).( As the picture shows)

 

 

 

 

Beat Regards,

Bof

View solution in original post

9 REPLIES 9
WarrenBelz
Super User III
Super User III

Hi @NSimpraga ,

You can do three filters on the one gallery and have them all filter. The example below uses StartsWith() as it is Delegable and also allows the user to see the whole gallery if nothing is entered into any of the boxes and these all have the Default of "" (empty string).

So using Department as the list name, ServiceAreaName, SquadName and SquadronName as the field names and AreaSearch, SquadSearch and SquadronSearch as the search boxes, the Items property of the gallery would be something like

Filter(
   Department,
   StartsWith(
      ServiceAreaName,
      AreaSearch.Text
   ),
   StartsWith(
      SquadName,
      SquadSearch.Text
   ),
   StartsWith(
      SquadronName,
      SquadronSearch.Text
   )
)

 

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.

v-bofeng-msft
Community Support
Community Support

Hi @NSimpraga :

Do you want to get a list of employees by entering their (squad/squadron/service area)?

Can you tell me :

What Is the data type of " Department " field? Text? Or?

I assume the date type of " Department " is Text.

Ive made a test for your reference:

Case1: Filter the records by a keyword.

Step1:add a TextInput(TextInput) control and clear its default property:

Step2:add a Gallery control and set its items property to:

 

Search(Test1, TextInput.Text," Department")             /*Test1 is my datasource*/

 

3.PNG

In this case, users can search for records by entering a keyword in the textinput control. ( As the picture shows)

Case2: Filter the records with squad,squadron,service area respectively

Step1:add three TextInput(Area,Squad,Squadron) controls and clear their default property:

Step2:add a Gallery control and set its items property to:

 

 

Filter(
    Test1,                                                      /*Test1 is my datasource*/
    Area.Text in Match(
        Department,
        "\S*\("
    ).FullMatch,
    Squad.Text in Match(
        Department,
        "\S*\,"
    ).FullMatch,
    Squadron.Text in Match(
        Department,
        "\S*\)"
    ).FullMatch
)

 

 

2.PNG3.PNG

In this case, users can match keywords in a specified position(area/squad/squadron).( As the picture shows)

 

 

 

 

Beat Regards,

Bof

View solution in original post

Hi @NSimpraga ,

A couple of options - please tag whichever one best suits your model to continue the thread.

NSimpraga
Helper I
Helper I

Sorry for the late answer, I've been caught up in some other things.

Thank  you for the answers, but I forgot to say that all these users are in Azure AD (thought that didn't need mentioning), I don't really have them neatly in a table. 

I suppose the first step then would be to get them in a table, should I collect them into a collection on app start or?

Don't know if I can collect their departments this way, I've tried something like that but it didn't work.

 

Hi @NSimpraga :

Do you want to get a list of uers from Azure AD?

Can you tell me :

  • Are these users in one group?

I've made a test:

Case 1:One group

  1. Step1:add a connection(Azure AD)
  2. Step2:add a button and set it's OnSelect property to:

 

ClearCollect(Mycollection,AzureAD.GetGroupMembers("your group ID").value) 

 

Case 2:Integrate multiple groups

  1. Just try this code:

 

ClearCollect(Mycollection,AzureAD.GetGroupMembers("your group ID1").value);
Collect(Mycollection,AzureAD.GetGroupMembers("your group ID2").value);
Collect(Mycollection,AzureAD.GetGroupMembers("your group ID3").value);……

 

I think this link will help you a lot: 

Azure AD:https://docs.microsoft.com/en-us/connectors/azuread/

If you still have problems, chould you tell me more detials about them?

Best Regards,

Bof

Yes, all of the users the search is supposed to be done on are in Azure AD. Every one of those users has a Department field with the same template as I described it - Service Area (SquadName, SquadronName) 

 

The problem with collecting them through AzureAD.GetGroupMembers is that the Department field doesn't get fetched this way so I can't do filtering through Departments, I've already tried this way. Maybe there is a way to add the Department field too? 

Update - I managed to get the data I need in a table!

I used 'Office365Users.SearchUserV2({searchTerm:""}).value'  and selected DisplayName and Department as the fields for a table and now I have all my users with their respective Departments in a table on which I can do searches like you said.

 

The problem I am having is with the search functions you wrote. When using Search(TableName, TextInput.Text, "DepartmentColumn") function, I get a "Invalid argument type (Control). Expecting a Table value instead." error.

It's referring to the table I passed to the Search function with TableName. I don't understand why it's throwing an error because I am literally passing the table right into the function. 

I tried TableName.value but it doesn't exist. What am I missing here with this?

NSimpraga
Helper I
Helper I

Final update: got it working!

Problem with the error message I was getting was due to the fact that the table I was passing to the Search was kind of dynamically generated with the SearchUser function, it wasn't a proper data source to pass into the function.

 

How I solved it was using 'Collect(Collection, Office365Users.SearchUserV2({searchTerm:""}).value)' with OnStart to get a proper table stored in the Collection and then passed that Collection to the Search function, after which it started working as intented.

 

Thanks @WarrenBelz and @v-bofeng-msft for your time, you helped me to start researching in the right direction!

That’s great @NSimpraga ,

You have accepted your original post as the solution. You might consider accepting those that assisted you most (this can be more than one person)

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (12,472)