Thursday, June 3, 2021

Getting More Than 2000 Items In PowerApps

 Hello Friends,

Welcome back with another post on Power Automate. Today, we will try to read more than 2000 records in PowerApps from SharePoint list. The reason for this article is that, PowerApps, by default read only first 500 records from SharePoint. This can be extend up to 2000 through File >> Settings >> General >> Data row limit. Beyond 2000, PowerApps doesn't support by default. Also, PowerApps do not provide any looping functionality like For loop, For-Each loop, Do-While loop, Do-Until loop. So, we have to play some tricks to achieve this. However this could not be said as best practice but in view of non availability of any looping functionality, we have to use it sometimes to achieve the desired result. There are lots of similar posts / blogs / example available online. All those are also good, The only difference between them any my example is that, I had managed to reduce the number of calls to SharePoint in case if there are no records returned in the previous call. Let's see how-

First, I will give you the scenario and after then we will perform the activity. I have a list named "DailyTaskSheetForPowerApps" where each member of my team put his/her daily task entry. On an average daily around 25 entries got inserted in list which in around 500 per month (averaging 20 working days in a month) or 6000 per year. Now if I want to check the task details of any team member for 2 years back, it would not be possible. So what I will do is-

  1. Create a numeric column named "FormattedInDate" which will hold the task date in YYYYMMDD format.
  2. Create this column as Index column through List Settings >> Indexed columns >> Create a new index.
  3. Now in PowerApps, on button click, I will perform below activity-
    1. Define a variable "varRowsCountInPreviousCall" and set it to 0. This variable will help us to check if my previous call attempt returned records or not.
    2. Define a temporary blank collection "DailyTaskSheetAllItemsCollectionTemp" which will hold the result returned for each call.
    3. Define a blank collection "DailyTaskSheetAllItemsCollection". It will hold all the result returned by each call.
    4. (Optional) Define a variable "varExecutionParts" to show how many execution calls were went to SharePoint.
  4. The code is-
    1. UpdateContext({varRowsCountInPreviousCall: 0});
ClearCollect(
    DailyTaskSheetAllItemsCollectionTemp,
    []
);
ClearCollect(
    DailyTaskSheetAllItemsCollection,
    []
);

UpdateContext({varExecutionParts:""});
  5. Now, I have to bring all records on the basis of "FormattedInDate".
  6. I had set "Data row limit" as 2000 already as mentioned above.
  7. My first call will be for the first 2000 records from 2000 days back to today and for current logged in user.
  8. The code is-
    1. //Execution #1
ClearCollect(
    DailyTaskSheetAllItemsCollection,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 20151212 && FormattedInDate <= 20210603 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollection)});
UpdateContext({varExecutionParts:"#1"});
    2. Here I am making a call to SharePoint for return data from 12-Dec-2015 to 03-Jun-2021 for current logged in user.
    3. The result returned is counted and count of total number of rows are saved in "varRowsCountInPreviousCall".
    4. The result set is directly added in "DailyTaskSheetAllItemsCollection" as this is my first call.
    5. Lastly, I had updated the variable "varExecutionParts" with "#1" as this is my first call.
  9. Now I will make my second call for next 2000 records from 21-Jun-2010 to less than 12-Dec-2015 for current user.
  10. The code is-
    1. //Execution #2
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 20100621 && FormattedInDate < 20151212 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #2")});
);
  11. See, here I am first check if my previous call has returned the records. If yes, I am making my call to SharePoint for next 2000 records. This resultset is stored in my temporary collection "DailyTaskSheetAllItemsCollectionTemp".
  12. The result returned is counted and count of total number of rows are saved in "varRowsCountInPreviousCall".
  13. The result set is now added in "DailyTaskSheetAllItemsCollection".
  14. Then reset the temporary collection "DailyTaskSheetAllItemsCollectionTemp' to blank.
  15. Lastly, I had updated the variable "varExecutionParts" with appending " #2" as this is my second call.
  16. Now, the steps 9 - 15 have been repeated for next 3 times for each next 2000 records-
    1. //Execution #3
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 20041229 && FormattedInDate < 20100621 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #3")});
);

//Execution #4
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 19990709 && FormattedInDate < 20041229 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #4")});
);

//Execution #5
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 19940116 && FormattedInDate < 19990709 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #5")});
);
  17. Lastly, I will print the total number of records returned by all my previous calls and the number of iteration being executed.
    1. Notify(
    Concatenate(
        "DailyTaskSheetAllItemsCollection Items Count- ",
        Text(CountRows(DailyTaskSheetAllItemsCollection)), "  ~~  ",varExecutionParts
    ),
    Success
);
  18. My SharePoint list is having total 4793 records in which 15 records were for other users and rest 4778 are of mine.
  19. Let's see the result displayed as notification-
  20. As you can see, I had written 5 iterations. But out of these first 4 calls brought data from SharePoint while the last one was skipped. The reason is that in 4th iteration, the result count was 0 due to which my 5th iteration condition was not met and hence it saved my 1 call to SharePoint.
  21. The complete code is-
    1. UpdateContext({varRowsCountInPreviousCall: 0});
ClearCollect(
    DailyTaskSheetAllItemsCollectionTemp,
    []
);
ClearCollect(
    DailyTaskSheetAllItemsCollection,
    []
);

UpdateContext({varExecutionParts:""});

//Execution #1
ClearCollect(
    DailyTaskSheetAllItemsCollection,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 20151212 && FormattedInDate <= 20210603 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollection)});
UpdateContext({varExecutionParts:"#1"});

//Execution #2
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 20100621 && FormattedInDate < 20151212 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #2")});
);

//Execution #3
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 20041229 && FormattedInDate < 20100621 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #3")});
);

//Execution #4
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 19990709 && FormattedInDate < 20041229 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #4")});
);

//Execution #5
If(varRowsCountInPreviousCall > 0,
Collect(
    DailyTaskSheetAllItemsCollectionTemp,
    Filter(
        DailyTaskSheetForPowerApps,
        FormattedInDate >= 19940116 && FormattedInDate < 19990709 && 'Created By'.Email = User().Email
    )
);
UpdateContext({varRowsCountInPreviousCall: CountRows(DailyTaskSheetAllItemsCollectionTemp)});
Collect(DailyTaskSheetAllItemsCollection,DailyTaskSheetAllItemsCollectionTemp);
ClearCollect(DailyTaskSheetAllItemsCollectionTemp,[]);
UpdateContext({varExecutionParts:Concatenate(varExecutionParts, " #5")});
);

Notify(
    Concatenate(
        "DailyTaskSheetAllItemsCollection Items Count- ",
        Text(CountRows(DailyTaskSheetAllItemsCollection)), "  ~~  ",varExecutionParts
    ),
    Success
);
  22. As, currently there is no looping functionality provided by PowerApps, we have to use this workaround.
  23. The next question arises is that, here I am using Date Stamp to filter the data but the date changes every day then how can we manage.
  24. The answer is, when you are creating a list is SharePoint-
    1. Create one more column named "ReplicaID" of numeric type.
    2.  Create this column as Index column through List Settings >> Indexed columns >> Create a new index.
    3. Create a "Automated cloud flow" in Power Automate for "When an item is created (SharePoint)".
    4. In this flow, choose "Update item" action and update the ReplicaID with ID (ItemID of that list item) of list item.
    5. Now, in PowerApps, use the above logic. the only change you need to do is start filtering on this ReplicaID from value 1 to 2000, then 2001 to 4000 and so on up to a certain number of Item ID that you assume to be sufficient for next N number of years.
  25. This way, we can get all records from SharePoint in PowerApps.
  26. You can also customize the DateStamp variables to make it dynamic and assign them the value in YYYYMMDD format starting from current date and then subtracting 2000 from the first variable and so on.
  27. Let me show you, how-
    1. Define a variable varDateNow and assign the value of Now() to it using UpdateContext.
      1. UpdateContext({varDateNow:Now()});
    2. Now convert it to date stamp in format "YYYYMMDD" as below and assign to another variable varDateStamp01-
      1. UpdateContext(
    {
        varDateStamp01: Value(
            Concatenate(
                Text(Year(varDateNow)),
                If(
                    Month(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Month(varDateNow))
                    ),
                    Text(Month(varDateNow))
                ),
                If(
                    Day(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Day(varDateNow))
                    ),
                    Text(Day(varDateNow))
                )
            )
        )
    }
);
    3. Now subtract 2000 days from varDateNow-
      1. UpdateContext({varDateNow:DateAdd(varDateNow,-2000,Days)});
    4. Repeat Step 26 >> 2 >> 1 to convert it to date stamp in format "YYYYMMDD" and assign to variale varDateStamp02-
      1. UpdateContext({varDateNow:DateAdd(varDateNow,-2000,Days)});
UpdateContext(
    {
        varDateStamp02: Value(
            Concatenate(
                Text(Year(varDateNow)),
                If(
                    Month(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Month(varDateNow))
                    ),
                    Text(Month(varDateNow))
                ),
                If(
                    Day(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Day(varDateNow))
                    ),
                    Text(Day(varDateNow))
                )
            )
        )
    }
);
    5. Repeat Steps 26 >> 3 and 26 >> 4 two times for assign value to varDateStamp03 and varDateStamp04 in similar manner.
    6. Not print the values using Notify-
      1. Notify(
    Concatenate("V01:- ",
        Text(varDateStamp01),
        "  ~~  ",
        "V02:- ",
        Text(varDateStamp02),
        "  ~~  ",
        "V03:- ",
        Text(varDateStamp03),
        "  ~~  ",
        "V04:- ",
        Text(varDateStamp04)
    ),
    Success
);
    7. The output will be-


    8. If you compare, values in both logic are same-
      1. The process defined earlier in the post where we had put static values in filter.
      2. The process defined just above where we had made these values dynamically.
    9. The complete code for this dynamic value set-
      1. UpdateContext({varDateNow:Now()});
UpdateContext(
    {
        varDateStamp01: Value(
            Concatenate(
                Text(Year(varDateNow)),
                If(
                    Month(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Month(varDateNow))
                    ),
                    Text(Month(varDateNow))
                ),
                If(
                    Day(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Day(varDateNow))
                    ),
                    Text(Day(varDateNow))
                )
            )
        )
    }
);


UpdateContext({varDateNow:DateAdd(varDateNow,-2000,Days)});
UpdateContext(
    {
        varDateStamp02: Value(
            Concatenate(
                Text(Year(varDateNow)),
                If(
                    Month(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Month(varDateNow))
                    ),
                    Text(Month(varDateNow))
                ),
                If(
                    Day(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Day(varDateNow))
                    ),
                    Text(Day(varDateNow))
                )
            )
        )
    }
);

UpdateContext({varDateNow:DateAdd(varDateNow,-2000,Days)});
UpdateContext(
    {
        varDateStamp03: Value(
            Concatenate(
                Text(Year(varDateNow)),
                If(
                    Month(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Month(varDateNow))
                    ),
                    Text(Month(varDateNow))
                ),
                If(
                    Day(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Day(varDateNow))
                    ),
                    Text(Day(varDateNow))
                )
            )
        )
    }
);

UpdateContext({varDateNow:DateAdd(varDateNow,-2000,Days)});
UpdateContext(
    {
        varDateStamp04: Value(
            Concatenate(
                Text(Year(varDateNow)),
                If(
                    Month(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Month(varDateNow))
                    ),
                    Text(Month(varDateNow))
                ),
                If(
                    Day(varDateNow) < 10,
                    Concatenate(
                        "0",
                        Text(Day(varDateNow))
                    ),
                    Text(Day(varDateNow))
                )
            )
        )
    }
);



Notify(
    Concatenate("V01:- ",
        Text(varDateStamp01),
        "  ~~  ",
        "V02:- ",
        Text(varDateStamp02),
        "  ~~  ",
        "V03:- ",
        Text(varDateStamp03),
        "  ~~  ",
        "V04:- ",
        Text(varDateStamp04)
    ),
    Success
);
  28. Please remember that- 
    1. Getting all records from SharePoint in PowerApps may lead to performance degrade (if number of records are quite large).
    2. Every call to SharePoint takes some time so as the number of calls gets increase, it will take more time to get the data loaded and hence will put your action in hang mode.
  29. So, please try to avoid as much as possible to get all records in PowerApps. Review your process to avoid such workarounds.
  30. Please read Delegation in PowerApps to understand why I had not used the default ID column for filtering and created a custom column to achieve the goal. The column ID is not delegable. 
  31. That's all for this topic.

With this, I am concluding this post.
Happy Coding !!!
Will see you again with some new topics.

Stay Safe !
Stay Healthy !

at
Labels: , ,

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)