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!