Answered

Delegation problem with Excel list in OneDrive

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi guys!

I have a problem regarding a large Excel list.

My app is used as an application for certain products. For someone to order a product they need to fill in a project manager and a project code. These items, the projectmanager and code are derived from an excel workbook in my onedrive business account.

The list looks like this:

ProjectManager	ProjectName
John D.	Project1
Carl G.	Project2
John D.	Project3

In my app I have two dropdowns, with a distinct formula to sort the projectnames by projectmanager.

In the first dropdown I use the following code inside the Item property:
Sort(Distinct(ProjectName, 'Project Partner'), Result)

In the second dropdown where I select the projectname I have the following code:
Distinct(Filter(ProjectName, 'Project Partner'=DropDownPartner.Selected.Result),'Project Name')

"DropDownPartner" is the name of the first dropdown.

This works great. However the problem I run into is the following, the projectnames list is over 2500 rows long. And for some reason I cannot see any more items after the "S".

What is the best way to store my project data so I am able to see all projects in the list? Can I use a sharepoint list?

Thanks in advance 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

WarrenBelz 156,277 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

Firstly, SharePoint has significant advantages over an Excel as a data source, however the Distinct function is not Delegable.

I have done a blog on Delegation that may contain some useful suggestions for you to manage this. Happy to elaborate further if any of this is useful.

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Warren,

Thanks for your reply!
Definetly gonna use an sharepoint list over the Excel spreadsheet.
However I need the distincs feature or something similair, because I need to filter the Project Names by Project Managers.

Any idea how I can solve this?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,277 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Did you read my blog? Also there is another linked at the bottom using the With() statement. In particular, is the items you are looking for are all in the newest 2,000 records, you can use

With( { wList: Sort( YourListName, ID, Descending ) }, Distinct( wList, YourFieldName ) )

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

For now I made the list under 2000 items, this should work for now.
I did however try the collect function as Warren suggested, this works for up to 4000 items!

Was this reply helpful? Yes No