Power Platform Community Forum Thread Details

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 ?

Categories:

Building Power Apps

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-p/379730

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

ForAll function, LookUp function

Best regards,