cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PatrickProvoke
Frequent Visitor

Slow Sort/Filtering - not sure what's left to change?

Hey folks - I have a client here who's created a PowerApp that is using multiple SP lists as datasources - these lists are then stored locally in collections which are being filtered for a gallery.

 

The app switches between sorting/filtering based on a button press (passing a variable to a switch statement to determine which filter to use. When the user uses the 'All' switch in this case, the filtering is fast. However the 'Sent', 'Received' and 'Notified' filters take around 30-40 seconds. Any ideas as to why this may be such a dramatic slow down?

 

 

Switch(
    varParentFilter,
    // All Form Filters
    "All",
    Sort(
        Filter(
            If(
                acbxNotApprovedByMe.Value = true,
                Filter(
                    colAllForm,
                    ID in Distinct(
                        Filter(
                            Switch(
                                FormType,
                                "GR Form",
                                'General Form Signoff Table',
                                "SA Form",
                                'Seal Application Form Signoff Table',
                                "PR Form",
                                'PR Form Signoff Table',
                                "POR Form",
                                'POR Form Signoff Table',
                                "MER Form",
                                'Process Sign Off Table'
                            ),
                            Status = "Pending" && Approver.Email = currentUser.Email
                        ).SubmissionID,
                        'Submission ID'
                    ).Result && Status.Value <> "Approved" && Status.Value <> "Rejected" && Status.Value <> "Draft"
                ),
                Filter(
                    colAllForm,
                    Created >= adateRaisedFrom.SelectedDate && Created <= adateRaisedTo.SelectedDate 
                    && (RequestedBy.Email = currentUser.Email || Author.Email = currentUser.Email || currentUser.Email exactin NotificationEmail || currentUser.Email exactin NotificationEmails || currentUser.Email in LookUp(
                        'Admin Access Control',
                        Admin.Email = currentUser.Email
                    ).Admin.Email || ID in Distinct(
                        Filter(
                            Switch(
                                FormType,
                                "GR Form",
                                'General Form Signoff Table',
                                "SA Form",
                                'Seal Application Form Signoff Table',
                                "PR Form",
                                'PR Form Signoff Table',
                                "POR Form",
                                'POR Form Signoff Table',
                                "MER Form",
                                'Process Sign Off Table'
                            ),
                            Approver.Email = currentUser.Email
                        ).SubmissionID,
                        'Submission ID'
                    ).Result && Status.Value <> "Draft")
                )
            ),
            IsBlank(atxtSerialNoValue.Text) || atxtSerialNoValue.Text in Title || atxtSerialNoValue.Text in SerialNumber || atxtSerialNoValue.Text in PurchasingRequestNumber || atxtSerialNoValue.Text in PORNum || atxtSerialNoValue.Text in Author.DisplayName || atxtSerialNoValue.Text in PONumber || atxtSerialNoValue.Text in MERNumber ||
                   atxtSerialNoValue.Text in FormCategory.Value,
            IsBlank(acbxChosenStatus.SelectedItems.Value) || IsEmpty(acbxChosenStatus.SelectedItems) || Status.Value in (acbxChosenStatus.SelectedItems),
            IsBlank(acbxFormType.Selected.Abbreviation) || IsEmpty(acbxFormType.Selected.Abbreviation) || FormType = acbxFormType.Selected.Abbreviation
        ),
        Created,
        Descending
    ),
    // Sent filter
    "Sent",
    Sort(    
        Filter(
            colAllForm,
            Created >= adateRaisedFrom.SelectedDate && 
            Created <= adateRaisedTo.SelectedDate &&
            //acbxNotApprovedByMe.Value = true && 
            (ID in Distinct(
                Filter(
                    Switch(
                        FormType,
                        "GR Form",
                        'General Form Signoff Table',
                        "SA Form",
                        'Seal Application Form Signoff Table',
                        "PR Form",
                        'PR Form Signoff Table',
                        "POR Form",
                        'POR Form Signoff Table',
                        "MER Form",
                        'Process Sign Off Table'
                    ),
                    RequestBy.Email = currentUser.Email
                ).SubmissionID,
                'Submission ID'
                ).Result && Status.Value <> "Draft"
            ),
            IsBlank(atxtSerialNoValue.Text) || 
            atxtSerialNoValue.Text in Title || 
            atxtSerialNoValue.Text in SerialNumber || 
            atxtSerialNoValue.Text in PurchasingRequestNumber || 
            atxtSerialNoValue.Text in PORNum || 
            atxtSerialNoValue.Text in Author.DisplayName || 
            atxtSerialNoValue.Text in PONumber ||
            atxtSerialNoValue.Text in MERNumber ||
            atxtSerialNoValue.Text in FormCategory.Value,
            IsBlank(acbxChosenStatus.SelectedItems.Value) || 
            IsEmpty(acbxChosenStatus.SelectedItems) || 
            Status.Value in (acbxChosenStatus.SelectedItems),
            IsBlank(acbxFormType.Selected.Abbreviation) || 
            IsEmpty(acbxFormType.Selected.Abbreviation) || 
            FormType = acbxFormType.Selected.Abbreviation
        ),
    Created,
    Descending
    ),
    // Received filter
    "Received",
    Sort(
        Filter(
            If(
                acbxNotApprovedByMe.Value = true,
                Filter(
                    colAllForm,
                    ID in Distinct(
                        Filter(
                            Switch(
                                FormType,
                                "GR Form",
                                'General Form Signoff Table',
                                "SA Form",
                                'Seal Application Form Signoff Table',
                                "PR Form",
                                'PR Form Signoff Table',
                                "POR Form",
                                'POR Form Signoff Table',
                                "MER Form",
                                'Process Sign Off Table'
                            ),
                            Status = "Pending" && Approver.Email = currentUser.Email
                        ).SubmissionID,
                        'Submission ID'
                    ).Result && Status.Value <> "Approved" && Status.Value <> "Rejected" && Status.Value <> "Draft"
                ),
                Filter(
                    colAllForm,
                    Created >= adateRaisedFrom.SelectedDate && Created <= adateRaisedTo.SelectedDate && (ID in Distinct(
                        Filter(
                            Switch(
                                FormType,
                                "GR Form",
                                'General Form Signoff Table',
                                "SA Form",
                                'Seal Application Form Signoff Table',
                                "PR Form",
                                'PR Form Signoff Table',
                                "POR Form",
                                'POR Form Signoff Table',
                                "MER Form",
                                'Process Sign Off Table'
                            ),
                            Approver.Email = currentUser.Email
                        ).SubmissionID,
                        'Submission ID'
                    ).Result && Status.Value <> "Draft")
                )
            ),
            IsBlank(atxtSerialNoValue.Text) || atxtSerialNoValue.Text in Title || atxtSerialNoValue.Text in SerialNumber || atxtSerialNoValue.Text in PurchasingRequestNumber || atxtSerialNoValue.Text in PORNum || atxtSerialNoValue.Text in Author.DisplayName || atxtSerialNoValue.Text in PONumber|| atxtSerialNoValue.Text in MERNumber ||
                   atxtSerialNoValue.Text in FormCategory.Value,
            IsBlank(acbxChosenStatus.SelectedItems.Value) || IsEmpty(acbxChosenStatus.SelectedItems) || Status.Value in (acbxChosenStatus.SelectedItems),
            IsBlank(acbxFormType.Selected.Abbreviation) || IsEmpty(acbxFormType.Selected.Abbreviation) || FormType = acbxFormType.Selected.Abbreviation
        ),
        Created,
        Descending
    ),
    // Notified
    "Notified",
    Sort(
        Filter(
          
                Filter(
                    colAllForm,
                    Created >= adateRaisedFrom.SelectedDate && Created <= adateRaisedTo.SelectedDate && (currentUser.Email exactin NotificationEmail || currentUser.Email exactin NotificationEmails)
                ),
         
            IsBlank(atxtSerialNoValue.Text) || atxtSerialNoValue.Text in Title || atxtSerialNoValue.Text in SerialNumber || atxtSerialNoValue.Text in PurchasingRequestNumber || atxtSerialNoValue.Text in PORNum || atxtSerialNoValue.Text in Author.DisplayName || atxtSerialNoValue.Text in PONumber|| atxtSerialNoValue.Text in MERNumber ||
                   atxtSerialNoValue.Text in FormCategory.Value,
            IsBlank(acbxChosenStatus.SelectedItems.Value) || IsEmpty(acbxChosenStatus.SelectedItems) || Status.Value in (acbxChosenStatus.SelectedItems),
            IsBlank(acbxFormType.Selected.Abbreviation) || IsEmpty(acbxFormType.Selected.Abbreviation) || FormType = acbxFormType.Selected.Abbreviation
        ),
        Created,
        Descending
    )
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
PatrickProvoke
Frequent Visitor

Solved this one 🙂 Added all the direct calls to the SharePoint lists 'tables' into a local connection along with the other collections I already had.

View solution in original post

1 REPLY 1
PatrickProvoke
Frequent Visitor

Solved this one 🙂 Added all the direct calls to the SharePoint lists 'tables' into a local connection along with the other collections I already had.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,499)