Answered

Sorting data on joined Sharepoint lists

(1) Share

Report

Posted on by OCJ

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:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

Mark Nanneman 993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
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 34 on at

Like
a
(0)

Report
Copy link

Link copied!

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!