Retrieving financial periods from Visma Net

Questions
,
1

I am currently trying out the retrieval of data from Visma.net Financials through Invantive Cloud.

However, I noticed some key columns containing duplicates so I investigated. I have one example for you, though I it is possible that this applies for more endpoints.

The call to FinancialPeriod (/controller/api/v1/financialPeriod), when tested in Visma’s Swagger environment for the company, retrieves the periods just fine.

However, when calling upon the same data, for example via the SQL editor in Invantive Cloud, it does not retrieve period “2020/01” and retrieves “2020/05” twice. I have an inkling that it has something to do with pagination, though this subject would be too technical for me.

Does someone know what the cause of this can be?

image
image547×923 37.2 KB

image
image831×560 33.6 KB

2

What was the actual query used in the SQL Editor to get the results displayed in the second image?

3

image

use all

select *
from   VismaNet.FinancialPeriod.FinancialPeriods
where  Company_Code = ...
limit  1000
4

Hi, was wondering if there is any news about this issue?

5

The problem reproduces on

select company_code
,      year
,      count(*) cnt
,      count(distinct period) cnt
from   VismaNet.FinancialPeriod.FinancialPeriods
group
by     company_code
,      year
order
by     company_code
,      year

For the year 2030, there are 12 periods, but only 11 distinct values. The period 203001 is missing and 203005 occurs twice.

The problem seems to occur at the boundary between so-called pages (with 100 rows), which is an area in which Visma.net has changed multiple times over the last years.

A developer will analyze the issue further.

6

Analysis leads to the conclusion that the order of rows returned by Visma.NET is inconsistent, leading to paged data to be returned in an unexpected manner. For example, when fetching the financial periods, this request is being made (note the page size of 1):

https://integration.visma.net/API/controller/api/v1/financialPeriod?numberToRead=1&skipRecords=0

Response (on a test environment):

[
  {
    "year":2022,
    "period":"202202",
    "startDate":"2022-02-01T00:00:00",
    "endDate":"2022-02-28T00:00:00",
    "description":"februari",
    "active":true,
    "closedInSupplierLedger":false,
    "closedInCustomerLedger":false,
    "closedInInventoryManagement":false,
    "closedInGeneralLedger":false,
    "closedInCashManagement":false,
    "closedInFixedAssets":false
  }
]

When fetching the next page, we have to increase the value of skipRecords with 1. Resulting in the URL https://integration.visma.net/API/controller/api/v1/financialPeriod?numberToRead=1&skipRecords=1.

As you can see the result is identical:

[
  {
    "year":2022,
    "period":"202202",
    "startDate":"2022-02-01T00:00:00",
    "endDate":"2022-02-28T00:00:00",
    "description":"februari",
    "active":true,
    "closedInSupplierLedger":false,
    "closedInCustomerLedger":false,
    "closedInInventoryManagement":false,
    "closedInGeneralLedger":false,
    "closedInCashManagement":false,
    "closedInFixedAssets":false
  }
]

So, concluding, this is a bug in Visma.NET Financials/ERP.

Suggestion is to report this issue to Visma.NET Support as behavior deviating from the API-specifications. Please note that we have informally been informed that Visma.NET in the Netherlands might be paused for further development and use by accounting firms. Visma can inform you more accurately about the actual state of the product.

We will ask a question regarding this issue on the developer forums, but there is no guaranteed response time and/or solution time. In case we acquire additional information or an additional viable workaround, an answer will be added.

Please note that Visma.net has had various breaking changes over the last years with paging. It seems to be a sensitive logic with various exceptions. Ultimately, all these issues have been fixed by Visma.

7

According to Visma, pagination is not supported on the financial periods and marked as “deprecated”. In the future, their team might implement pagination.

The use of NumberToRead and SkipRecords seems to work partially, but unreliable. We will await a response on what alternatives are available. We have seen that providing a page size of 250 works with financial period and returns 250 rows instead of the 100 normal limit when 250 is tried.

How many financial periods do your companies have at most currently?

8

We are awaiting information from Visma regarding the actual status of the obsoleted marked parameters.

Can you indicate how many financial periods your companies have at most currently?

9

At most currently we are tracking back to 2013, so including 2023, it would be 132 periods.

10

Additional question. I am curious about this. What source do you have?

11

It was rumoured that some accounting firm received this message regarding Visma.net. I have checked it with Visma.net Partner Support and they state that further maintenance is still on-going.

12

There is yet no information how a workaround could be done in the core of Invantive SQL’s driver for Visma.net.

As a temporary workaround using the greaterThan-parameter and the implicit ordering per year as a workaround, add the following view to your On Startup SQL as described in https://forums.invantive.com/t/using-custom-database-views-for-sql-and-bi-tools/2116:

create or replace view FinancialPeriodsWorkaround
as
select fpd.*
--
-- 8 years per query: 12 months, 96 rows, circumvents paging bug.
--
from   ( select 8 yearperquery ) stg
--
-- Start year 2000, up to 10 years in future.
--
join   range(ceil((year(sysdateutc) + 10 - 2000) / stg.yearperquery), 0)@datadictionary rge
join   VismaNet.FinancialPeriod.FinancialPeriods(greaterThanValue => to_char(200000 + rge.value * stg.yearperquery * 100 - 1 /* greaterThan year, not period */)) fpd
on     fpd.year < 2000 + stg.yearperquery * ( rge.value + 1 )

This workaround uses typically 2…3 extra API-calls per company to retrieve the financial periods from Visma.net Financials.

The following test query shows 12 periods per year then, which is correct:

select fpd.company_code
,      fpd.year
,      count(*) cnt
,      count(distinct period) cnt
from   FinancialPeriodsWorkaround@DataDictionary fpd
group
by     fpd.company_code
,      fpd.year

Replace use of FinancialPeriods by FinancialPeriodsWorkaround as long as there is no bug fix nor workaround.

13

It has been confirmed by Visma that pagination can be disabled at this endpoint and all data will be returned. At some date in the future, pagination must be enabled again when pagination has been added.

A new release of Invantive Cloud has been taken into production which no longer applies pagination. The workaround given is no longer necessary.

14

Great! I can confirm the financial periods retrieved now are clean. Thanks for your help!

1 like
15

Just an update. Needs no action.

Removal of the deprecated parameters in Financial Period endpoint has been planned by Visma for Feb 28, 2023. The numberToRead and skipRecords parameters for the financial period endpoint are deprecated and will be removed.