web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Sorting data on joined...
Power Apps
Unanswered

Sorting data on joined Sharepoint lists

(1) ShareShare
ReportReport
Posted on by 34
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!
Categories:
I have the same question (0)
  • Verified answer
    Mark Nanneman Profile Picture
    991 Super User 2025 Season 2 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  
  • OCJ Profile Picture
    34 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!

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

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 836 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 327 Super User 2025 Season 2

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 231 Super User 2025 Season 2

Last 30 days Overall leaderboard