University of Wisconsin–Madison

Towards a Better Finance API: A New Style of Filtering

During our July 7 sprint review, we discussed how filter query parameters work on the new internalServiceProviders endpoint and the changes that have been made. Since then, we have finalized their design and are ready to announce how things will work going forward, how it compares to the previous state of affairs, and what these changes will entail for your integrations.

(If you just want to see how filters will work going forward, check out the Filters documentation, which was updated in advance of this blog post.)

Traditional (“Old Style”) Finance API Queries

To show how things currently work (and fail to work), consider the following queries:

GET /finance/ledgerAccounts?filter[name]=Petty Cash,Investments
GET /finance/ledgerAccounts?filter[name]=ENDS_WITH:Cash,Petty Cash,Investments

The first query works as you would expect, finding ledger accounts with a name exactly matching “Petty Cash” or “Investments” (case-insensitively, of course). But how should the second query work? Let’s consider the possibilities:

  • Find all ledger accounts whose name ends with “Cash”, “Petty Cash”, or “Investments”.
  • Find all ledger accounts whose name ends with “Cash”, exactly matches “Petty Cash”, or exactly matches “Investments”.
  • Find all ledger accounts whose name ends with “Cash,Petty Cash,Investments”.

The first option has a certain logic to it, though it precludes the ability to use multiple operators (i.e. you could not include a STARTS_WITH and ENDS_WITH in the same filter query parameter instance), and the second also has a logic to it, with greater flexibility in specifying operators, while the third option doesn’t line up well with the behavior of the first query and is even less flexible than the first option.

But as it stands today, none of these three options is what actually happens. Instead, if there are any commas in the filter query parameter instance, you can only do literal matches. That is, as currently implemented, the second query would find ledger accounts whose name exactly matched “ENDS_WITH:Cash”, “Petty Cash”, or “Investments”.

Clearly, this is not ideal, but what’s worse is what happens when you specify the same attribute in multiple filter query parameter instances. According to what we previously advertised on our filter documentation, this query should be an exclusive date range:

GET /finance/projects?filter[endDate]=GREATER_THAN:2025-03-31&filter[endDate]=LESS_THAN:2025-05-01

However, multiple filter query parameter instances for the same attribute are not actually evaluated, only the last one is. That is, this query will discard the first “filter[endDate]” query parameter and only use the second, so this query would only find projects whose endDate preceded 2025-05-01. And you cannot work around this by specifying multiple ordinal operators in the same filter query parameter instance, as the following query will produce an error:

GET /finance/projects?filter[endDate]=GREATER_THAN:2025-03-31,LESS_THAN:2025-05-01
{
  "errors": [
    {
      "detail": "2025-03-31,LESS_THAN:2025-05-01 is not a valid ISO date or datetime."
    }
  ]
}

Finally, not all attributes can be filtered on. While some attributes may have little reason to be filtered on, others do have good reasons, but they were just never enabled for querying. For instance, the following query will produce an error that the filter query parameter “referenceId” is invalid:

GET /finance/sponsors?filter[referenceId]=123456

This behavior occurs across most of the Finance API’s routes, with the exceptions being those which use the new query parameter parser, such as internalServiceProviders. But although the behaviors described above may be buggy and the queryable attribute restrictions may be annoying, the way filter query parameters were intended to work isn’t a bad idea, and that leads us to how things will work moving forward.

The New Style of Querying

Consider the following query:

GET /finance/internalServiceProviders?filter[lastModified]=2025-03-01,GREATER_THAN:2025-03-01
&filter[lastModified]=2025-04-01,LESS_THAN:2025-04-01
&filter[internalCatalogs.id]=STARTS_WITH:UWMSN
&filter[internalCatalogs.id]=CONTAINS:DOIT
&filter[internalCatalogs.name]=CONTAINS:API,CONTAINS:IICS

With our new query parameter parser, each value on a filter query parameter instance gets grouped with a logical OR operator. Filter query parameter instances targeting the same resource get combined with a logical AND, with an additional AND being used to combine all the resource groups in a query. This means the above query will be evaluated as follows:

  • Find all internal service providers matching all of the following criteria:
  • (lastModified == 2025-03-01 OR lastModified > 2025-03-01) AND (lastModified == 2025-04-01 OR lastModified < 2025-04-01)
    • Simplified, this evaluates to: (2025-03-01 <= lastModified <= 2025-04-01)
  • At least one entry in internalCatalogs matching all of the following criteria:
    • (id like 'UWMSN*') AND (id like '*DOIT*')
    • (name like '*API*') OR (name like '*IICS*')

Operator Isolation

One of the biggest changes here is that each value specified in a filter is evaluated in isolation, so you can both use special operators (like STARTS_WITH) and match against multiple values, including multiple values with special operators on the same attribute.

Logical ANDing for Attributes

Another significant change relates to using multiple filters on the same attribute. Rather than only using one (as our “old style” routes currently do, just plucking the last instance) or ORing together all values and special operators across all filter query parameter instances for a given attribute, everything within the a given filter query parameter instance will be OR’d, with an AND combining all of these OR groups across filter query parameter instances. Using this, we retain the ability to filter for an attribute matching at least 1 value and add the ability to filter for an attribute that matches multiple conditions.

With this AND vs OR behavior in mind, the lastModified query may seem odd, but it’s there to show how you would combine ordinals with exact matches. For instance, if your users expect date ranges to be inclusive but you do not want to have to determine how many days a given month has when adapting that to exclusive range operators (especially for February), you could just insert basic equality values into your query to DIY greater than or equal and less than or equal operators.

Query ALL the Attributes (Even the Nested Ones)!

Finally, a change which this query hints at is: whenever possible, every attribute will be queryable, and if an attribute is nested within an array of objects on a parent resource (like “internalCatalogs” on internalServiceProviders or “billToContacts” on externalCustomers), you will be able to query it using dot notation. Furthermore, these nested attribute queries will be grouped together using a logical AND operator, so that a record will only be returned if at least one of its sub-resources matches all of the filter query parameters on that sub-resource.

That is, if a given internalServiceProvider has 2 entries in internalCatalogs, one which only matches the “internalCatalogs.id” filter condition and another which only matches the “internalCatalogs.name” filter condition, it will not be returned; at least 1 entry must match both. And if an internalServiceProvider has no entries in internalCatalogs, it will not be returned.

Range Operators for Strings

In the past, both old- and new-style endpoints did not support range operators (GREATER_THAN, LESS_THAN) on string values, and the way this played out was inconsistent:

# Old-style endpoints
GET /finance/programs?filter[name]=GREATER_THAN:UWMSN
=> HTTP 200, no results (attempted to do a literal match for name="GREATER_THAN:UWMSN")
# New-style endpoints
GET /supplierInvoices?filter[company]=GREATER_THAN:UWMSN
=> HTTP 400, invalid operator for string field

Now, string fields support range operators, and like regular matches, are case-insensitive, with character sort order and equivalence dictated by the Unicode Collation Algorithm version 9.0.0. As such, range operators on strings can generally be thought of as being accent-insensitive and case-insensitive, rather than a strict ordinal comparison.

Note that range operators will not match null values, regardless of what direction you try to point the range in. Instead, if you need to match null values, you can just provide an empty value as detailed below:

Matching Null Values

Previously, there was no way to match null values, and how a blank value was treated varied, with the behavior being undefined and having little consistency:

# Old-style endpoints
GET /finance/awards?filter[lastModified]=
=> Error 400, invalid date/datetime format
GET /finance/costCenters?filter[active]=
=> Error 400, invalid boolean
GET /finance/programs?filter[piId]=
=> Matches nothing, not even programs with a null piId
GET /finance/programs?filter[piName]=
=> Matches nothing, not even programs with "" (empty string) piName

# New-style endpoints, prior to 2026-07-28
GET /finance/billingSchedules?filter[awardId]=
=> Matches nothing because awardId is always either present (non-empty string) or null
GET /finance/billingSchedules?filter[scheduleDistributionMethod]=
=> Matches all billingSchedules with "" (empty string) scheduleDistributionMethod
GET /finance/billingSchedules?filter[lastModified]=
=> Error 400, invalid date/datetime format
GET /finance/billingSchedules?filter[prepaidConsumptionStatus]=
=> Error 400, invalid boolean
GET /finance/supplierInvoices?filter[amountDue]=
=> Matches all supplierInvoices with amountDue=0.0

New-style endpoints now treat blank values (where the entire value, including special operators, is an empty string) as a special case: if the field is a string, match “” (empty string) or null; for all other field types, match null.

This special value can also be combined with other values and operators:

GET /finance/internalServiceProviders?filter[lastModified]=2025-03-01,GREATER_THAN:2025-03-01
&filter[lastModified]=2025-04-01,LESS_THAN:2025-04-01
&filter[active]=,false
&filter[description]=STARTS_WITH:UWMSN,
&filter[fundId]=,FD0100
&filter[internalCatalogs.id]=STARTS_WITH:UWMSN
&filter[internalCatalogs.id]=CONTAINS:DOIT

The above query will be evaluated as follows:

  • Find all internal service providers matching all of the following criteria:
  • (lastModified == 2025-03-01 OR lastModified > 2025-03-01) AND (lastModified == 2025-04-01 OR lastModified < 2025-04-01)
    • Simplified, this evaluates to: (2025-03-01 <= lastModified <= 2025-04-01)
  • (active is null OR active == false)
  • (description like 'UWMSN*' OR description is null OR description == '')
  • (fundId is null OR fundId == '' OR fundId == 'FD0100')
  • At least one entry in internalCatalogs matching all of the following criteria:
    • (id like 'UWMSN*') AND (id like '*DOIT*')

Filters Dictate Scope, Not Content

When querying attributes on nested resources, like “internalCatalogs.id” on internalServiceProviders and “billingInstallments.amount” on billingSchedules, the resources returned are those which match the filter query parameters, but non-matching sub-resources are unaffected by them. To illustrate this, consider the following query:

GET /finance/externalCustomers?filter[active]=true
&filter[address.city]=Madison,Milwaukee,Oshkosh
&filter[address.stateCode]=WI

This query is looking for active external customers who have at least 1 address in Madison, Milwaukee, or Oshkosh, WI. This means:

  • Customer CUS0000001 has two addresses: one in Oshkosh, Nebraska and the other in Madison, Minnesota. They would not be returned by this query since neither address matches the stateCode filter.
  • Customer CUS0000002 has two addresses: one in Madison, Indiana and the other in Green Bay, Wisconsin. They also would not be returned by this query since neither address matches both the city and stateCode filters.
  • Customer CUS0000003 has three addresses: one in Madison, Indiana, another in Minneapolis, Minnesota, and the last in Oshkosh, Wisconsin. This customer will be returned, along with all three addresses.

The reason CUS0000003 will have both their matching and non-matching addresses returned is because the filters define the population, the scope of the query (the “who”); they do not dictate the content of the response (the “what”). The Finance API returns the complete state of the resources that fall into the query’s scope, not a view tailored to the filters provided.

Note that this behavior is the same regardless of whether an endpoint uses old-style or new-style querying, which is to say nothing is changing here. The reason for mentioning it is because this behavior was previously undefined, though in the past it didn’t really matter since very few nested attributes could be queried on. But since this is changing (see “Query ALL the Attributes (Even the Nested Ones)!” above), it seemed prudent to formally define the expected behavior.

Escaping Characters (Future Work)

In a future sprint, we will add character escaping so that you can query for values like contains:foobar or foo,bar without invoking the CONTAINS operator or searching for multiple values.

We will enable this through the use of backslashes, so for the examples above, you could use them in a query such as:

filter[someString]=contains\:foobar,foo\,bar

To search for a literal backslash, you can escape it as well. For instance, if you wanted to find a resource containing a literal backslash, you could do so with this query:

filter[someString]=CONTAINS:\\

When we take on this character escaping functionality, we will provide 1 sprint heads up since, although it is a very minor change, seeing as the backslash has no special value today, it would break any queries currently using it.

Change Guidance & Rollout

Despite the large number of changes listed here, there are only two that will be truly breaking.

The first will be escaping characters: when implemented, any integrations querying with literal backslashes will need to start escaping them on new-style endpoints. When we implement this, we will provide 1 sprint advance notice to give adequate time to update any existing integrations that may rely on this.

The second will be the elimination of the “special” filter attributes on the /finance/awards endpoint. On this endpoint, there exists the “coInvestigatorId” and “coPrincipalInvestigatorId” fields. When the endpoint is migrated over to new-style querying, any integrations filtering on them will need to switch to dot-notated fields (“coInvestigator.id” and “coPrincipalInvestigators.id”, respectively), as the old special attributes will no longer be recognized. We will also give at least 1 sprint advance notice before making this change.

/finance/costCenters is the only other old-style endpoint with a nested resource (“leadershipRoleAssignees”), and it lacks the ability to query on that nested resource, so switching to new-style filters will not be a breaking change for it. This endpoint does have a special filter for querying the cost center hierarchy (“descendantOf”), and we will continue to support it or an equivalent (if we go with an equivalent, we will provide at least 1 sprint advance notice) when we eventually migrate the endpoint over to new-style querying.

The rest of these changes are not considered breaking because we are just making filters work the way the spec describes (e.g. the logical AND behavior for date ranges) and formally defining the previously undefined behavior surrounding multiple values, especially those with special operators (e.g. multiple STARTS_WITH values on the same filter query parameter instance). If your integrations were inadvertently using broken queries, take note that these changes may cause the output of the Finance API to differ from what you expect.

The following resources use the “new style” of filter query parameter parsing and have implemented these changes as of the time of publication:

As we convert more endpoints over to new-style querying, in addition to discussing this during our sprint reviews, we will also note them on the Documentation and Endpoints pages by appending “(NSQ)” to their listings. In addition, since endpoints implementing new-style querying allow any attribute to be queried on, those endpoints OpenAPI specifications can usually be identified by the length of the filter list alone.