Filtering collection resources to get the entries you need

The various collection resources (e.g. /{accountPathComponent}/api/order/) support filtering using a query parameter. The filter is placed in the query parameter as Base64 encoded JSON. The JSON is a single object with a single key corresponding to the key in the result object that should be filtered. The value is the query object is an array with two elements corresponding to the lower and upper bounds of the range to filter. If either bound is null, then the range to filter is unbounded on that side. The range is closed on both ends (the result includes elements that match either the lower or upper bound). To filter by a single value, set the range to have that value as both the lower and upper bound.

Important Notes

• The system is optimized for filtering by lastUpdatedDate. There is one collection, the inventoryitem collection, that does not support filtering.
• The lastUpdateDate filter must be a range: for example: lastUpdatedDate=["2022-01-01","2022-01-05"]

Some collections that you access may return more than 120,000 entities from the database.

• Please note filtering large collections (greater than 120,000 entities) using the resource endpoints is not supported without a lastUpdatedDate filter because the results will not be accurate. The lastUpdatedDate filter always works, but the response is limited to 120k entities. Other filtering is supported as long as you have a lastUpdatedDate filter that will reduce the collection size below 120,000 entities.
• The lastUpdatedDate filter is applied first before any other filters. If there are more entities matching the lastUpdatedDate filter than the maximum limit of 120k entities, other filters will only apply to those entities within that limit. That's why you would see other filters having issues when there are more than 120k entities. The limit parameter applies after all filtering.
• When using the lastUpdatedDate , if it doesn't filter results down below the maximum limit (120k), then all the other filters only apply to data within that limited 120K response. Any entities that exist past the 120K entity after the start of the range would be ignored.

Filter Example

• To filter by lastUpdatedDate:
  • Required JSON: {"lastUpdatedDate":["2015-11-19T20:09:57Z",null]}
  • Base 64 encoding: eyJsYXN0VXBkYXRlZERhdGUiOlsiMjAxNS0xMS0xOVQyMDowOTo1N1oiLG51bGxdfQ
  • Full URL: https://app.finaleinventory.com/{accountPathComponent}/api/product/?filter=eyJsYXN0VXBkYXRlZERhdGUiOlsiMjAxNS0xMS0xOVQyMDowOTo1N1oiLG51bGxdfQ

Additional Filters

• To filter for committed sales orders:

  • Required JSON: {orderTypeId:['SALES_ORDER','SALES_ORDER'],statusId:['ORDER_LOCKED','ORDER_LOCKED']}
  • Base 64 encoding: eyJvcmRlclR5cGVJZCI6WyJTQUxFU19PUkRFUiIsIlNBTEVTX09SREVSIl0sInN0YXR1c0lkIjpbIk9SREVSX0xPQ0tFRCIsIk9SREVSX0xPQ0tFRCJdfQ==
  • Full URL: https://app.finaleinventory.com/{accountPathComponent}/api/order/?filter=eyJvcmRlclR5cGVJZCI6WyJTQUxFU19PUkRFUiIsIlNBTEVTX09SREVSIl0sInN0YXR1c0lkIjpbIk9SREVSX0xPQ0tFRCIsIk9SREVSX0xPQ0tFRCJdfQ==

• Important note: If you do not use the optimized lastUpdateDate filter, and only use the statusId filter, the initial fetch of records would truncate at 120,000 rows, and then the statusId filter will be applied to the truncated results.

• Similar filters can be applied to any value that is a scalar type at the top level of the object. Therefore you can't filter on products in an order/shipment, or user-defined fields.

Caching and Performance

• The system is optimized for filtering by lastUpdatedDate. If you experience performance problems requesting large collections, consider caching your collections and only fetch updates using the lastUpdatedDate filter. The collection APIs can respond quicker when you limit the number of entries. Add a limit parameter to the URL with an integer value.

• Full URL for first 1000 products ordered by their lastUpdatedDate: https://app.finaleinventory.com/{accountPathComponent}/api/product/?limit=1000

  • Full URL to query for next 1000 products changed since lastUpdateDate: https://app.finaleinventory.com/{accountPathComponent}/api/product/?limit=1000&filter=eyJsYXN0VXBkYXRlZERhdGUiOlsiMjAxNS0xMS0xOVQyMDowOTo1N1oiLG51bGxdfQ

Product Lookups

• To filter for all product lookups for a specific product by productUrl:

  • Required JSON: {productUrl:['/demo/api/product/10000','/demo/api/product/10000']}
  • Base 64 encoding: eyJwcm9kdWN0VXJsIjpbIi9kZW1vL2FwaS9wcm9kdWN0LzEwMDAwIiwiL2RlbW8vYXBpL3Byb2R1Y3QvMTAwMDAiXX0=
  • Full URL: https://app.finaleinventory.com/{accountPathComponent}/api/scanlookup/?filter=eyJwcm9kdWN0VXJsIjpbIi9kZW1vL2FwaS9wcm9kdWN0LzEwMDAwIiwiL2RlbW8vYXBpL3Byb2R1Y3QvMTAwMDAiXX0=

• To filter for a regular product lookup by scan key:

  • Required JSON: {scanKey:['10000','10000'],scanTypeId:['UNSPECIFIED_TEXT','UNSPECIFIED_TEXT']}
  • Base 64 encoding: eyJzY2FuS2V5IjpbIjEwMDAwIiwiMTAwMDAiXSwic2NhblR5cGVJZCI6WyJVTlNQRUNJRklFRF9URVhUIiwiVU5TUEVDSUZJRURfVEVYVCJdfQ==
  • Full URL: https://app.finaleinventory.com/{accountPathComponent}/api/scanlookup/?filter=eyJzY2FuS2V5IjpbIjEwMDAwIiwiMTAwMDAiXSwic2NhblR5cGVJZCI6WyJVTlNQRUNJRklFRF9URVhUIiwiVU5TUEVDSUZJRURfVEVYVCJdfQ==

• To filter for a range of Finale short codes by scan key:

  • Required JSON: {scanKey:['200001000100','200001000200'],scanTypeId:['INVENTORY_SHORT_CODE','INVENTORY_SHORT_CODE']}
  • Base 64 encoding: eyJzY2FuS2V5IjpbIjIwMDAwMTAwMDEwMCIsIjIwMDAwMTAwMDIwMCJdLCJzY2FuVHlwZUlkIjpbIklOVkVOVE9SWV9TSE9SVF9DT0RFIiwiSU5WRU5UT1JZX1NIT1JUX0NPREUiXX0=
  • Full URL: https://app.finaleinventory.com/{accountPathComponent}/api/scanlookup/?filter=eyJzY2FuS2V5IjpbIjIwMDAwMTAwMDEwMCIsIjIwMDAwMTAwMDIwMCJdLCJzY2FuVHlwZUlkIjpbIklOVkVOVE9SWV9TSE9SVF9DT0RFIiwiSU5WRU5UT1JZX1NIT1JUX0NPREUiXX0=