Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Sorting data on joined Sharepoint lists

(1) ShareShare
ReportReport
Posted on by 22
Hello,
 
I'm trying to sort data coming from a join between two different Sharepoint lists, but the way I did it will cause delegation issues, which I can't have due to the size of the lists (over 2000 rows). Maybe is there a way to do it in a way to keep normalization of data and avoid delegation, because nowadays my solution is to duplicate the desired field on the other list, so the sort can work without issues.
 
Here's the context for the issue:
 
List A
Field 1: ID (auto from Sharepoint)
Field 2: aQuestionSequence (number)
Field 3: aQuestionTitle (single line text)

List B
Field 1: ID (auto from Sharepoint)
Field 2: bSiteName (single line text)

List C:
Field 1: ID (auto from Sharepoint)
Field 2: cQuestionID (relates to A.ID)
Field 3: cSiteID (relates to B.ID)
Field 4: cQuestionResponse 
So what I would like to have is something similar to this (using SQL notation to easier comprehension):
SELECT 
   A.aQuestionSequence, A.aQuestionTitle, C.cSiteID, C.cQuestionResponse
FROM 
   A,C 
WHERE 
   C.aQuestionID = A.ID and cSiteID = [specific site ID]
ORDER BY 
   A.aQuestionSequence ASC;
I tried using this idea in PowerApps, which translated to this:
SortByColumns(
   Filter(C, cSiteID = [specific site ID]),
   LookUp(A, A.ID = cQuestionID).aQuestionSequence, ascending
)
Which gave me the delegation warning on the LookUp part of the formula. To workaround it I had to duplicate the Sequence column from list A into list C, ending up with this code:
SortByColumns(
   Filter(C, cSiteID = [specific site ID]),
   cDuplicatedQuestionSequenceField, ascending // this is a duplicated field with the same content as aQuestionSequence from list A
)
So, again, is there a way to have such SQL statement translated into PowerApps to achieve this kind of sorting keeping data normalization? Additionally I don't want to use collections (to merge the data and then sort it) because performance would go down as all rows would be handled in memory and I want to avoid that.
 
Thanks!
  • OCJ Profile Picture
    OCJ 22 on at
    Sorting data on joined Sharepoint lists
    Thanks @Mark Nanneman, that's what I was looking for.
     
    One question: Since I'm creating a new "table" from the actual List 9using the result from AddColumns), I assume usage of regular forms to change the data (if solution is used as the [Items] from a gallery, for instance) will be limited, having to resort to patching manually the records, correct?
     
    Thanks again!
  • Verified answer
    Mark Nanneman Profile Picture
    Mark Nanneman 981 on at
    Sorting data on joined Sharepoint lists
    If I'm following correctly, you should be able to accomplish this with AddColumns().

    The expression would look something like this:
     
    AddColumns(
        '<data_list_with_sharepoint_id_columns>',
        '<new_column_from_sharepoint_list_a>', 
        LookUp('<sharepoint_list_a>',ID = 'SharePoint List A ID').Title,
        '<new_column_from_sharepoint_list_b>', 
        LookUp('<sharepoint_list_b>',ID='Site ID').Title
    )
    E.g.

    Here it is in the new table control:
     
    If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

    Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff  | YouTube: Mark's Power Stuff  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,580

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,909

Leaderboard