cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syhrh
Dual Super User
Dual Super User

Filter/Group By from a different list and collection

Hi, I have this collection, NewSubordinates that is filtered according to the Manager's Name.

anonymous21_0-1659949912916.png

I need to check who's the approver(s) for each of the subordinates inside the collection. To check the subordinates' approver(s), it is in another list, the Employee list.

anonymous21_1-1659950056395.png

Name, Manager's Name, Approver Level 1 and Approver Level 1 are people columns.

I also need to check whether the subordinates have 1 or more approvers to create the approval for the specific subordinate.

//App.OnStart
With(
   {
      ApproverName:
      Filter(
         Employee,
         'Approver Level 1' And 'Approver Level 2'
      ),
      EmpName:
      Filter(
         NewSubordinates, Requester.DisplayName)
   },
   
   First(ForAll(
      ApproverName As aCol,
      Collect(
         Newcollect,
         Filter(
            EmpName,
            Requester.DisplayName = aCol.'Approver Level 1' && aCol.'Approver Level 2'
         )
      )
   )
));

I tried this formula but it doesn't work. How to get the who's the approver(s) for the subordinates?

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions
BCBuizer
Super User
Super User

Hi @syhrh ,

 

//App.OnStart
With(
   {
      wSub:
      AddColumns(
		  Filter(
			 Employee,
			 'Manager''s Name'.Email = User().Email
		  ),
		  "EmployeeDisplayName",
			Name.DisplayName
		),
      wLeave:
		AddColumns(
		  Sort(
			 Test, 
			 ID,
			 Descending
		  ),
		  "RequesterDisplayName",
		  Requester.DisplayName
   },
   
	ForAll(
      wSub,
      Collect(
         NewSubordinates,
			AddColumns(
			 Filter(
				wLeave,
				RequesterDisplayName = EmployeeDisplayName && Status.Value="Pending" 
			 ),
			"Approver Level 1",
			LookUp(wSub, EmployeeDisplayName = RequesterDisplayName, 'Approver Level 1')
			"Approver Level 2",
			LookUp(wSub, EmployeeDisplayName = RequesterDisplayName, 'Approver Level 2')
      )
   )
);

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

7 REPLIES 7
BCBuizer
Super User
Super User

HI @syhrh ,

 

Can you please share the code you use to create the NewSubordinates collection? I'm sure it will be easier to add a column / columns that have the approver data in there than it is to create two different collections.



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
syhrh
Dual Super User
Dual Super User

@BCBuizer 

//App.OnStart
With(
   {
      wSub:
      Filter(
         Employee,
         'Manager''s Name'.Email = User().Email
      ),
      wLeave:
      Sort(
         Test, 
         ID,
         Descending
      )
   },
   
   First(ForAll(
      wSub As aCol,
      Collect(
         NewSubordinates,
         Filter(
            wLeave,
            Requester.DisplayName = aCol.Name.DisplayName && Status.Value="Pending" 
         )
      )
   )
));
BCBuizer
Super User
Super User

Hi @syhrh ,

 

The below modified code will add two columns to the NewSubordinates collection: "Approver Level 1" and "Approver Level 2" for which the value is pulled from aCol (= wSub) = Employee)).

 

//App.OnStart
With(
   {
      wSub:
      Filter(
         Employee,
         'Manager''s Name'.Email = User().Email
      ),
      wLeave:
      Sort(
         Test, 
         ID,
         Descending
      )
   },
   
   First(ForAll(
      wSub As aCol,
      Collect(
         NewSubordinates,
			AddColumns(
			 Filter(
				wLeave,
				Requester.DisplayName = aCol.Name.DisplayName && Status.Value="Pending" 
			 ),
			"Approver Level 1",
			LookUp(aCol, Requester.DisplayName = aCol.Name.DisplayName, 'Approver Level 1')
			"Approver Level 2",
			LookUp(aCol, Requester.DisplayName = aCol.Name.DisplayName, 'Approver Level 2')
      )
   )
));


Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
syhrh
Dual Super User
Dual Super User

@BCBuizer 

I got this error

anonymous21_0-1660265072763.png

 

BCBuizer
Super User
Super User

Hi @syhrh ,

 

//App.OnStart
With(
   {
      wSub:
      AddColumns(
		  Filter(
			 Employee,
			 'Manager''s Name'.Email = User().Email
		  ),
		  "EmployeeDisplayName",
			Name.DisplayName
		),
      wLeave:
		AddColumns(
		  Sort(
			 Test, 
			 ID,
			 Descending
		  ),
		  "RequesterDisplayName",
		  Requester.DisplayName
   },
   
	ForAll(
      wSub,
      Collect(
         NewSubordinates,
			AddColumns(
			 Filter(
				wLeave,
				RequesterDisplayName = EmployeeDisplayName && Status.Value="Pending" 
			 ),
			"Approver Level 1",
			LookUp(wSub, EmployeeDisplayName = RequesterDisplayName, 'Approver Level 1')
			"Approver Level 2",
			LookUp(wSub, EmployeeDisplayName = RequesterDisplayName, 'Approver Level 2')
      )
   )
);

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
BCBuizer
Super User
Super User

Hi @syhrh ,

 

Did my post resolve your issue? If yes, please mark it as the solution for the benefit of the community. If not, please let me know what you are running into.



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
syhrh
Dual Super User
Dual Super User

@BCBuizer Sorry for the late reply. Yes, it does what I need. Thank you!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (2,508)