Filter gallery on other gallery using lookup fields

[Rachel]

Hello all,

I am working on creating a resume editor app. The idea is that employees could find their name (gallery 1), see the projects they've worked on (gallery 2), and write/edit a paragraph about what they've done on the project (edit form).

I have an Employees table with an ID field. I have a EmployeeProjects table with a lookup field to Employees.ID. EmployeesProjects has ID, EID (lookup to Employees.ID), and then a long text field with the paragraph.

I am trying to make it so you click on the arrow and it goes from an Employees gallery (MenuList) to an EmployeeProjects (ProjectsList) gallery which only shows the projects that specific employee has worked on (i.e. not all possible projects).

I have tried a couple different filters on the second gallery:

Filter(EmployeeProjects, EID = MenuList.Selected.ID)

Filter(EmployeeProjects, Employees.ID = MenuList.Selected.ID)

I also tried the pair:

In MenuList: Navigate(ProjectDetails, ScreenTransition.Fade, {_ActiveEmployee:ThisItem.ID})

In ProjectList: Filter(EmployeeProjects, EID = _ActiveEmployee)

Errors I've received:

Warning: Invalid argument type.

Blue Dot: Left is Text, Right is Number

Blue Dot: Part of this Filter formula cannot be evaluated... (I think this is from delegation and I have more than 500 records.)

Is there anything else that might work? I read somewhere that creating calculated fields for the lookup fields might be a workaround - would that work here?

Thank you in advance!

[Jeff_Thorpe · Accepted solution]

Try this:

Filter(EmployeeProjects, EID.Value = MenuList.Selected.ID)

[Rachel]

Thanks Jeff - I found that worked but there were delegation issues (I imagine because of the .Value) and we have way more than 500 rows in the EmployeeProjects table. I ended up changing the Sharepoint List fields to just be static fields, not lookups, and then made it:

Filter(EmployeeProjects, EID = MenuList.Selected.ID). 

[Jeff_Thorpe]

@Rachel,

Complex field types (Choice, Lookup, Person, etc) aren't delegable in SharePoint. If your list is under 2,000 you can use a new feature that allows you to change the non-delegable row limit to up to 2,000.

PowerApps data row limit for non-delegable queries