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

Creating a collection with filter and lookup from another SP list

I have 2 list in Sharepoint

Customer list (aprox 3000 items)

Name

Address (lookup form Sharepoint list: Adresses

Active (yes,no)

Adresses list:

Adress

Postnumber

 

I like to create a collection using with customers that are active, and i also need to have the postnumber field in the collection. 

The collection should be like this:   Customer name: Name,  Adress: Adress, Postnumber: Postnumber

 

There are aprox 3000 customers in the list.

 

Can you please give me a tip how to create the collection based on Customer  with looking up the postnumber field from the Address list ?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating a collection with filter and lookup from another SP list

Hi @TrulsB ,

Could you please share a bit more about your scenario?

Do you want load 3000 items from your SP List ('Customer list') into a Collection in your app once time?

Could you please share a bit more about the Active column in your Customer list? Is it a Yes/No type column?

 

I have made a test on my side, please take a try with the following workaround:

1. If the Active column is a Yes/No type column:

Set the OnSelect property of a Button to following:

Clear(RecordsCollection);    /* <-- the final collection */
ForAll( Filter( 'Customer list', Active <> false), /* <-- Here would cause a Delegation warning issue. It is an known limit with 'Yes/No' type column in SP List */ Collect( RecordsCollection, { 'Customer name': Name, Adress: Address.Value, Postnumber: LookUp('Adresses list', Adress = Address.Value, Postnumber) } ) )

Note: If the Active column is a Yes/No type column, above formula may cause a Delegation warning issue. Then if the amount of customers who is Active in your 'Customer list' is more than 2000, I afraid that there is no direct way to achieve your needs in PowerApps currently. As an alternative solution, please consider change the Active column from "Yes/No" type to "Choice" type in your 'Customer list'.

 

2. If the Active column is a Choice type column:

Set the OnSelect property of a Button to following:

Clear(RecordsCollection);        /* <-- the final collection */
ForAll( Filter( 'Customer list', Active.Value = "no"), Collect( RecordsCollection, { 'Customer name': Name, Adress: Address.Value, /* <-- Address is the LookUp column in your 'Customer list', the Adress is the column 'Adresses list'*/ Postnumber: LookUp('Adresses list', Adress = Address.Value, Postnumber) } ) )

Please take a try with above solution, check if the issue is solved.

 

If above two solutions both have a Delegation warning issue, it means that you could only process 2000 records at most locally within your app. If the amount of customers who is Active in your 'Customer list' is more than 2000, there is no direct way to achieve your needs.

As an alternative solution, you could consider bulk-load your 'Customer list' records into multiple separated collections in your app, then merge these collections into a single one collection, then use the single one collection as data source in your app instead od the original 'Customer list' data source.

Please check the alternative solution mentioned within the following thread for further help:

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Sort-gallery-with-multiple-fields/td...

 

More details about the ForAll function, LookUp function, please check the following article:

ForAll function, LookUp function

 

 

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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Creating a collection with filter and lookup from another SP list

Hi @TrulsB ,

Could you please share a bit more about your scenario?

Do you want load 3000 items from your SP List ('Customer list') into a Collection in your app once time?

Could you please share a bit more about the Active column in your Customer list? Is it a Yes/No type column?

 

I have made a test on my side, please take a try with the following workaround:

1. If the Active column is a Yes/No type column:

Set the OnSelect property of a Button to following:

Clear(RecordsCollection);    /* <-- the final collection */
ForAll( Filter( 'Customer list', Active <> false), /* <-- Here would cause a Delegation warning issue. It is an known limit with 'Yes/No' type column in SP List */ Collect( RecordsCollection, { 'Customer name': Name, Adress: Address.Value, Postnumber: LookUp('Adresses list', Adress = Address.Value, Postnumber) } ) )

Note: If the Active column is a Yes/No type column, above formula may cause a Delegation warning issue. Then if the amount of customers who is Active in your 'Customer list' is more than 2000, I afraid that there is no direct way to achieve your needs in PowerApps currently. As an alternative solution, please consider change the Active column from "Yes/No" type to "Choice" type in your 'Customer list'.

 

2. If the Active column is a Choice type column:

Set the OnSelect property of a Button to following:

Clear(RecordsCollection);        /* <-- the final collection */
ForAll( Filter( 'Customer list', Active.Value = "no"), Collect( RecordsCollection, { 'Customer name': Name, Adress: Address.Value, /* <-- Address is the LookUp column in your 'Customer list', the Adress is the column 'Adresses list'*/ Postnumber: LookUp('Adresses list', Adress = Address.Value, Postnumber) } ) )

Please take a try with above solution, check if the issue is solved.

 

If above two solutions both have a Delegation warning issue, it means that you could only process 2000 records at most locally within your app. If the amount of customers who is Active in your 'Customer list' is more than 2000, there is no direct way to achieve your needs.

As an alternative solution, you could consider bulk-load your 'Customer list' records into multiple separated collections in your app, then merge these collections into a single one collection, then use the single one collection as data source in your app instead od the original 'Customer list' data source.

Please check the alternative solution mentioned within the following thread for further help:

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Sort-gallery-with-multiple-fields/td...

 

More details about the ForAll function, LookUp function, please check the following article:

ForAll function, LookUp function

 

 

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.

View solution in original post

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,251)