Power App | Canvas - Stopped working

(2) Share

Report

Posted on by CU29092301-0

115

I have a power app that has data sources from four SharePoint lists. The lists are simple in nature, and the app is set to simply bring back data for a specific user's request that they have submitted on the app. So basically, a search app.

The app had been working fine and then one day, nothing. There is one gallery that sorts and filters all the items in one SP list and returns that data if "All" is selected from the drop down and subsequently there are 10 other status values that can be selected here as well. Nothing is populating and the total is showing 0.

I refreshed all the lists and still nothing returns. I also removed and re-added the main list with no results. It's as if the App is not retrieving any data source information. Any help would be appreciated.

The App - OnStart is set to ClearCollect( and filter on the user email, ClearCollect(statusvalues "All", Collect(statusvalues, and ClearCollect(MyCCs on a sort.
Gallery - Items - If ddStatus selcted value = All, SortByColumns and then Filter(SortByColumns

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Suggested answer

Valantis 6,735 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU29092301-0,

A likely reason this stopped working is that the app is using ClearCollect in App.OnStart to load SharePoint data into a collection.

This is not delegable, which means Power Apps may only pull part of the list. If the list has grown, the records you expect might no longer be included, so the gallery can show 0 items even though the data is still there.

Another possible reason is timing. Microsoft also says the app screen can load before App.OnStart has finished, so the collection may not be ready when the gallery tries to display the data.

The best next step is to stop using the SharePoint collection for this gallery and instead connect the gallery directly to a Filter / SortByColumns formula against the SharePoint list. Also, if you are filtering on a Person column, use Email or DisplayName only.

I would also recommend checking App Checker / Monitor to see whether the query is failing or showing delegation warnings.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

Was this reply helpful? Yes No
CU29092301-0 115 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Valantis,

Thank you for your quick response!

Yes, I did some preliminary testing, and it appears that the clear collect is failing and the query isn't running because too many lookup columns.

On the App checker performance, I found that there are 2 warnings, unused variable and data row limit for non-delegable queries, which is set to 2000, unused variable removed. On the App checker Data source, also found, that two of the SharePoint data sources are showing as "unused".

I tried creating a Sort/Filter directly tied to the SharePoint list instead using the Filter/SortByColumns formula, it too is failing. Error saying too much for lookup threshold. Not sure what to do here next.

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @CU29092301-0,

Thanks for the update. Since the direct Filter / SortByColumns query is also failing with a lookup threshold message, this now looks more like a SharePoint list design / threshold issue than just a Power Apps formula issue.

The most likely cause is that the main SharePoint list has too many complex columns involved, such as People, Lookup, or Managed Metadata columns. Microsoft documents that these count toward the SharePoint lookup threshold, and once that threshold is hit, queries can start failing.

My recommendation would be:

review the main SharePoint list and reduce unnecessary People / Lookup / Managed Metadata columns where possible

use simple columns for app filtering, such as plain text for email or status, where practical

avoid loading the full SharePoint list with ClearCollect on startup

keep the gallery directly connected to SharePoint with a delegable Filter / SortByColumns formula

make sure the columns used for filtering and sorting are indexed

So in short, this now seems to be a SharePoint threshold/schema issue, not only an app formula issue.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

Was this reply helpful? Yes No
Valantis 6,735 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU29092301-0,

Just wanted to check in and see if everything is working now. If you still need any help, feel free to let me know.

Also, if the issue is resolved, it would be great if you could mark the answer as solved so others with the same question can find it easily.

Thanks and have a great day!

Was this reply helpful? Yes No
CU29092301-0 115 on at

Like
a
(1)

Report
Copy link

Link copied!
Good afternoon, Valantis,

Thank you for reaching out. I had to briefly switch gears to another issue that needed resolving. I'm back to focus on this next week.

I do still need help with the App. I've made a copy that I'm going to use to rebuild the app hopefully connect it directly to the list instead of running formula on the OnStart.

Here is what I'm hoping to achieve.

I have four search fields; the first one is Status (it holds 11 different values). "All" would bring back all the active records, "Submitted" would bring back the status of submitted, and so on with the other values if selected.

The second search field is PPD, which narrows the search based on a specific PPD value entered. The next to fields would do the same, one is Job Title and one last field. All four of the fields are in the data source list.

One of the main goals of the App is, when a user accesses the app, I want to have the App load their specific records where they are listed as the "Hiring Manager" by using the Hiring Manager email || Created By email || Another field email

This is it; hopefully this shouldn't be too hard to accomplish. The app currently is not working; the SharePoint list has a number of lookup fields that I don't want to go down that road to change/update.

Hopefully I can accomplish this without having to re-work the list, which is not an option.

Was this reply helpful? Yes No
Valantis 6,735 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CU29092301-0,

i hope it will work out at the end :) if you need any help, happy to assist. just ping me here ;)

have a nice day

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

Was this reply helpful? Yes No
CU29092301-0 115 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello there,

I really need help with this App. Are you able to point me in the right direction for help? I appreciate your efforts.

I copied the existing App so that I could rebuild it.

Removed the StartScreen, OnStart formulas to start fresh

I also tried to create a view with only the necessary fields for the App search (nine); however, this didn't seem to work

I have confirmed that the four of the main fields are already indexed columns

I tried building a lightweight collection, no go

I tried doing a Raw filter on just the 9 columns, no go

Everything just keeps erroring on lookup threshold error.

This should be a lot simpler, the app is simply supposed to search on one of four fields, show five fields of that search and click a button to see the full record if needed.

Was this reply helpful? Yes No