cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeHaynes
Level: Powered On

Filter SharePoint List from a table

I am trying to make an Employee Database PowerApp with SharePoint holding the data.  It consists of several lists

 

  1. Employee Records - Information about the employee.  This contains a field called AccessID, which also happens to be the part before the @ symbol on our Active Directory UPNs.
  2. Employee Roles - Information about the job like name, responsibilities.  The main thing is that this contains the Permissions that are used within the Employee Database PowerApp.  There are four permissions,
    • View
    • Edit
    • Add
    • Delete
    The possible values for the permissions are:
    • None
    • Direct Subordinates
    • All Subordinates
    • All
  3. Employee Role Assignments - This ties the employee to a role and indicates the supervisor, start date and end date.  This allows people to fulfill multiple roles at the same time whcih was a requested requirement.  The Supervisor field in item number 3 is a Person field that is tied to our Active Directory.  

I am trying to tie together the AccessID field with the UPN in the Supervisor Claims field in order to create two collections:

  • Direct Subordinates
  • All Subordinates

This along with the permissions will allow me to control the functionality of the application.  The problem I am running into is Delegation.  Below is some code I use repeatedly to get a table of subordinates that belong to a table of Supervisors.

 

 

 

 

UpdateContext
({
    tmpSubordinates:

    Filter
    (
        'Employee Records',
        ID in 
      
            // Select the employee ID from the records
            ShowColumns
            (
                // Select subordinate records where the SupervisorUPN is in the tmpSupervisors table.
                Filter
                (
                    // Add the Unique Identifier (WSU email address with AccessID) to the list of columns
                    // Also add the EmployeeID to the list of columns
                    AddColumns
                    (
                        'Employee Role Assignments',
                        "SupervisorUPN",
                        // Ensure UPN is lowercase.
                        Lower
                        (
                            // Extract UPN from the Supervisor's Claims field.
                            Right
                            (
                                Supervisor.Claims, Len(Supervisor.Claims) - Find("@test.com",Supervisor.Claims)+6
                            )
                        ),
                        "EmployeeID",
                        Employee.Id
                    ),
                    And(SupervisorUPN in tmpSupervisors.Value, Or(IsBlank(ActualEndDate), ActualEndDate>Today()))
                ),
                "EmployeeID"
            )
    )
})

 

tmpSupervisors is a single column table containing UPNs for a list of supervisors.  tmpSubordinates is where I plan to store the results of this process.

 

The idea with this code is that it can be used repeatedly to work your way through the organization and grab all the subordinates.

 

The code above works but it complains about the "In" in the 6th line. (I highlighted it red.)  It says "Part of this formula cannot be evaluated remotely."  Since the "Employee Records" datasource will easily go over 500 records, I am concerned about staying with delegation requirements.  

 

I have tried breaking this into two steps.

  1. Collect the UPNs from the 'Employee Role Assignments' list
  2. Filter the 'Employee Records' list using the list from step 1.

However it complained about delegation then too.

 

I figure that using tables to filter SharePoint lists would be a pretty common thing, so I am not sure what I am doing wrong.  Any help is appreciated.

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Filter SharePoint List from a table

Hi @DeHaynes,

 

Could you please share a bit more about the error message within your app?

 

Based on the formula that you provided, I think you have faced Non-delegable issue within your app. 

 

The in operator (Filter and Lookup delegable predicates) could not be delegated within SharePoint data source (On your side, it is "Employee Records"). 

 

More details about the Filter and Lookup delegable predicates within Delegable data source, please check the following article:

Filter and Lookup delegable predicates

 

If you have faced Non-delegable issue within your app, you could only processed 500 records locally at most. Currently, you could also take a try to change the Non-delegable limits within your app (App settings-> Experimental features-> Data row limit for non-delegable queries), you could processed 2000 records locally at most. 

 

More details about changing the Non-delegable limits in PowerApps, please check the following article:

Change Non-delegable limits

 

More details about Delegation in PowerApps, please check the following:

Delegation in PowerApps

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 146 members 4,589 guests
Please welcome our newest community members: