Fortnox API SQL documentation

Fortnox integration [guider]
,
#1

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

:information_source: You need an active subscription from Enhanza to access these resources.

Summary

We suggest this hierarchical structure while building your SQL queries.

  1. Fortnox Resources (collect ingredients :chicken:)
    This is your raw data available in Fortnox - field by filed.
  2. Staged resources (preparing ingredients :egg:)
    These queries structure and make more sense of the raw data resources.
  3. Reports (make the omelette :fried_egg:)
    With prepared ingredients, staged resources, it’s easy peasy to complete the omelette.

For each of these steps, we’ll guide you all the way - from collecting the egg to enjoying your tasty omelette.

1. Fortnox Resources

Made available for you by Enhanza in Google Bigquery dataset enhanza-elt.fortnox_api.

Available Fortnox resources

  • accounts
  • articles
  • budgets
  • company_settings
  • cost_centers
  • customers
  • financial_years
  • invoices
  • labels
  • orders
  • projects
  • stockbalance
  • stockpoints
  • supplier_invoices
  • suppliers
  • voucher_series
  • vouchers
  • vouchers_duplicates

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

2. Staged resources

We have prepare (staged) multiple Fortnox resources for you on: enhanza-elt.fortnox_stg.

:information_source: All staged queries are subject for continuously improvement. If static result structure is critical for your use case, copy the query to your own environment or let us created a separate dataset for you.

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

  1. creates a yearly counter where current year is 0, previous is -1 and so on.
  2. 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

fortnox-financial-years-enhanza
fortnox-financial-years-enhanza1008×716 51.2 KB

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

fortnox-accounts-receivable-enhanza
fortnox-accounts-receivable-enhanza1220×424 52.6 KB

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

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
1 gillning
Fortnox rapporter i Excel
Fortnox dashboard i Google Data Studio