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

filtering one SP list based off data in another SP list

Hello, 

I'm trying to write a filter and I'm struggling with what needs to be in it.  I am looking to have the powerapp filter results of "forecast Form" based off of the "rep name" in another list called "global Accounts" and filter those results based off of the current user.  I already had the app working where it would filter the "forecast form" by "Rep Name" in the same SP list but now I'm asked to reference the "global accounts" SP list since the rep name was removed from the forecast form.  

 

Filter('Forecast form ', 'Global Accounts' = CurrentUser || 'GWA ID L/U:Region_TXT'= CurrentUser)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: filtering one SP list based off data in another SP list

@Nate167 ,

Are the Sales Manager and Area Manager individual lookups (and primary lookups)? If so, try this

Filter(
   AddColumns(
      'Forecast form', 
      "aSales",
      sales manager_TXT.Value,
      "aAccount",
      Account Manager_TXT.Value
   ),
   aSales = CurrentUser ||
   aAccount = CurrentUser
)

 

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.

View solution in original post

11 REPLIES 11
Highlighted
Super User III
Super User III

Re: filtering one SP list based off data in another SP list

Hi @Nate167 ,

You cannot filter on a Lookup field. Using your syntax try this

Filter(
   AddColumns(
      'Forecast form', 
      "aRegion",
      'GWA ID L/U:Region_TXT'
   ),
   'Global Accounts' = CurrentUser ||
   aRegion = CurrentUser
)

 

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.

Highlighted
Helper I
Helper I

Re: filtering one SP list based off data in another SP list

Thank you, I forgot about not being able to use lookup columns from SP.  I ended up having 2 columns added to the SP list "global accounts" one for account managers and one for sales managers so it can filter based off those 2 columns and the current user.  below is what I changed from your reply but I'm getting a invalid argument error.  

 

Filter(AddColumns('Forecast form', "aregion", 'accountmanger_txt'), 'Global Accounts' = CurrentUser || "aregion", 'salesmanager_TXT' = CurrentUser)

Highlighted
Super User III
Super User III

Re: filtering one SP list based off data in another SP list

Hi @Nate167 ,

Your syntax is invalid in this bit

'Global Accounts' = CurrentUser || "aregion", 'salesmanager_TXT' = CurrentUser

If Global accounts = CurrentUser (OK), but after the Or() || statement, your have put the new column on it own without an equals argument, then salesmanager_TXT = CurrentUser (OK).

Do you need the new column now, if so it has it equal something.

Highlighted
Helper I
Helper I

Re: filtering one SP list based off data in another SP list

I only need the new column if it's required for powerapps to filter properly.  The data is pretty much the same in "global accounts" as it is in forecast with the exception of account rep and manager's names to filter by.  I was adding the additional columns thinking that was the right way to go since it was in your previous solution.  The adding of columns and filtering by another SP list are new to me as I learn powerapps.  

Highlighted
Super User III
Super User III

Re: filtering one SP list based off data in another SP list

@Nate167 

Yes but it I still had to equal something. Remove it and see if this gets you what you want.

Highlighted
Helper I
Helper I

Re: filtering one SP list based off data in another SP list

So something like this?

 

Filter('Forecast form (WRFP-2)', 'Global Wireline Accounts'.displayname, = CurrentUser)
Highlighted
Super User III
Super User III

Re: filtering one SP list based off data in another SP list

@Nate167 ,

Apart from the capitalisation of display name, that is valid code, providing it references matching fields and controls.

 

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.

Highlighted
Helper I
Helper I

Re: filtering one SP list based off data in another SP list

@WarrenBelz 

 

I capitalized the DisplayName but I'm not getting any accounts returned when I test it along with .displayname and = are showing as invalid arguments.  Am I missing the column it should be referencing on the SP list "Global Wireline Accounts"?  The forecast form is filtered in SP based off lookup columns that look up the users from global wireline accounts SP List and I'm trying to accomplish the same in the app.  My data sources in powerapps are the 2 SP lists mentioned above and office365users.  

 

Capture.JPG 

Filter('Forecast form (WRFP-2)', 'Global Wireline Accounts'.DisplayName = CurrentUser)
 
 

 

Highlighted
Super User III
Super User III

Re: filtering one SP list based off data in another SP list

@Nate167 ,

I have been mainly looking at syntax to this point. I cannot see your data, so can you please summarise what field have to equal what values in what lists.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

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 designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,114)