Canvas Power App, fetching records on start-up not working with my filters

(0) Share

Report

Posted on by allan_t

310

Possibly a delegation issue, not sure how to resolve.

SharePoint list called WorkRequest. Has a column called Status, there are currently 77 items with Status value of Open and >3000 with Status as Closed. The app is set to load 2000 records. In the gallery, it's not displaying any recent items with status of Open. I'm thinking it's loading the first 2000 records on load (regardless of status value)?

On my gallery page, there is a combo box control for the user to display Open vs. Closed, plus a search field to further filter.

[ComboBox3, value is either Open or Closed]

[inpSearchNameID, input text that will search based on DisplayName of a person field, ID of list item]

The items property for this gallery is:

SortByColumns(Filter(WorkRequest, (StartsWith(Open_Closed, ComboBox3.Selected.Value) && (StartsWith(ID, inpSearchNameID.Text) || StartsWith(RequestShortName, inpSearchNameID.Text) || StartsWith(Submitter.DisplayName, inpSearchNameID.Text) || CountRows(Filter(LeadAssigned,DisplayName = inpSearchNameID.Text))>0))),"Modified",SortOrder.Descending)

Any advice on how to resolve this?

Is there a way to ensure all Open items are loaded on start?

Is there a way to ensure all Open items for the User logged in are loaded on start?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

Hassan_SZ_365 542 on at

Like (1)

Report
Copy link

Link copied!
Hi @allan_t ,
Follow these steps:
Optimize Filters for Delegation: Ensure all your filters are delegable. SharePoint's StartsWith, =, and some other functions are delegable, but complex filters, especially nested ones, might not be.
Filter by Status on Start: To ensure all Open items are loaded at the start, set the gallery's Items property to filter by the 'Open' status by default and sort by the 'Modified' date in descending order:

SortByColumns(Filter(WorkRequest, Open_Closed = "Open"),"Modified", Descending)

Filter by User: To load items for the logged-in user, add a filter condition to check if the 'Submitter' or 'LeadAssigned' is the current user:

SortByColumns(Filter(WorkRequest, Open_Closed = "Open" && Submitter.Email = User().Email),"Modified", Descending)

Adjust Submitter.Email to the correct field that stores the user email in your list.
4. Increase Data Row Limit for Non-Delegable Queries: If you have non-delegable queries and can't optimize them further, consider increasing the data row limit for non-delegable queries up to the maximum of 2000 (or 500 for some data sources).
Go to File > Settings > Advanced settings > Data row limit for non-delegable queries.
5. Load Data in Batches: If you need more items than the delegation limit allows, you may have to load your data in batches. You can do this by loading the first set of data and then using the 'Load More' button to load additional data as needed.
Please note that complex filters and operations might still be subject to delegation limits, so you'll have to experiment with the filters to ensure they work within those constraints. For non-delegable queries, you may have to restructure your app's logic or change your data model to ensure efficient data retrieval.

Best Regards,
Hassan Raza

Was this reply helpful? Yes No
allan_t 310 on at

Like (0)

Report
Copy link

Link copied!

I will try this ASAP. Thank you for explaining!

Was this reply helpful? Yes No
allan_t 310 on at

Like (0)

Report
Copy link

Link copied!

@Hassan_SZ_365 Re: Filter by Status on Start: To ensure all Open items are loaded at the start, set the gallery's Items property to filter by the 'Open' status by default and sort by the 'Modified' date in descending order

Should this filter be applied to the gallery (after start-up), or to the app.onstart property?

Was this reply helpful? Yes No
Hassan_SZ_365 542 on at

Like (1)

Report
Copy link

Link copied!
Hi ,
On App Start: If you want to load the 'Open' items when the app starts, you could set a global variable in the App.OnStart property that defines the initial filter for the gallery. For example:
Set(gblInitialStatus, "Open");
Gallery Items Property: Use this variable to filter the gallery items when the app starts. Set the gallery's Items property like this:
SortByColumns(Filter(WorkRequest, Open_Closed = gblInitialStatus), "Modified", Descending)
By setting the filter in the gallery's Items property, you ensure that the gallery is always showing items based on the current filter criteria, which will start with 'Open' items when the app is first launched. If the user changes the selection in ComboBox3, the gallery will automatically update to show the relevant items based on the new filter.

Best Regards,
Hassan Raza
@allan_t

Was this reply helpful? Yes No
allan_t 310 on at

Like (0)

Report
Copy link

Link copied!

@Hassan_SZ_365 I've been able to get the filter to work without delegation warning, there are 3 criteria in this gallery Items property. 1) AppLoad is true, this is a boolean column on the SharePoint list. I'll manage this to keep the number of items under 2000. 2) RequestShortName, text column that is using the StartsWith function. 3) SearchableID, text column that mirrors the SharePoint List ID.

It works, but is not user-friendly because users have to scroll through to find their items. I'd like to add another filter condition, but keep getting a delegation warning. I have a global variable set On Start where varUser = User(). I'd like to default the search box text (datacard is inpSearchNameID) to be the User DisplayName, that way when the user opens the page, it's filtered to their items. The user can choose to blank out their DisplayName and enter an ID or RequestShortName. Note: the user needs ability to search items that are not their own, so I couldn't use the && function in the filter. I don't know how to get this to work without a delegation warning. I read that DisplayName is supposed to be delegable. The SharePoint person column is called LeadAssigned, permits multiple values, some will have blank values until a person is assigned.

Here's my current Items property formula. Any advice you have is greatly appreciated. Thank you.

SortByColumns(
Filter(
WorkRequest,
AppLoad = true,
(StartsWith(
RequestShortName,
inpSearchNameID.Text
) || SearchableID=inpSearchNameID.Text)
),
"Modified",
SortOrder.Descending
)

Was this reply helpful? Yes No
Hassan_SZ_365 542 on at

Like (1)

Report
Copy link

Link copied!
@allan_t ,

Follow these steps:
Set Global Variable for User:
You mentioned setting varUser = User() in the OnStart property. Ensure this variable holds the DisplayName or Email of the user, depending on which field is more reliable for delegation in your SharePoint list.
Modify Gallery Items Formula:
Adjust the formula to include a user filter condition. Since DisplayName can be a non-delegable field, using Email might be more effective. Your modified formula would look something like this:

SortByColumns( Filter( WorkRequest, AppLoad = true, (StartsWith(RequestShortName, inpSearchNameID.Text) || SearchableID = inpSearchNameID.Text), Submitter.Email = varUser.Email // Assuming 'Submitter' is the field name ), "Modified", SortOrder.Descending )
Replace Submitter.Email with the appropriate field name in your list that stores user email addresses.

Default the Search Box to User's DisplayName:
Set the Default property of inpSearchNameID to varUser.DisplayName. This will automatically populate the search box with the user's display name on app startup.
To allow users to clear or change this value, ensure inpSearchNameID is editable.
Handling Delegation Warnings:
If you still encounter delegation warnings, it's likely due to the limitations of SharePoint's delegation capabilities. You might need to explore other approaches, such as filtering records on the client side (which is less efficient) or restructuring your data to better fit delegable queries.
Test and Adjust:
After implementing these changes, test the app to ensure the filters work as expected and that user-specific items are showing up without the need to scroll excessively.
Further Optimization:
If you have control over the SharePoint list structure, consider optimizing it for better delegation, such as using more delegable fields like Email instead of DisplayName.
Best Regards,
Hassan Raza

Was this reply helpful? Yes No
allan_t 310 on at

Like (0)

Report
Copy link

Link copied!

@Hassan_SZ_365 I was able to get 1 of 2 scenarios to work. Scenario 1, the Submitter column does not permit multiple people, I got this to work without a delegation issue, thank you! Scenario 2 which is the critical one, I couldn't get to work because the LeadAssigned column permits multiple people, this causes a delegation warning I'm assuming because the column contains a table of people rather than a single person like Submitter.

These delegation constraints are my nemesis 😞

Was this reply helpful? Yes No