Power Apps - Building Power Apps

Improve the performance of the canvas app while maintaining delegable query to sharepoint

Like (0) Share

Report

Posted on 18 Feb 2025 21:28:52 by johnjohnPter

1,546 Super User 2024 Season 1

We have 3 SharePoint lists/libraries:-

1) WorkOrders list

2) AttachmentDocuments library to store images

3) AttachmentDocumentsRelatedID list to store the relation between WorkOrders and AttachmentDocument, by storing the ids of both sides.

Now each workorder can have zero to many attachmentdocuments, so inside the workorders screen i am getting all the AttachmentDocument using those, inside the screen onvisible property:-

ClearCollect(
    colAttachmentDocumentsRelatedID,
    Filter(
        AttachmentDocumentsRelatedID,
        'Work Order ID' = varWorkOrder.ID
    )
);
ForAll(
    colAttachmentDocumentsRelatedID As ForAllRecord,
    Collect(
        colDelegableDocumentsResults,
        Filter(
            AttachmentDocuments,
            ActiveStatus.Value = "Active" And ID = ForAllRecord.RelatedDocumentID And ('Picture Type'.Value = "After" Or 'Picture Type'.Value = "Before")
        )
    )
);

then inside a gallery inside the WorkOrder screen i am showing the attachmentdocuments as follow, be defining this inside the Gallery's Item property:-

Sort(colDelegableDocumentsResults,ID,SortOrder.Descending)

this is working and it can handle delegable limitation, of course a workorder will not have more than 2K attachmentdocuments, so it is fine to store those inside a collection (colDelegableDocumentsResult).. but it is a bit slow, espically when a work order has like 5 to 6 attachments where the gallery will load the images one by one and it can take some time.. any way to improve this?

thanks

All responses (9)

Answers (0)

johnjohnPter 1,546 Super User 2024 Season 1 on 20 Feb 2025 at 12:35:28

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

@@jrletner

i think you need to review what you provided or your knowledge on delegable filtering with SharePoint..., what you provided will not work if the AttachmentDocuments library has more than 2K , which is of course the case.. so your query will not see any items after the 2K for the all the work orders.. what i mentioned that a workorder will not have more than 2K is still valid, that why i am storing this inside s collection,, those are 2 separate things !!! in other words your query:-

Filter(

AttachmentDocuments,

ActiveStatus.Value = "Active" &&

ID in Filter(AttachmentDocumentsRelatedID, 'Work Order ID' = varWorkOrder.ID).RelatedDocumentID &&

('Picture Type'.Value = "After" || 'Picture Type'.Value = "Before")

)

will fail on a library with more than 2K even if the work order has 1 document.. you can not have a filter that is part delegable and part that are not, that why i used ForAll to over come this, mainly to make the query as delegable
Suggested answer

jrletner 720 Super User 2025 Season 1 on 20 Feb 2025 at 12:19:15

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

That's correct. I was going based off your original post where you said

"of course a workorder will not have more than 2K attachmentdocuments, so it is fine to store those inside a collection (colDelegableDocumentsResult).. but it is a bit slow,".

So if you were never going to exceed 2k, but needed speed, then you could try a different approach to try and speed things up.

If that requirement has changed, we'd need to try something different entirely.

So you now are asking for something delegable and faster?

The solution may actuallly need to be a power automate job. It does not have delegation issues and it is faster, by far.
johnjohnPter 1,546 Super User 2024 Season 1 on 20 Feb 2025 at 11:58:39

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

@@jrletner

but your query will not work for lists with more than 2K images!!

you are using Addcolumn + in operator where those both operation are not delegable against sharepoint... did you know this info?
Suggested answer

jrletner 720 Super User 2025 Season 1 on 20 Feb 2025 at 00:36:28

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

While I agree with Pstork1, if this is a requirement, maybe just change the way you're attempting to populate your collection. ForAll is extremely slow in Power Apps, so maybe try something else like loading attachements in one step by using AddColumns and Filter to fetch all related attachments at once.

ClearCollect(
colDelegableDocumentsResults,
AddColumns(
Filter(
AttachmentDocuments,
ActiveStatus.Value = "Active" &&
ID in Filter(AttachmentDocumentsRelatedID, 'Work Order ID' = varWorkOrder.ID).RelatedDocumentID &&
('Picture Type'.Value = "After" || 'Picture Type'.Value = "Before")
),
AttachmentURL, AttachmentDocuments.'{Link}'
)
);

This should speed things up for you a little.
Pstork1 66,114 Most Valuable Professional on 19 Feb 2025 at 14:07:26

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

Yes, but unless the user actually wants to check a specific work order loading the images for that workorder is a waste of time and resources. To improve performance you want to get rid of loading unnecessary data. There is no other way.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog
johnjohnPter 1,546 Super User 2024 Season 1 on 19 Feb 2025 at 13:49:20

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

@@Pstork1

Thanks, but in my case i am not getting all the images, i am only getting the ones link to the workorder and are active and have their status = After or Before
Pstork1 66,114 Most Valuable Professional on 19 Feb 2025 at 13:39:08

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

Yes, it wont be instantaneous but overall the app will respond much more quickly because you won't be retrieving a lot of documents that you don't need to retrieve.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog
johnjohnPter 1,546 Super User 2024 Season 1 on 19 Feb 2025 at 01:20:24

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

@@Pstork1 but if i do so, then when the user select or click on a work order, the next page which shows the attachment documents will take time to load as it will be retreiving the realted documents before doing the actual navigation
Pstork1 66,114 Most Valuable Professional on 18 Feb 2025 at 21:47:37

Like (0)

Report

Improve the performance of the canvas app while maintaining delegable query to sharepoint

The best way I've found to improve performance when working with related tables is to NOT load the actual related items into the Gallery. Instead use a second Gallery and retrieve the related items there as an item is selected in the main gallery. This avoids all the time wasted by preloading a bunch of child items (in this case documents) that the user isn't going to work with. Instead focus on quickly loading the attachments when they pick a workorder.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog