This guide was written in 2021-05-04, and so will contain instructions that are no longer correct.

Create a user with the relevant set of permissions

For creating reports, you would ideally have a user with only READ rights. You might also consider having different users for reports that only involve resource data, and reports involving user or circulation data.

The stricter the permissions, the smaller the risk of making mistakes such as allowing access to user data too freely, or accidentally changing or deleting records.

Get credentials for the API request

You need a URL (not the same as the one you see in your browser’s address bar) and an ID for your tenant, as well as a token which is specific to your FOLIO user and holds the information about user permissions.

1. Log into the FOLIO web interface as your user of choice.

2. Press F12 on your keyboard to open your browser’s Developer tools, and click on the Network pane.

3. Refresh the page.

4. In Developer tools, click on a GET request to open the detailed information.

5. You will find the data you need in Host, X-Okapi-Tenant and the X-Okapi-Token under Headers > Request headers.

Open

Test the request query in Postman

Use Postman or another tool for making API requests.

Set up the basics

1. Create a new GET request by clicking New in the top left corner.

Open

2. The new request should look like this. Verify that the method (to the left of the URL field) is GET.

Open

3. In the request URL field, add https//: followed by the Host value you found in Developer Tools

4. Under headers, add the following:

   Open

   

Key X-Okapi-Tenant with the X-Okapi-Tenant value you found in Developer Tools

Key X-Okapi-Token with the X-Okapi-Token value you found in Developer Tools

Specify an API endpoint

To get a list of FOLIO Items, you make a GET request to the inventory/items endpoint.

The following request would return all* existing items:

Open

_{* The API may have a default limit. If the number of items in your Inventory is higher than that limit, you need to use pagination to retrieve all items.}

Specify a query

In order to get only those items which have an item status of Missing, you need to add a CQL (Contextual Query Language) query.

The following request would return a maximum of 100 items with the item status Missing.

Open

This request consists of the following elements:

• A base URL

• The API endpoint /inventory/items

• A “?” which introduces the parameters

• The parameter query, which contains our CQL query

• The parameter limit, which limits the response to 100 items - I added this because, in many cases, you only get 10 objects back if you don’t specify a limit higher than that

The CQL query status.name=="Missing" returns items where status > name exactly matches the string “Missing”.

       "status": {

       "name": "Missing"

     }

• More information about how to use Postman: Send your first API request | Postman Docs

• Information about FOLIO API endpoint and data objects can be found in the FOLIO API Reference documentation: FOLIO Developers | Reference | API documentation

• More information about using CQL with FOLIO: GitHub - folio-org/raml-module-builder: Framework allowing easy module creation based on RAML files

• About CQL in general: CQL: the Contextual Query Language: Specifications (SRU: Search/Retrieval via URL, Standards, Library of Congress)

• About JSON objects in general: W3Schools.com

• One nifty way of learning more about requests and CQL queries can be constructed is to examine the requests in Developer tools while doing things in the FOLIO interface.

Run the query from an Excel document

1. This can be useful if you plan to recreate the same report semi-regularly.

2. In an empty Excel spreadsheet/tab, click Data and select From Web.

Open

3. Select Advanced and fill the following (just like in Postman):

Open

4. Click OK

Design the report in Excel

5. When you saved your “From Web” connection, or if you import data from a JSON file, PowerQuery opens. This is where you can tailor the JSON response into something that is concise and easy to read by selecting which data elements to display.

6. Click on the word List to expand the list of records (one per missing FOLIO item).

   Open

   

7. Once you see the list expanded, click on To Table.

Open

8. Click OK without changing anything:

   Open

   

9. Click the little square with two arrows, in the top right corner of the column header, to select the JSON data elements you want to use as columns.

Open

10.  Make a selection, and click OK. (If you are missing something, you can click Load more to see all existing elements.)

11.  If all rows in a column still display as “Record”, click the double arrows in the header to expand that column. Choose the element you want to include in your table.

Open

12.  Reorder and rename the columns to your heart’s content, and then click Close & load (under the tab Home).

Open

13.  Tada! The data from FOLIO is loaded into a table.

Open

Refresh the data to get a fresh report

Since you’ve saved the connection and query to the FOLIO API, you can at any time make a new request to FOLIO and get fresh data by clicking Refresh all. You can save the document and refresh the data next time you open it.

Open

Update the token

Sometimes you need to update the token used in your query. This can be after a FOLIO version upgrade, or after you’ve added a new permission to your report user. The easiest way to do that is in Power query’s Advanced editor.

1. Open your Excel file and go to Data >  Queries & Connections.

2. In the Queries & Connections pane the right, double click the query you want to edit.

Open

3. In the Power Query Editor that shows up, choose Advanced Editor.

4. Find and update your token. Use Display Options to wrap text for better view. {{add-new-token-here}}

   Open

   

5. Click Done. Choose Close & Load. Repeat for all your queries.

6. Done till next upgrade.