Contents x
    Concepts: Filtering & Sorting Collections
    • 2 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    Concepts: Filtering & Sorting Collections

    • 2 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article summary

    Introduction

    In this article, we dive into the more intermediate concepts of filtering and sorting through a collection. This page aims to provide a variety of examples on how filters and sorting can be used instead of outlining any specific step-by-step guides. We encourage you to try out some of the examples with whatever collection commands you would like using the following templates as a guide:

    For filtering, provide your filters as a string for the `Filter` input parameter

    PowerShell

    # Template:
# {your_collection_command} -Filter "{your_filter_string}"

# Examples:
$activeClientsCollection = Get-Clients -Filter "Active eq true"

$mattersIn2023Collection = Get-matters -Filter "year(CreatedOn) eq 2023"

$rushProjectsCollection = Get-Projects -Filter "Priority eq 'Rush'"

    For sorting, provide your sorts as a string for the `OrderBy` input parameter

    # Template:
# {your_collection_command} -OrderBy "{your_sort_string}"

# Examples:
$recentFirstClientsCollection = Get-Clients -OrderBy "CreatedOn desc"

$mattersSortedByClientsCollection = Get-matters -OrderBy "ClientName, Name"

$projectsSortedByStatusCollection = Get-Projects -OrderBy "Status"

    You may combine filtering and sorting for the same collection command

    $activeMattersWithRecentFirstCollection = Get-Matters -Filter "Active eq true" -OrderBy "CreatedOn desc"

    Check out the commands and reference section of the documentation to help locate other collection commands. Remember, all collection commands start with the `Get` verb and will be plural.

    Filters Reference

    Below are tables that represents the filtering expressions and how to apply them to a collection command.

    The following table shows comparison operators:

    Operation

    Operator

    Examples

    Equals

    eq

    • Text: "Name eq 'Agility Blue'"

    • Number: "MaterId eq 1"

    • Boolean: "Active eq true"

    • Date: "CreatedOn eq 2023-12-01Z"

    Not equals

    ne

    • Text: "Description ne 'Agility Blue'"

    • Number: "NumberOfTasks ne 0"

    • Boolean: "EnableNotifications ne true"

    • Date: "LastUpdatedOn ne 2023-12-01"

    Greater than

    gt

    • Number: "UnitPrice gt 10"

    • Date: "Date gt 2023-01-01-06:00"

    Greater than or equal to

    ge

    • Number: "TotalPrice ge 1000"

    • CreatedOn ge 2023-01-01-06:00

    Less than

    lt

    • Number: "UnitPrice lt 10"

    • Date: "Date lt 2023-01-01-06:00"

    Less than or equal to

    le

    • Number: "TotalPrice le 1000"

    • CreatedOn le 2023-01-01-06:00

    The following table shows functions that may be applied in a filter expression

    Operation

    Function

    Examples

    Contains

    contains(field, value)

    • Text: "contains(CF_10, 'agility blue')"

    Not contains

    indexof(field, value) eq -1

    • Text: "indexof(Name, 'processing') eq -1"

    Starts with

    startswith(field, value)

    • Text: "startswith(Name, 'produce')

    Ends with

    endswith(field, value)

    • Text: "endswith(Name, 'documents')

    Length

    length(field) expr value

    • Text: "length(Name) lt 10"

    Year

    year(field) expr value

    • Date: "year(CreatedOn) eq 2023"

    Month

    month(field) expr value

    • Date: "month(CreatedOn) eq 1"

    Day

    day(field) expr value

    • Date: "day(CreatedOn) eq 1"

    Hour

    hour(field) expr value

    • Date: "hour(LastUpdatedOn) eq 15"

    Minute

    minute(field) expr value

    • Date: "minute(LastUpdatedOn) eq 30"

    Second

    second(field) expr value

    • Date: "second(LastUpdatedOn) eq 0"

    All of the above operations may be grouped using parenthesis and logical `and` or `or` concatenation. Examples:

    Logical group operator

    Examples

    and

    • Text: "(Name eq 'Processing') and (contains(ProjectComputedTags, 'billable'))

    • Number: "(Quantity lt 10) and (TotalPrice gt 5000)"

    • Boolean: "(Active eq true) and (CF_22 ne null)"

    • Date: "(year(CreatedOn) eq 2023) and (month(CreatedOn) ge 4) and (month(CreatedOn) le 6)"

    or

    • Text: "contains(ProjectComputedTags, 'process data') or contains(ProjectComputedTags, 'produce data')"

    • Number: "(Quantity le 10) or (Quantity ge 100)"

    • Boolean "(CF_22 eq null) or (CF_22 eq false)"

    • Date: "(year(CreatedOn) eq 2022) or (year(CreatedOn) eq 2023)"

    Sorting (Order By) Reference

    Sorting follows the following format:

    field (desc)[, ...field (desc)]

    For example, to sort a field named "Name" in ascending order, you would simply use:

    "Name"

    To sort by the same field, but in descending order:

    "Name desc"

    You can sort by multiple fields by separating the expressions with a comma. For example:

    "Reference, ClientReference"

    Reverse sorting can be applied individually on any field:

    "Reference desc, ClientReference desc"

    Sorting examples on a collection command

    Example: Sort by the reference field on the clients collection command:

    Get-Clients -OrderBy "Reference"

    Example: Sort matter names but keep clients together:

    Get-Matters -OrderBy "ClientName, Name"

    Example: Sort billing entries by highest total price while keeping matters together:

    Get-BillingEntries -OrderBy "MatterName, TotalPrice desc"

    What's Next