Fortnox API SQL documentation

jonshubeli · 19 Augusti 2021 05:24

Use the powerful SQL language to query all your Fortnox data, available in Fortnox API.

You need an active subscription from enhanza.se/fortnox to access these resources.

Fortnox API Resources

These tables are a direct reflection of the Fortnox API, field by field.

Dataset in Google Bigquery: enhanza-elt.fortnox_api.
(Read more about all Fortnox datasets)

Available Fortnox resources

accounts
articles
budgets
company_settings
cost_centers
customers
financial_years
invoices
labels
orders
pricelists
prices
projects
stockbalance
stockpoints
supplier_invoices
suppliers
voucher_series
vouchers
vouchers_duplicates
warehouse_stockbalance
warehouse/stockpoints-v1

Do you miss any resources? Contact us. We add new on customer request.

SQL Queries

Budget

Budget in Fortnox is set per month in format “yyyyMM”, e.g. “202210”. But there are multiple reasons why you would like it in date format:

Map and compare with transactions like vouchers, invoices and orders.
Better work with the date filter in your favourite BI tool.

This is our suggested approach. Convert budget date format from “yyyyMM” to “yyyy-MM-DD”.

select parse_date("%Y%m",Month)
from `enhanza-elt.fortnox.budgets`
select last_day(parse_date("%Y%m","202201")) as Month

Then make sure all dates in the report are converted to the last date in that month using SQL statement last_day(). That will secure that only complete month are shown in your reports.

# Budget
select last_day(
   parse_date("%Y%m",Month)
   )
from `enhanza-elt.fortnox.budgets`

# Vouchers
select last_day(TransactionDate) as Month
from `enhanza-elt.fortnox.vouchers`

Remember to always create a date array as timeline backbone, so your reports always contain all months.

Financial Years

Working with financial years in Fortnox could be tricky. One could be fooled to think that the ID is numbered in chronologic order, but they aren’t.

To ease your analysing we have suggest a query that

creates a yearly counter where current year is 0, previous is -1 and so on.
adds a one value in format yymmdd-yymmdd.

WITH
  pre AS (
  SELECT
    CASE -- Order Financial Year by date
      WHEN FromDate>CURRENT_DATE() THEN 0
    ELSE
    -1
  END
    * ROW_NUMBER() OVER (PARTITION BY OrgId ORDER BY fromdate DESC) AS rn,
    *
  FROM
    `enhanza-elt.fortnox_api.financial_years` )
SELECT
  OrgId,
  Id Year,
  CASE
    WHEN rn = 0 THEN 99 -- Future Fy
    WHEN rn <> 0 THEN rn +1
  ELSE
  NULL
END
  AS FyCounter,
FromDate,
ToDate,
concat(format_timestamp("%y%m%d", FromDate),"-",format_timestamp("%y%m%d", ToDate)) FinancialYear,
FROM
  pre

Example result

Invoices

The following examples for invoices also applies to quotes and orders.

Invoice rows

The InvoicesRows is the table on an invoice in which the articles is listed. One invoice can contain multiple articles (InvoicesRows) and that must be considered during aggregation (e.g. SUM).

SELECT
  i.InvoiceDate,
  SUM(r.price) AS Price
FROM
  `enhanza-elt.fortnox_api.invoices` i,
  UNNEST(InvoiceRows) r
GROUP BY
  i.InvoiceDate

Accounts receivable

It’s important to keep track of unpaid invoices. This query helps you do just that. It adds these two extra fields:

Due. Is the invoice paid (true/false)?
DueStatus. Is it sent and not paid - how long since it was paid or should been paid?

SELECT
  DocumentNumber,
  InvoiceDate,
  DATE(DueDate) DueDate,
  IF (DueDate < current_date, TRUE, FALSE) Due,
  CASE
    WHEN Sent IS FALSE THEN "NotSent"
    WHEN Sent IS TRUE
    AND Balance = 0 THEN "Paid"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate BETWEEN CURRENT_DATE()
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 15 day)
      OR DueDate BETWEEN CURRENT_DATE()
      AND DATE_ADD(CURRENT_DATE(), INTERVAL -15 day)
    ) THEN "0-15d"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL 16 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 30 day)
      OR DueDate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 16 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 30 day)
    ) THEN "16-30d"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL 31 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 45 day)
      OR DueDate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 31 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 45 day)
    ) THEN "31-45d"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL 46 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 60 day)
      OR DueDate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 46 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 60 day)
    ) THEN "46-60d"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL 61 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 90 day)
      OR DueDate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 61 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 90 day)
    ) THEN "61-90d"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL 61 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 90 day)
      OR DueDate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 61 day)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL 90 day)
    ) THEN "90-120d"
    WHEN Sent IS TRUE
    AND Balance <> 0
    AND (
      DueDate > DATE_ADD(CURRENT_DATE(), INTERVAL 121 day)
      OR DueDate > DATE_SUB(CURRENT_DATE(), INTERVAL 121 day)
    ) THEN ">120d"
    ELSE "[calc_error]"
  END DueStatus,
  Reminders,
  DATE(LastRemindDate) LastRemindDate,
FROM
  `enhanza-elt.fortnox_api.invoices`
ORDER BY
  DocumentNumber DESC

Example result

Currencies and Discount

To convert non SEK currency, amounts must be multiplied with the currency rate.
Fortnox sets this rate automatically per the Invoice Date. For SEK amount, the rate is 1.

To adjust for discount, procent or amount, use the suggested IF statements below.

This query gives the price per Invoice row, in SEK and after discount. Tax is not included.

SELECT
  SUM(r.DeliveredQuantity * r.price * i.CurrencyRate *
  IF --if any % discount rows
    (DiscountType = "PERCENT",
      1-r.Discount/100,
      1) 
    -
  IF --if any amount discount rows
    (DiscountType = "AMOUNT",
      r.Discount,
      0) 
    ) price
FROM
  `enhanza-elt.fortnox_api.invoices` i,
  UNNEST(InvoiceRows) r

Vouchers

Balance

To retrieve balance during aggregation.

SELECT
  SUM(r.Credit-r.Debit) AS Balance
FROM
  `enhanza-elt.fortnox_api.vouchers` v,
  UNNEST(VoucherRows) r

General guides

Generate Date array

Sometimes you need the result to show all dates, not only those that happened to have a record on it. In these cases you can use FinancialYear and have the SQL statement GENERATE_DATE_ARRAY() generate the date array.

select
  a as Date
from
  `enhanza-elt.fortnox_api.financial_years`
  cross join UNNEST(
    GENERATE_DATE_ARRAY(FromDate, ToDate, INTERVAL 1 DAY)
  ) a
order by
  Date

Exclude Cancelled

Often you want your query to ignore cancelled/removed (makulerade) items. This field is Boolean can be called “Cancelled” (quotes, orders, invoices), “Removed” (vouchers) or Active (accounts).

SELECT *
from `enhanza-elt.fortnox_api.vouchers`
where r.removed is not true

Multiple organisations

You can have access to multiple organisations in Fortnox. Use the initial field OrgId in all resource tables to filter on organisation number.

Filter on certain organisations by applying WHERE statement.

SELECT *
FROM
  `enhanza-elt.fortnox_api.invoices`
WHERE OrgId = "1234567890"

Join with table company_settings to get organisation information like company name.

SELECT
  i.OrgId,
  s.Name
FROM
  `enhanza-elt.fortnox_api.invoices` i
LEFT JOIN
  `enhanza-elt.fortnox_api.company_settings` s
ON
  i.OrgId = s.OrgId