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

Set items in List A based on a column of Filtered List B

Hello All

Super new to PowerApps and looking for input on the best way to accomplish something. We have two lists:

- SharePoint List A is a list of all customers and everyone will have a territory associated to it

- SharePoint List B is a list of resources assigned to each territory 

 

Initially we were trying to load all 8,000 customers into a ComboBox for searching but realized it was only searching 500 rows. We ramped that 500 to 2,000 in settings but if we can't get the full 8,000 we need to filter the broader list down. So we created List B thinking that if we only show the customers in the territories that resource is responsible for than we can boost performance AND ensure they see all their customers.

 

Challenge is I can't find any documentation on filtering a list based on column values from another list. So Scenario

- List B is filtered to only include territories where the Resource Email = Assigned Resource Email

- List A is filtered to only include customers where the Customer Territory = Resource Territory

 

Some other things that might help to know:

- List A has columns for Customer ID, Customer Name, Customer Territory (territories from List B)

- List B has columns for Territory, Resource ID, Resource Email (same email as User().Email)

- Selecting the customer populates fields in the form for you 

- I am populating list B using: filter(List B, ResourceEmail = User().Email)

 

Hoping that their is something that I just can't find myself

 

5 REPLIES 5
Super User
Super User

Re: Set items in List A based on a column of Filtered List B

Heya @blindmallard 

 

You could filter the List A dropdown with:

Filter(ListA, CustomerTerritory = ListB.Selected.ResourceTerritory)

It might be that depending on how you've set up that dropdown that it asks for .Value, either way if you use intellisense after typing ListB.Selected. then you should be able to figure it out from there,


Let me know after you've given it a try and if you get completely stuck, happy to help if you've tried a few things and still struggling,

 

Cheers,

Sancho

 




Don't forget to 'Mark as Solution' and 'Thumbs Up' those who deserve it!
Super User
Super User

Re: Set items in List A based on a column of Filtered List B

Hi @blindmallard 

 

You can use lookup columns to asosciate 2 lists together

Here is a video on how to filter on lookup columns

https://www.youtube.com/watch?v=pn50AKn3Q1Y

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Community Support Team
Community Support Team

Re: Set items in List A based on a column of Filtered List B

Hi @blindmallard ,

Could you please share a bit more about the 'Customer Territory' column in your List A? Is it a LookUp column, which references values from the Territory column in your List B?

Could you please show more details about the 'Customer Name' column in your List A? Is it a Text type column or a Person type column?

 

I assume that the 'Customer Territory' column in your List A is a LookUp column, which references values from the Territory column in your List B, is it true?

Please consider take a try with the following workaround:

Set the OnStart property of the App to following:

Set(CurrentUserEmail, User().Email)

1. If the 'Customer Name' column in your List A is a Text type column:

Please set the Items property of the ComboBox to following:

Distinct(
Filter(
'List A',
'Customer Territory'.Value in Filter('List B', ResourceEmail = CurrentUserEmail).Territory
),
'Customer Name'
)

2. If the 'Customer Name' column in your List A is a Person type column:

Distinct(
        Filter(
               'List A',
               'Customer Territory'.Value in Filter('List B', ResourceEmail = CurrentUserEmail).Territory
        ),
        'Customer Name'.DisplayName
)

Note: Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Please take a try with above solution, then re-load your app (fire the OnStart property of App), then check if the issue is solved.

 

Best regards,

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.
blindmallard
Level: Powered On

Re: Set items in List A based on a column of Filtered List B

I assume that the 'Customer Territory' column in your List A is a LookUp column, which references values from the Territory column in your List B, is it true?

No they are two seperate sources with no reference to each other. List A is a list of company names with a column for the territory that customer sits in. List B is a list of resources tied to each territory but makes no reference to the customer. I am trying to show a resource only the companies they work with, but since List A doesn't include their email I need to look up their territories in List B than filter List A to only those territories. 

 

I was able to do this by adding another combobox but I am still getting a delegate warning. So now I am not sure if I am even on the right track. I set the Items in A using this:

Filter('ListA',SalesTerritory=ListBComboBox.Selected.SalesTerritory)

 

Community Support Team
Community Support Team

Re: Set items in List A based on a column of Filtered List B

Hi @blindmallard ,

Could you please share a bit more about the Delegation warning issue with your formula?

Could you please show more details about the SalesTerritory column? Is it a Text type column?

 

Based on the formula you provided, I could not find any syntax error with it. If you have faced a Delegation warning issue with your formula, it means that you could not delegate the data process to the data source itself, instead, you could only process your data locally.

In default, you could only process 500 records locally at most. You could change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then you could process 2000 records locally in your app at most.

If the amount of your SP List records is not more than 2000, you could ignore this Delegation warning issue. More details about the Delegation in PowerApps, please check the following article:

Delegation

 

More details about the delegable function or operator supported in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-delegable-functions-and-oper...

 

Best regards,

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

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

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

Top Kudoed Authors
Users Online
Currently online: 297 members 1,906 guests
Please welcome our newest community members: