Use the powerful SQL language to query all your Fortnox data, available in Fortnox API.
You need an active subscription from Enhanza to access these resources.
Summary
We suggest this hierarchical structure while building your SQL queries.
-
Fortnox Resources (collect ingredients
)
This is your raw data available in Fortnox - field by filed. -
Staged resources (preparing ingredients
)
These queries structure and make more sense of the raw data resources. -
Reports (make the omelette
)
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
.
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
- 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
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