Using the /query endpoint

If you’ve found this post, its likely that you’ve tried to output some formulas or some complex information via the /records or another API endpoint, but found that you can’t access them – that’s correct, the /records endpoint doesn’t retrieve formulas, only the native values stored in the database.

That’s where the /query endpoint comes in very useful, as this gives you 100% flexibility on the data you output. So you can format values in exactly the way you wish, hide any unneeded values, plus even drill down into subtables to retrieve more than just the fields from the record you’re on.

Example, you have a table called “Invoices” that contains “Invoice Line Items”. Each Invoice is linked to a “Customer”. The invoice total is a formula field that sums the line total of each “Invoice Line Item[s]”.

The customer’s ID is 25, which we’ll use in the query

select Invoices[Customer = 25].{
  id: number(Id),
  invoiceNumber: 'Invoice Number',
  date: format('Invoice Date',"DD/MM/YYYY"),
  dueDate: format('Due Date',"DD/MM/YYYY"),
  total: sum('Invoice Line Items'.'Line Total')
}

The result of this will be a json object, simlar to the /records endpoint but will include the data in the exact format specified:

[
  {
    id: 1,
    invoiceNumber: "INV-001",
    date: "01/01/2022",
    dueDate: "31/01/2022",
    total: "495.00"
  },
  {
    id: 5,
    invoiceNumber: "INV-005",
    date: "01/02/2022",
    dueDate: "28/02/2022",
    total: "6000.00"
  },
  {
    id: 42,
    invoiceNumber: "INV-042",
    date: "14/02/2022",
    dueDate: "14/03/2022",
    total: "125.55"
  }
]