Concepts: Paging Through Collections

Introduction

Continuing with collections, we will explore how to implement a paging strategy. This is crucial when the total number of records we need to process exceeds the number we receive, either due to hitting the hard page limit of 1,000 records or the “Top” limit specified in the collection command.

What is Paging?

When you invoke a collection command, the response includes an object with a Collection property. This property contains a "page" of data. The response object also includes Top, Skip, and TotalCount properties:

• Top: Indicates the number of items allowed per page.

• Skip: Specifies the current page.

• TotalCount: Shows the total number of records available.

If TotalCount is greater than the number of records in the Collection property, you are viewing only a subset of the overall data (a “page”). In this case, you need to implement a paging strategy to seamlessly navigate through subsequent subsets, or pages, of the collection. Proper paging is essential for performance, ensuring that your script processes all required records efficiently.

With this in mind, let’s setup a situation that requires a paging solution to get through matter records.

Setup & Execution

Create a new script named "Matter collection paging tests" and start with the following body:

$matters = Get-Matters -Top 10

Write-Output $matters | ConvertTo-Json -Depth 1

Execute the script and you'll see something like the following results:

{
  "Collection": [
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable",
    "System.Collections.Hashtable"
  ],
  "Top": 10,
  "Skip": 0,
  "TotalCount": 66
}

Your TotalCount will be different based on how many matters you have in your workspace, but the important thing to point out is that we passed in a Top parameter of 10 to limit the number of records per page that we will see in our collection. The Top parameter tells Agility Blue that you only want this many records returned back to you. If you don’t provide a Top parameter, it will default to 40. The top limit is 1,000 records and exceeding this limit will cause the following error:

[Uncaught Exception] The limit of '1000' for Top query has been exceeded

In our example above, we can see that we have 66 total matters, a top of 10, and a skip of 0. The Skip parameter indicates the number of records that should be skipped and it should be a multiple of the Top parameter. If you would like to think of the data in terms of “pages”, you can determine what page of data you are on by using the following formula: Page = (Skip/Top) ​+ 1. If you need to figure out what value to use for the skip parameter based on a page number, you can use this formula: Top * (Page - 1) = Skip. So, in our example above, if we wanted to retrieve page 3 of data (records 20-29), you would plug in 10 * (3 - 1) = 10 * 2 = 20 so the value of Skip would be 20. Note that the skip is just the 0-based starting index of a set of records to retrieve, so in our example of using 10 records per page, records 0-9 would be a skip of 0, records 10-19 would be a skip of 10, records 20-29 would be a skip of 20, and so on.

In PowerShell, we can implement paging by utilizing a do/while loop where the code simply loops through a block of code until the skip parameter has a number greater than the total count of records. Each loop would represent the next page of records until we’ve gone through all pages. Update your script to look like the following:

$top = 10
$skip = 0
$count = 0

do {
  $matters = Get-Matters -Top $top -Skip $skip

  $count += $matters.Collection.Count

  # Here, we could loop through the collection and process individual matters within
  # this page. For our example, we'll just print out a message indicating what page we're on.
  
  $page = ($skip / $top) + 1
  $totalPages = [Math]::Ceiling($matters.TotalCount / $top)
  
  Write-Output "Currently on page $page of $totalPages pages (encountered $($count) records at $top records per page)"

  $skip += $top
} while ($skip -lt $matters.TotalCount)

Execute the script, and you will see as many lines as you have pages of data that were iterated in the loop:

Currently on page 1 of 7 pages (encountered 10 records at 10 records per page)
Currently on page 2 of 7 pages (encountered 20 records at 10 records per page)
Currently on page 3 of 7 pages (encountered 30 records at 10 records per page)
Currently on page 4 of 7 pages (encountered 40 records at 10 records per page)
Currently on page 5 of 7 pages (encountered 50 records at 10 records per page)
Currently on page 6 of 7 pages (encountered 60 records at 10 records per page)
Currently on page 7 of 7 pages (encountered 66 records at 10 records per page)

Points of Consideration:

• We defined 3 variables outside of the do/while loop that the loop uses:

  • The $top variable defines how many records per loop (page) are retrieved.

  • The $skip variable is updated at the end of each loop that simply adds the $top value so the correct record index is applied. It’s also used to determine when the loop should continue by checking if it’s less than the total count of records in the while() condition.

  • The $count variable is incremented by the matter collection count so we know how many records we’ve encountered

• After we retrieve the matters collection for the current page, we could then further process the individual records within the Collection property by looping through the collection. For example, you may want to check that each matter has a valid Reference value and output the MatterId that violates a naming convention as a way of auditing data. Or perhaps you want to update matters as Active = $false if the LastProjectCreatedOn date is over 90 days ago. There are a lot of possibilities here, but the point is that you now have an effective way of combing through all of the data.

• The $page and $totalPages variables are optional, and not required for paging. We’ve added these to show how you could get the current page and total pages numbers so that information can be included in an output message, which can be useful for debugging.

• Keep in mind that if you have a large collection, you should consider performance implications. For example, you wouldn’t want to apply an event trigger to a script that iterates over thousands of matters each time a matter is updated, or it would take a while for the script to finish and it would seem like the UI is hung up to the end user. In scenarios like this, it’s better to execute a script manually or trigger the script for execution via the API on a schedule.

Conclusion

In this tutorial, you learned how to leverage a paging strategy in an Agility Blue PowerShell script to help you iterate through your collection data. By utilizing a combination of filtering and paging, you can find the most effective way to manipulate collections of data while applying custom logic unique to your workflows.