Over 2000 Data Rows

[tapper]

hey guys, 

I have created an app for tracking steps and presenting the data on a leader board. The solution is driven from a SharePoint List where steps entries are recorded. 

The app has been a lot more successful than originally planned, which has lead us to this point where we have over 2000 entries, which has caused issues with retrieving and calculating the total steps by user to present on the leaderboard

Originally the below code was used to pull through and the total steps, grouped by user and present a leader board via a vertical gallery. This worked perfectly up to the 2000 data row limit. 

Sort(
AddColumns(
GroupBy(
WellbeingStepsChallenge_StepsLog,
"UserEmail","UserDisplayName",
"Steps"
),
"TotalSteps",
Sum(
Steps,
Steps
)
),
TotalSteps,
Descending
)
TotalSteps,
Descending
),UserEmail = User().Email
)

To resolve the issue, I am attempting to implement a solution provided by PAUL RODRIGUES (@ https://officepoweruser.com/how-to-collect-over-2000-records-in-powerapps/). However, I cant see to integrate the below section of code into the formula

ClientID = varClientID && ID > varIDForNextTransRun

The below formula is an attempt, while it doesn't present any errors, it also doesn't retrieve the correct 'total steps' grouped by user Email. I understand that I have missed out the 'ClientID = varClientID && ID > varIDForNextTransRun' variable, to calculate the next collect position, however I need some guidance on how to implement this into my code. 

Set(varUserEmail, User().Email); ClearCollect(
StepsLogVar,
Sort(
AddColumns(
GroupBy(
WellbeingStepsChallenge_StepsLog,
"UserEmail","UserDisplayName",
"Steps", "ID"
),
"TotalSteps",
Sum(
Steps,
Steps
)
),
TotalSteps,
Descending
)
);If(
CountRows(StepsLogVar) = 2000,
Set(
varIDForNextTransRun, // Set this variable to get a the last ID saved into the collection.
2001
);
Collect(
StepsLogVar,
Sort(
Filter(
AddColumns(
GroupBy(
WellbeingStepsChallenge_StepsLog,
"UserEmail","UserDisplayName",
"Steps", "ID"
),
"TotalSteps",
Sum(
Steps,
Steps
)
),
TotalSteps,
Descending),
varIDForNextTransRun = 4001
))
);

See attached for the data source columns 

Alternatively, if there are better solutions to this issue, then I welcome the advice

Thank you in advance for your help!

 

 

[RandyHayes]

@tapper 

This is a bit of a challenge depending on a few factors.

But let's start with a few questions:

1) Do you really need ALL of the records in WellbeingStepsChallenge_StepsLog?  Are all of them relevant or have some "retired" or "expired" over the time your data has been collecting?  I do not see anything in your data definition that would indicate there is anything that would mark something as essentially - no longer needed.  Often we use an additional column to indicate "expiration".  This way we can filter out the expired records from the total amount of data we are really pulling back or processing. We may have 100k records in a list, but if only 500 of them are not-expired, then we are able to filter out the rest and are well within the limits.

2) You are hinting at using the ID to filter (although your formula is actually incomplete and does not have any of the actual filter conditions in it).  I will tell you upfront that this will not work as the ID column is non-delegable to SharePoint.  So, if you have ID's 1 - 2000 in your list, if you try to Filter for ID>2000, you will get no records.  You need to devise an alternate ID column in your list that is delegable.  

I tend to think that your data is a "running" list of items and perhaps #1 above might not apply.  So, #2 may be your option and if so, then you will need to devise another column for doing a filter.

One other option is to review how you can "chunk" your data by delegable actions. You show your column names but not the types of columns. If, for example, your "Country" column is Text and no one particular total of country records is more than 2000, then you can delegate a filter on that (then loop through and gather all the countries that might exist in the data).  Of, if the JourneyDate is in a Text format (preferably yyyymmdd) then you can delegate a filter on particular time spans, and then loop over those as well.

All in all, you can certainly break the 2000 barrier, but once you start to do so, your data must be equipped to give you the results you want.

I hope this is helpful for you. 

[Drrickryp · Accepted solution]

Hi @tapper 

To obtain a collection up to 4000 items you can use the following formula.  Once you exceed 4k items, you have to get creative in SharePoint.

Concurrent(
          ClearCollect(
                        List1, Sort(
                                     SPList,ID,Ascending
                                )
            ),
          ClearCollect(
                        List2, Sort(
                                     SPList,ID,Descending
                                )
           )
);
ClearCollect(
              ListAll ,List1, Filter(
                                    List2, Not(
                                                ID in List1.ID
                                            )
                             )
);
Clear(List1);
Clear(List2)

The result is a collection called ListAll that removes the duplicates caused by the overlap of the SPlist sorted ascending and descending. 

[tapper]

Hi, 

To resolve this issue I used a similar method as the solution, however the key was to delegating the 'filter' to the data source. This allows me to create multiple collections (all with a 2000 limit) and then create a 'CombinedStepsVar' Collection, which allowed me to sort and filter on gallery objects to my hearts content. 

The link below is to a great article on which operators/functions can be delegated. In my case I had to split out the month into weeks using the 'Created >= Date(xx,xx,xxxx)' method.

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

Solution: Run on Start up and when the Refresh button was pressed.

ClearCollect(
    stepsLogVar1,
    Filter(
        WellbeingStepsChallenge_StepsLog,
        Created >= Date(
            2020,
            08,
            01
        ) && Created <= Date(
            2020,
            08,
            08
        )
    )
);
ClearCollect(
    stepsLogVar2,
    Filter(
        WellbeingStepsChallenge_StepsLog,
        Created > Date(
            2020,
            08,
            08
        ) && Created <= Date(
            2020,
            08,
            15
        )
    )
);
ClearCollect(
    stepsLogVar3,
    Filter(
        WellbeingStepsChallenge_StepsLog,
        Created > Date(
            2020,
            08,
            15
        ) && Created <= Date(
            2020,
            08,
            22
        )
    )
);
ClearCollect(
    stepsLogVar4,
    Filter(
        WellbeingStepsChallenge_StepsLog,
        Created > Date(
            2020,
            08,
            22
        ) && Created <= Date(
            2020,
            08,
            29
        )
    )
);
ClearCollect(
    stepsLogVar5,
    Filter(
        WellbeingStepsChallenge_StepsLog,
        Created > Date(
            2020,
            08,
            29
        ) && Created <= Date(
            2020,
            08,
            31
        )
    )
);
ClearCollect(
    CombinedStepsVar,
    stepsLogVar1,
    stepsLogVar2,
    stepsLogVar3,
    stepsLogVar4,
    stepsLogVar5
);

[RandyHayes]

@tapper 

The only issue with your solution is the Date comparisons like you are doing are non-delegable.  So, your results will be incomplete if your datasource is more than the row limit you have set in your app.

For true delegable date filtering, you need to have your dates in a yyyymmdd text format in your datasource..  This way, if you need to, for example, get all the records from July 2020, you can Filter using startswith on the text date column for "202007".  This is delegable and, as long as your results are within the row limit, will give you accurate results.