Use the powerful SQL language to query all your Fortnox data, available in Fortnox API.
You need an active subscription from enhanza.com/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