Missing vouchers from 2025 in Vouchers on Fortnox

[she20]

We have vouchers in 2025.

They can be found (as shown in Missing Fortnox-table for VoucherRows of a voucher) using the query:

select vse.code
,      fyr.FromDate
,      vle.VoucherNumber
,      vcr.Voucher_ReferenceNumber
,      vcr.Voucher_ReferenceType
,      vcr.Voucher_TransactionDate
,      vle.Account
,      vle.Credit
,      vle.Debit
,      vle.Description
from   VoucherSeries vse
join   Range@DataDictionary(10, 1) rge
join   FinancialYearsByDate fyr
on     fyr.FromDate = trunc(sysdateutc, -2)
join   VouchersByVoucherSeriesAndVoucherNumber(vse.code, rge.value, fyr.id) vcr
join   VoucherLinesByVoucherSeriesAndVoucherNumber(vse.code, rge.value, fyr.id) vle
where  vse.code like 'B%'

However, this generates an enormous amount of API calls. An alternative would seem to use:

select *
from   Vouchers
where  year(transactiondate) != 2024

But there are absolutely no rows in Vouchers not within the previous year.

How can I retrieve the financial vouchers of 2025 in a fast way?

[she20]

The same holds for instance for:

select *
from   VouchersByVoucherSeries('B')
where  year(TransactionDate) != 2024

Also returns no rows.

As an alternative, I have tried to retrieve them using a manually crafted URL over the secured connection:

insert into NativePlatformScalarRequests@fnx
( url
) 
values 
( 'https://api.fortnox.se/3/vouchers?page=1&limit=500&financialyeardate=2025-01-01'
)

Note that financialyeardate is added. This one is NOT in the Swagger/OpenAPI specification, but is listed on Parameters

The result now contains vouchers from 2025:

{"MetaInformation":{"@TotalResources":69,"@TotalPages":1,"@CurrentPage":1},"Vouchers":[{"@url":"https:\/\/api.fortnox.se\/3\/vouchers\/A\/1?financialyear=16","Comments":null,"Description":"Periodisering av leverant

as visible through select * from NativePlatformScalarRequests@fnx.

Is it possible to extend the data model to include these global parameters?

[she20]

The default behaviour of APIs seems to be that the last complete financial year or something alike is used.

[lsg23]

I had the same problem but I found a workaround by creating a view in the Startup SQL:

create or replace force view CurrentVouchers
as
select vce.TransactionDate
,	   vce.Description
,	   vle.Account
,	   vle.Credit
,      vle.Debit
,      vle.Description		
from   Vouchers
       (12
       ) vce
join   VoucherLinesByVoucherSeriesAndVoucherNumber
       ( vce.VoucherSeries
       , vce.VoucherNumber
       , 12
       ) vle

Drawback is obviously that the fiscal year is hard coded (to 12 in my case). Also, I am new to Invantive Cloud since two days and I did not find any means to store 12 in a variable.

[sda]

Same result/issue here

select *
from   Vouchers V

gives me results for 2025, current year only.

The number of records in the result matches the number of vouchers in the Accounting/Vouchers in the Fortnox webUI, for the current accounting year, 2025.

select * 
from   Vouchers V
where  V.Year = 26

gives an empty result whereas we have 2597 vouchers in the Fortnox webUI in 2024.

[lsg23]

My understanding is that you have to give the fiscal year as a parameter in the query. Have you tried:

select *
from   Vouchers(26)

[sda]

Ah yes, it works.

That means Invantive language is not passing the parameter

select * 
from   Vouchers
where  Year = 26

is not the same as

select *
from   Vouchers(26)

May be this is something Invantive is willing to improve since Fortnox API only gives current fiscal year values when year parameter is not specified ?

[forums]

It is planned to add functionality as described in Parameters in addition to the current implementation which is solely based on Swagger/OpenAPI specification.

There is currently no planned date; focus is currently on disentangling US-based components and Invantive UniversalSQL Server sneak preview

[forums]

In release 24.0.633, available on Invantive Cloud, extensions have been made that allow multiple years to be retrieved in one query.

Starting release 24.0.634, the views Vouchers and VoucherLines will reflect these:

Fortnox Vouchers across all Years

select fyr.Id YearId
,      fyr.FromDate YearStart
,      fyr.ToDate YearEnd
,      fyr.AccountingMethod YearAccountingMethod
--
,      vcr.VoucherSeries VoucherSeries
,      vcr.VoucherNumber VoucherNumber
,      vcr.TransactionDate VoucherTransactionDate
,      vcr.ReferenceNumber VoucherReferenceNumber
,      vcr.Description VoucherDescription
,      vcr.ApprovalState VoucherApprovalState
from   Fortnox.FinancialYears.FinancialYearsByDate fyr
join   Fortnox.Vouchers.Vouchers(financialyear=> fyr.id) vcr

Fortnox Voucher Lines across all Years

select fyr.Id YearId
,      fyr.FromDate YearStart
,      fyr.ToDate YearEnd
,      fyr.AccountingMethod YearAccountingMethod
--
,      vcr.VoucherSeries VoucherSeries
,      vcr.VoucherNumber VoucherNumber
,      vcr.TransactionDate VoucherTransactionDate
,      vcr.ReferenceNumber VoucherReferenceNumber
,      vcr.Description VoucherDescription
,      vcr.ApprovalState VoucherApprovalState
--
,      vle.Account
,      vle.CostCenter
,      vle.Credit
,      vle.Debit
,      vle.Description
,      vle.Project
,      vle.Quantity
,      vle.Removed
,      vle.TransactionInformation
from   Fortnox.FinancialYears.FinancialYearsByDate fyr
join   Fortnox.Vouchers.Vouchers(financialyear=> fyr.id) vcr
join   Fortnox.Vouchers.VoucherLinesByVoucherSeriesAndVoucherNumber
       ( vcr.VoucherSeries
       , vcr.VoucherNumber
       , vcr.Year
       ) vle

Performance is not especially good given the amount of API calls to execute. Filters on for instance ToDate help a lot since the Invantive Optimizer can project those into the execution plan.